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?
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='j...@google.com'),
Address(email_address='j...@yahoo.com')]
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 = "f...@example.com"), ]
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("a...@a.com")
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="a...@a.com" 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 sqlalch...@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.
