On Dec 28, 2010, at 2:56 AM, ammar azif wrote: > Hi, > > Attached is a simple script that inserts a record using > Table.insert().execute() method and session.execute(Table.insert()). > > When I tried running session.execute(Table.select()), both records inserted > by Table.insert().execute() and session.execute(Table.insert()) are > retrieved, but when I tried running > Table.select().execute(), only the record inserted through > Table.insert().execute() is shown. > > What are the differences between session.execute() and > Table.insert/select.execute() ? Are they using different connection objects? > Or is this behavior much more related to the transactions used by both > execute methods? > > Note that when using sqllite in memory db, both records inserted by both > methods are always retrieved when query is issued.
You're coming across transactional isolation among distinct connections. This doesn't occur with sqlite :memory: because there's only one global connection in use in that case. insert().execute() is known as connectionless, implicit execution, which includes autocommit of statements immediately after they occur. Session.execute(table.insert()) is performing explicit execution using a Connection linked to the current transaction within the session, and there is no autocommit since the Session maintains a transaction. For some background, extremely early versions of SQLA had just one giant "connection per thread" for everything, and everything was autocommitting by default, which would be consistent with your expectation here. This mode of operation was abandoned almost immediately as it provided no control whatsoever over transaction scope. It can still be enabled through the use of the "threadlocal" engine and "autocommit=True" on Session, but I would strongly recommend against using such a scheme as its better to have an explicit transaction start/end cycle to an application, both for data consistency as well as performance. Relevant docs here: http://www.sqlalchemy.org/docs/core/connections.html#connectionless-execution-implicit-execution http://www.sqlalchemy.org/docs/orm/session.html#managing-transactions http://www.sqlalchemy.org/docs/orm/session.html#using-sql-expressions-with-sessions http://www.sqlalchemy.org/docs/dialects/sqlite.html#threading-behavior > > -- > 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. > <session_test.py> -- 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.
