python3 操作mongodb

库安装  pip3 install pymongo==3.12.2

from pymongo import MongoClient
import pymongo 

# 连接 MongoDB（无账号密码）
client = MongoClient(host='localhost',port=27017)
# 连接 MongoDB（有账号密码 admim/admin）
#client = MongoClient(host='mongodb://admin:admin@localhost/',port=27018)
    
# 选择数据库
db = client['xxx']

# 选择集合
# 方式二
collection = db['name']

                                                                # 插入
# # ----------------插入数据
# dic={'name':'小明','English':100,'math':90}
# rep = collection.insert_one(dic)
# print(rep.inserted_id)     #返回字符

# # ----------------插入多个数据
# dic=[{'name':'小明','English':100,'math':90},{'name':'小明2','English':100,'math':90},{'name':'小明3','English':100,'math':90}]
# rep = collection.insert_many(dic)
# print(rep.inserted_ids)    #返回数组


# # ----指定id插入--id不可有重复
# dic={"_id":"6349105f58251771d6d7381a",'name':'小明','English':100,'math':90}
# rep = collection.insert_one(dic)
# print(rep.inserted_id)     #返回字符

# 																查询

# # 查询指定字段展示 1的字段为展示,0为不展示
# for x in collection.find({},{ "_id": 1, "math": 1 }):
#   print(x)


# 字段筛选加查询条件
# dic = { "name": "小明唯一","name": { "$gt": "1" }}    #相同字段查询需要用$and 见文章最下面注意点!!!!
# for x in collection.find(dic,{ "_id": 1, "math": 1 }):
#   print(x)


# # 查询一条数据
# x = collection.find_one()
# print(x)

# # 查询多条数据
# for x in collection.find():
#   print(x)

# #查询多条输出list   --------------查询多条数据直接输出list
# print(list(collection.find()))

# # 根据指定条件查询   select * from table where name="小明唯一"
# dic = { "name": "小明唯一" }
# mydoc = collection.find(dic)
# for x in mydoc:
#   print(x)

# # 大于查询
# dic =  { "name": { "$gt": "H" } }
# mydoc = collection.find(dic)
# for x in mydoc:
#   print(x)


# # 正则表达式查询和like   查询name字段第一个字母是小开头的  
# dic =  { "name": { "$regex": "^小" } }
查询name包含有小字的 where name like "%小%"
# dic =  { "name": { "$regex": "小" } }
# mydoc = collection.find(dic)
# for x in mydoc:
#   print(x)

# 指定条数返回
# mydoc = collection.find().limit(3)
# for x in mydoc:
#   print(x)

# # 查询返回的条数
# count = collection.find().count()
# print(count)

# # 正序 ASCENDING，倒序 DESCENDING。list()将游标对象转成列表--排序  order by name
# dic =  { "name": { "$gt": "H" } }
# data = list(collection.find(dic).sort('name',pymongo.DESCENDING))
# print(data)


# --------------------------------------------------------------聚合查询
#聚合查询先用list()转成数组再用len查看有多少条
# # 条件查询  where name="小明"
# match_dict = {"$match":{"name":"小明"}}
# for i in collection.aggregate([match_dict]):
# 	print(i)


# # 查询条件  where name=小明唯一
# # match_dict = {"$match": {"name": "小明唯一"}}
# match_dict = {"$match": {}}
# # _id为要分组的字段是那个
# #$sum指定1为出现的次数    select name,count(1) where table group by name 
# group_dict = {"$group":{"_id":"$name","Count":{"$sum":1}}}
# #$sum指定字段math为累加  select name,sum(1) where table group by name 
# group_dict = {"$group":{"_id":"$name","Count":{"$sum":"$math"}}}
## $max指定字段math获取最大的 select name,max(math) where table group by name 
# group_dict = {"$group":{"_id":"$name","Count":{"$max":"$math"}}}

# # 给分组出来的数据追加指定字段展示
# group_dict = {"$group":{"_id":"$name","Count":{"$sum":"$math"},"msg":{"$push":"$$ROOT"}}}
{"$push":"$$ROOT"}这个push是追加字段 $$ROOT是追加所有字段,推指定的字段可以  这么写group_dict={"$group": {"_id": "$id","name":{"$push":"$name"}}}
 group_dict = {"$group": {"_id": "$id", "storyname": {"$push": "$name"}, "workspace_id": {"$push": "$workspace_id"}}}
{'_id': '小明2', 'Count': 270, 'msg': [{'_id': ObjectId('636a0b238029e69e992defa3'), 'name': '小明2', 'English': 100, 'math': 90}, {'_id': ObjectId('636a0b5bee95dbdba8f55b8d'), 'name': '小明2', 'English': 100, 'math': 90}, {'_id': ObjectId('636a0ba4ff7af5c881a8c132'), 'name': '小明2', 'English': 100, 'math': 90}]}
#where version="线上版本" and created like '%20022%'
#match_dict = {"$match": {"$and": [{"version_report": {"$eq":"线上版本"}},obj2,{"created":{"$regex":"2022"}}]}}
   
# #多字段分组
# group_dict = {"$group":{"_id":{"name":"$name","math":"$math"}}}
# for i in collection.aggregate([match_dict,group_dict]):
# 	print(i)
输出:
{'_id': {'name': '小明2', 'math': 90}}
{'_id': {'name': '小明', 'math': 90}}
{'_id': {'name': '小明1', 'math': 90}}
{'_id': {'name': '小明', 'math': 80}}


select * from table where created>=begintime and created<=endtime and creator in [MMM,MMM] group by creator
# 查询语句拼接  # membersDetails ->list[nickname,nickname2]
    obj = {"creator": {"$in": membersDetails}}
    match_dict = {"$match": {"$and": [{"created": {"$gte": begintime}}, {"created": {"$lte": endtime}}, obj]}}
# 根据创建人分组统计
    group_dict = {"$group": {"_id": "$creator"}}
for i in collection.aggregate([match_dict, group_dict]):
print(i)
 # --------------------------------------------------------------- 更新

#  # 更新一条
# condition = {'name':"小明唯一"}  #查询条件
# dic = {'math':60}  #更新数据
# rep = collection.update_one(condition,{'$set':dic})
# print(rep.raw_result)

# # 更新多条
# condition = {'name':"小明唯一"}  #查询条件
# dic = {'math':60}  #更新数据
# rep = collection.update_many(condition,{'$set':dic})
# print(rep.raw_result)


# ----------------------------------------------------------------删除
# dic = {'math':902}  #删除的条件
# rep=collection.delete_many(dic)
# print(rep.raw_result)


# 聚合函数: https://www.cnblogs.com/meloncodezhang/p/12385372.html
# 基础查询: https://www.cnblogs.com/shenh/p/14416111.html

比较运算符

http://www.manongjc.com/detail/51-xomoieuduhpbjfk.html

#select id from table where  status !="resolved"
collection.find({"status": {"$ne": "resolved"}},{"id": 1})

#select id from table where  status !="done" and status !="rejected"
collection.find({"status": {"$ne": "done"},"status": {"$ne": "rejected"}}, {"id": 1})    //1表示展示 0表示不展示 

#select * from table where  age in (25,27)
collection.find( { age: { $in: [ 25, 27 ] } } )  //多值匹配

字段不为空
{"storyName": {"$ne":None}

---------------------------------count()

#select count(1) from table where created >=begintime and created <= endtime
begintime=kwargs.get("begintime","2022-11-03 00:00:00")
endtime=kwargs.get("endtime", "9999-11-11 00:00:00")
dic =  { "$and":[{"created": { "$gte":begintime}},{"created": { "$lte":endtime}}]}

collection.count_documents(dic)
#效果和上面一样但是建议用上面的
collection.find(dic).count()

查询后的非空判断------count()

collection.find({'name':'小明'},{ "_id": 1, "math": 1 }).count()

查询同一个字段多个条件要使用 $and （踩过坑！！ 没用and）

match_dict ={"$and":[{"activity_time":{"$gte": beginTime+":00"}},{"activity_time":{"$lte": endTime+":00"}},
                                     {"task_id": patrol_task_id},{"req_id":req["id"]}]}

设置过期时间 TTL索引 (time to live)

https://blog.csdn.net/weixin_43464076/article/details/120521657

https://blog.csdn.net/qq_34518898/article/details/81459543

# 连接 MongoDB（有账号密码 admim/admin）
#client = MongoClient(host='mongodb://admin:admin@localhost/',port=27018)
    
# 选择数据库
db = client['xxx']

# 选择集合
# 方式二
collection = db['name']

#添加过期时间一星期(s)
collection.create_index([("extimer",1)],expireAfterSeconds=302400)
dic={'name':'小明','English':100,'math':90,'extimer':datetime.utcnow()}   #注意!!要添加时要加上索引的字段且放入utc时间,不然过期效果会不生效
rep = collection.insert_one(dic)