基础应用

• 数据库工具
• SQL转换器 使用

db.test.aggregate([
{$match:{"name": 'xiaoming'}}
])

导入文件

mongoimport.exe /uri:mongodb://user:passwd@ip:27017/?authSource=admin /db:admin /collection:pjc_px /file:d://pjc_px.csv

查询

查询数据

list(db.find())[0]['GROUP_NO']

模糊查询

db=mongo['admin']['pjc_px']
result=db.find({'LOCAL_ITEM_DESC':re.compile('碎番茄')}).limit(10)
for s in result:
    print(s)

多表查询-lookup

创建订单
db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
])
创建库存集合
db.inventory.insert([
   { "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
   { "_id" : 5, "sku": null, description: "Incomplete" },
   { "_id" : 6 }
])
查询
db.inventory.aggregate([
  {
    "$lookup": {
         from: "orders",
         localField: "sku",
         foreignField: "item",
         as: "order_docs"
       }
  },
//指定字段查询
  {
    "$project": {
		"order_docs1":"$order_docs.price"
    }
  }
])

返回结果

{
    "_id" : NumberInt("1"),
    "item" : "almonds",
    "price" : NumberInt("12"),
    "quantity" : NumberInt("2"),
    "inventory_docs" : [
        {
            "_id" : NumberInt("1"),
            "sku" : "almonds",
            "description" : "product 1",
            "instock" : NumberInt("120")
        }
    ]
}


{
    "_id" : NumberInt("2"),
    "item" : "pecans",
    "price" : NumberInt("20"),
    "quantity" : NumberInt("1"),
    "inventory_docs" : [
        {
            "_id" : NumberInt("4"),
            "sku" : "pecans",
            "description" : "product 4",
            "instock" : NumberInt("70")
        }
    ]
}


{
    "_id" : NumberInt("3"),
    "inventory_docs" : [
        {
            "_id" : NumberInt("5"),
            "sku" : null,
            "description" : "Incomplete"
        },
        {
            "_id" : NumberInt("6")
        }
    ]
}

插入

pandas读取导入

采用pandas读取文件可处理由编码导致乱码问题

db=mongo["admin"]["pjc_px"]
df =pd.read_csv(open("D:/pjc_px.csv"),nrows=10000,encoding="utf-8")
print(df)
mongo["admin"]["pjc_px"].drop()
result=db.insert_many(df.to_dict(orient="record"))

索引

创建索引

db.create_index(keys="ITEM,LOC")

GridFS

应用

import pymongo
from gridfs import GridFS
mongo = pymongo.MongoClient('mongodb://root:root@10.0.68.231:27017/?authSource=admin') # 组装成url进行连接
# db=mongo['log']['info']
fs=GridFS(mongo['log'],collection="files")
with open("d:\\tnsnames.ora",'rb') as file:
    fs.put(file.read(), filename=file.name)

导入数据

mongoimport.exe --host 127.0.0.1 --port 27017 -u root -p root -d test --authenticationDatabase admin -c pjc_px --type=csv --file d:/pjc_px.csv --headerline

释放磁盘空间

db.runCommand({compact:'pjc_px'})

聚合函数

分组求合

包含二级数组dist_list:['a':{}]

[  {
  "$group" : {
    "_id" : {
      "__mongo_group_all_data" : "$service_name"
    },
    "sum_dist_list_totle" : {
      "$sum" : {'$sum':'$dist_list.totle'}
    }
  }
}, {
  "$project" : {
    "_id" : 0,
    "__mongo_group_all_data" : "$_id.__mongo_group_all_data",
    "sum_dist_list_totle" : 1
  }
} ]