sqlalchemy与mysql区别_你真的了解SQLAlchemy吗
SQLAlchemy作为⼀款强⼤⽽⼜实⽤的ORM框架，越来越频繁的出现在各类Python项⽬中。如果你是⼀名Python开发⼯程师，肯定可以熟练的写出SQLAlchemy的查询语句来满⾜⾃⼰的业务需求。然⽽，SQLAlchemy背后的知识你⼜了解多少呢？它是什么时候与数据库建⽴连接的呢？⼜是怎样的连接⽅式呢？SQLAlchemy中的Session与数据库的Transaction是⼀回事吗？如何优雅地管理SQLAlchemy的连接与事务呢？本⽂将结合SQLAlchemy⽂档解答这些问题。
为了⽅便展⽰，我在本地MySQL中创建了⼀张student表，同时打开MySQL的general-log来追踪MySQL的⾏为。
CREATE TABLE `student` (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '⾃增主键',
name STRING NOT NULL DEFAULT '' COMMENT '姓名',
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学⽣表';
set global general_log=1;
同时在iPython中创建我们的ORM对象。
DeclarativeBase = declarative_base()
class StudentModel(DeclarativeBase):
__tablename__ = 'student'
id = Column(BigInteger, primary_key=True)
name = Column(String, default=u'')
连接
SQAlchemy通过create_engine创建Engine对象来实现数据库连接.
# DB_CONNECT_STRING = 'mysql+pymysql://root:[email protected]/test?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, pool_size=5, max_overflow=2, pool_recycle=60, echo=True)
这⾥设置echo=True来显⽰所执⾏的SQL⽇志。
延时加载
执⾏create_engine后general-log和stdout并没有显⽰任何信息，因为SQLAlchemy到数据库的连接是延时加载的，只有真正需要建⽴连接时才会创建。The Engine, when first returned by create_engine(), has not actually tried to connect to the database yet; that happens only the first time it is asked to perform a task against the database.
调⽤connect⽅法建⽴连接：
connection = t()
此时general-log显⽰如下信息，表⽰真正建⽴了到数据库的连接，连接线程ID为22，除此之外，SQAlchemy会默认将数据库的autocommit设置为0，在查询时会隐式开始Transaction。
2018-11-27T13:52:48.695968Z 22 Connect [email protected] on test using TCP/IP
2018-11-27T13:52:48.701437Z 22 Query SET AUTOCOMMIT = 0
连接池
Engine对象维护着⼀个连接池pool，如果没有通过poolclass=NullPool来禁⽤连接池，在连接关闭后会被暂存在pool中，下次创建连接时会直接从pool中获取连接，如果从连接池中拿到的连接距离创建时间超过pool_recycle，Engine将会将此连接释放并创建新的连接。
在pool_recycle时间内调⽤close关闭连接，同时创建⼀个新连接：
connection.close()
connection2 = t()
general-log并没有新⽇志显⽰，查看MySQL的连接发现并没有新连接产⽣。
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 12 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 22 | root | localhost:64797 | test | Sleep | 11 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
⽽如果在pool_recycle之外执⾏上⾯的语句，general-log则会展⽰⽼连接释放，新连接创建的过程。
2018-11-27T13:53:14.045708Z 22 Query ROLLBACK
2018-11-27T14:05:03.360576Z 22 Quit
2018-11-27T14:05:03.367828Z 23 Connect [email protected] on test using TCP/IP
2018-11-27T14:05:03.372500Z 23 Query SET AUTOCOMMIT = 0
Session
调⽤sessionmaker⽅法绑定Engine对象创建Session Factory，实例化后产⽣Session对象。
DBSession = sessionmaker(bind=engine, expire_on_commit=False)
session = DBSession()
Session状态
Session有两个状态：begin和transactional。
区别在于Session有没有真正建⽴到数据库的连接，即绑定的Engine对象是否通过调⽤connect⽅法变为Connection对象。如果autocommit为默认值False，Session在实例化之后即处于begin状态。如果autocommit值为True，则需要显式调⽤session.begin()⽅法来开启事务。
当调⽤query、execute、flush⽅法后，将创建到数据库的连接，Engine变为Connection对象，Sessio
n进⼊transactional状态。
之后继续调⽤commit或rollback⽅法后，连接关闭放⼊连接池，Connection对象变为Engine对象，Session也重新回到begin状态。When the transactional state is completed after a rollback or commit, the Session releases all Transaction and Connection resources, and goes back to the “begin” state, which will again invoke new Connection and Transaction objects as new requests to emit SQL statements are received.
实例化⼀个ORM对象，依次调⽤add、flush和rollback⽅法，stdout显⽰了隐式开启Transaction的步骤：
a = StudentModel()
session.add(a)
session.flush()
# 2018-11-27 23:04:02,579 ine.base.Engine BEGIN (implicit)
# 2018-11-27 23:04:02,579 ine.base.Engine INSERT INTO student (id, name) VALUES (%s, %s)
# 2018-11-27 23:04:02,579 ine.base.Engine (2, u'')
# 2018-11-27 23:04:14,036 ine.base.Engine ROLLBACK
general-log也显⽰在flush之后建⽴了连接并执⾏了查询语句，之后进⾏了回滚。
2018-11-27T15:04:02.573576Z 32 Connect [email protected] on test using TCP/IP
2018-11-27T15:04:02.573872Z 32 Query SET AUTOCOMMIT = 0
2018-11-27T15:04:02.580709Z 32 Query INSERT INTO student (id, name) VALUES (2, '')
2018-11-27T15:04:14.036762Z 32 Query ROLLBACK
2018-11-27T15:04:14.042584Z 32 Query ROLLBACK
保留上⾯的session在1分钟后重复执⾏上⾯的步骤，general-log显⽰了⽼连接释放和新连接的建⽴过程。
2018-11-27T15:10:43.755010Z 32 Quit
2018-11-27T15:10:43.762052Z 33 Connect [email protected] on test using TCP/IP
2018-11-27T15:10:43.763616Z 33 Query SET AUTOCOMMIT = 0
2018-11-27T15:10:43.764588Z 33 Query INSERT INTO student (id, name) VALUES (2, '')
2018-11-27T15:11:11.283859Z 33 Query ROLLBACK
2018-11-27T15:11:11.289628Z 33 Query ROLLBACK
flush与add的区别
通过上⾯的展⽰也可以发现，add实际上不会真正发送请求到数据库，这⾥所做的任何修改其他事务都是不可见的。
⽽flush则会发送请求到数据库，⽽此时的变更属于未提交变更，对其他事务是否可见取决于数据库的隔离机制。
expire_on_commit
expire_on_commit可以⽤来更改SQLAlchemy的对象刷新机制，默认值为True即在session调⽤commit
之后会主动将同⼀个session在commit之前查询得到的ORM对象的_sa_pire属性设置为Flase，再次读取该对象属性时将重载这个对象，⽅法是重新调⽤之前的查询语句。
将expire_on_commit设置为True后重新执⾏代码，发现在获取b属性时⼜执⾏了⼀次查询。
b = session.query(StudentModel).filter(StudentModel.id==2).first()
# 2018-11-27 23:52:03,584 ine.base.Engine SELECT student.id AS student_id, student.name AS student_name
# FROM student
# WHERE student.id = %s
# LIMIT %s
# 2018-11-27 23:52:03,585 ine.base.Engine (2, 1)
b.name = u'test'
session.add(b)
session.flush()
# 2018-11-27 23:52:31,789 ine.base.Engine UPDATE student SET name=%s WHERE student.id = %s
# 2018-11-27 23:52:31,789 ine.base.Engine (u'test', 2)
sessionmit()
# 2018-11-27 23:53:06,424 ine.base.Engine COMMIT
b._sa_instance_state.__dict__
# {'_instance_dict': ,
# '_strong_obj': None,
# 'callables': {'id': ,
# 'name': },
# 'class_': __main__.StudentModel,
# 'committed_state': {},
# 'expired': True,
# 'key': (__main__.StudentModel, (2,)),
# 'manager': at 1036b64f0>,
# 'modified': False,
# 'obj': ,
# 'runid': 2,
# 'session_id': 1}
b.name
# 2018-11-27 23:54:34,431 ine.base.Engine BEGIN (implicit)
# 2018-11-27 23:54:34,431 ine.base.Engine SELECT student.id AS student_id, student.name AS student_name
mysql下载starting the server
# FROM student
# WHERE student.id = %s
# 2018-11-27 23:54:34,431 ine.base.Engine (2,)
虽然这样可以保持数据同步，但实际应⽤中⼀般将这个值设置为Flase，避免内存中的数据属性被更改。
实战Q&A
有了上⾯的理论基础，下⾯从实际应⽤出发，思考⼀些项⽬中的常见问题。
Q1.是否应该启⽤连接池，如何配置连接池
⾸先，MySQL建⽴连接是⾮常复杂的过程，我们需要启⽤连接池来保持与MySQL的长连接，以减少建⽴连接的次数。
那连接池是否越⼤越好呢，甚⾄所有连接都使⽤长连接？答案也是否定的，因为MySQL在执⾏过程中使⽤的临时内存是存放在连接对象中的，如果保持⼤量长连接会导致MySQL的内存快速增长，以致于
被系统强⾏杀掉(OOM)异常重启。因此需要结合业务的TPS来配置连接池⼤⼩，来保证与MySQL的活跃连接维持在pool_size+max_overflow内。
⾄于连接池的连接回收时间，先来看⼀个使⽤MySQL时的常见报错，在查询时与MySQL断开了连接：
OperationalError: (OperationalError) (2013, "Lost connection to MySQL server during query (error(54, 'Connection reset by peer'))") 'SELECT student.id AS student_id, student.name AS student_namenFROM studentnWHERE student.id
=%snLIMIT%s' (1, 1)
这个错误是由MySQL连接器抛出的，原因⼀般有两个：所连接的MySQL服务真的发⽣了重启操作，这种问题⽆法避免。
MySQL连接器会主动关闭空闲(Sleep)时间达到wait_timeout时间的连接，这个超时时间的默认值为8⼩时。
SQLAlchemy在捕获到MySQL连接器的抛错后会主动清空连接池⾥的连接，如果需要进⾏查询需要重新建⽴连接。因此，你的连接池pool_recycle参数⾄少要⼩于MySQL的wait_timeout时间，以避免连接池中存在被MySQL连接器主动断开的连接。当然也不能太⼩，使连接池失去意义。⼀般的建议值为30
分钟之内。
Q2.如何在应⽤中管理Session
关于这点，SQLAlchemy官⽅⽂档中给出了合理的⽅案，但需要注意⼀些细节。As a general rule, keep the lifecycle of the session separate and external from functions and objects that access and/or manipulate database data. This will greatly help with achieving a predictable and consistent transactional scope.
Make sure you have a clear notion of where transactions begin and end, and keep transactions short, meaning, they end at the series of a sequence of operations, instead of being held open indefinitely.
⼀般来说，你需要在访问数据库的时候创建Session，开启Transaction同时执⾏查询语句，在所有查询语句执⾏结束之后关闭Transaction和Session。任何时候，只要⼀个Session被创建并建⽴数据库连接，则它必须被关闭从⽽将数据库连接释放到连接池中，否则连接将永远不会释放直到达到数据库连接的超时时间。
对于Web应⽤来说，由于⼀个请求的处理时间⾜够短，可以更简单地将Session的⽣命周期同请求的⽣
命周期保持⼀致，即如果⼀个请求需要访问数据库，则创建Session，处理完这个请求则关闭Session。
由于Session是必须被关闭的，为了⽅便的管理Session，保证⼀个请求内只有⼀个Session是更好的选择，你可以将创建的Session作为全局变量使⽤，或者更⽅便地，使⽤scoped_session，保证同⼀个线程内实例化的Session都为同⼀个对象。
session_factory = sessionmaker(bind=engine, expire_on_commit=True)
DBSession = scoped_session(session_factory)
这样，在请求结束时，你只需要关闭⼀个Session即可。例如你可以在Flask应⽤中的after_request函数中加⼊⼀⾏代码调⽤Session Factory的remove⽅法，就可以保证请求中的Session对象被关闭，它会调⽤Session的close⽅法，将数据库连接放回连接池并将未commit的Transaction回滚。
需要注意的是，scoped_session对在同⼀个线程的判断⽅式为thrending.local()，当使⽤多线程处理某个查询语句时，正确的使⽤⽅法是在当前线程中实例化⼀个Session并将其传⼊多线程函数中，否则其他线程中的Session将⽆法被关闭，数据库连接和Transaction也将⼀直不会被释放。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。