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
617 views
in Technique[技术] by (71.8m points)

python - Make SQLAlchemy COMMIT instead of ROLLBACK after a SELECT query

I am developing an app together with a partner. I do the database part (PostgreSQL), my partner implements the app on the web-server with python using SQLAlchemy. We make heavy use of stored procedures. A SELECT query on one of those looks like this in the db log:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT col_a, col_b FROM f_stored_proc(E'myvalue');

ROLLBACK;

In the stored procedures I write certain input to a log table. The app queries by SELECT, SQLAlchemy only sees a SELECT statement and insists on a ROLLBACK. Logging fails. I need it to COMMIT instead. My partner claims there is no easy way, we would have to remove SQLAlchemy altogether. I think he must be wrong but lack the konwledge to claim otherwise.

Is there an easy way to make SQLAlchemy COMMIT instead of ROLLBACK?
What keeps me from just executing trans.commit()? Do I need to set autoflush=False for that?

I have scanned the FAQ, but did not find an answer there.
Searching SO revealed some related questions like here and here, but I am not in the clear.
Maybe this recipe would work?

See Question&Answers more detail:os

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

1 Reply

0 votes
by (71.8m points)

If you're using SQLAlchemy's connection pooling, then what you're seeing is probably the automatic rollback that happens when a connection is closed after use. It's apparently necessary to guarantee that the connection is 'clean' for the next time it's pulled out of the pool. See this page for more info; search for 'pooling mechanism' near the top.

From what I recall (it's been a couple years since I last worked with this) changing the isolation level to autocommit won't solve the problem, since it won't see the SELECT statement as requiring a commit.

You really just want to wrap that statement in a transaction. I don't know how your code is structured, but you should just be able to use SQLAlchemy's connection.begin and connection.commit. You could even just execute the BEGIN and COMMIT as arbitrary SQL.


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

...