Calling stored procedures in SQLAlchemy

[Will Orr]

Hello all!

I'm having this *exact* bug from a few years ago wrt. calling stored procedures.

https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/qA_ypVgJ1B0

What makes it worse, however, is that adding the autocommit execution option or explicitly starting and stopping a transaction do nothing.

    session.execute(text('call add_logentry(:username, :hostname, :action, \'0000-00-00 00:00:00\')'), {

        'username': username,

        'hostname': hostname,

        'action'  : action

    })

There's the code. It should insert some values into some tables, however those values are never inserted, though the primary key counter is incremented.

I'm using SQLAlchemy with ZopeTransactionExtension.

[Michael Bayer]

did you call Session.commit() ?  otherwise you're still in an open transaction, assuming default settings.

Session.execute() is not the same as engine.execute(), where the latter is autocommitting (assuming you also called execution_options(autocommit=True) for this particular text() construct).

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/si1vqn5kmjoJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Will Orr]

Yes. I tried adding the autocommit execution option and calling execute, as well as turning autocommit off and manually calling commit.

Like I said, the stored procedure is getting run because the primary key is incrementing internally, (that is to say, that if my last id entry was 1, I run this code, and then on the next insert the id entry is 3) and the results are getting rolled back.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[Michael Bayer]

Do you have echo=True on and do you see the COMMIT logging lines that you expect to see ?  if so, does it work with a raw DBAPI script that sends out the necessary SQL ?  otherwise it sounds like the procedure itself is broken. 

I dont see how the concept of primary key incrementing impacts this.  Are you saying, this is a trigger?  What database backend is this even ?   There's a lot of detail here that anyone would need in order to debug this issue further.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/5y7_u014dn8J.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.