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.
