动态SQL WHERE子句生成 [英] Dynamic SQL WHERE clause generation

问题描述

为了记录，我正在使用Python和SQLlite.我有一个可以生成所需SQL的工作函数，但它似乎不正确.

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.

还有更好的主意吗?

修改: 看起来不对的是，我是根据字符串动态构造SQL的.通常这不是最佳方法. SQL注入附件，需要正确地转义字符串.我不能使用占位符，因为其中一些值为None，并且不需要处于WHERE子句条件中.

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.

推荐答案

您真的不想使用字符串格式来包含值.通过SQL参数将其留给数据库API.

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

使用参数:

• 使数据库有机会准备语句并重用查询计划以提高性能.
• 省去适当地转义值(包括避免允许SQL转义和进行这些SQL注入攻击)的麻烦.

由于SQLLite 支持命名的SQL参数，我将同时返回一个语句和一个带有参数的字典:

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

然后将两者传递给cursor.execute():

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

SQL生成变得非常复杂快速，您可能需要查看使用SQLAlchemy核心代替.

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

例如，您可以生成:

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

query对象可以对其应用其他过滤器，进行排序，分组，用作其他查询的子选择，联接并最终发送以执行，此时SQLAlchemy会将其转换为适合您特定数据库的SQL正在连接.

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.

这篇关于动态SQL WHERE子句生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！