mongoengine|mongodb|flask_sqlalchemy简单使用对比

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那是先进行聚合查询然后进行表连接。