使用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屋！