CODEWITHSUNDEEP
pythonsqlite
ben121
ben121

Reputation: 897

SQLite WHERE statement which is empty

I have a table in SQLite. As expected, I can access to this with a WHERE variable.

import sqlite3

name = 'john'
age = 16

conn = sqlite3.connect('AmApp.db')
sql = """SELECT * FROM clients WHERE first_name='{}' AND age={}""".format(name, age)

df = pd.read_sql_query(sql, conn)
print (df)

However, if either name or age was empty then this does not work. Whilst this can be worked around this manually, my intention is that age and name are user inputs. Therefore how would I set this so that the WHERE statements do not filter is there is nothing to filter on?

Upvotes: 0

Views: 226

Answers (2)

Stef
Stef

Reputation: 30679

A standard way to create dynamic sql queries are case statements, although the query becomes a bit hard to read:

import pandas as pd
import sqlite3

conn = sqlite3.connect("DataSource=:memory:;Version=3;New=True;")
create_table=""" CREATE TABLE IF NOT EXISTS clients (
                                        id integer PRIMARY KEY,
                                        first_name text NOT NULL,
                                        age integer
                                    )"""
c = conn.cursor()
c.execute(create_table)
c.execute("insert into clients(id,first_name,age) values(1,'john',16)")
c.execute("insert into clients(id,first_name,age) values(2,'tom',20)")
c.execute("insert into clients(id,first_name,age) values(3,'tom',16)")

name = 'john'
age = 16

sql = f"""SELECT * FROM clients WHERE 
          case when '{name}' = '' then 1 else first_name end = case when '{name}' = '' then 1 else '{name}' end AND
          case when {age} = 0 then 1 else age end = case when {age} = 0 then 1 else {age} end"""


df = pd.read_sql_query(sql, conn)
print(df)

Examples:

name = 'john'
age = 16

   id first_name  age
0   1       john   16



name = ''
age = 16

   id first_name  age
0   1       john   16
1   3        tom   16



name = 'tom'
age = 0

   id first_name  age
0   2        tom   20
1   3        tom   16



name = ''
age = 0

   id first_name  age
0   1       john   16
1   2        tom   20
2   3        tom   16

Although you said that you don't want to work around this manually, it may be easier to build the query string manually depending on the selection parameters:

sql = "SELECT * FROM clients WHERE 1"
if name != '':
    sql += f" AND first_name='{name}'"
if age > 0:
    sql += f" AND age={age}"

Upvotes: 0

forpas
forpas

Reputation: 164214

One way of doing this is to introduce 2 new parameters, one for each of the name and age, that denote whether that column will be filtered or not, combined with the operator OR with the corresponding column:

sql = """SELECT * FROM clients WHERE (first_name='{}' OR 0={}) AND (age={} OR 0={})""".format(name, filterbyname, age, filterbyage)

These new parameters will have values 0 or 1.
Examples:
Both values 1 and valid values for name and age:

filterbyname = 1
name = 'john'
filterbyage = 1
age = 16

For filterbyname the value 1 and valid value for name and for filterbyage the value 0 and any vale for age (filter only by name):

filterbyname = 1
name = 'john'
filterbyage = 0
age = 0

For filterbyage the value 1 and valid value for age and for filterbyname the value 0 and any vale for name (filter only by age):

filterbyname = 0
name = ''
filterbyage = 1
age = 25

If you want to return all the rows of the table without filtering:

filterbyname = 0
name = ''
filterbyage = 0
age = 0

Upvotes: 1

Related Questions