Conditionally adding several filters to a SQLAlchemy query without duplicating code

Question

I have a SQLAlchemy model:

class Ticket(db.Model):

 __tablename__ = 'ticket'

 id = db.Column(INTEGER(unsigned=True), primary_key=True, nullable=False,
 autoincrement=True)
 cluster = db.Column(db.VARCHAR(128))

 @classmethod
 def get(cls, cluster=None):
     query = db.session.query(Ticket)

     if cluster is not None:
         query = query.filter(Ticket.cluster==cluster)

     return query.one()

If I add a new column and would like to extend the get method, I have to add one if xxx is not None like this below:

 @classmethod
 def get(cls, cluster=None, user=None):
     query = db.session.query(Ticket)

     if cluster is not None:
         query = query.filter(Ticket.cluster==cluster)
     if user is not None:
         query = query.filter(Ticket.user==user)

     return query.one()

Is there any way I could make this more efficient? If I have too many columns, the get method would become so ugly.

BTW, I wouldn't call your old code inefficient. Unrolling loops is something compilers typically do as an optimization, after all. — Charles Duffy
– Charles Duffy, Commented Sep 20, 2020 at 14:25

Charles Duffy · Accepted Answer · 2020-09-20 22:21:52Z

3

As always, if you don't want to write something repetitive, use a loop:

@classmethod
def get(cls, **kwargs):
    query = db.session.query(Ticket)
    for k, v in kwargs.items():
        query = query.filter(getattr(table, k) == v)
    return query.one()

Because we're no longer setting the cluster=None/user=None as defaults (but instead depending on things that weren't specified by the caller simply never being added to kwargs), we no longer need to prevent filters for null values from being added: The only way a null value will end up in the argument list is if the user actually asked to search for a value of None; so this new code is able to honor that request should it ever take place.

If you prefer to retain the calling convention where cluster and user can be passed positionally (but the user can't search for a value of None), see the initial version of this answer.

edited Sep 20, 2020 at 22:21

answered Sep 20, 2020 at 14:19

Charles Duffy

299k43 gold badges441 silver badges497 bronze badges

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

Collectives™ on Stack Overflow

Conditionally adding several filters to a SQLAlchemy query without duplicating code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related