On Aug 31, 2010, at 1:36 AM, Russell Warren wrote: > I've been through the orm tutorial several times now and the employee- >> addresses one-to-many example is a good one. > > I'm stumped on one thing so far. Say I want to add a new address for > a user where the user name is "wendy". I only know her name and new > email address. And I want it done in one query. Is this possible and > how can I do it?
It sounds like you're looking for INSERT from SELECT. SQL certainly allows this and you can build SQLAlchemy expression constructs that are similar, there's a mini example at http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#compiling-sub-elements-of-a-custom-expression-construct . In this case: INSERT into email_addresses (user_id, email_address) ( SELECT id, '[email protected]' from users where name='wendy') INSERT from SELECT is useful when you're writing database migrations - a new set of tables is to receive the records from the old tables en masse. For very large databases, while it might take days to fetch all the rows into a client application and re-INSERT them outwards, a migration script would use INSERT from SELECT so that the operation takes place within the database process space and be much more efficient. INSERT from SELECT is a bulk operation so is not really something an ORM concerns itself with. > > I've replicated an entire example below, based on the orm tutorial. > Questions and examples are in the code. > > --- > > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy import Table, Column, Integer, String, MetaData, > ForeignKey > from sqlalchemy import create_engine > from sqlalchemy.orm import relationship, backref > from sqlalchemy import func > > engine = create_engine('sqlite:///:memory:', echo=True) > metadata = MetaData() > > Base = declarative_base() > class User(Base): > __tablename__ = 'users' > > id = Column(Integer, primary_key=True) > name = Column(String) > fullname = Column(String) > password = Column(String) > > def __init__(self, name, fullname, password): > self.name = name > self.fullname = fullname > self.password = password > > def __repr__(self): > return "<User('%s','%s', '%s')>" % (self.name, self.fullname, > self.password) > > class Address(Base): > __tablename__ = 'addresses' > id = Column(Integer, primary_key=True) > email_address = Column(String, nullable=False) > user_id = Column(Integer, ForeignKey('users.id')) > > user = relationship(User, backref=backref('addresses', > order_by=id)) > > def __init__(self, email_address): > self.email_address = email_address > > def __repr__(self): > return "<Address('%s')>" % self.email_address > > Base.metadata.create_all(engine) > > from sqlalchemy.orm import sessionmaker > Session = sessionmaker(bind=engine) > sess = Session() > sess.add_all([ > User('ed', 'Ed Jones', 'edpassword'), > User('wendy', 'Wendy Williams', 'foobar'), > User('mary', 'Mary Contrary', 'xxg527'), > User('fred', 'Fred Flinstone', 'blah')]) > > jack = User('jack', 'Jack Bean', 'gjffdd') > jack.addresses = [Address(email_address='[email protected]'), > Address(email_address='[email protected]')] > sess.add(jack) > sess.commit() > > ### > #Now to try add email addresses one by one to a user (in unique > sessions)... > ### > > #Below is an example of how I can make it work easily (with 2 > queries)... > s2 = Session() > fred = s2.query(User).filter(User.name == "fred").one() > fred.addresses += [Address(email_address = "[email protected]"), ] > s2.commit() > > #Creating Address and directly assigning the exact user_id works, but > only if > #I know the id directly (which I usually won't)... > s3 = Session() > a = Address("[email protected]") > a.user_id = 4 # fred.id == 4 (not so useful) > s3.add(a) > s3.commit() > > #Now I want to give "wendy" an email address in one nice query... > how?!? > --- > > I've tried many formulations of this and just can't figure it out. > I've tried various combos of joins and some subquery formulations but > with no luck. > > I confess that I don't know the to formulate this insert in SQL either > (which would presumably help me do it in sqla), but it sure seems like > it should be possible. If it were an update, I would try some SQL > like "update address set email="[email protected]" where name in (select name > from employees)"... or something like that, anyways... but that has > not helped me solve the single-query insert issue in SQLA. > > If the appropriate orm combo does exist, please let me know! I'm > trying to be more efficient with SQLA as I've realized I have > query.one() and query.all() calls all over the place that are cranking > up my query count. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
