In the process of upgrading from 0.4 to 0.5 I've come across a
troubling issue. With the following setup:
####################################################################################
from sqlalchemy import Table, Column, Integer, String, MetaData,
create_engine, ForeignKey
from sqlalchemy.orm import relation, sessionmaker, scoped_session
engine = create_engine('sqlite:///mystuff.sqlite', echo=True)
Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=engine))
metadata = MetaData()
mapper = Session.mapper
time_zones = Table('time_zones', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(35)))
contacts = Table('contacts', metadata,
Column('id', Integer, primary_key=True),
Column('display_as', String(35)),
Column('time_zone_id', Integer, ForeignKey
('time_zones.id')))
phone_numbers = Table('phone_numbers', metadata,
Column('id', Integer, primary_key=True),
Column('number', String(35)),
Column('contact_id', Integer, ForeignKey
('contacts.id')))
class TimeZone(object): pass
class Contact(object): pass
class PhoneNumber(object): pass
mapper(TimeZone, time_zones)
mapper(Contact, contacts, properties={
'time_zone': relation(TimeZone, backref='contacts'),
'phone_numbers': relation(PhoneNumber, backref='contact',
cascade='all, delete-orphan')
})
mapper(PhoneNumber, phone_numbers)
metadata.create_all(bind=engine)
##############################################################################
Under 0.4 the following code executes fine:
############
c = Contact(display_as='Fake, User')
c.time_zone = TimeZone.query.filter_by(name='Africa/Algiers').first()
ph = PhoneNumber(full='1234567890')
c.phone_numbers.append(ph)
Session.commit()
############
But under 0.5 I receive an orphaned object error (see below for full
output). I understand that the TimeZone query causes a flush in
between the creation of the Contact and of the PhoneNumber. Without
the flush in between (if the TimeZone query line is removed) SA 0.5 is
correctly able execute the sample script.
As per this thread (http://groups.google.com/group/sqlalchemy/
browse_thread/thread/6c71c61bc59223f?tvc=2) I see that a suggested
remedy is to change the relation to cascade='all' rather than
cascade='all, delete-orphan'.
I would prefer not to do this as it really does make no sense in this
case to have a PhoneNumber without a Contact. I could also set the
relation via 'ph.contact = contact' but I would prefer to not have to
comb all of our existing code for this new class of bug.
What doesn't make sense to me is why 0.4 was able to correctly delay
the insert of the new phone number record until after the query for
the collection and now 0.5 can't.
Thank you for the assistance
-Brad
Below is the full echo output of 0.4 and 0.5
0.4.8dev_r5095:
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
BEGIN
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
INSERT INTO contacts (display_as, time_zone_id) VALUES (?, ?)
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
['Fake, User', None]
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
SELECT time_zones.id AS time_zones_id, time_zones.name AS
time_zones_name FROM time_zones WHERE time_zones.name = ? ORDER BY
time_zones.oid LIMIT 1 OFFSET 0
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
['Africa/Algiers']
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
SELECT phone_numbers.id AS phone_numbers_id, phone_numbers.number AS
phone_numbers_number, phone_numbers.contact_id AS
phone_numbers_contact_id FROM phone_numbers WHERE
phone_numbers.contact_id = ? ORDER BY phone_numbers.oid
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70 [4]
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
INSERT INTO phone_numbers (number, contact_id) VALUES (?, ?)
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
[None, 4]
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
COMMIT
0.5.4p2:
sa_test.py:8: SADeprecationWarning: The 'transactional' argument to
sessionmaker() is deprecated; use autocommit=True|False instead.
Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=engine))
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
PRAGMA table_info("time_zones")
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
()
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
PRAGMA table_info("contacts")
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
()
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
PRAGMA table_info("phone_numbers")
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
()
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
BEGIN
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
INSERT INTO contacts (display_as, time_zone_id) VALUES (?, ?)
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
['Fake, User', None]
2009-07-02 18:54:41,674 INFO sqlalchemy.engine.base.Engine.0x...2f30
SELECT time_zones.id AS time_zones_id, time_zones.name AS
time_zones_name FROM time_zones WHERE time_zones.name = ? LIMIT 1
OFFSET 0
2009-07-02 18:54:41,674 INFO sqlalchemy.engine.base.Engine.0x...2f30
['Africa/Algiers']
Traceback (most recent call last):
File "sa_test.py", line 47, in <module>
c.phone_numbers.append(ph)
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\attributes.py", line 158,
in __get__
return self.impl.get(instance_state(instance), instance_dict
(instance))
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\attributes.py", line 374,
in get
value = callable_()
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\strategies.py", line 568,
in __call__
result = q.all()
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\query.py", line 1193, in a
ll
return list(self)
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\query.py", line 1286, in _
_iter__
self.session._autoflush()
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\session.py", line 899, in
_autoflush
self.flush()
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\session.py", line 1354, in
flush
self._flush(objects)
File "c:\program files\python\lib\site-packages\sqlalchemy-0.5.4p2-
py2.5.egg\sqlalchemy\orm\session.py", line 1414, in
_flush
mapperutil.state_str(state), path))
sqlalchemy.orm.exc.FlushError: Instance <PhoneNumber at 0xf8ae30> is
an unsaved, pending instance and is an orphan (is n
ot attached to any parent 'Contact' instance via that classes'
'phone_numbers' attribute)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
