Dynamic SQL queries with SQLite3-CodePudding

I would like to allow users to query a sql database. The database is here

So the user will be able to enter the queries they want:

csr_city= input("Enter a city >>").lower().strip().replace(' ','')
csr_type = input("Enter a type >>").lower().strip()

and then the query will execute:

cur = conn.cursor()
cur.execute('SELECT * FROM crime_scene_report WHERE city=?  AND type=? ', (csr_city,csr_type))
rows = cur.fetchall()
rows

If the user enters both variables like city='SQL City' and type='murder' it works as it finds both values in the same row, but if the user leaves one empty, ex type, then it returns a blank table as both conditions do not exist on one single row.

What I would like to do is for SELECT to ignore the variable if it is empty. I guess I could do it with if statements but that would create a mess?? Is there a better way?

I tried How to ignore the condition that user did not pass in SQLITE?, but didnt work for me, still getting empty tables.

CodePudding user response：

You're effectively looking for an SQL query builder (such as SQLAlchemy's core layer), but to begin with, some ifs are just fine:

csr_city = input("Enter a city >>").lower().strip().replace(" ", "")
csr_type = input("Enter a type >>").lower().strip()

cur = conn.cursor()

sql_where = []
args = []
if csr_city:
    sql_where.append("city = ?")
    args.append(csr_city)
if csr_type:
    sql_where.append("type = ?")
    args.append(csr_type)

if not sql_where:
    sql_where.append("1 = 1")  # always true

sql_query = f'SELECT * FROM crime_scene_report WHERE {" AND ".join(sql_where)}'
cur.execute(sql_query, args)
rows = cur.fetchall()