[sqlalchemy] Is it safe to re-use a closed session?

['Brian Candler' via sqlalchemy]

The documentation  
doesn't seem to be 100% clear on this point: if you close() a session, can 
you continue to use it as if it were a fresh session?  (Unlike say a closed 
filehandle)

In other words, in a non-threaded application, is this a valid pattern?

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

def my_request():
# Each request uses the same session object but cleans it up at the end
try:

finally:
session.close()

As opposed to:

def my_request():
# Each request uses a separate session object
session = Session()
try:

finally:
session.close()

The reason for asking is that there are times where it would be more 
convenient to create a single session up-front and use it where required, 
closing at the end of each unit of work, than have to pass around a new 
session through multiple levels of function calls.

In particular, flask-sqlalchemy wants to create a single session (albeit a 
scoped session).  Hence if I want to use the same models in non-Flask code, 
I need to reference that session and just close it at the end of each unit 
of work, rather than create a fresh session each time.

Thanks,

Brian.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] When is session.close required?

['Brian Candler' via sqlalchemy]

Hello,

I have a question about when "close" should or should not be called on a 
session.

At http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html it 
says:

"When the transactional state is completed after a rollback or commit, the 
Session releases all Transaction and Connection resources, and goes back to 
the “begin” state, which will again invoke new Connection and Transaction 
objects as new requests to emit SQL statements are received."

>From this description, given that the session releases its resources, it 
seems that there's no need to call "close" explicitly on the session, 
whether or not you want to re-use the session object or not.  There is also 
some example code, which doesn't invoke session.close().  

session = Session()try:
...
# commit.  The pending changes above
# are flushed via flush(), the Transaction
# is committed, the Connection object closed
# and discarded, the underlying DBAPI connection
# returned to the connection pool.
session.commit()except:
# on rollback, the same closure of state
# as that of commit proceeds.
session.rollback()
raise


However, under 
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
 
there's a different example, this time explicitly calling close() on the 
session:

def run_my_program():
session = Session()
try:
ThingOne().go(session)
ThingTwo().go(session)

session.commit()
except:
session.rollback()
raise
finally:
session.close()


So my question is, what does session.close() do that commit/rollback does 
not?

It's also not entirely clear to me if a session object can be reused after 
it has been closed. 
 At 
http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.close
 
it says:

"If this session were created with autocommit=False, a new transaction is 
immediately begun. Note that this new transaction does not use any 
connection resources until they are first needed."

So it sounds to me like a closed session *can* be re-used.  Is that correct?

Related to this is calling scoped_session.remove. 
 At 
http://docs.sqlalchemy.org/en/latest/orm/contextual.html#unitofwork-contextual 
it says:

web request ends  -> # the registry is instructed to
 # remove the Session
 Session.remove()


As I understand it, this calls "close" on the underlying session *and* 
removes it from the registry, so you get a fresh Session next time. 
 However if the framework already does a commit/rollback, why not just 
allow the registry to retain the same session object?

Thanks,

Brian.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Reproducible oddity in with_for_update()

[Brian Candler]

I have an issue which I have boiled down to a full test case below. This 
test program reproduces the problem with both sqlalchemy 0.9.9 and 1.0.5, 
under python 2.7.6 and ubuntu 14.04, and PyMySQL-0.6.2.

There are a combination of circumstances:

1. After you rollback a session, touching any attribute of an object (even 
just accessing its id) causes the whole object to be re-read from the 
database. That's OK.
2. Reading the object again using a new query and with_for_update() 
generates a fresh query with SELECT .. FOR UPDATE. This is what I expect. 
It also correctly blocks if another client has the row locked.
3. However, once the query has completed, the data seen in the object 
appears to be the value read from the previous query, not the SELECT .. FOR 
UPDATE one.

In the test program, a database object is created with val=abc. Two 
threads both read the row under a lock, append X and write it back again. 
So the final answer should be abcXX, but in fact it's abcX.

Points to note:

- this has to be run on a proper database (I am using mysql). sqlite 
doesn't support SELECT .. FOR UPDATE.

- I have some workarounds. If instead of reading a new object I do 
db.refresh(v, 
lockmode=update) then all is fine. However I understood that the 
lockmode=string interface is being deprecated.

Similarly, if I discard the object using db.expire(v) before reading it 
again then it also works correctly. But in any case, I'd like to understand 
why it doesn't work to fetch the new object in the way I am, and I suspect 
a bug. Surely if SQLAlchemy has just issued a SELECT .. FOR UPDATE then the 
object should be updated with the values of that SELECT?

Regards,

Brian.

-
from __future__ import absolute_import, division, print_function, 
unicode_literals
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from contextlib import contextmanager
from six.moves.queue import Queue, Empty
from threading import Thread

DEFAULT_DB_URI = 'mysql+pymysql://root@localhost/testdb'

Base = declarative_base()

class Foo(Base):
__tablename__ = foo
id = Column(Integer, primary_key=True)
val = Column(String(255))

engine = create_engine(DEFAULT_DB_URI, echo=True)
try: Base.metadata.drop_all(engine)
except: pass
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

@contextmanager
def private_session():
s = Session()
try:
yield s
finally:
s.rollback()
s.close()

def runner(ref, omsg, imsg):
with private_session() as db:
print( Read object)
v = db.query(Foo).filter_by(id=ref).one()
print( Discard session)
db.rollback()
print( Get object's id)
id = v.id
print( Reload object with FOR UPDATE)
# db.expire(v)
v = db.query(Foo).filter_by(id=id).with_for_update().one()
# Alt: db.refresh(v, lockmode='update')
print( v.val=%r % v.val)
omsg.put(started)
imsg.get()
v.val += X
db.commit()

with private_session() as db:
f = Foo(id=1, val=abc)
db.add(f)
db.commit()

o1 = Queue()
i1 = Queue()
o2 = Queue()
i2 = Queue()

t1 = Thread(target=runner, kwargs={ref:1, omsg: o1, imsg: i1})
t2 = Thread(target=runner, kwargs={ref:1, omsg: o2, imsg: i2})

t1.start()
assert o1.get(True, 1) == started
# Next thread should block on SELECT FOR UPDATE
t2.start()
try:
o2.get(True, 1)
raise RuntimeError(This thread should be blocked on SELECT FOR 
UPDATE)
except Empty:
pass
# Let first thread complete
i1.put(go)
# Now second thread is unblocked
assert o2.get(True, 1) == started
i2.put(go)

t1.join(2)
assert not t1.isAlive()
t2.join(2)
assert not t2.isAlive()

# Check final state
print(*** FINISHED ***)
id = f.id
print(*** RESULTS ***)
print(id=%d % f.id)
print(val=%r % f.val)

Base.metadata.drop_all(engine)

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.