使用 SQLAlchemy ORM 高效更新数据库 [英] Efficiently updating database using SQLAlchemy ORM

问题描述

我正在启动一个新应用程序并考虑使用 ORM -- 特别是 SQLAlchemy.

I'm starting a new application and looking at using an ORM -- in particular, SQLAlchemy.

假设我的数据库中有一个列foo"，我想增加它.在直接的 sqlite 中，这很容易:

Say I've got a column 'foo' in my database and I want to increment it. In straight sqlite, this is easy:

db = sqlite3.connect('mydata.sqlitedb')
cur = db.cursor()
cur.execute('update table stuff set foo = foo + 1')

我想出了 SQLAlchemy SQL-builder 等价物:

I figured out the SQLAlchemy SQL-builder equivalent:

engine = sqlalchemy.create_engine('sqlite:///mydata.sqlitedb')
md = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table('stuff', md, autoload=True)
upd = table.update(values={table.c.foo:table.c.foo+1})
engine.execute(upd)

这有点慢，但内容不多.

This is slightly slower, but there's not much in it.

这是我对 SQLAlchemy ORM 方法的最佳猜测:

Here's my best guess for a SQLAlchemy ORM approach:

# snip definition of Stuff class made using declarative_base
# snip creation of session object
for c in session.query(Stuff):
    c.foo = c.foo + 1
session.flush()
session.commit()

这样做是正确的，但所需时间是其他两种方法的不到 50 倍.我认为这是因为它必须先将所有数据都放入内存中，然后才能使用它.

This does the right thing, but it takes just under fifty times as long as the other two approaches. I presume that's because it has to bring all the data into memory before it can work with it.

有没有办法使用 SQLAlchemy 的 ORM 生成高效的 SQL?或者使用任何其他python ORM?还是我应该回去手工编写 SQL?

Is there any way to generate the efficient SQL using SQLAlchemy's ORM? Or using any other python ORM? Or should I just go back to writing the SQL by hand?

推荐答案

SQLAlchemy 的 ORM 旨在与 SQL 层一起使用，而不是隐藏它.但是，在同一事务中使用 ORM 和普通 SQL 时，您必须牢记一两件事.基本上，从一方面来说，ORM 数据修改只会在您从会话中刷新更改时才会命中数据库.另一方面，SQL 数据操作语句不会影响会话中的对象.

SQLAlchemy's ORM is meant to be used together with the SQL layer, not hide it. But you do have to keep one or two things in mind when using the ORM and plain SQL in the same transaction. Basically, from one side, ORM data modifications will only hit the database when you flush the changes from your session. From the other side, SQL data manipulation statements don't affect the objects that are in your session.

所以如果你说

for c in session.query(Stuff).all():
    c.foo = c.foo+1
session.commit()

它会做它所说的，从数据库中获取所有对象，修改所有对象，然后当需要将更改刷新到数据库时，逐行更新行.

it will do what it says, go fetch all the objects from the database, modify all the objects and then when it's time to flush the changes to the database, update the rows one by one.

相反，您应该这样做:

session.execute(update(stuff_table, values={stuff_table.c.foo: stuff_table.c.foo + 1}))
session.commit()

这将像您期望的那样作为一个查询执行，并且因为至少默认会话配置会使会话中的所有数据在提交时过期，所以您不会遇到任何过时的数据问题.

This will execute as one query as you would expect, and because at least the default session configuration expires all data in the session on commit you don't have any stale data issues.

在即将发布的 0.5 系列中，您也可以使用此方法进行更新:

In the almost-released 0.5 series you could also use this method for updating:

session.query(Stuff).update({Stuff.foo: Stuff.foo + 1})
session.commit()

这将基本上运行与前一个片段相同的 SQL 语句，但也会选择更改的行并使会话中的任何陈旧数据过期.如果您知道更新后您没有使用任何会话数据，您还可以将 synchronize_session=False 添加到更新语句中并删除该选择.

That will basically run the same SQL statement as the previous snippet, but also select the changed rows and expire any stale data in the session. If you know you aren't using any session data after the update you could also add synchronize_session=False to the update statement and get rid of that select.

这篇关于使用 SQLAlchemy ORM 高效更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！