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