Sqlalchemy 使用总结

1、常用语句总结

# 查询语句

print(session.query(User).get({'id': 1}))                               # 根据id获取一条数据
print(session.query(User).filter(User.id == 1).one())                   # 查询符合条件的任何一条记录
print(session.query(User).filter(User.id == 1).first())                 # 查询返回的第一条记录
print(session.query(User).filter_by(id=1).all())                        # 查询返回符合条件的左右记录
print(session.query(User).filter(User.id == 1).first().user_name)       # 如果未查询到数据，则会抛出异常
print(session.query(User).filter_by(user_name='test2').limit(2).all())  # 限制最多返回2条记录
print(session.query(User).filter_by(user_name='test2').offset(1).all()) # 从查询到的第二条记录开始返回
print(session.query(User).filter(or_(User.id == 1, User.id == 2)).all()) # or_多条件查询
print(session.query(User).filter(User.id.in_((1, 2))).all())             # in多条件查询
print(session.query(User).filter(not_(User.id == 1)).all())             # not_非条件过滤
print(session.query(func.count('*')).select_from(User).scalar())        # 使用查询传递函数统计
print(session.query(func.count(User.id)).scalar())                      # 使用查询传递函数统计
print(session.query(User).filter(User.user_name.like('%xx%')))          # 模糊匹配查询
print(session.query(User).filter(User.id == 1).scalar().user_name)      # 如果有记录，返回第一条记录的第一个元素

# 排序
all_users = session.query(User).order_by('user_name').all()             # 根据user_name排序
all_users = session.query(User).order_by(User.user_password).all()      # 根据user_password排序
all_users = session.query(User).order_by(User.user_password.desc()).all()  # 根据user_password降序排序
all_users = session.query(User).order_by(User.user_name, User.user_password).all() # user_name升序，user_password降序排序

# 连接
print(session.query(User).join(Admin, User.id == Admin.id).filter().all())  # 内连接，如果没有外键的话，也可以手动指定关联外键
print(session.query(User).outerjoin(Admin).filter().all())                  # 左外连接，此处Admin必须有字段为User的外键

# 插入sql语句进行查询
session.query(User).filter(text("id<:value and name=:name")).\
...     params(value=224, name='fred').order_by(User.id).one()

查询对象有这么几种方法.one()，.all()，.scalar()，.one_or_none()，.get()，以及.first()等。
下面对这几个方法的用法及效果做简单解释。

• .all()

  返回查询到的所有的结果。
  这个方法比较危险的地方是，如果数据量大且没有使用limit子句限制的话，所有的结果都会加载到内存中。
  它返回的是一个列表，如果查询不到任何结果，返回的是空列表。

• .first()

  返回查询到的第一个结果，如果没有查询到结果，返回None。

• .scalar()

  这个方法与.one_or_none()的效果一样。
  如果查询到很多结果，抛出sqlalchemy.orm.exc.MultipleResultsFound异常。
  如果只有一个结果，返回它，没有结果返回None。

• .one()

  如果只能查询到一个结果，返回它，否则抛出异常。
  没有结果时抛sqlalchemy.orm.exc.NoResultFound，有超过一个结果时抛sqlalchemy.orm.exc.MultipleResultsFound。

• .one_or_none()

  比起.one()来，区别只是查询不到任何结果时不再抛出异常而是返回None。

• .get()

  这是个比较特殊的方法。它用于根据主键来返回查询结果，因此它有个参数就是要查询的对象的主键。
  如果没有该主键的结果返回None，否则返回这个结果。

2、高姿势操作

•  如何批量插入大批数据？

# 此种方式速度很快，ORM则方式则需要花费很长时间
session.execute(
    User.__table__.insert(),
    [{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]
)
session.commit()

• 如何替换一个已有主键的记录？

# 此种方式比update更节省代码，实质也是先执行select再执行的update
user = User(id=1, name='ooxx')
session.merge(user)
session.commit()

• 如何使用无符号整数？

from sqlalchemy.dialects.mysql import INTEGER

id = Column(INTEGER(unsigned=True), primary_key=True)

• 为什么无法删除 in 操作查询出来的记录？

# 此条语句执行会报错
session.query(User).filter(User.id.in_((1, 2, 3))).delete()

     抛出这样的异常

sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python.  Specify 'fetch' or False for the synchronize_session parameter.

     这样执行是正确的

session.query(User).filter(or_(User.id == 1, User.id == 2, User.id == 3)).delete()
# 或者下面的方式
session.query(User).filter(User.id.in_((1, 2,3))).delete(synchronize_session=False)
session.commit() # or session.expire_all()

    此外，update 操作也有同样的参数，如果后面立刻提交了，那么加上 synchronize_session=False 参数会更快。