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. > > 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. > > -- 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.
