On Aug 31, 2010, at 11:40 AM, Russell Warren wrote:
> On Aug 31, 10:33 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> 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
>> athttp://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=....
>> In this case:
>>
>> INSERT into email_addresses (user_id, email_address) (
>> SELECT id, 'we...@foo.com' 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.
>
> Thanks for clarifying and for the example... it explains why I
> couldn't figure out how to do it with the ORM.
>
> So if you were creating a simple "AddUserEmail" function you would do
> it as in my two-query example below?
>
> AddUserEmail(UserName, NewEmail):
> sess = Session()
> newAddress = Address(email_address = NewEmail)
> user = sess.query(User).filter_by(name = UserName).one()
> user.addresses.append(newAddress)
> sess.commit()
>
> I'm content with that if that is the "best" way. It is just the
> annoying and ever present optimization demon sitting on my shoulder
> wanting me to cut that down to one query, but I can shush him (I
> think).
Yes the above way is the most straightforward for most use cases and allows one
to work with the ORM naturally, instead of tinkering with foreign key
attributes. Although you should probably look into having a longer spanning
transaction than just within one function like that.
However I forgot one other approach that's also overkill for the general case,
but is more useful with the ORM, which is that you can embed subqueries and
other expressions into a flush. I usually use it with UPDATE statements when I
need to do something like increment a counter atomically, which is the more
important use case. But here you could use it with your INSERT:
from sqlalchemy import select
session.add(
Address(
user_id=select([User.id]).where(User.name=='wendy').as_scalar(),
email_address='we...@yahoo.com'
)
)
Optimizing flushes is a use case that usually applies to large data updates.
For small operations its not worth it. The main strategy to optimizing a
large update is to get into memory all the things you need to construct your
new data en-masse, rather than using ad-hoc functions that emit individual
SELECTs for each segment of information.
--
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.
