python拼接sql语句_【Python】拼接MySQL常⽤语句import pymysql
class MK_sql ():
"""
构建mySQL常见语句：增删改查排序
"""
def __init__(self, table_name):
self.table_name = table_name
def mk_create(self, value: list):
"""
构建mySQL创建表语句
@param value: 字段列表
@return:
"""
if not isinstance (value, list):
raise Exception ("TypeError")
sql = "create table %s (%s)" % (self.table_name, _str (value))
return sql
def mk_delete(self, condition: dict):
"""
构建mySQL删除指定字段语句
@param condition: 删除条件，dict型
@return:
"""
if not isinstance (condition, dict):
raise Exception ("TypeError")
sql = "delete from %s" % self.table_name
sql += " where %s " % _str_and (condition)
return sql
def mk_insert(self, value: list):
"""
构建mySQL插⼊字段语句
@param value: 需插⼊的整⾏数据
@return:
"""
if not isinstance (value, list):
raise Exception ("TypeError")
sql = "insert into %s values " % self.table_name
sql += str (value)
return sql
def mk_update(self, value: dict, condition: dict):
"""
构建mySQL更新字段语句
@param value: dict 要更新的字段
@param condition: dict SQL条件语句
@return:
"""
if not isinstance (value, dict) or not isinstance (condition, dict):
raise Exception ("TypeError")
sql = "update %s set " % self.table_name
sql += _str (value)
sql += " where %s " % _str_and (condition)
return sql
def mk_select(self, cols=None, limit=None, distinct=False):
"""
构建mySQL select查询语句
@param cols: list型，要查询的字段名称
@param limit: int型
@param distinct: bool型，True：去重，只作⽤于⾮全部查询
@return:
"""
if (cols != None and not isinstance (cols, list)) or \
(limit !=None and not isinstance (limit, int)) or not isinstance(distinct,bool): raise Exception ("TypeError")
if not limit:
if not cols:
sql = "select * from %s" % self.table_name
else:
if not distinct:
sql = "select %s from %s" % (_str (cols), self.table_name)
else:
sql = "select distinct %s from %s" % (_str (cols), self.table_name)
return sql
else:
if not isinstance (limit, int):
raise Exception ("TypeError")
if not cols:
sql = "select * from %s limit %s" % (self.table_name, limit)
else:
if not distinct:
sql = "select %s from %s limit %s" % (_str (cols), self.table_name, limit)
else:
sql = "select distinct %s from %s limit %s" % (_str (cols), self.table_name, limit) return sql
def mk_order_by(self, value: list, asc=True):
"""
构建mySQL排序语句
@param value: 单个字段或多个字段
@param asc: 默认为True升序，FALSE为降序
@return:
"""
if not isinstance (value, list) or not isinstance(asc,bool):
raise Exception ("TypeError")import语句
if asc:
sql = "order by %s from %s" % (_str (value), self.table_name)
else:
sql = "order by %s from %s desc" % (_str (value), self.table_name)
return sql
def to_str_and(self, value: dict):
"""
内部函数，⽤于转换mySQL带and条件语句
@param value:dict型
@return: [key='value' and key='value' ]
"""
info_list = ["%s='%s'" % (str (k), str (v)) for k, v in value.items ()]
return " and ".join (info_list)
def to_str(self, value):
"""
内部函数，转换成SQL指定的str格式
@param value: list/dict
@return: 1、list参数不带引号的str，2、dict参数返回[key='value', key='value'...] """
if isinstance (value, list):
cnt = ["%s" % value for value in value]
return ",".join ((cnt))
elif isinstance (value, dict):
cnt = ["%s='%s'" % (str (k), str (v)) for k, v in value.items ()]
return ",".join (cnt)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。