execute() and commit() and "commit"

[Bob Farrell]

Hi there,

I'm having a bit of trouble with session.execute() and session.commit()

Here's a simple example that demonstrates what I'm trying to do:

import os
import sys

import sqlalchemy.orm as orm
import sqlalchemy as sa

Session = orm.sessionmaker()
session = None

def setup():
global session

Session.configure(bind=sa.create_engine('sqlite:///test.sqlite'))

session = Session(autocommit=False)

try:
os.unlink("test.sqlite")
except OSError:
pass
session.execute("create table test (a, b, c)")
session.commit()

def test1():
session.execute("insert into test values (1, 2, 3)")
session.commit()

def test2():
session.execute("insert into test values (4, 5, 6)")

def test3():
session.execute("insert into test values (7, 8, 9)")
session.execute("commit")

if __name__ == "__main__":
dispatch = {'test1': test1,
'test2': test2,
'test3': test3}
if len(sys.argv) > 1 and sys.argv[1] in dispatch:
setup()
dispatch[sys.argv[1]]()

And here's the output from running each test:

{<. bob@mote: ~/dev/sasqlconsole .]
� python test.py test1 && sqlite3 test.sqlite "select * from test;"
1|2|3
{<. bob@mote: ~/dev/sasqlconsole .]
� python test.py test2 && sqlite3 test.sqlite "select * from test;"
{<. bob@mote: ~/dev/sasqlconsole .]
� python test.py test3 && sqlite3 test.sqlite "select * from test;"
Traceback (most recent call last):
File "test.py", line 41, in <module>
dispatch[sys.argv[1]]()
File "test.py", line 33, in test3
session.execute("commit")
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py", line 753, in execute
clause, params or {})
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement
return self.__execute_context(context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) cannot commit - no transaction is active u'commit' []

Tests 1 & 2 behave as I'd expect, but it's when I try to pass in a "commit" as
a string to execute() that I see explosions.

This is for a SQL repl I wrote some time ago and I decided to add transactions
to it so I need a sane way to deal with "commit" being typed. I'd like to make
this behaviour optional so that it can mimic other sql repls such as sqlplus.

Any suggestions on the best way to go about doing this are welcome.

Thanks,
--
Bob Farrell

[Bob Farrell]

Sorry, I forgot to mention that if I run my "select * from test;"
*after* I get the error for test3, it shows that insert did in fact
get committed to the database:

{<. bob@mote: ~/dev/sasqlconsole .]
£ sqlite3 test.sqlite "select * from test"
7|8|9

So it seems that the "commit" is getting sent to the database and it's
processing correctly, but SQLAlchemy is stepping in somewhere on the
way back before the execute() returns and causing some mischief.

On Feb 12, 12:19 pm, Bob Farrell <robertanthonyfarr...@googlemail.com>
wrote:

[Bob Farrell]

I tried this against Oracle and it works without a hitch, so it looks
like it's a problem with sqlite - we've got some ideas on how to fix
it so we'll carry on looking. So this thread can be ignored now, as
it's not a sqlalchemy issue (unless sqlalchemy planned to special case
"commit" as text for sqlite).

Thanks,

On Feb 12, 12:33 pm, Bob Farrell <robertanthonyfarr...@googlemail.com>

[Michael Bayer]

Bob Farrell wrote:
> I tried this against Oracle and it works without a hitch, so it looks
> like it's a problem with sqlite - we've got some ideas on how to fix
> it so we'll carry on looking. So this thread can be ignored now, as
> it's not a sqlalchemy issue (unless sqlalchemy planned to special case
> "commit" as text for sqlite).

the mechanics of "commit" and such are highly DBAPI specific. You should
ask on the pysqlite list about this.

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> 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.
>
>