【Python】单表多功能查询的SQL封装

前言

因为最近业务问题，面临大批量的条件查询、排序、分页、字段筛选，所以用原生sql写了这么一个封装。

适用范围

• 前端查询：包括分页、排序、条件、字段筛选。
• 后端查询：单表查询直接使用。
• 数据库查询：自动生成SQL语句，可直接使用。

功能以及接口使用

参数介绍

• ‘condition’:[]
  解释：参数由列表输入：[‘name@like:小明’,year@>:16] '@‘和’：‘为固定格式，后台代码用其进行通配符，@前为字段名，’:'后为内容。包含 !=,=,>,>=,<,<=,in,notin,like。
• ‘orderby’:[]
  解释：参数由列表输入：['date@desc,‘name@asc’] @为固定格式。
• ‘exclude’:[]
  解释：参数由列表输入：[‘remark’,‘name’] 数据为不包含的字段名。
• ‘page’:int
  解释：参数为页数，类型为整型。
• ‘pagesize’:int
  解释：参数为每页的大小，类型为整型。

注意：不需要的字段可以不给

源码及使用说明

sqlcomment.py:封装的SQL代码

import re

def querycomment(param:dict,tablename:str,cursor,cursorclass):
    '''
    :param param:
    :param tablename:
    :param cursor:
    :param cursorclass:
    :return: sql,sql_count
    '''
    sql = f'select * from {tablename}'
    if v := param.get('exclude'):
        if isinstance(v,list) and len(v) != 0:
            tup = tuple(v)
            if len(tup) == 1:
                tup = str(tup).replace(',','')
            str_ = f"CONCAT(' select ',GROUP_CONCAT(COLUMN_NAME),' from ', '{tablename}',' ;')"
            include_col = f"SELECT {str_} FROM information_schema.COLUMNS WHERE table_name = '{tablename}' and COLUMN_NAME != {tup}"

            cursor.execute(include_col)
            if cursorclass == 'DictCursor':
                sql = cursor.fetchone()[str_].replace(';', '')
            elif cursorclass == 'Cursor':
                sql = cursor.fetchone()[0].replace(';','')


    if condition:=param.get('condition'):
        print(condition)
        if isinstance(condition,list) and len(condition) != 0:

            condi_sql = ''
            for c in condition:
                re_content = re.findall("(.*?)@(.*?):(.*?)$", c.replace(' ', ''))[0]
                try:
                    tuple_ = tuple(eval(re_content[2]))
                except:
                    tuple_ = re_content[2]

                if re_content[1] == 'notin':
                    if 'where' in condi_sql:
                        condi_sql += f' and {re_content[0]} not in {tuple_}'
                    else:
                        condi_sql += f' where {re_content[0]} not in {tuple_}'
                elif re_content[1] == 'like':
                    if 'where' in condi_sql:
                        condi_sql += f' and {re_content[0]} like "%{re_content[2]}%"'
                    else:
                        condi_sql += f' where {re_content[0]} like "%{re_content[2]}%"'
                elif re_content[1] == 'in':
                    if 'where' in condi_sql:
                        condi_sql += f' and {re_content[0]} in {tuple_}'
                    else:
                        condi_sql += f' where {re_content[0]} in {tuple_}'
                else:
                    if 'where' in condi_sql:
                        condi_sql += f" and {re_content[0]} {re_content[1]} '{re_content[2]}'"
                    else:
                        condi_sql += f" where {re_content[0]} {re_content[1]} '{re_content[2]}'"
            sql += condi_sql
    find_col = re.findall(r'select (.*?) from', sql)[0]
    sql_count = sql.replace(find_col, 'count(*)')

    if orderby := param.get('orderby',[]):
        if orderby != []:
            order_sql = ' order by '
            flag = False
            for order in orderby:
                re_content = re.findall("(.*?)@(.*?)$", order.replace(' ', ''))[0]
                if flag:
                    order_sql += f',{re_content[0]} {re_content[1]}'
                    continue
                order_sql += f'{re_content[0]} {re_content[1]}'
                flag = True
            sql += order_sql
    page, pagesize = param.get('page', 1), param.get('pagesize', 20)
    start = (page-1)*pagesize
    sql += f' limit {pagesize} offset {start}'

    return sql,sql_count

test:案例使用
解析：querycomment函数中，cursor为游标；cursorclass为游标类别：用于获取字典或者数组，pymysql可以通过db.cursorclass.__name__获取，但其他并不一定，故在querycomment的第四个参数cursorclass枚举两个值分别为Cursor、DictCursor。若使用其他连接工具，记得更改哦

from sqlcomment.sql import querycomment

def db_connect():
    db = pymysql.connect(
        host=host,
        port=port,
        user=user,
        passwd=passwd,
        db=database,
        charset='utf8',
        cursorclass = pymysql.cursors.DictCursor
    )
    cursor = db.cursor()
    return db,cursor

param = {'exclude':['url'],
      'condition':['area@notin:("绵竹市","德阳市")','date@>:2023-01-01'],
      'orderby':['date@desc','title@desc'],
      'page':2,
      'pagesize':10
      }

dbname = 'dy'
db,cursor = db_connect()
cursorclass = db.cursorclass.__name__

sql,sql_count = querycomment(param,dbname,cursor,cursorclass)
_,data = cursor.execute(sql),cursor.fetchall()
_,count = cursor.execute(sql_count),cursor.fetchone()

sql,sql_count 返回的是sql语句，需要在返回之后手动执行一次获取数据，这样设计的原因是可能有时候在业务需求上我们还需要对sql语句进一步编写，如联表查询等。其中data是查询回来的数据，count是符合条件的总数据 这样的话有助于前端进行分页操作

whl文件获取

此模块属于抽象部分，故将其打包成whl文件。可直接pip install后使用，安装后也可在lib中查看源码
链接：https://pan.baidu.com/s/1b1rp993bHPyp-J81HUBsxQ
提取码：3u8t

结语

我这里给经常写原生查询SQL的同学一些向导，祝大家学有所成。