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.