1、查询
mongoengine定义的一个模型:
class WeiXinUser(Document):
openid = StringField(required=True, primary_key=True)
uid = IntField()
unionid = StringField(required=True, unique=True)
_nickname = StringField(required=True, db_field="nickname")
_sex = IntField(choices=(1, 2), db_field="sex") # 1男2女
_province = StringField(required=True, db_field="province")
_city = StringField(required=True, db_field="city")
_country = StringField(required=True, db_field="country")
_headimgurl = StringField(db_field="headimgurl")
_privilege = StringField(db_field="privilege")
# 定义为索引
meta = {
'indexes': ["uid"]
}
(1)查询操作:
1.查询所有的数据
1.SQLAlchemy
rs = WeiXinUser.query.all()
for i in rs:
print(i.__dict__)
2.MongoDB
db.wei_xin_user.find()
3.mongoengine
wx_obj = WeiXinUser.objects [objects,objects,....] # 取得这个对象(所有数据)
wx_data = wx_obj.to_json() # 显示其中的数据
2.返回指定的列
# MySQL:SELECT unionid from wei_xin_user;
1.SQLAlchemy
db.session.query(WeiXinUser.uid, WeiXinUser.unionid).all()
2.MongoDB
# 例如查询unionid这一列数据(_id为主键,默认是显示的,如果不想显示,则"_id":0)
db.wei_xin_user.find({},{"unionid":1,"_id":0})
3.mongoengine
rs = WeiXinUser.objects.aggregate({"$project": {"unionid": 1,"_id":0}})
print(rs._CommandCursor__data)
3.添加where条件
# MySQL:SELECT * from wei_xin_user where unionid="23"
1.SQLAlchemy
# 如果查不到,返回None
rs = WeiXinUser.query.filter_by(unionid="23").first()
2.MongoDB
db.wei_xin_user.find({"unionid":"23"})
3.mongoengine
rs = WeiXinUser.objects(unionid="23")
print(rs.to_json())
# MySQL:SELECT unionid from wei_xin_user where unionid="23";
1.SQLAlchemy
q = {"uid":1,"unionid":"23"}
rs = WeiXinUser.query.filter_by(**q).count()
2.MongoDB
db.wei_xin_user.find({"unionid":"23"},{"unionid":1})
3.mongoengine
rs = WeiXinUser.objects(unionid="23").aggregate({"$project": {"unionid": 1,"_id":0}})
print(rs._CommandCursor__data)
4.添加and条件
# MySQL:SELECT * FROM wei_xin_user WHERE unionid="23" and sex=1;
db.wei_xin_user.find({"unionid":"23","sex":1})
q = {"unionid": "23", "_sex": 1}
rs = WeiXinUser.objects(**q)
print(rs.to_json())
-- 当然也可以使用Q
-- rs = WeiXinUser.objects(Q(unionid="23") & Q(_sex=1)).aggregate({"$project":{"unionid":1,"_id":0}})
-- print(rs._CommandCursor__data)
5.添加or条件
# MySQL:SELECT unionid FROM wei_xin_user WHERE unionid="23" or sex=2;
-- db.wei_xin_user.find({"$or":[{"unionid":"23"},{"sex":2}]})
1.SQLAlchemy
from sqlalchemy import _or
User.query.filter(or_(User._name == "zh2", User._name =="zh4")).all()
2.MongoDB
db.wei_xin_user.find({"$or":[{"unionid":"23"},{"sex":2}]},{"unionid":1})
3.mongoengine
rs = WeiXinUser.objects(Q(unionid="23") | Q(_sex=2)).aggregate({"$project":{"unionid":1,"_id":0}})
print(rs._CommandCursor__data)
6.添加比较 ( lt, lte, gt, gte )
1.SQLAlchemy
rs = WeiXinUser.query.filter(WeiXinUser.uid >=2,WeiXinUser.uid<=3).count()
2.MongoDB
db.wei_xin_user.find({"sex":{"$gte":1,"$lte":2}})
3.mongoengine
q = {"_sex__gte": 1, "_sex__lte": 2}
rs = WeiXinUser.objects(**q)
print(rs.to_json())
7.添加in,not in( in, nin)
1.SQLAlchemy
rs = WeiXinUser.query.filter(WeiXinUser.uid.in_((1, 2))).all()
2.MongoDB
db.wei_xin_user.find({"sex":{"$in":[1,2]}})
3.mongoengine
q = {"_sex__in": [1, 2]}
rs = WeiXinUser.objects(**q)
print(rs.to_json())
(2)更新操作
1.update操作
1.SQLAlchemy
rs = WeiXinUser.query.filter_by(uid=3).first()
rs.uid = 1
rs.unionid = 23
db.session.add(rs)
2.MongoDB
db.wei_xin_user.update({"uid":1},{"$set":{"uid":3}})
3.mongoengine
wx_obj = WeiXinUser.objects(uid=1).first()
wx_obj.uid = 3
wx_obj.save()
关于数组方面的目前不涉及。
(3)删除
1.SQLAlchemy
# 如果不存在,不报错
rs = WeiXinUser.query.filter_by(uid=1)
rs.delete()
2.MongoDB
db.wei_xin_user.remove({uid:1});
3.mongoengine
rs = WeiXinUser.objects(uid=1).first()
if rs is not None:
rs.delete()
(4)聚合查询
#MySQL: select school.name,count(*) as count from user,school where school.id=user.sid group by school.name;
db.comp_user.aggregate([{$group:{_id:"$sid",count:{$sum:1}}},{$lookup:{ "from":"school", "localField":"_id","foreignField":"_id","as":"school"}},{$project:{school:"$school.school",count:1,}}])
CompUser.objects().aggregate({"$group": {"_id": "$sid","count": {"$sum": 1}}},
{"$lookup": {"from": "school",
"localField": "_id",
"foreignField": "_id",
"as": "school"}},
{"$project":{"school":"$school.school","count": 1})
# 只不过MySQL是先进行连接查询然后进行聚合查询,而下面mongodb那是先进行聚合查询然后进行表连接。