In SQLAlchemy, how do I query composite primary keys?

user_1492322 0
Viewed 7236

I'm using SQLAlchemy to programmatically query a table with a composite foreign key. e.g.:

CREATE TABLE example (
id INT NOT NULL,
date TIMESTAMP NOT NULL,
data VARCHAR(128)
PRIMARY KEY (id, date)
)

I'd like to take a list of values and get rows back e.g.:

interesting_data = (
    (1, '2016-5-1'), 
    (1, '2016-6-1'),
    (2, '2016-6-1'),
    (3, '2016-5-1'),
    (3, '2016-6-1'),
)
select(
    [example.c.id, example.c.date, example.c.data],
).where(example.primary_key.in_(interesting_data)

If each column was independent I could do 

interesting_ids = [1,2,3]
interesting_dates = ['2016-5-1', '2016-6-1']
select(
    [example.c.id, example.c.date, example.c.data],
).where(
    example.c.id.in_(interesting_ids)
).where(
    example.c.date.in_(interesting_dates)
)

But that clearly fails to bring only the unique matching (id, date) tuples. I suspect there is a way to specify the compound primary key to query, but I can't find any documentation after a search.

compound-key sqlalchemy sql python
2 Answers

Assuming your model class is called Example with (id, date) is the composite primary key:

You can query either with:

import sqlalchemy
...
Example.query.get((id, date))

or 

import sqlalchemy
from sqlalchemy.orm import sessionmaker
...
engine = sqlalchemy.create_engine('postgresql://user:pass@localhost/db_name')
session = sessionmaker(bind=engine)()
session.query(Example).get((id, date))
user_4486250
Related