On 19 Jul 2013, at 16:01, Michael Bayer <[email protected]> wrote:
> On Jul 19, 2013, at 9:53 AM, Ed Singleton <[email protected]> wrote:
>> On 15 Jul 2013, at 18:11, Michael Bayer <[email protected]> wrote:
>>> On Jul 15, 2013, at 11:06 AM, Ed Singleton <[email protected]> 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.
>
> weird. that seems like a bug, I'd have to work out a self contained test
> script on that, do you think you could send me something short I could run
> that does that ? just some small mappings and a way to generate that query.
In creating it for you I realised I had made two stupid mistakes in my script.
`temptable.get_column(col)` was a custom function and I was passing in a column
instead of a column name. It was then returning `None`. Also I was passing in
the wrong column to the update values.
It also only seemed to happen with our `lastmodified` column which has an
`on_update` param. if I removed that column it also worked. Fixing any one of
those three things made it work.
A sample script is below (bear in mind that the script contains the two errors
and I wouldn't now expect it to work anyway).
Thanks for your help.
Ed
```
import datetime
import sqlalchemy as sa
from sqlalchemy import (String, Unicode, Integer,
DateTime, ForeignKey, Table, Column)
metadata = sa.MetaData()
email_table = Table("Email", metadata,
Column("id", Integer, primary_key=True),
Column("to_addr", Unicode(256), primary_key=True),
Column("subject", Unicode(256), nullable=False),
Column("lastmodifieddate", DateTime(), default=datetime.datetime.now,
onupdate=datetime.datetime.now)
)
email_archive_table = Table("EmailArchive", metadata,
Column("id", Integer, primary_key=True),
Column("to_addr", Unicode(256), primary_key=True),
Column("subject", Unicode(256), nullable=False),
Column("lastmodifieddate", DateTime(), default=datetime.datetime.now,
onupdate=datetime.datetime.now)
)
where_clause = sa.and_(
email_table.c.id==email_archive_table.c.id,
email_table.c.to_addr==email_archive_table.c.to_addr)
update_values = dict(
[(col, None) for col in email_archive_table.columns])
query = email_table.update().values(update_values)
query = query.where(where_clause)
print query
```
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
