On Feb 6, 2012, at 12:39 PM, Michael Naber wrote:
> 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?
the ORM-centric way to move the FK on these items, though not necessarily the
"all in one UPDATE statement" way, is to just move them at the Python level:
steve.addresses = mike.addresses
steve.vehicles = mike.vehicles
Session.commit()
if you want to emit an UPDATE for all FKs at once and skip the unit of work,
the SQL UPDATE statement supports this, you can get at it in terms of ORM
objects using query.update():
session.query(Address).with_parent(mike).update({"person_id":50})
session.query(Vehicle).with_parent(mike).update({"person_id":50})
The above two statements will try to search through the session to find all
objects already present which are affected, in order to refresh them. If you
just need to emit the two UPDATE statements and don't need to worry about any
Address or Vehicle objects that may have been affected, you can pass the flag
"synchronize_session=False" to the update() call - saves some time.
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query.update#sqlalchemy.orm.query.Query.update
--
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.
