I'm having a problem where I'm doing writes to MySQL that aren't being
committed. I'm obviously calling the procedure from an execute(). I'm okay
with explicit commits, but since all of my data logic is in procedures (all
operations are one step), autocommit is preferable. I'm using 0.9.1 .
Until recently, the application was only doing SELECTs, so I hadn't noticed.
There seem to be dozens of offered solutions online, but none of them work.
I'm testing using the following procedure:
delimiter //
CREATE PROCEDURE test_func()
BEGIN
SELECT @@AUTOCOMMIT `autocommit`;
END//
delimiter ;
I'm doing my tests from the command-line, in a simple script.
- I've tried setting the autocommit to True at the engine level, but I get
the "Commands out of sync" error:
engine = create_engine(DSN, echo=True).execution_options(autocommit=True)
- I've tried setting the autocommit at the session level, but the result
indicates that autocommit is "0" (the option was ignored):
Code:
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
query = 'CALL test_func()'
r = session.execute(text(query, autocommit=True))
Debug output:
2014-03-27 14:45:36,956 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2014-03-27 14:45:36,956 INFO sqlalchemy.engine.base.Engine ()
2014-03-27 14:45:37,293 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'character_set%%'
2014-03-27 14:45:37,293 INFO sqlalchemy.engine.base.Engine ()
2014-03-27 14:45:37,350 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2014-03-27 14:45:37,350 INFO sqlalchemy.engine.base.Engine ()
2014-03-27 14:45:37,416 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-03-27 14:45:37,417 INFO sqlalchemy.engine.base.Engine CALL test_func()
2014-03-27 14:45:37,417 INFO sqlalchemy.engine.base.Engine ()
- I've tried setting the autocommit on a text(), but it's ignored (with the
same result as the above):
r = session.execute(text(query, autocommit=True))
- I've tried using an execute() on the engine, but autoflush is either got
ignored or I got the "out of sync" error (like the above). I can't remember.
- I've also tried to do the actual commits, both directly or using the
context-manager, but I get the "out of sync" message every time.
This is driving me crazy. Can anyone give me a short, working example of
calling a stored procedure with autocommit, as well as with an explicit
commit?
Dustin
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
