Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
369 views
in Technique[技术] by (71.8m points)

python - SQLAlchemy: prevent automatic closing

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

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)

And with engine:

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

First answer - on "preventing automatic closing".

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

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

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

Second answer - on "how to do bulk insert/update and get resulting rows".

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.

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...