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>
wrote:
> 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.
