I am trying to efficiently update all things that foreign key to a
particular record so that they instead foreign key to a different record. I
provided an example that illustrates the problem I am trying to solve.
Please see my question at the bottom of the code.
Thanks for your help,
Michael
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
backref
Base = declarative_base()
Session = scoped_session(sessionmaker())
def init_model(dsn):
engine = create_engine(dsn)
Session.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(50))
addresses = relationship("Address", backref="person",
cascade="all", passive_updates=False)
vehicles = relationship("Vehicle", backref="person",
cascade="all", passive_updates=False)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String(50))
person_id = Column(Integer, ForeignKey('person.id'))
#backref: person
def __repr__(self):
return 'id: %s, person_id: %s, email: %s' % \
(self.id, self.person_id, self.email)
class Vehicle(Base):
__tablename__ = 'vehicle'
id = Column(Integer, primary_key=True)
color = Column(String)
kind = Column(String)
person_id = Column(Integer, ForeignKey('person.id'))
#backref: person
def __repr__(self):
return 'id: %s, person_id: %s, kind: %s, color: %s' % \
(self.id, self.person_id, self.kind, self.color)
init_model('sqlite:///:memory:')
s = Session()
s.add_all([Person(name='Mike',
addresses=[Address(email='mi...@mike.com'),
Address(email='mi...@mike.com')],
vehicles=[Vehicle(color='red', kind='truck'),
Vehicle(color='white', kind='van')]),
Person(name='Steve',
addresses=[Address(email='ste...@steve.com')],
vehicles=[Vehicle(color='orange', kind='car')])])
mike = s.query(Person).filter_by(name='Mike').one()
'''
I can easily change the person_id value of everything that had a FK to mike
by changing mike.id:
'''
mike.id = 50
for vehicle in s.query(Vehicle).all():
print vehicle.person_id
for address in s.query(Address).all():
print address.person_id
'''
Question:
What would I do if I wanted to take all the things that FK to Mike, and
then FK them to Steve instead?
Not possible to do mike.id = steve.id because primary key must be unique.
Of course in this example I could issue separate updates to Address and
Vehicle which would not be too bad,
but in my real project there are dozens of other classes besides Address
and Vehicle. Is there a more efficient way besides
separate updates?
Thanks,
Michael
'''
--
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.
