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.