使用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构建方式：

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()

这个方法可以正确执行，但它的速度大约是前两种方法的五十倍慢。我猜这是因为它需要先把所有数据加载到内存中才能处理。

有没有办法用SQLAlchemy的ORM生成高效的SQL？或者用其他的Python ORM？还是说我应该回去手动写SQL呢？

Answer 1

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

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

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

3) conn = engine.connect()
   table = Stuff.__table__
   stmt = table.update().values({'foo': Stuff.foo + 'a'})
   conn.execute(stmt)
   conn.commit()

Answer 2

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

试试这个 =)

Answer 3

SQLAlchemy的ORM（对象关系映射）是用来和SQL层一起使用的，而不是把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，这样就可以省去那条选择语句。