Querying a composite key in SQLAlchemy-ORM

Question

Background

I have a composite index defined on a SQLAlchemy object, say:

class Shirt(Base):
    __tablename__ = 'shirt'
    id = Column(Integer, primary_key=True)
    size = Column(String(32))   # e.g. small, medium large
    color = Column(String(32))  # e.g. blue, red, white

Index('color_size', Shirt.size, Shirt.color)

Question

I'd now like to do a search for small and red shirts, taking advantage of the color_size composite index.

How do I write this query?

Would the use of and_() automatically take advantage of the index?
For example:

results = Shirt.query.filter( and_(Shirt.size=='small', Shirt.color=='red') ).all()

BTW background research: I tried looking this up on SO this way and this way and Googled this but have not been able to find a suitable answer. — tohster
– tohster, Commented Mar 31, 2015 at 19:20

davidism · Accepted Answer · 2015-04-01 04:02:59Z

2

Yes, the index should be used. SQLAlchemy isn't using the index though, it only defines the index. It's up to the database to use it for a given query.

You can use EXPLAIN to prove that the index is being used. For example, PostgreSQL shows an Index Scan.

example => explain select id from shirt where color = 'red' and size = 'small';
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Index Scan using ix_shirt_size_color on shirt  (cost=0.15..8.17 rows=1 width=4)
   Index Cond: (((size)::text = 'small'::text) AND ((color)::text = 'red'::text))

answered Apr 1, 2015 at 4:02

davidism

128k31 gold badges417 silver badges348 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Caner · Accepted Answer · 2020-06-11 21:30:28Z

-1

Assuming your model class is called Shirt with (size, color) is the composite primary key:

You can query either with:

import sqlalchemy
...
Example.query.get((size, color))

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((size, color))

answered Jun 11, 2020 at 21:30

Caner

59.7k37 gold badges184 silver badges185 bronze badges

1 Comment

ncenerar Over a year ago

Ok for primary keys but what about index (like the one in the OP question)?

Collectives™ on Stack Overflow

Querying a composite key in SQLAlchemy-ORM

Background

Question

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background

Question

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related