python - Dynamic SQL WHERE clause generation

Question

For the record, I'm using Python and SQLlite. I have a working function that generates the SQL I need, but it does not seem right.

def daily(self, host=None, day=None):
    sql = "SELECT * FROM daily WHERE 1"
    if host:
        sql += " AND host = '%s'" % (host,)
    if day:
        sql += " AND day = '%s'" % (day,)
    return sql

I will probably need to add multiple columns and criteria later on.

Any better ideas?

Edit: What does not look right is that I am constructing the SQL dynamically from Strings. This is generally not the best approach. SQL injections attacs, need to properly escape strings. I cannot use placeholders because some of the values are None and do not need to be in the WHERE clause condition.

Martijn Pieters Martijn Pieters · Accepted Answer · 2013-03-19T10:04:21

You really do not want to use string formatting to include values. Leave that to the database API via SQL parameters.

Using parameters you:

give the database a chance to prepare the statement and reuse the query plan for better performance.
save yourself the headache of escaping the value properly (including avoiding allowing SQL escapes and with those SQL injection attacks).

Since SQLLite supports named SQL parameters, I'd return both a statement and a dictionary with parameters:

def daily(self, host=None, day=None):
    sql = "SELECT * FROM daily"
    where = []
    params = {}
    if host is not None:
        where.append("host = :host")
        params['host'] = host
    if day is not None:
        where.append("day = :day")
        params['day'] = day
    if where:
        sql = '{} WHERE {}'.format(sql, ' AND '.join(where))
    return sql, params

then pass both to cursor.execute():

cursor.execute(*daily(host, day))

SQL generation becomes complex fast, you may want to look at SQLAlchemy core to do the generation instead.

For your example, you can generate:

from sqlalchemy import Table, Column, Integer, String, Date, MetaData

metadata = MetaData()
daily = Table('daily', metadata, 
    Column('id', Integer, primary_key=True),
    Column('host', String),
    Column('day', Date),
)
from sqlalchemy.sql import select

def daily(self, host=None, day=None):
    query = select([daily])
    if host is not None:
        query = query.where(daily.c.host == host)
    if day is not None:
        query = query.where(daily.c.day == day)
    return query

The query object can have additional filters applied to it, ordered, grouped, used as a subselect to other queries, joined and finally sent to be executed at which point SQLAlchemy will turn this into SQL fit for the specific database you are connecting to.

python - Dynamic SQL WHERE clause generation

2 Answers