使用 SQLAlchemyORM 高效更新数据库

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

假设我的数据库中有一列“ foo”，我想要增加它。在笔直的草坪上，这很容易:

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

这样做是正确的，但是需要的时间是另外两种方法的50倍。我假设这是因为它必须将所有数据存入内存，然后才能使用它。

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

230443 次浏览
小开

通过测试，我会尝试:

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

(IIRC，commit ()在不使用 ush ()的情况下工作。

我发现有时候执行一个大型查询然后在 python 中迭代要比执行大量查询快2个数量级。我假设对查询对象进行迭代的效率低于对查询对象的 all ()方法生成的列表进行迭代的效率。

[请注意下面的评论-这根本没有加速事情]。

小开

如果是因为创建对象方面的开销，那么使用 SA 可能根本无法加快速度。

如果是因为它正在加载相关的对象，那么您可以使用延迟加载来做一些事情。是否由于引用而创建了很多对象？(IE，获取一个 Company 对象也获取所有相关的 People 对象)。

小开
最佳答案

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。

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

试试这个

小开

下面是一个不需要手动映射字段就能解决同样问题的例子:

from sqlalchemy import Column, ForeignKey, Integer, String, Date, DateTime, text, create_engine
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.attributes import InstrumentedAttribute


engine = create_engine('postgres://postgres@localhost:5432/database')
session = sessionmaker()
session.configure(bind=engine)


Base = declarative_base()




class Media(Base):
__tablename__ = 'media'
id = Column(Integer, primary_key=True)
title = Column(String, nullable=False)
slug = Column(String, nullable=False)
type = Column(String, nullable=False)


def update(self):
s = session()
mapped_values = {}
for item in Media.__dict__.iteritems():
field_name = item[0]
field_type = item[1]
is_column = isinstance(field_type, InstrumentedAttribute)
if is_column:
mapped_values[field_name] = getattr(self, field_name)


s.query(Media).filter(Media.id == self.id).update(mapped_values)
s.commit()

因此，要更新 Media 实例，可以执行以下操作:

media = Media(id=123, title="Titular Line", slug="titular-line", type="movie")
media.update()
小开

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

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

提交答案