On 15 Jul 2013, at 18:11, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Jul 15, 2013, at 11:06 AM, Ed Singleton <singleto...@gmail.com> wrote:
>
>> I have two tables in SQLAlchemy which are identical and I want to update one
>> from the other where the rows have the same primary key, and I want to do it
>> in an efficient way.
>>
>> I tried joining the tables on the primary key, but SQLAlchemy doesn't appear
>> to support updates on joined tables at the moment (except using a subquery
>> for every column, which was too inefficient).
>
> SQLAlchemy supports this for those backends which also do, SQL Server is
> included, you just wouldn't use the JOIN keyword, instead use an implicit
> join. Example:
>
> addresses.update().
> values(email_address=users.c.name).
> where(users.c.id == addresses.c.user_id).
> where(users.c.name == 'ed')
Thanks for this. I've got this down to the following:
def do_update(basetable, temptable):
key_names = basetable.get_primary_key_names()
cols = temptable.columns
where_clause = sa.and_(
*[getattr(basetable.c, key)==getattr(temptable.c, key) for key in
key_names])
update_values = dict(
[(col, temptable.get_column(col)) for col in cols])
query = basetable.update().values(update_values)
query = query.where(where_clause)
return query
(where get_primary_key_names is a custom function that just returns the primary
key names)
Unfortunately, I get the following error:
CompileError: bindparam() name 'lastmodifieddate' is reserved for automatic
usage in the VALUES or SET clause of this insert/update statement. Please use
a name other than column name when using bindparam() with insert() or update()
(for example, 'b_lastmodifieddate').
I can't quite work out what to do with the bindparams as I'm not really using
any. Any clues as to what I'm missing would be gratefully received.
Thanks again
Ed
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
