Hi,
I am using SQLAlchemy 0.6.4 with postgres db. I have two tables - users and
addresses tables with addresses table having a foreign key constraint
referencing the users table. Each address record is identified by a unique
constraint key 'email_address'.
In my test case, each user instance have a collection of addresses. For each
user instance, I want to delete every address instance in the addresses
collection that the user instance has and then add new address instances
(they may have the same unique key that the previously deleted address
instance had). The problem I am having now is that at the end of the flush
call, unique constraint error for 'email_address' from addresses table is
thrown even though delete operation is done earlier than insert. Looking at
the echo output, INSERTs are indeed done first than DELETEs. The work around
that I have now is to call flush() right after the deletion of address
instances in each user.
My question is - what is the precedence of insert, delete and update in
session flush? It would also be helpful if someone can explain the overview
of the mechanics of flushing in SQLAlchemy.
Attached is the python script that I wrote to understand why this problem
mentioned above happens.
Thanks.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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.ext.declarative import declarative_base
from sqlalchemy.schema import Column, UniqueConstraint, ForeignKeyConstraint
from sqlalchemy.types import String, Integer
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy import create_engine
engine = create_engine('postgres://postgres:data01@localhost:5432/flush_test')
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
__table_args__ = (
UniqueConstraint('name',
name='users_name_key'),
{})
id = Column(Integer, primary_key=True)
name = Column(String(20), nullable=False)
def __init__(self, name):
self.name = name
def __repr__(self):
return "<User('%s')>" % self.name
class Address(Base):
__tablename__ = 'addresses'
__table_args__ = (
UniqueConstraint('email_address',
name='addresses_email_address_key'),
ForeignKeyConstraint(['username'],
['users.name'],
onupdate='cascade'),
{}
)
id = Column(Integer, primary_key=True)
email_address = Column(String(20), nullable=False)
username = Column(String(20), nullable=False)
user = relationship(User, backref=backref('addresses', cascade='all, delete-orphan'))
def __init__(self, email_address):
self.email_address = email_address
def __repr__(self):
return "<Address('%s')>" % self.email_address
metadata = Base.metadata
metadata.create_all(engine)
Session = sessionmaker(bind=engine, autoflush=False)
session = Session()
session.rollback()
# begin init code that puts the persisted instance into the database
if session.query(User).filter_by(name='user1').count()==0:
user1 = User('user1')
user1.addresses.append(Address('us...@email.com'))
session.add(user1)
session.commit()
else:
user1=session.query(User).filter_by(name='user1').one()
if len(user1.addresses)==0:
user1.addresses.append(Address('us...@email.com'))
session.commit()
# end init code
engine.echo = True
for address in user1.addresses:
session.delete(address)
user1.addresses.append(Address('us...@email.com'))
# After the flush line below unique constraint error for address table is thrown,
# echo output shows that insertion of child item was done first before delete, although we call delete first.
# This does not happen if we flush after delete.
session.flush()
