SQLAlchemy：防止自动关闭 [英] SQLAlchemy: prevent automatic closing

问题描述

我需要通过SQLAlchemy插入/更新批量行。并获取插入的行。

I need to insert/update bulk rows via SQLAlchemy. And get inserted rows.

我尝试使用session.execute来做到这一点：

I tried to do it with session.execute:

 >>> posts = db.session.execute(Post.__table__.insert(), [{'title': 'dfghdfg', 'content': 'sdfgsdf', 'topic': topic}]*2)
 >>> posts.fetchall()

 ResourceClosedError                       Traceback (most recent call last)

并使用引擎：

In [17]: conn = db.engine.connect()  

In [18]: result = conn.execute(Post.__table__.insert(), [{'title': 'title', 'content':  'content', 'topic': topic}]*2)

In [19]: print result.fetchall()
ResourceClosedError: This result object does not return rows. It has been closed automatically.

同一响应是对象已自动关闭。

The same response is an object has been closed automatically. How to prevent it?

推荐答案

第一个答案-关于防止自动关闭。

SQLAlchemy使用insert运行DBAPI execute（）或executemany（），并且不执行任何选择查询。
因此，您遇到的例外是预期的行为。执行插入查询后返回的ResultProxy对象包装了不允许对其执行 .fetchall（）的DB-API游标。一旦 .fetchall（）失败，ResultProxy将向用户返回您所看到的异常。

SQLAlchemy runs DBAPI execute() or executemany() with insert and do not do any select queries. So the exception you've got is expected behavior. ResultProxy object returned after insert query executed wraps DB-API cursor that doesn't allow to do .fetchall() on it. Once .fetchall() fails, ResultProxy returns user the exception your saw.

唯一可获取的信息插入/更新/删除操作将是受影响的行数或自动递增后的主键值（取决于数据库和数据库驱动程序）。

The only information you can get after insert/update/delete operation would be number of affected rows or the value of primary key after auto increment (depending on database and database driver).

如果您的目标是收到此类信息，请考虑检查 ResultProxy方法和属性，例如：

If your goal is to receive this kind information, consider checking ResultProxy methods and attributes like:

• .inserted_primary_key


• .last_inserted_pa​​rams（）


• .lastrowid


• 等

• .inserted_primary_key
• .last_inserted_params()
• .lastrowid
• etc

第二个答案-关于如何进行批量插入/更新并获得结果行。

在使用DBAPI进行单次插入查询时，无法加载已插入的行。您用于批量插入/更新的SQLAlchemy SQL Expression API也未提供此类功能。
SQLAlchemy运行DBAPI executemany（）调用，并依赖于驱动程序的实现。有关详细信息，请参见本节。

There is no way to load inserted rows while doing single insert query using DBAPI. SQLAlchemy SQL Expression API you are using for doing bulk insert/updates also doesn't provide such functionality. SQLAlchemy runs DBAPI executemany() call and relies on driver implementation. See this section of documentation for details.

解决方案是设计表的方式，使每条记录都具有用于标识记录的自然键（以独特方式标识记录的列值的组合）。因此，插入/更新/选择查询将能够针对一条记录。
这样做之后，可以先进行批量插入/更新，然后再通过自然键进行选择查询。因此，您不需要知道自动递增的主键值。

Solution would be to design your table in a way that every record would have natural key to identify records (combination of columns' values that identify record in unique way). So insert/update/select queries would be able to target one record. After doing it would be possible to do bulk insert/update first and then doing select query by natual key. Thus you won't need to know autoincremented primary key value.

另一种选择：也许您可以使用用于创建对象的SQLAlchemy对象关系API -然后，SQLAlchemy可能会尝试通过对executemany进行一次查询来优化插入您。使用Oracle DB时对我有用。
开箱即用将不会进行任何优化。查看此SO问题，以获取有效的批量更新建议

Another option: may be you can use SQLAlchemy Object Relational API for creating objects - then SQLAlchemy may try to optimize insert into doing one query with executemany for you. It worked for me while using Oracle DB. There won't be any optimization for updates out of the box. Check this SO question for efficient bulk update ideas

这篇关于SQLAlchemy：防止自动关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！