Re: [sqlalchemy] Question about how session works in SQLAlchemy

[Michael Bayer]

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

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

[sqlalchemy] Question about how session works in SQLAlchemy

[ammar azif]

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

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.pool as pool
import psycopg2

def getconn():
c = psycopg2.connect(user='postgres', host='127.0.0.1', database='session_test')
return c

engine = create_engine('postgresql+psycopg2://', creator=getconn)
Base = declarative_base()

class User(Base):
__tablename__ = 'users'

id = Column(Integer, primary_key=True)
name = Column(String)

def __init__(self, name):
self.name = name

Base.metadata.create_all(engine)
Base.metadata.bind = engine

Session = sessionmaker(bind=engine)
session = Session()

session.execute(User.__table__.insert().values(name='John'))
User.__table__.insert().values(name='Tim').execute()
# Only user Tim is shown
print User.__table__.select().execute().fetchall()
# Both user Tim and John are shown
print session.execute(User.__table__.select()).fetchall()
session.commit()