Python SQL multiple nullable parameters

Question

Suppose we have a table employees which contains name, employee_id and citizen_id. We want to filter the table using also 3 python variables name, employee_id and citizen_id for the WHERE clause. Example:

name = 'John'
citizen_id = 'ID001'
employee_id = 'EMP023'

The condition is those 3 variables are NULLABLE, one or two of them can be NULL but at least one variable are not. How we create a sql query that allow one/two of that 3 variables in the WHERE clause?

('''SELECT 
    *
FROM
    people
WHERE
    name = '{0}'
OR citizen_id = '{1}'
OR employee_id = '{2}'''').format(name, citizen_id, employee_id)

Barbaros Özhan · Accepted Answer · 2022-05-20 06:30:43Z

1

You can distinguish the cases by adding

.. AND <theRespectiveParameter> IS NOT NULL) OR <theRespectiveParameter> IS NULL for each,

and change the currently stated operators between them from OR to AND considering parametrized query techniques for Spark SQL such as

Q1 = spark.sql(('''
                  SELECT *
                    FROM people
                   WHERE ((name = {0} AND {0} IS NOT NULL) OR {0} IS NULL)
                     AND ((citizen_id = {1} AND {1} IS NOT NULL) OR {1} IS NULL)
                     AND ((employee_id = {2} AND {2} IS NOT NULL) OR {2} IS NULL)
                ''').format(name, citizen_id, employee_id)

or another option would be

params  = {"name":name,               # based on current parameter names
           "citizen_id":citizen_id,
           "employee_id":employee_id}
           
Q1 = spark.sql(('''
                  SELECT *
                    FROM people
                   WHERE ((name = {name} AND {name} IS NOT NULL) OR {name} IS NULL)
                     AND ((citizen_id = {citizen_id} AND {citizen_id} IS NOT NULL) OR {citizen_id} IS NULL)
                     AND ((employee_id = {employee_id} AND {employee_id} IS NOT NULL) OR {employee_id} IS NULL)
                ''').format(**params)

getting rid of quotes for both cases

edited May 20, 2022 at 6:30

answered May 19, 2022 at 13:49

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

4 Comments

AMR Over a year ago

the name, citizen_id, employee_id after OR are the table column name?

Barbaros Özhan Over a year ago

Good catch @AMR , needed to replace them with parameter names. Btw, what's your DBMS?

AMR Over a year ago

not all DBMS able to do parameter on the left side of equation. I am using SparkSQL so it cannot be done through your solution.

Barbaros Özhan Over a year ago

@AMR Check out as a future reference please : Why should I "tag my RDBMS"?

Collectives™ on Stack Overflow

Python SQL multiple nullable parameters

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related