Sqlalchemy常用方法的使用

查询语句

print(session.query(User).get({'id': 1}))                               # 需根据主键来返回查询结果
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_by(user_name='test2').limit(2).all())  # 限制最多返回2条记录，默认省去的0
print(session.query(User).filter_by(user_name='test2').limit(page_size).offset((page_number - 1) * page_size).all())  # 查询page_number页的数据，每页page_size条数据
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())      # 如果有记录，返回第一条记录的第一个元素
print(session.query(User.Name,User.Id.label("UserId")).filter(User.id == 1).one()) # 指定字段查询，返回结果不是model对象，label可以重命名返回列名

指定字段查询

1、session.query(User.Name,User.Id).all()
2、session.query(User).with_entities(User.Name,User.Id).all()
3、session.query(User).options(load_only(User.Name,User.Id)).all()

前2个方法返回的是列表里面套元组,直接就是数据(例:[(‘张三’,12345),(‘李四’,3456),(‘王五’,67893)])
第三个方法返回model列表，列表里面是一个个对象[对象1,对象2,对象3]，且需要导入一下from sqlalchemy.orm import load_only
前两个方法获取到的数据转对象代码示例：

def __asdict(collection_result_obj_):
    result = {}
    utc_offset_= 8
    for key, value in zip(collection_result_obj_.keys(), collection_result_obj_):
        if isinstance(value, datetime):
            result[key] = value + timedelta(minutes=utc_offset_)
        elif isinstance(value, Decimal) and value == 0:
            result[key] = Decimal(0)
        else:
            result[key] = value
    return result

def __asdict_to_list(self, collection_result_objs_):
    # 多条结果时转为list(js-on)
    v = map(self.__asdict, collection_result_objs_)
    return v

排序
每次在查询中定义排序太费时，太麻烦了。可以在查询时自动排序。
我们在创建模型时，通过定义一个类属性 mapper_args，将其order_by设置为 create_time.desc() 即可。

class User(Base):　　
　　__tablename__ = "user"　　
　　id = Column(Integer , primary_key=True , autoincrement=True)　　
　　name = Column(String(50) , nullable=False)　　
　　create_time = Column(DateTime , nullable=False , default=datetime.now)
　　
　　__mapper_args__ = {　　
　　"order_by":create_time.desc()　　
　　}　　

all_users = session.query(User).order_by(User.create_time.desc()).all() # 根据create_time降序排序
也可以：
all_users = session.query(User).order_by("create_time").all()　  
all_users = session.query(User).order_by(User.create_time).all()      # 根据create_time排序
all_users = session.query(User).order_by(User.create_time, User.user_name.desc()).all() # create_time升序，user_name降序排序

注意：排序与分页是先排序，再分页

session.query(User).order_by(User.create_time).limit(page_size).offset((page_number - 1) * page_size).all()

连接

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，否则返回这个结果。

如何批量插入大批数据？

此种方式速度很快，ORM则方式则需要花费很长时间

session.execute(
    User.__table__.insert(),
    [{'name': `str(i)`,'age': randint(1, 100)} for i in range(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 参数会更快。

synchronize_session参数,可选False、‘fetch’、‘evaluate’；

• 选填False, 意味着不同步session,
  即不论对应session的identity_map中是否存在这次更新或删除的数据，identity_map中的数据都不改变。优点：性能好，缺点：如果直接从identity_map中获取数据，会发现还是旧数据。
• 选填’fetch’, 意味着同步session, identity_map中对应的数据会自动更新或删除。
  优点：identity_map中始终都是最新数据,
  缺点：性能差(因为可能会触发查询操作，用以更新identity_map中数据，使用数据库mysql时就是这种情况)
• 选填’evaluate’（默认值), 意味着同步session,
  identity_map中对应的数据会自动更新或删除。优点：identity_map中始终都是最新数据,
  缺点：不稳定，查询条件复杂时会报错，因为其用python对where条件进行估值，不一定总是可行。