def decorated_function(*args, **kwargs):
newargs = []
# 先转义参数，再执行方法
for arg in args:
# 字符串，包括中文
if type(arg) is types.StringType or type(arg) is types.UnicodeType:
newargs.append(pymysql.escape_string(arg))
# 字典
elif isinstance(arg, dict):
newargs.append(
pymysql.escape_dict(
arg, {
types.StringType: _str_escape,
types.UnicodeType: _str_escape,
types.IntType: _no_escape,
types.FloatType: _no_escape
}))
# 其他类型不转义
else:
newargs.append(arg)
newargs = tuple(newargs)
func = f(*newargs, **kwargs)
return func
def get_s_sql(self,
table,
keys,
conditions,
isdistinct=0,
limit=None,
order_by=None):
'''
生成select的sql语句
@table，查询记录的表名
@key，需要查询的字段
@conditions,插入的数据，字典
@isdistinct,查询的数据是否不重复
'''
if isdistinct:
sql = 'select distinct %s ' % ",".join(keys)
else:
sql = 'select %s ' % ",".join(keys)
sql += ' from %s ' % table
if conditions:
sql += ' where %s ' % self.dict_2_str_and(conditions)
if limit:
if isinstance(limit, int):
sql += ' limit %s ' % limit
elif isinstance(limit, list) or isinstance(limit, tuple):
sql += ' limit %s,%s ' % limit
if order_by:
order_by = pymysql.escape_dict(order_by, 'utf8')
tmp = ''
for k, v in order_by.items():
tmp += "%s,%s " % (str(k), str(v))
sql += ' order by %s' % tmp
return sql
def __get_model_data(self, model):
"""Returns filtered model's data.
:param model: PyAR sql model.
:type model: ASQLModel
:rtype: dict
"""
columns = self.__get_columns(model)
return dict((key, value) for key, value in pymysql.escape_dict(model.get_data(False), "'").items()
if key in columns)
def __get_model_data(self, model):
"""Returns filtered model's data.
:param model: PyAR sql model.
:type model: ASQLModel
:rtype: dict
"""
columns = self.__get_columns(model)
return dict((key, value) for key, value in pymysql.escape_dict(
model.get_data(False), "'").items() if key in columns)
def insert(self, table, value):
"""
:param: value: {key: value, ...} or [{key: value,...}, {key: value,...}, ...]
"""
all_fields = self.fields(table)
if not all_fields:
log.info('func:insert|all_fields get fail')
return False, None
if not isinstance(value, list):
value = [value]
number = 0
for one_data in value:
if set(one_data.keys()) - all_fields:
log.error(
'func:insert|table:{}|value:{}|info:fields not in table'.
format(table, value))
return False, None
keys = []
values = []
for key in one_data:
keys.append('`{}`'.format(key))
values.append('{{{}}}'.format(key))
values = ','.join(values)
keys = ','.join(keys)
sql = r'''insert into `{}` ({}) values ({});'''.format(
table, keys, values)
sql = sql.format_map(
pymysql.escape_dict(one_data, self.conn.charset))
try:
if len(sql) > 200:
log.info(
'func:insert|sql:insert into `{}` ({}) value (...)'.
format(table, keys))
else:
log.info('func:insert|sql:{}'.format(sql))
number += self.cur.execute(sql)
except Exception:
log.error(traceback.format_exc())
self.rollback()
break
else:
if self.auto_commit:
self.commit()
return True, number
return False, None
def update(self, table, values, *, where):
"""param: values: {}
"""
all_fields = self.fields(table)
if not all_fields:
return False, None
setsql = []
for key, value in values.items():
if value == None:
setsql.append('`{}`=null'.format(key))
else:
setsql.append('`{0}`= {{{0}}}'.format(key))
setsql = ','.join(setsql)
whsql = self.where(where, all_fields)
if whsql:
sql = 'update `{}` set {} where {};'.format(table, setsql, whsql)
else:
log.error(
'func:update|where:{}|info:where check fail'.format(where))
return False, None
sql = sql.format_map(pymysql.escape_dict(values, self.conn.charset))
if len(sql) > 200:
log.info('func:update|sql:update `{}` set ... where {};'.format(
table, whsql))
else:
log.info('func:update|sql:{}'.format(sql))
try:
data = self.cur.execute(sql, values)
except:
self.rollback()
log.error(traceback.format_exc())
return False, None
else:
log.info('update number is: {}'.format(data))
if self.auto_commit:
self.commit()
return True, data
def read(self, model_cls, select=None, joins=None, where=None, having=None, limit=None, offset=None, distinct=False,
group=None, order=None, params=dict(), query=None, **kwargs):
"""Build SQL query and execute it.
:param model_cls: PyAR model class.
:type model_cls: IModel
:param select: Allows to specify query field. Representing the SELECT-part of the SQL statement.
:type select: str
:param joins: Allows to specify row sql joins. Representing the JOIN-part of the SQL statement.
:type joins: str
:param where: Allows to specify query conditions. Representing the WHERE-part of the SQL statement.
:type where: str
:param having: Allows to specify GROUP BY conditions. Representing the HAVING-part of the SQL statement.
:type having: str
:param limit: Allows to specify number of records. Representing the LIMIT-part of the SQL statement.
:type limit: int
:param offset: Allows to specify position of the beginning rows.
Representing the OFFSET-part of the SQL statement.
:type offset: int
:param distinct: Allows to specify grabbing a single record per unique value in a certain field.
Representing the DISTINCT-part of the SQL statement.
:type distinct: bool
:param group: Allows to specify grouping of result. Representing the "GROUP BY"-part of the SQL statement.
:type group: str
:param order: Allows to specify ordering of result. Representing the "ORDER BY"-part of the SQL statement.
:type order: str
:param params: Allows to specify query parameters.
In other words it replaces every ":key" of builded query by value.
:type params: dict
:param query: Allows to specify full sql query.
:type query: str
:param kwargs: Allows to specify query conditions with AND statement.
Representing the WHERE-part of the SQL statement.
:type kwargs: dict
:rtype: list
"""
super().read(model_cls, **kwargs)
if query is None:
query = 'SELECT' \
'%s' \
'%s' \
'FROM %s' \
'%s' \
'%s' \
'%s' \
'%s' \
'%s' \
'%s' % (
' DISTINCT' if distinct else '',
' %s ' % select if select is not None else ' %s.* ' % model_cls.get_resource(),
model_cls.get_resource(),
' ' + joins if joins is not None else '',
self.__build_read_where(where, model_cls.get_resource(), kwargs),
' GROUP BY ' + group if group is not None else '',
' HAVING ' + having if having is not None else '',
' LIMIT ' + str(limit) if limit is not None else '',
' OFFSET ' + str(offset) if offset is not None else '',
)
params.update(kwargs)
if len(params):
params = pymysql.escape_dict(params, "'")
pattern = re.compile('|'.join([':' + key for key in params.keys()]))
query = pattern.sub(lambda x: params[x.group()[1:]], query)
cursor = self.execute(query, pymysql.cursors.DictCursor)
result = [model_cls(row, False) for row in cursor]
return result
def escape_dict(value):
from pymysql import escape_dict
from pymysql.charset import charset_by_id
charset = charset_by_id(224) # utf8mb4 utf8mb4_unicode_ci
return escape_dict(value, charset)
def read(self,
model_cls,
select=None,
joins=None,
where=None,
having=None,
limit=None,
offset=None,
distinct=False,
group=None,
order=None,
params=dict(),
query=None,
**kwargs):
"""Build SQL query and execute it.
:param model_cls: PyAR model class.
:type model_cls: IModel
:param select: Allows to specify query field. Representing the SELECT-part of the SQL statement.
:type select: str
:param joins: Allows to specify row sql joins. Representing the JOIN-part of the SQL statement.
:type joins: str
:param where: Allows to specify query conditions. Representing the WHERE-part of the SQL statement.
:type where: str
:param having: Allows to specify GROUP BY conditions. Representing the HAVING-part of the SQL statement.
:type having: str
:param limit: Allows to specify number of records. Representing the LIMIT-part of the SQL statement.
:type limit: int
:param offset: Allows to specify position of the beginning rows.
Representing the OFFSET-part of the SQL statement.
:type offset: int
:param distinct: Allows to specify grabbing a single record per unique value in a certain field.
Representing the DISTINCT-part of the SQL statement.
:type distinct: bool
:param group: Allows to specify grouping of result. Representing the "GROUP BY"-part of the SQL statement.
:type group: str
:param order: Allows to specify ordering of result. Representing the "ORDER BY"-part of the SQL statement.
:type order: str
:param params: Allows to specify query parameters.
In other words it replaces every ":key" of builded query by value.
:type params: dict
:param query: Allows to specify full sql query.
:type query: str
:param kwargs: Allows to specify query conditions with AND statement.
Representing the WHERE-part of the SQL statement.
:type kwargs: dict
:rtype: list
"""
super().read(model_cls, **kwargs)
if query is None:
query = 'SELECT' \
'%s' \
'%s' \
'FROM %s' \
'%s' \
'%s' \
'%s' \
'%s' \
'%s' \
'%s' % (
' DISTINCT' if distinct else '',
' %s ' % select if select is not None else ' %s.* ' % model_cls.get_resource(),
model_cls.get_resource(),
' ' + joins if joins is not None else '',
self.__build_read_where(where, model_cls.get_resource(), kwargs),
' GROUP BY ' + group if group is not None else '',
' HAVING ' + having if having is not None else '',
' LIMIT ' + str(limit) if limit is not None else '',
' OFFSET ' + str(offset) if offset is not None else '',
)
params.update(kwargs)
if len(params):
params = pymysql.escape_dict(params, "'")
pattern = re.compile('|'.join([':' + key
for key in params.keys()]))
query = pattern.sub(lambda x: params[x.group()[1:]], query)
cursor = self.execute(query, pymysql.cursors.DictCursor)
result = [model_cls(row, False) for row in cursor]
return result
