Awesome! This is exactly what I needed, thanks Mike.
On Thu, Apr 10, 2014 at 10:17 PM, Michael Bayer <[email protected]>wrote: > we do UPDATE..FROM but getting the VALUES thing in there requires some > extra recipes as we don't have that structure built in right now. also > the alias part of it where it names out the columns in the AS portion is > not natively built in either. The recipe is here: > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PGValues. At > some point I showed someone the AS part, but that's not in there....OK I've > just updated it, it also needed help to work in the UPDATE context. So: > > from sqlalchemy.ext.compiler import compilesfrom sqlalchemy.sql.expression > import FromClause > class values(FromClause): > named_with_column = True > > def __init__(self, columns, *args, **kw): > self._column_args = columns > self.list = args > self.alias_name = self.name = kw.pop('alias_name', None) > > def _populate_column_collection(self): > for c in self._column_args: > c._make_proxy(self) > > @compiles(values)def compile_values(element, compiler, asfrom=False, **kw): > columns = element.columns > v = "VALUES %s" % ", ".join( > "(%s)" % ", ".join( > compiler.render_literal_value(elem, column.type) > for elem, column in zip(tup, columns)) > for tup in element.list > ) > if asfrom: > if element.alias_name: > v = "(%s) AS %s (%s)" % (v, element.alias_name, (", ".join(c.name > for c in element.columns))) > else: > v = "(%s)" % v > return v > if __name__ == '__main__': > from sqlalchemy import MetaData, create_engine, String, Integer, Table, > Column > from sqlalchemy.sql import column > from sqlalchemy.orm import Session, mapper > m1 = MetaData() > class T(object): > pass > t1 = Table('mytable', m1, Column('mykey', Integer, primary_key=True), > Column('mytext', String), > Column('myint', Integer)) > mapper(T, t1) > t2 = values( > [ > column('mykey', Integer), > column('mytext', String), > column('myint', Integer) > ], > > > (1, 'textA', 99), > (2, 'textB', 88), > > alias_name='myvalues' > ) > e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) > m1.create_all(e) > sess = Session(e) > sess.query(T).filter(T.mykey==t2.c.mykey).\ > update(dict(mytext=t2.c.mytext, myint=t2.c.myint)) > > > > > > > > > On Apr 11, 2014, at 12:01 AM, Joe Dallago <[email protected]> wrote: > > I want to write a query like so with SQLAlchemy: > > UPDATE mytable > SET > mytext = myvalues.mytext, > myint = myvalues.myint > FROM ( > VALUES > (1, 'textA', 99), > (2, 'textB', 88), > ...) AS myvalues (mykey, mytext, myint) > WHERE mytable.mykey = myvalues.mykey > > Is this kind of thing supported natively in the ORM? Or will I need to use > session.execute() to run raw SQL? > > Relevant SO thread: > http://stackoverflow.com/questions/23002086/does-sqlalchemy-support-update-from-syntax-for-postgres > . > > -- > 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/d/optout. > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/T0TgNUtkgn0/unsubscribe. > To unsubscribe from this group and all its topics, 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/d/optout. > -- Joe Dallago clusterFlunk.com <http://clusterflunk.com/> *C*: 3192135077 -- 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/d/optout.
