Hello Michael
Sorry for the late reply.
So, below is my full stack trace:
Traceback (most recent call last):
File
"/Users/foobar/Developpement/nursery_project/applications/nurserydb/utils_scripts/test2.py",
line 64, in <module>
plant.taxon = taxon_new # triggers an IntegrityError
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py",
line 303, in __set__
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py",
line 804, in set
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py",
line 824, in fire_replace_event
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py",
line 1131, in emit_backref_from_scalar_set_event
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py",
line 638, in append
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py",
line 788, in set
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py",
line 613, in get
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/strategies.py",
line 524, in _load_for_state
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/strategies.py",
line 585, in _emit_lazyload
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/query.py", line
2104, in all
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/query.py", line
2215, in __iter__
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py", line
1138, in _autoflush
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py", line
1817, in flush
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py", line
1935, in _flush
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/util/langhelpers.py",
line 58, in __exit__
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py", line
1899, in _flush
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/unitofwork.py",
line 372, in execute
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/unitofwork.py",
line 525, in execute
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/persistence.py",
line 58, in save_obj
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/persistence.py",
line 491, in _emit_update_statements
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py", line
662, in execute
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py", line
761, in _execute_clauseelement
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py", line
874, in _execute_context
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py", line
1024, in _handle_dbapi_exception
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/util/compat.py", line
163, in raise_from_cause
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py", line
867, in _execute_context
File "build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/default.py",
line 324, in do_execute
sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column
"taxon_id" violates not-null constraint
'UPDATE botany.plant SET taxon_id=%(taxon_id)s WHERE botany.plant.id =
%(botany_plant_id)s' {'taxon_id': None, 'botany_plant_id': -2147483643}
I guess it correspond to what you mentioned.
I have tried to use what you told :
with session.no_autoflush:
plant.taxon = taxon_new
...and it works fine.
But the thing is that I don't need it in case I comment out the line :
taxon_old = session.query(Taxon).get(-2147483634)
and the test:
print plant.taxon is taxon_old # True
It seems that the problems occurs because the taxon_old is already loaded
in the identity map so the taxon.plant of the already loaded taxon_old must
be changed to reflect the change made by the below line:
plant.taxon = taxon_new # triggers an IntegrityError
This is totally understandable.
However, I get confused by SQLAlchemy trying to set taxon_id to NULL or
even worse, by SQLAlchemy trying to delete the plant if I configure a
"cascade='all, delete-orphan'" on the backref side of the relationship. I
understand that it might not be "easy" for SQLAlchemy to understand what I
am trying to do, but the fact that I end up with two different results
depending on whether or not taxon_old is previously loaded is confusing.
Anyway, SQLAlchemy is still very impressive and never ceases to amaze me
every day I discover new features. Thank you for that.
Le mardi 4 juin 2013 16:38:30 UTC+2, Etienne Rouxel a écrit :
>
> 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.
