pypika

前言

目的

  代码中的sql需要保证防注入，则参数需要独立传入。但一般情况下，就需要写死sql， 还要构造参数列表，比较麻烦。此库可以代码方式生成sql。

pypika是什么

  pypika是一个python的第三方库，用于生成sql语句。官方地址

使用

from pypika import MySQLQuery, Table, CustomFunction, Order
from pypika import functions as fn
from pypika.queries import QueryBuilder
from pypika.terms import Values

查

def gen_select_sql(from_table: str, cols: list,
                   where_condition: dict = None, sum_item: dict = None, group_by: list = None,
                   limit: int = None, offset: int = None, order_by: list = None):
    """

    :param from_table: 表名
    :type from_table: day_reports_2
    :param cols: 需要查询的字段，可不包含求和的字段
    :type cols: ['pg', 'ad_platform']
    :param where_condition: 查询条件，见gen_wheres()用法
    :type where_condition: {'ad_event': {'=': 'revenue'}, 'ad_platfomr': {'in': ['admob', 'fyber']}}
    :param sum_item: 需要求和的字段。{字段: 字段别名}
    :type sum_item: {'amount': 'amount'}
    :param group_by: 需分组的字段
    :type group_by: ['date']
    :param limit: 查询条数
    :type limit: 1
    :param offset:
    :type offset:
    :param order_by: 排序字段
    :type order_by: [('date', 'DESC')]
    :return: sql与值
    :rtype:
    """
    table = Table(from_table)

    # 查询的字段
    selects = []
    for col in cols:
        if col == '%*':
            selects.append(table.star)
        else:
            selects.append(table[col])

    q = MySQLQuery.from_(table).select(*selects)

    if sum_item:
        for key in sum_item:
            q = q.select(fn.Sum(table[key]).as_(sum_item[key]))

    # 查询条件
    where_value_list_all = []
    if where_condition:
        q, where_value_list_item = gen_wheres(table, q, where_condition)
        where_value_list_all += where_value_list_item

    if group_by:
        for item in group_by:
            q = q.groupby(table[item])

    if order_by is not None:
        for order in order_by:
            o = order[1]
            o = o.upper()
            if o == 'DESC':
                o = Order.desc
            else:
                o = Order.asc
            q = q.orderby(order[0], order=o)

    if limit:
        q = q.limit(limit)
    if offset:
        q = q.offset(offset)  # 分页步长

    sql_ = q.get_sql()
    sql_.replace('\'%s\'', '%s')
    return sql_, where_value_list_all


conditions = {'id': {'=': 1}}
sql, args = gen_select_sql('test_table', ['name', ], where_condition=conditions)

增

def gen_insert_sql(table: str, row: dict, update_cols: list = None):
    """
    插入语句
    :param table: 表名
    :param row: 单行数据
    :param update_cols: 主键相同时，需要更新的字段
    :return: 一条sql语句
    """
    customers = Table(table)

    cols, values, str_list = [], [], []
    for k, v in row.items():
        cols.append(k)
        values.append(v)
        str_list.append('%s')

    q = MySQLQuery.into(customers).columns(cols).insert(str_list)

    if update_cols:
        for item in update_cols:
            q = q.on_duplicate_key_update(
                customers[item],
                Values(customers[item])
            )

    sql_ = q.get_sql()
    sql = sql_.replace('\'%s\'', '%s')
    return sql, values


def gen_insert_sqls(table: str, row: list, update_cols: list = None):
    """
    插入语句
    :param table: 表名
    :param row: 需要插入的多行数据
    :param update_cols: 主键相同时，需要更新的字段
    :return: 一条sql语句，及对应的值
    """
    customers = Table(table)

    all_cols, values, all_args = [], [], []
    for _ in row:
        one_args = []
        if not all_cols:
            for k, v in _.items():
                all_cols.append(k)
                values.append(v)
                one_args.append('%s')
        else:
            for col in all_cols:
                values.append(_[col])
                one_args.append('%s')
        all_args.append(one_args)

    q = MySQLQuery.into(customers).columns(all_cols[0])

    for one_args in all_args:
        q = q.insert(one_args)

    if update_cols:
        for item in update_cols:
            q = q.on_duplicate_key_update(
                customers[item],
                Values(customers[item])
            )

    sql_ = q.get_sql()
    sql = sql_.replace('\'%s\'', '%s')
    return sql, values


row = {'name': 'abc', 'age': 18}
sql, args = gen_insert_sql('test_table', row, update_cols=['age'])

改

def gen_update_sql(table_name, obj: dict, conditions: dict):
    table = Table(table_name)

    q = MySQLQuery.update(table)
    args = []

    for f in obj:
        q = q.set(f, '%s')
        args.append(obj[f])

    q, args = gen_wheres(table, q, conditions, args)
    sql = q.get_sql().replace('\'%s\'', '%s')
    return sql, args


row = {'age': 20}
conditions = {'name': {'=': 'abc'}}
sql, args = gen_update_sql('test_table', row, conditions)

删

def gen_delete_sql(table_name, conditions: dict):
    table = Table(table_name)

    q = MySQLQuery.from_(table)

    q, args = gen_wheres(table, q, conditions)
    sql = q.delete().get_sql().replace('\'%s\'', '%s')
    return sql, args


conditions = {'name': {'=': 'abc'}}
sql, args = gen_delete_sql('tset_table', conditions)

条件语句

当前没有处理 或 条件

# {'date': {'>=': 'value1', '<=': 'value2'}, 'test1': {'=': 'value3'}, 'test2': {'in': []}}
def gen_wheres_part(table, conditions: dict, args: list = None):
    where_value_list = []
    if args is not None:
        where_value_list.extend(args)
    where_strs = []
    table_str = ''
    if table:
        table_str = f'`{table}`.'
    for field in conditions:
        condition_value = conditions[field]
        for op in condition_value:
            condition_op_value = condition_value[op]
            op = op.upper()
            match = False
            if op == '=':
                where_strs.append(f"{table_str}{field} = %s")
                match = True
                pass
            elif op == '>':
                where_strs.append(f"{table_str}{field} > %s")
                match = True
                pass
            elif op == '>=':
                where_strs.append(f"{table_str}{field} >= %s")
                match = True
                pass
            elif op == '<':
                where_strs.append(f"{table_str}{field} < %s")
                match = True
                pass
            elif op == '<=':
                where_strs.append(f"{table_str}{field} <= %s")
                match = True
                pass
            elif op == '!=' or op == '<>':
                where_strs.append(f"{table_str}{field} != %s")
                match = True
                pass
            elif op == 'LIKE':
                where_strs.append(f"{table_str}{field} like %s")
                match = True
                pass
            elif op == 'IN':
                condition_value_len = len(condition_op_value)
                if condition_value_len == 0:
                    condition_op_value = ['']
                    condition_value_len = len(condition_op_value)
                where_strs.append(f"{table_str}{field} IN ({','.join(['%s'] * condition_value_len)})")
                match = True
                pass
            elif op == 'NOT IN':
                condition_value_len = len(condition_op_value)
                if condition_value_len == 0:
                    condition_op_value = ['']
                    condition_value_len = len(condition_op_value)
                where_strs.append(f"{table_str}{field} NOT IN ({','.join(['%s'] * condition_value_len)})")
                match = True
                pass
            elif op == 'BETWEEN':
                condition_value_len = len(condition_op_value)
                where_strs.append(f"{table_str}`{field}` BETWEEN %s AND %s")
                match = True
                pass
            if match:
                if isinstance(condition_op_value, list):
                    where_value_list.extend(condition_op_value)
                else:
                    where_value_list.append(condition_op_value)
            pass

    return ' AND '.join(where_strs), where_value_list

其他

1. 一些复杂的函数，还可以通过CustomFunction方法来自定义。