Commiting a transaction from a PostgreSQL function in flask

Question

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.

Moritz · Accepted Answer · 2014-01-31 08:45:50Z

1

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

answered Jan 31, 2014 at 8:45

Moritz

4,8012 gold badges25 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David S Over a year ago

Thank you very much for your reply. I've tred that exact code and it does not commit the transacton. It doesn't raise an error either.

Moritz Over a year ago

Hmm. So how does add_purchase work? Do you have the code for that? That might be helpful...

David S Over a year ago

Yes; please see original question. "The above code runs without error. From the log message, I can see the the correct data is getting returned from the database."

Collectives™ on Stack Overflow

Commiting a transaction from a PostgreSQL function in flask

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related