子查询时做分页
sub_query = db.session.query(Table,Table.id).offset((page_no - 1) * page_size).limit(page_size).subquery()
query1 = db.session.query(sub_query, Table4, Table3, Table2) \
.outerjoin(Table2) \
.outerjoin(Table3) \
.outerjoin(Table4) \
SQLAlchemy查询之exists
SELECT *
FROM table1
WHERE EXISTS (SELECT *
FROM (SELECT max(table2.event_type) AS event_type, table2.event_code AS event_code
FROM table2
WHERE table2.`status` = 200 GROUP BY table2.event_code) AS anon_1
WHERE table1.id = anon_1.event_code AND anon_1.event_type = 100 ) ORDER BY table1.create_time DESC
query = db.session.query(Table1).order_by(Table1.create_time.desc())
func_criteria = db.session.query(func.max(Table2.event_type).label('event_type'),
Table2.event_code.label('event_code'))\
.group_by(Table2.event_code).filter(Table2.status == 200).subquery()
query = query.filter(exists().where(Table1.id == func_criteria.c.event_code)\
.where(func_criteria.c.event_type== status))
官网样例
>>> subq = (
... select(func.count(address_table.c.id)).
... where(user_table.c.id == address_table.c.user_id).
... group_by(address_table.c.user_id).
... having(func.count(address_table.c.id) > 1)
... ).exists()
>>> with engine.connect() as conn:
... result = conn.execute(
... select(user_table.c.name).where(subq)
... )
... print(result.all())
sqlalchemy的update手动commit之前通过yield执行远程异步调用时session对象被替换
SQLAlchemy==1.2.18
result = db.session.query(ObjectModel).get(ids.get("id"))
result.status = 2 #此时 session._is_clean() = False
#中间执行一次yield远程调用
#外部请求函数
def onAyncFetch():
req = ...省略其它代码
response = yield async_http.fetch(req)
...省略其它代码
return Return(json_decode(response.body))
print id(result.query.session)
data = yield onAyncFetch(...)
print id(result.query.session) #两次打印内存地址不相同
...省略其它代码
#执行yield调用结束之后 session._is_clean() = True
db.session.commit() #再进行commit的会进行如下代码段,上面修改的status字段无法被commit
上面是一段伪代码.流程大概就这样,下面贴sqlalchemy时commiit的源码
sqlalchemy/orm/session.py
def _prepare_impl(self):
self._assert_active()
if self._parent is None or self.nested:
self.session.dispatch.before_commit(self.session)
stx = self.session.transaction
if stx is not self:
for subtransaction in stx._iterate_self_and_parents(upto=self):
subtransaction.commit()
if not self.session._flushing:
for _flush_guard in range(100):
if self.session._is_clean(): # 判断对象信息是否变更,如果变更则执行flush()
break
self.session.flush()
else:
raise exc.FlushError(
"Over 100 subsequent flushes have occurred within "
"session.commit() - is an after_flush() hook "
"creating new objects?"
)
if self._parent is None and self.session.twophase:
try:
for t in set(self._connections.values()):
t[1].prepare()
except:
with util.safe_reraise():
self.rollback()
self._state = PREPARED