[sqlalchemy] Inequality filtering on a composite primary key

[Gerald Thibault]

I have this simple class declaration:

class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    sub_id = Column(Integer, primary_key=True)

I'm trying to figure out how to get sqlalchemy to emit this sql:

SELECT * FROM items
WHERE (id, sub_id) > (10,3)

but I'm unable to do it. I tried this:

query = session.query(Item) \
        .filter((Item.id,Item.sub_id) > (10,3))

but the resulting sql completely omits the second column:

SELECT items.id AS items_id, items.sub_id AS items_sub_id 
FROM items 
WHERE items.id > ?

Does that second column/value actually get stored anywhere, or are they 
being silently discarded?

Is there any way to have sqlalchemy generate the desired sql above, or do i 
need to do some silliness like 'id > 10 OR id==10 and sub_id > 3'? I've got 
a composite primary key on those 2 columns, it seems like I should be able 
to take advantage of that.

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



import sys
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import create_session


e = create_engine('sqlite:////tmp/foo.db', echo=True)
Base = declarative_base(bind=e)

class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    sub_id = Column(Integer, primary_key=True)

if __name__ == '__main__':
    Base.metadata.drop_all()
    Base.metadata.create_all()

    session = create_session(bind=e, autocommit=False)    

    q = session.query(Item) \
        .filter((Item.id,Item.sub_id) > (0,0))
    print str(q)