利用SQLAlchemy ORM高效更新数据库

Question

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

假设我的数据库中有一个列‘foo’，我想增加它。在直sqlite中，这很简单：

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

我找到了SQLAlchemy SQL builder的等价物：

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)

这有点慢，但没有太多。

以下是我对SQLAlchemy ORM方法的最佳猜测：

# 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倍。我想这是因为它必须把所有的数据都带到内存中，然后才能使用它。

是否有任何方法可以使用SQLAlchemy的ORM生成有效的SQL？或者使用其他python ORM？或者我应该回去手工编写SQL吗？

Answer 1

session.query(Clients).filter(Clients.id == client_id_list).update({'status': status})
session.commit()

试试这个=）

Answer 2

有几种方法可以使用sqlalchemy进行更新

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

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

3) conn = engine.connect()
   stmt = Stuff.update().\
       values(Stuff.foo = (Stuff.foo + 1))
   conn.execute(stmt)

Answer 3

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

所以如果你说

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

它将按照它所说的做，从数据库中获取所有对象，修改所有对象，然后在刷新对数据库的更改时，逐个更新行。

相反，你应该这样做：

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

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

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

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

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