Dynamic binding on WHERE for query string on sql python

Question

My query string in dict used to filter data on WHERE clause.

parameters = 
{
    "manufacuturerId": "1",
    "fileName": "abc1234 ",
    "categoryName": "normal"
}

And SQL query as:

fileSql = "select * from file_table as a 
           left join category_table as b 
               on a.fId = b.fId
           left join manufacturer_table as c
               on c.mId = a.mId
           where c.manufacturerId = %(manufacturerId)s and 
               a.file_name = %(fileName)s and 
               b.name = %(categoryName)s ;"

cursor.execute(fileSql,(parameters))

This works well to bind the value of dict to SQL query based on key using parametrized queries.

But this way is not flexible if my query string changed to

{
    "manufacuturerId": "1",
    "fileName": "abc1234 "
}

Then the code will die.

The only manufacuturerId is must and others key-value pair is optional to further filter.

How to optimize the code?

bruno desthuilliers · Accepted Answer · 2019-06-26 07:21:42Z

The simple obvious answer is to build your query dynamically, ie:

fileSql = """
    select * from file_table as a 
           left join category_table as b on a.fId = b.fId
           left join manufacturer_table as c on c.mId = a.mId
           where c.manufacturerId = %(manufacturerId)s 
    """

if  "fileName" in parameters:
    fileSql += " and a.file_name = %(fileName)s "
if "categoryName" in parameters:
    fileSql += " and b.name = %(categoryName)s "

Note that this is still not optimal since we keep the join on category_table even when we don't need it. This can be solved in a similar way by dynamically building the "from" clause too, and that's ok if you only have a couple such case in your project - but most often database-drievn apps require a lot of dynamic queries, and building them by hand using plain strings quickly becomes tedious and error-prone, so you may want to check what an ORM (Peewee comes to mind) can do for you.

Collectives™ on Stack Overflow

Dynamic binding on WHERE for query string on sql python

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