On 19 Jul 2013, at 16:01, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Jul 19, 2013, at 9:53 AM, Ed Singleton <singleto...@gmail.com> wrote: >> 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. > > 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 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.