If I:
1) start a session (sess1)
2) store some data to sess1 without commit
3) start a second session and link to uncommited data from sess1
4) do a rollback on sess1
I'm finding that the rollback does absolutely nothing - the sess1 data
persists after the rollback. It is making me not trust rollbacks!
I suspect this is not sqlalchemy that is at fault, but just in case
I've included a contrived example at the bottom of this post.
<assuming you check the code...>
Is this purely a weird sqlite concurrency/locking thing? I'm not 100%
clear what behaviour I want/expect, but at this point I'm thinking
that the address attach to 'jack' should have failed because 'jack'
was not actually committed yet. Otherwise it gets confusing what to
do about sess3 being invalid after sess3 is already done and over
with.
Jumping ahead with some assumptions... if managing this "properly" is
something to do with transactions that I'm simply not yet aware of
(I'm not a DBA), is there a DBMS agnostic way through sqlalchemy to
enable "proper" transaction management?
####
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship, backref
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData()
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String, unique = True)
def __init__(self, name):
self.name = name
def __repr__(self):
return "User('%s')" % self.name
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship(User, backref=backref('addresses',
order_by=id))
def __init__(self, email_address, UserId = None):
self.email_address = email_address
if UserId is not None:
self.user_id = UserId
def __repr__(self):
return "<Address('%s')>" % self.email_address
Base.metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
sess = Session()
sess.add_all([User('ed'), User('fred')])
sess.commit()
#sess2 will add a new user (jack), but we'll roll it back in the end
sess2 = Session()
jack = User('jack')
jack.addresses = [Address(email_address='j...@example.com'),
Address(email_address='j...@example.org')]
sess2.add(jack)
sess2.flush() #no commit
jack_id = jack.id #flush gets us the id without committing
#Now leave sess2 open without commit for a bit while we link to it in
sess3
#sess3 will create an address linked to jack (before he gets rolled
back)
sess3 = Session()
addr3 = Address("h...@hmm.com", jack_id) #but this id will be rolled
back
# adding to jack in another session succeeds even though jack is not
commited...
sess3.add(addr3)
sess3.commit()
#Now rollback sess2, which should get rid of jack... but doesn't??
sess2.rollback()
print Session().query(User).all()
####
--
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.
