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:
{<. b...@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:
> 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.
