a1.sqlalchemy-研究

Sqlalchemy And Flask-Sqlalchemy

使用sqlalchemy时，先搞明白：

• sqlalchemy和flask-sqlalchemy不相等。flask-sqlalchemy是一个flask的扩展，简化了在flask中使用sqlalchemy的操作。
• sqlalchemy提供了高层orm，也提供了使用数据库原生sql的底层功能。

sqlalchemy使用

• db.create_all()可以用来创建表，若表已存在，那么其不会进行创建或者更新。但是在修改模型后要把改动应用到现有的数据库中，粗暴的方式为先删除表，再重新创建。db.drop_all()/db.create_all()因此，可以用Django的那套orm来创建表，sqlalchemy只用来操作表内的数据。

事务操作（数据库会话也成为事务）

• 通过数据库会话管理对数据库所做的改动。sqlalchemy中用db.session标识
  • db.session.add() 添加到会话
  • db.session.flush()刷新会话，这里可以拿到对象的id，对应的数据已到数据库缓存中，只是还未提交。
  • db.session.commit() 提交会话
• 数据库会话能保证数据库的一致性。
• 提交操作使用原子方式把会话中的对象全部写入数据库。如果写入的过程发生了错误，整个会话都会失效。（只要把相关的改动放在会话中提交，就能避免因部分更新导致的数据库的不一致，就能完成事务的操作）
• db.session.rollback() 数据库会话的回滚。调用后，添加到数据库会话的所有对象都会还原到他们在数据库时的状态。

db.session.flush详解

通过query可以拿到数据，执行mysql语句同理也可以，但是这个只限于在当前连接的缓存内体现，数据还未被持久化到数据库文件。别的账户连接，是看不到这个数据的。也就是数据库没有这条数据，但是在缓存内是有这个数据的。

# TODO 试验db.session.flush()
@ns.route('/test')
class test(Resource):
    def get(self):
        # 最后一个id是122
        count_obj = dbm.TaskCount()
        count_obj.date = '1111-11-11'
        count_obj.total_count = 11
        count_obj.complete_count = 11
        count_obj.task_template_id = 646
        db.session.add(count_obj)
        db.session.flush()
        logger.info(">>>>>>>>> flush_id >>>>>%s"%count_obj.id)
        query_obj = dbm.TaskCount.query.filter_by(id=count_obj.id).first()
        if query_obj:
            logger.info(".....%s"%query_obj.id)
            logger.info('......data..... %s'%query_obj.date)
        temp_id = count_obj.id
        query2 = db.session.execute("select * from patrol_inspect_task_count where id=:id", {'id': temp_id}).fetchall()
        logger.info('******** query_2 ********* %s'%query2)
        return 'yes!'


# 得到的结果（基于mysql的缓存，预提交机制autocommit机制的设置）

# flush可以拿到id
>>>>>>>>> flush_id >>>>>125

.....125
......data..... 1111-11-11

# 直接执行sql语句拿到的结果
******** query_2 ********* [(125, u'1111-11-11', 11, 11, 646)]

sqlalchemy注意点

1. filter_by(name='haha').count()的坑

# 执行上述的count方式：
temp_count = dbm.PersonnelBlacklist.query.filter(text(sql_text)).params(**sql_params).count()

# sqlalchemy执行的sql为:
 """转化sql含义为先搜索出所有sql对象，在统计所有数据对象的数量，当数据量大的时候，这样会严重影响速度。"""
SELECT count(*) AS count_1 
FROM (SELECT * FROM account_personnel_blacklist) AS anon_1


# 优化方式：
temp_count = db.session.query(func.count(dbm.PersonnelBlacklist.id)).\
            filter(text(sql_text)).params(**sql_params).first()[0]
# 这样转化过来的sql为：
# 直接执行搜索数量，数据量大的时候，提升回很明显
SELECT count(account_personnel_blacklist.id) AS count_1 
FROM account_personnel_blacklist  LIMIT %(param_1)s

2.pagination_obj.items和pagination_obj.total的弊端

• 得到pagination的分页对象后<flask_sqlalchemy.Pagination object at 0x7fd7046df9d0>，内部有items用于获取所有对象，total获取总的数量（筛选条件下，不做分页）

• 虽然得到的是一个对象，但想获取到数据和总数量，这里会隐式的执行两条sql（一条用于查数据，一条用于查总数量）

• 如下的两条(与上边count的坑一致)：

  • select * from account
  • select count(*) from account (select * from account) as temp_data

• 但但是上边的操作，对coder比较友好，一行代码即可搞定。若要优化好可以将获取数据，获取总数量(select count(id) from account)分成两步精简的sql进行执行