[sqlalchemy] 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:

{<. b...@mote: ~/dev/sasqlconsole .]
£ python test.py test1 && sqlite3 test.sqlite "select * from test;"
1|2|3
{<. b...@mote: ~/dev/sasqlconsole .]
£ python test.py test2 && sqlite3 test.sqlite "select * from test;"
{<. b...@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

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.