[sqlalchemy] Changing a value linked with a one-to-one relationship
Hello
I would like to change a value in a one-to-one relationship but I cannot
because of some actions that SQLAlchemy try to do, and I don't know why.
Here is my simplified code :
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_taxon_table = Table('taxon', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_foliagetype_table = Table('foliagetype', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_plant_table = Table('plant', Base.metadata,
Column('id', Integer, primary_key=True),
Column('taxon_id', Integer),
Column('foliagetype_id', Integer),
ForeignKeyConstraint(['taxon_id'], ['botany.taxon.id']),
ForeignKeyConstraint(['foliagetype_id'], ['botany.foliagetype.id']),
schema='botany'
)
class Taxon(Base):
__table__ = _taxon_table
class Foliagetype(Base):
__table__ = _foliagetype_table
class Plant(Base):
__table__ = _plant_table
taxon = relationship('Taxon',
backref=backref('plant',
uselist=False))
foliagetype = relationship('Foliagetype',
backref=backref('plants'))
if __name__ == '__main__':
engine = create_engine('postgresql://xxx@localhost:5432/xxx')
Session = sessionmaker(bind=engine)
session = Session()
taxon_old = session.query(Taxon).get(-2147483634)
taxon_new = session.query(Taxon).get(-2147483645)
foliagetype_old = session.query(Foliagetype).get(-2147483646)
foliagetype_new = session.query(Foliagetype).get(-2147483645)
plant = session.query(Plant).get(-2147483643)
print -- Change foliagetype --
print plant.foliagetype is foliagetype_old # True
plant.foliagetype = foliagetype_new
print plant.foliagetype is foliagetype_new # True
print -- Change taxon --
print plant.taxon is taxon_old # True
plant.taxon = taxon_new # triggers an IntegrityError
print plant.taxon is taxon_new
So a plant must have one and exactly one foliagetype and same with the the
taxon : a plant must have one and exactly one taxon. The difference
however, is that a foliagetype can be linked to several plants while a
taxon can only be linked to a single plant.
In my code above the change of foliagetype leads SQLAlchemy to output the
expected SQL :
UPDATE botany.plant SET foliagetype_id= -2147483645 WHERE botany.plant.id =
-2147483643
However, concerning the change of taxon, I don't understand what SQLAlchemy
is doing. It first output:
SELECT botany.plant.id AS botany_plant_id, botany.plant.taxon_id AS
botany_plant_taxon_id, botany.plant.foliagetype_id AS
botany_plant_foliagetype_id
FROM botany.plant
WHERE -2147483634 = botany.plant.taxon_id
and then:
UPDATE botany.plant SET taxon_id=NULL WHERE botany.plant.id = -2147483643
which fails because NULL is not allowed for taxon_id. So, why do not
SQLAlchemy just output this instead?
UPDATE botany.plant SET taxon_id= -2147483645 WHERE botany.plant.id =
-2147483643
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Changing a value linked with a one-to-one relationship
On Jun 4, 2013, at 10:38 AM, Etienne Rouxel rouxel.etie...@gmail.com wrote: and then: UPDATE botany.plant SET taxon_id=NULL WHERE botany.plant.id = -2147483643 which fails because NULL is not allowed for taxon_id. So, why do not SQLAlchemy just output this instead? UPDATE botany.plant SET taxon_id= -2147483645 WHERE botany.plant.id = -2147483643 if you look in your stack trace, you'll see the word autoflush (I adapted your excerpt into a full test case to reproduce): Traceback (most recent call last): File test.py, line 74, in module plant.taxon = taxon_new # triggers an IntegrityError File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 303, in __set__ ... File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 824, in fire_replace_event value = fn(state, value, previous, initiator or self) File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 1131, in emit_backref_from_scalar_set_event passive=PASSIVE_NO_FETCH) File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 638, in append ... File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py, line 585, in _emit_lazyload result = q.all() File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py, line 2219, in all return list(self) File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py, line 2330, in __iter__ self.session._autoflush() File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py, line 1139, in _autoflush self.flush() you can see that the ORM is not getting as far as taxon_new being fully assigned. The assignment is resulting in the backref firing off and leading to an assignment on the Taxon.plant side.The assignment then tries to load the existing value for Taxon.plant, which results in an autoflush, which results in the integrity error. There seems to be some unfortunate effect going on, as the issue of backrefs loading collections like this was long ago resolved to defer that activity, but it appears that when uselist=False is present, this logic no longer takes place. Ticket http://www.sqlalchemy.org/trac/ticket/2741 has been added to look into this as this should not be happening. Anytime a stack trace has autoflush in it where you don't want autoflush to occur, defer autoflush for those operations like this: with session.no_autoflush: plant.taxon = taxon_new session.flush() # optional, emits the UPDATE you're looking for sooner rather than later print plant.taxon is taxon_new -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Changing a value linked with a one-to-one relationship
On Jun 4, 2013, at 11:15 AM, Michael Bayer mike...@zzzcomputing.com wrote: There seems to be some unfortunate effect going on, as the issue of backrefs loading collections like this was long ago resolved to defer that activity, but it appears that when uselist=False is present, this logic no longer takes place. Ticket http://www.sqlalchemy.org/trac/ticket/2741 has been added to look into this as this should not be happening. OK I've looked into it, and it actually should be happening, because the difference between uselist=True and uselist=False is that when setting a uselist=False, the *old* value must be removed, so we have to load the old value in order to reflect that in the history. So when we say: plant.taxon = taxon_new SQLAlchemy must load taxon.plant on taxon_new to get the previous plant value that's present, if any, so that it can be de-associated with taxon_new as taxon.plant is uselist=False. This can't be determined with taxon_new alone - the database must be scanned for taxon_new's identity in the plant table. So by default this will also autoflush, so here you just want to temporarily disable autoflush. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
