在烧瓶中提交来自PostgreSQL函数的事务 [英] Commiting a transaction from a PostgreSQL function in flask

问题描述

我是Flask和SQLAlchemy的新手（过去三年来一直和Django一起工作）。我需要调用一个现有的PostgreSQL函数来写入数据库中的3个不同的表。这是我无法控制的（我只需要使它工作）。该函数返回一个记录（自定义Postgres类型）与关于结果的信息。这里是代码：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'



retval = db.engine.execute（
'SELECT * FROM add_purchase（％s，％s，％s，％s，％ s，％s，％s）;'，
clean_data.account_id，amount，tax，deposit，clean_data.pos_id，
g.token_id，clean_data.external_reference_id）.first（）

app.logger.info（retval）＃用于调试

db.session.commit（）

上面的代码运行没有错误。从日志消息中，我可以看到正确的数据从数据库中返回。但是，如果我去psql，我看不到新插入的数据。这似乎是事实从来没有实际承诺。

大多数的文档和示例，我可以找到基于使用SQLAlchemy的ORM。我在这里做错了什么？

堆栈信息：

  Flask == 0.10.1 
 Flask-SQLAlchemy == 1.0 
 psycopg2 == 2.5.2 
 Python 2.7.3 
 Postgresql 9.3 
  
 p> 
 
 
 更新  
 
 
我已经想出了一个办法工作，这里是一个实际为我工作的示例：
 
 
  from sqlalchemy import exc 
 
 .. 。
 
 connection = db.engine.connect（）
 trans = connection.begin（）
 try：
 retval = connection.execute（
' SELECT * FROM add_purchase（％s，％s，％s，％s，％s，％s，％s）;'，
 clean_data.account_id，amount，tax，deposit，
 clean_data.pos_id ，g.token_id，
 clean_data.external_reference_id）.first（）
除了exc.SQLAlchemyError：
 trans.rollback（）
 raise 
 else：
 trans.commit（）
  
我回顾了SQLAlchemy的connect方法中的代码。好像调用connect（）只是从池中获得一个新的连接（默认情况下它被设置为size = 5，至少我没有设置为5）。所以，我不确定是否有很多伤害。但是，通过使原始问题中的示例运行起来，似乎应该让代码变得更清洁。我仍然希望有人能告诉我如何去做。
认为你必须自己发布BEGIN和COMMIT语句。尝试添加如下内容： 
 
 
  ... 
 begin_retval = db.engine.execute（'BEGIN;'） 
 retval = db.engine.execute（'SELECT * FROM add_purchase（％s，％s，％s，％s，％s，％s，％s）;'，
 clean_data.account_id， （'COMMIT;'）
 ... 
 
  
 
I'm a newbie to Flask and SQLAlchemy (been working with Django for the last 3 years).  I need to call an existing PostgreSQL function that writes to 3 different tables in a database. This is out of my control (I just have to make it work).  The function returns a record (custom Postgres type) with information about the results.  Here is the code:
from flask.ext.sqlalchemy import SQLAlchemy

db = SQLAlchemy()

...

retval = db.engine.execute(
    'SELECT * FROM add_purchase(%s, %s, %s, %s, %s, %s, %s);',
    clean_data.account_id, amount, tax, deposit, clean_data.pos_id, 
    g.token_id, clean_data.external_reference_id).first()

app.logger.info(retval) # for debugging

db.session.commit()
The above code runs without error.  From the log message, I can see the the correct data is getting returned from the database.  However, if I go to psql, I cannot see the newly inserted data.  It would seem that the transaction is never actually committed.

Most of the documentation and examples I can find are based on using SQLAlchemy's ORM. What am I doing wrong here?

Stack info:
Flask==0.10.1
Flask-SQLAlchemy==1.0
psycopg2==2.5.2
Python 2.7.3
Postgresql 9.3
Update

I have figured out a way to make it work, here is a sample that actually works for me:
from sqlalchemy import exc

...

connection = db.engine.connect()
trans = connection.begin()
try:
    retval = connection.execute(
        'SELECT * FROM add_purchase(%s, %s, %s, %s, %s, %s, %s);',
        clean_data.account_id, amount, tax, deposit,
        clean_data.pos_id, g.token_id,
        clean_data.external_reference_id).first()
except exc.SQLAlchemyError:
    trans.rollback()
    raise
else:
    trans.commit()
And I've reviewed the code in SQLAlchemy's connect method.  It seems like calling "connect()" just gets a new connection from the pool (which seem to be set to size=5 by default - at least I'm not setting it to 5).  So, I'm not sure if there is much harm in it.  However, it seems like there should be away to make this code cleaner by making the example in the original question work.  I'm still hoping someone can tell me how to do that.
解决方案
If you are using raw SQL with SQLAlchemy, then I think you have to issue the BEGIN and COMMIT statements yourself. Try adding something like this:
...
begin_retval = db.engine.execute('BEGIN;')
retval = db.engine.execute('SELECT * FROM add_purchase(%s, %s, %s, %s, %s, %s, %s);',
clean_data.account_id, amount, tax, deposit, clean_data.pos_id, 
g.token_id, clean_data.external_reference_id).first()
commit_retval = db.engine.execute('COMMIT;')
...


                        
这篇关于在烧瓶中提交来自PostgreSQL函数的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！