a2.sqlalchemy-操作

一、sqlalchemy中的查询分析

1、基本查询

filter和filter_by的区别：

• filter可以在后边加判断条件，但是每次只能过滤一个条件
• filter_by可以过滤多个，与Django的orm的方式类似，但是只能用“=”

如何查看sqlalchemey的sql语句

在不添加结尾的all()``first()等，得到的具体查询对象，直接利用str方法，便可将其转换为sql语句

row = session.query(User).filter(User.username!='aaa')
print(row)

结果

SELECT user.id AS user_id, user.username AS user_username, user.password AS user_password, user.createtime AS user_createtime, user._locked AS user__locked 
FROM user 
WHERE user.username != %(username_1)s

表中单个字段查询

print(session.query(User.username).filter(User.username!='dandan').all())

first()

仅查询显示第一个

print(session.query(User.username).filter(User.username!='dandan').first())

one()

只查询出来第一个，有且只有一个
如果有两个符合条件的，会报错

print(session.query(User).filter(User.username=='choupi').one())

结果

<User(id='12' ,username='choupi',password='q1',createtime='2018-03-09 20:27:54',_locked ='False',)>

get 根据主键查询

主键在表中只有一个
例如ID为主键，查询id=3的元素

print(session.query(User).get(3))
# 结果
<User(id='3' ,username='tree',password='zzz111',createtime='2018-03-09 14:44:21',_locked ='False',)>

limit 限制查询结果

limit(3) 仅查出3条结果

print(session.query(User).filter(User.username!='dandan').limit(3).all())
# 结果
[<User(id='2' ,username='tobee',password='234qwe',createtime='2018-03-07 16:16:05',_locked ='False',)>, <User(id='3' ,username='tree',password='zzz111',createtime='2018-03-09 14:44:21',_locked ='False',)>, <User(id='4' ,username='aaa',password='111',createtime='2018-03-09 17:57:15',_locked ='False',)>]

offset() 限制前面n个，显示后面n+1个,向后便宜Nge

显示第N个以后

print(session.query(User.username).filter(User.username!='dandan').all())
print(session.query(User.username).filter(User.username!='dandan').limit(3).all())

print(session.query(User.username).filter(User.username!='dandan').offset(3).all())

结果：

[('tobee',), ('tree',), ('aaa',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]
[('tobee',), ('tree',), ('aaa',)]
[('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]

slice() 切片,也可以直接使用[1,9]

slice(1,3) 与python的slice一致，从0开始 左闭右开，显示1,2两个元素

print(session.query(User.username).filter(User.username!='dandan').slice(1,3).all())
# 结果
[('tree',), ('aaa',)]

order_by() 元素排序 顺序

print(session.query(User.username).filter(User.username!='dandan').order_by(User.username).all())

# 按数字字符顺序排序

[('111',), ('aaa',), ('choupi',), ('choupidan',), ('coding',), ('tobee',), ('tree',), ('youku',)]

desc() 逆序排序

from sqlalchemy import desc
print(session.query(User.username).filter(User.username!='dandan').order_by(desc(User.username)).all())

# 逆序排序

[('youku',), ('tree',), ('tobee',), ('coding',), ('choupidan',), ('choupi',), ('aaa',), ('111',)]

like() 模糊搜索，结合占位符%使用 与原生sql一致

print(session.query(User.username).filter(User.username.like('%e')).all())
# 结果
[('tobee',), ('tree',)]

ilike() 模糊搜索，不区分大小写

# 内部实现如下类似操作：
lower(a) LIKE lower(other)
# 同理还有 notilike() 方法，
# 跟like()用法一致，只是不区分大小写。
# 这个是sqlalchemy ORM 层做的强化，不是数据库层的用法

notlike()

print(session.query(User.username).filter(User.username.notlike('%e')).all())
# 结果
[('dandan',), ('aaa',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]

in_() 判断在xx里边

print(session.query(User.username).filter(User.username.in_(['dandan','aaa'])).all())
# 结果
[('dandan',), ('aaa',)]

notin_

print(session.query(User.username).filter(User.username.notin_(['dandan','aaa'])).all())
# 结果
[('tobee',), ('tree',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]

is_ 是xxx

两种表达方式 None

print(session.query(User.username).filter(User.username==None).all())
print(session.query(User.username).filter(User.username.is_(None)).all())

isnot

filter支持多条件查询

print(session.query(User.username).filter(User.username.isnot(None),User.password=='111').all())
# 结果
[('aaa',), ('111',)]

or_

from sqlalchemy import or_
print(session.query(User.username).filter(or_(User.username.isnot(None),User.password=='111')).all())

# 结果
[('dandan',), ('tobee',), ('tree',), ('aaa',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]

2、聚合函数(在sqlalchemy.func中)

count / group_by

from sqlalchemy import func
print(session.query(User.password,func.count(User.id)).group_by(User.password).all())

查询原生sql

SELECT user.password AS user_password, count(user.id) AS count_1 
FROM user GROUP BY user.password

[('111', 2), ('123asd', 1), ('234qwe', 1), ('333', 1), ('choupidan', 1), ('q1', 1), ('qwer', 1), ('zzz111', 1)]

having

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。
而having子句在聚合后对组记录进行筛选。真实表中没有此数据，这些数据是通过一些函数生存。

print(session.query(User.password,func.count(User.id)).group_by(User.password).\
      having(func.count(User.id)>1).all())
# 结果
[('111', 2)]

sum

print(session.query(User.password,func.sum(User.id)).group_by(User.password).all())
# 结果
[(‘111’, Decimal(‘18’)), (‘123asd’, Decimal(‘1’)), (‘234qwe’, Decimal(‘2’)), (‘333’, Decimal(‘16’)), (‘choupidan’, Decimal(‘15’)), (‘q1’, Decimal(‘12’)), (‘qwer’, Decimal(‘5’)), (‘zzz111’, Decimal(‘3’))]

max

print(session.query(User.password,func.max(User.id)).group_by(User.password).all())

# 结果
[(‘111’, 14), (‘123asd’, 1), (‘234qwe’, 2), (‘333’, 16), (‘choupidan’, 15), (‘q1’, 12), (‘qwer’, 5), (‘zzz111’, 3)]

min

print(session.query(User.password,func.min(User.id)).group_by(User.password).all())

# 结果
[(‘111’, 4), (‘123asd’, 1), (‘234qwe’, 2), (‘333’, 16), (‘choupidan’, 15), (‘q1’, 12), (‘qwer’, 5), (‘zzz111’, 3)]

lable 别名

lable别名不能用在having中

extract 提取

提取时间元素

from sqlalchemy import extract
print(session.query(extract('minute',User.createtime).label('minute'),func.count(User.id)).group_by('minute').all())

基于分钟排序

[(16, 1), (27, 1), (29, 3), (44, 1), (52, 1), (57, 2)]

基于天数排序

print(session.query(extract('day',User.createtime).label('day'),func.count(User.id)).group_by('day').all())

[(6, 1), (7, 1), (9, 7)]

二、进阶操作

1、反向查询

一对多反向查询

• sqlalchemy的数据库层

class TaskTemplateTable(db.Model, SessionMixin):
    """
    任务模板绑定的表单模板
    """
    __tablename__ = 'patrol_inspect_tasktemplatetable'
    # True: 显示点位信息
    # False 不显示点位信息
    id = db.Column(db.Integer(), primary_key=True, autoincrement=True)

    table_template_id = db.Column(db.Integer(), db.ForeignKey('patrol_inspect_tabletemplate.id'))

    task_template = db.relationship('TaskTemplate')

• 查询层

table_obj = db.session.query(TaskTemplateTable).filter_by(id=1).first()
# 要先拿到查询表下的对象，再根据对象，进行关联表的反向查询
print(table_obj.tamplate_table.name)
>>> 'ups巡检记录表'

2、数据批量操作

数据库批量数据的插入：

• 一般的操作都为，每一条数据提交一次的方式，这样相当影响性能，可以通过执行原生sql或者sqlalchemy支持的批量导入数据的方式。进行批量插入
• 转化为sql：insert into 表名 values(...),(...)...;

1) 批量插入10000条数据：

db.session.execute(
    User.__table__.insert(),
    [{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]
)
session.commit()

2）如何让执行的 SQL 语句增加前缀？

使用 query 对象的 prefix_with() 方法：

session.query(User.name).prefix_with('HIGH_PRIORITY').all()
session.execute(User.__table__.insert().prefix_with('IGNORE'), {'id': 1, 'name': '1'})

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

使用 session.merge() 方法替代 session.add()，其实就是 SELECT + UPDATE：

user = User(id=1, name='ooxx')
session.merge(user)
session.commit()

或者使用 MySQL 的 INSERT … ON DUPLICATE KEY UPDATE，需要用到 @compiles 装饰器，有点难懂，自己看吧：《SQLAlchemy ON DUPLICATE KEY UPDATE》 和 sqlalchemy_mysql_ext。

4）如何使用无符号整数？

可以使用 MySQL 的方言：

from sqlalchemy.dialects.mysql import INTEGER

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

5）模型的属性名需要和表的字段名不一样怎么办？

开发时遇到过一个奇怪的需求，有个其他系统的表里包含了一个“from”字段，这在 Python 里是关键字，于是只能这样处理了：
from_ = Column('from', CHAR(10))

6）如何获取字段的长度？

Column 会生成一个很复杂的对象，想获取长度比较麻烦，这里以 User.name 为例：
User.name.property.columns[0].type.length

7）如何指定使用 InnoDB，以及使用 UTF-8 编码？

最简单的方式就是修改数据库的默认配置。如果非要在代码里指定的话，可以这样：

class User(BaseModel):
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }

数据库批量数据的 删除DELETE：

• 当查询结果只有一个对象时，可直接调用改对象的delete方法即可
  • dbm.AccessToken.query.filter_by(id=1).delete()
• 但当结果不止一个时，直接这么操作，会报错。
  • 原因：《Sqlalchemy delete subquery》
• 删除记录时，默认会尝试删除 session 中符合条件的对象,而这里还不支持，所以报错

1) 批量删除数据：

# 方式一：  
token_obj_list = dbm.AccessToken.query.filter(text(sql_text)).params(**sql_params).all()

for obj in token_obj_list:
    db.session.delete(obj)
db.session.commit()

# 方式二：
     # 这里不让session同步
delte_count = AccessToken.query.filter(text(sql_text)).params(**sql_params).delete(synchronize_session=False)

print(delete_count)
session.commit()

3、复制copy对象，并新建数据到db

consume_obj = dbm.ConsumeOrderHistory.query.filter_by(id=raw_index).first()


# 直接copy对象，会造成session会话冲突，这个不能直接用copy的方式来操作
# consume_record = copy.deepcopy(consume_obj)

# 通过make_transient来实现
from sqlalchemy.orm.session import make_transient

logger.info(consume_obj)  # <pay_proxy.dbmodels.ConsumeOrderHistory object at 0x7f3ff62ac350>
logger.info(type(consume_obj))   # <class 'pay_proxy.dbmodels.ConsumeOrderHistory'>

logger.info("开始处理对象 ... ")
make_transient(consume_obj)

logger.info(consume_obj)   # <pay_proxy.dbmodels.ConsumeOrderHistory object at 0x7f3ff62ac350> ,对象的地址都没变，只是去掉了session链接
logger.info(type(consume_obj))   # <class 'pay_proxy.dbmodels.ConsumeOrderHistory'>
logger.info(dir(consume_obj))  # 仍然包含源对象的所有方法

"""
- 处理前后的两个对象是完全相等的！
- 经过其处理之后的对象，不再跟session绑定，此时只需要将其主键作相应替换即可实现复制一份数据到db
"""

# 两种方式：
# consume_obj.id = None  # 1、 主键设null，主键设置了autoincrement，则会自动生成
delattr(consume_obj, 'id')  # 2、删除其主键属性，commit到数据库时会自动赋值

db.session.add(consume_obj)
db.session.commit()

# 最终会在数据库添加除了主键不同之外，其他所有字段都跟查出来的consume_obj字段相一致的一条数据。

三、其他sql知识点

1、sql语句相关

数据库字段判空：

• 1.为null
• 2.为字符串的空’’
  判断是否为空（包括上边两种情况）：

select * from table where column is null or trim(column)=''

这样就可以排除字段内容为null、’'的。

判断某个字段不为空,可直接用!=来做判断，便可以排除掉null和""的判断

select * from table where trim(column) != ''