<p>您确实不想使用字符串格式来包含值。通过SQL参数将其留给数据库API。</p>
<p>使用参数可以:</p>
<ul>
<li>让数据库有机会准备语句并重用查询计划以获得更好的性能。</li>
<li>省去了正确转义该值的麻烦(包括避免允许SQL转义和那些SQL注入攻击)。</li>
</ul>
<p>由于SQLLite<a href="http://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.execute" rel="noreferrer">supports named SQL parameters</a>,我将返回一个语句和一个带参数的字典:</p>
<pre><code>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
</code></pre>
<p>然后将<em>两个</em>传递到<code>cursor.execute()</code>:</p>
<pre><code>cursor.execute(*daily(host, day))
</code></pre>
<p>SQL生成变得复杂,您可能需要查看<a href="http://docs.sqlalchemy.org/en/latest/core/" rel="noreferrer">SQLAlchemy core</a>来代替生成。</p>
<p>例如,可以生成:</p>
<pre><code>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
</code></pre>
<p><code>query</code>对象可以应用其他过滤器,对其进行排序、分组、用作其他查询的子选择、联接并最终发送以执行,此时SQLAlchemy会将其转换为适合您连接到的特定数据库的SQL。</p>