Get and Add WHERE clause in SQL Query Python

Question

I am coding in Python. I have query like this:

SELECT device_os, count(*) FROM warehouse WHERE device_year <= 2016 GROUP BY device_os;

Now, I have some additional filters which are coming dynamically from the user. For example,

device_id IN (15, 85, 65) OR device_model in ('MAX', 'SHARP', 'AD')

I have these extra conditions which I want to apply to the query. So, the final query should become:

SELECT device_os, count(*) FROM warehouse WHERE device_year <= 2016 AND (device_id IN (15, 85, 65) OR device_model in ('MAX', 'SHARP', 'AD')) GROUP BY device_os;

I have searched about sqlparse but has not been successful. How can I make it look like what I want?

You need to clarify the roadblock here, it's not clear why you can't just use the final query? ex. input sanitization, string formatting, etc. — ᴓᴓᴓ
– ᴓᴓᴓ, Commented Oct 12, 2021 at 22:33
@talfreds because the basic query is already saved somewhere, and I need to add extra filters in where clause which are coming dynamically. It is assured that the user does not enter any malicious input. The user does not have a direct control to the database. — Muhammad Muaaz
– Muhammad Muaaz, Commented Oct 12, 2021 at 22:38
I think you have the options of parsing the user text (or making it, field + list of values rather than an entire expression, then it should be easy), or using a limited user that only has SELECT ON db.warehouse privs (is that restricted enough?). — danblack
– danblack, Commented Oct 12, 2021 at 22:45

Toby Mao · Accepted Answer · 2021-10-25 05:42:01Z

2

I made a library called SQLGlot that makes this very easy.

import sqlglot
import sqlglot.expressions as exp

expression = sqlglot.parse_one("""
SELECT device_os, count(*) FROM warehouse WHERE device_year <= 2016 GROUP BY device_os
""") 

def transform(node):
    if isinstance(node, exp.Where):
        node.args["this"] = sqlglot.parse_one(f"({node.this.sql()}) AND device_model in (1,2,3)")
    return node

print(expression.transform(transform).sql(pretty=True))

# output
SELECT
  device_os,
  COUNT(*)
FROM warehouse
WHERE
  (device_year <= 2016)
  AND device_model IN (1, 2, 3)
GROUP BY
  device_os

answered Oct 25, 2021 at 5:42

Toby Mao

5944 silver badges11 bronze badges

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

Comments

Barmar · Accepted Answer · 2021-10-12 23:07:13Z

0

Define the SQL as a template format string, then substutute the dynamically generated conditions into it.

sql_format = 'SELECT device_os, count(*) FROM warehouse WHERE device_year <= 2016 AND ({}) GROUP BY device_os;'
where_clause = 'device_id IN (%s, %s, %s) OR device_model in (%s, %s, %s)'
sql = sql_format.format(where_clause)
params = [15, 85, 65, 'MAX', 'SHARP', 'AD']
cursor.execute(sql, params)

If you don't have any additional conditions, you can use

where_clause = '1=1'

This is a condition that's always true, so it doesn't change the result.

answered Oct 12, 2021 at 23:07

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

Get and Add WHERE clause in SQL Query Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related