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, self.to_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 " % self.to_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 += self.to_str (value)
sql += " where %s " % self.to_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" % (self.to_str (cols), self.table_name)
else:
sql = "select distinct %s from %s" % (self.to_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" % (self.to_str (cols), self.table_name, limit)
else:
sql = "select distinct %s from %s limit %s" % (self.to_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")
if asc:
sql = "order by %s from %s" % (self.to_str (value), self.table_name)
else:
sql = "order by %s from %s desc" % (self.to_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' and...]
"""
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)