Hi,
I am using SQLAlchemy - 0.8.1
Background - I have a table "event" which has a flag 'is_deleted'. This
table has a composite primary key (sid,cid). There are many other tables
related to "event" that store information regarding that event - e.g.
"iphdr". I want to give the user an option of deleting all the events that
he searched for. So if a user searchers for all events with dst ip
192.168.2.3, he should be able to delete them.
class Event(Base):
__tablename__="event"
sid=Column(Integer,ForeignKey("sensor.sid"),primary_key=True)
cid=Column(mysql.MSInteger(unsigned=True),primary_key=True)
timestamp=Column(DateTime)
isdel=Column('is_deleted',Boolean,default=False,nullable =False)
iphdr=relationship("IpHdr",uselist=False,backref=backref("event",lazy=True),cascade="all,delete-orphan",lazy=True)
class IpHdr(Base):
__tablename__="iphdr"
sid=Column(Integer,primary_key=True)
cid=Column(mysql.MSInteger(unsigned=True),primary_key=True)
ip_src=Column(mysql.MSInteger(unsigned=True),nullable=False)
ip_dst=Column(mysql.MSInteger(unsigned=True),nullable=False)
__table_args__=(ForeignKeyConstraint(['sid','cid',],['event.sid','event.cid',],),)
Querying for events with IPHdr is done by
session.query(Event).join(Event.iphdr).filter(IpHdr.ip_dst==func.inet_aton("192.168.2.10"))
which works fine.
Now I want to be able to delete (set is_deleted =1) for all events which
belong to this ip
Equivalent MySQL expression
update event join (select event.sid,event.cid from event join iphdr on
event.sid=iphdr.sid and iphdr.cid=event.cid where
iphdr.ip_dst=inet_aton("192.168.2.10")) e on e.sid=event.sid and
e.cid=event.cid set event.is_deleted = 1;
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?
Regards,
Dheeraj
--
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.
