On Jun 28, 2013, at 8:23 AM, RedBaron <dheeraj.gup...@gmail.com> wrote:
> But when I try to write it in SQLALchemy > > inner_q = > session.queryEvent.sid.label('sid'),Event.cid.label('cid')).options(lazyload('*')).join(Event.iphdr).filter(IpHdr.ip_dst==func.inet_aton("192.168.2.10")).subquery() > update_stmt = > tEvent.__table__.update().where(and_(inner_q_s.c.sid==Event.sid,inner_q_s.c.cid==Event.cid)).values({'is_deleted':True,}) > session.get_bind().execute(update_stmt) > > I get the correct statement but parameter order is wrong. From the debug > > 2013-06-28 17:49:53,999 INFO [sqlalchemy.engine.base.Engine][worker 4] > UPDATE event, (SELECT event.sid AS sid, event.cid AS cid FROM event LEFT > OUTER JOIN iphdr ON event.sid = iphdr.sid AND event.cid = iphdr.cid WHERE > event.is_deleted = false AND iphdr.ip_dst = inet_aton(%s)) AS anon_1 SET > event.is_deleted=%s WHERE anon_1.sid = event.sid AND anon_1.cid = event.cid > > 2013-06-28 17:49:54,000 INFO sqlalchemy.engine.base.Engine (1, '192.168.2.10') > As can be seen the order is reversed to what should ideally be there. > > In general, the update value is always the first and then all the search > parameters follow as per their order. > > Is this a bug or am I doing something wrong? I've created http://www.sqlalchemy.org/trac/ticket/2768 for this, and to understand what's going wrong one needs to appreciate that UPDATE.. against multiple tables is a non-standard syntax, where different backends put the second table in different places. this demo illustrates the issue: from sqlalchemy.sql import table, column, select from sqlalchemy.dialects import mysql t1 = table('t1', column('x')) t2 = table('t2', column('y'), column('z')) subq = select([t2]).where(t2.c.y == 7).alias() stmt = t1.update().values(x=5).where(t1.c.x == subq.c.z) compiled = stmt.compile(dialect=mysql.dialect()) # default impl, UPDATE..FROM . y follows x print stmt # mysql impl, UPDATE A, B, x follows y print compiled # but still getting y follows x print compiled.positiontup I don't have too great of a workaround here, in this case you can hardwire the IP number argument using literal_column: func.inet_aton(literal_column("'192.168.1.1'")) -- 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.