Python MySQLdb query parameters "IS NULL" instead of "= NULL"

Question

I'm working on a Python script to import data to a MySQL database. I use MySQLdb to execute my queries.

I also use a function to set a query and its data with named parameters to be automatically replaced inside the request, such as:

req = "SELECT * FROM sample WHERE `id` = %(id)s and `param1` = %(param1)s"
data = {'id': 15, 'param1': None}
cursor.execute(req, data)

I really like this way because I'm using the same data dict for 3 or 4 queries.

The problem is when I try to make a WHERE condition with None value, it does replace my None by NULL but I would like it to be able to replace a `param1` = NULL by `param1` IS NULL, so that the condition evaluates to true.

Is there any way to solve this issue directly? Or can I use just the parameter replace (without executing the query), then do a replace on my own (= NULL to IS NULL), and then execute the query.

saaj · Accepted Answer · 2017-11-21 16:00:26Z

2

You can do something like the following.

def query(params):
    # Make sure it's hardcoded and malicious user can't overwrite it
    param_whitelist = 'id', 'param1'

    sql = '''
        SELECT * 
        FROM sample 
        WHERE {placeholders}
    '''
    placeholders = ' AND '.join(
        '`{field}` {op} %s'.format(field=k, op='IS' if params[k] is None else '=') 
        for k in param_whitelist)
    sql = sql.format(placeholders=placeholders)

    cursor.execute(sql, params)
    return cursor


data = {'id': 15, 'param1': None}
print([r for r in query(data)])

edited Nov 21, 2017 at 16:00

answered Nov 21, 2017 at 15:05

saaj

25.5k6 gold badges116 silver badges116 bronze badges

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

1 Comment

saaj Over a year ago

@m.nachury Initially I've pasted not the latest version of the snippet where param_whitelist is not used. It's fixed in the update.

Collectives™ on Stack Overflow

Python MySQLdb query parameters "IS NULL" instead of "= NULL"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related