python - How to write multi column in clause with sqlalchemy

Question

Please suggest is there way to write query multi-column in clause using SQLAlchemy?

Here is example of the actual query:

SELECT  url FROM pages WHERE (url_crc, url) IN ((2752937066, 'http://members.aye.net/~gharris/blog/'), (3799762538, 'http://www.coxandforkum.com/'));

I have a table that has two columns primary key and I'm hoping to avoid adding one more key just to be used as an index.

PS I'm using mysql DB.

Update: This query will be used for batch processing - so I would need to put few hundreds pairs into the in clause. With IN clause approach I hope to know fixed limit of how many pairs I can stick into one query. Like Oracle has 1000 enum limit by default.

Using AND/OR combination might be limited by the length of the query in chars. Which would be variable and less predictable.

Answer 1

Assuming that you have your model defined in Page, here's an example using tuple_:

keys = [
    (2752937066, 'http://members.aye.net/~gharris/blog/'),
    (3799762538, 'http://www.coxandforkum.com/')
]

select([
    Page.url
]).select_from(
    Page
).where(
    tuple_(Page.url_crc, Page.url).in_(keys)
)

Or, using the query API:

session.query(Page.url).filter(tuple_(Page.url_crc, Page.url).in_(keys))