def add(self, _table, _param = None):
"""
添加单条数据
:param str _table: 表
:param dict _param: 添加内容
:return: False | 新增id
:rtype: bool or int
"""
if not _param:
return False
if not isinstance(_param, dict):
raise TypeError(_param, 'mysql')
# 获取添加内容
keys = []
values = []
for k, v in _param.items():
keys.append('`' + k + '`')
if v is None or v == '':
values.append('NULL')
elif tools.is_array(v):
values.append('\'' + re.escape(json.dumps(v)) + '\'')
else:
values.append('\'' + str(v) + '\'')
self.__sql = 'INSERT INTO ' + _table + ' ( ' + (','.join(keys)) + ') VALUES (' + (','.join(values)) + ')'
result_bool = self.query_sql('a')
return result_bool
def adds(self, _table, _params = None):
"""
添加多条数据
:param str _table: 表
:param dict _params: 添加内容
:return: False | 新增id
:rtype: bool or list
"""
if not _params:
return False
# 获取添加内容
keys = []
for k, v in _params[0].items():
keys.append('`' + k + '`')
values = []
value = []
for _param in _params:
if not isinstance(_param, dict):
tools.logs('/mysql/error',
{'file_type': 'mysql', 'message': _param},
'error',
_type = 3)
continue
for k, v in _param.items():
if v is None or v == '':
value.append('NULL')
elif tools.is_array(v):
value.append('\'' + re.escape(json.dumps(v)) + '\'')
else:
value.append('\'' + str(v) + '\'')
values.append('(' + (','.join(value)) + ')')
self.__sql = 'INSERT INTO ' + _table + ' ( ' + (','.join(keys)) + ') VALUES ' + (','.join(values))
res = self.query_sql('a')
return res
def update(self, _table, _where = None, _param = None, _limit = True):
"""
修改
:param str _table: 表
:param str|dict _where: 修改位置
:param str|dict _param: 修改内容
:param bool _limit: 修改数量 True -> 1 条 | False -> 多条
:return: False | 修改数量
:rtype: bool
"""
# 获取查询条件
self.get_where(_where, True)
if not _param:
raise ValueError('Param \'_param\' can not be empty', 'mysql')
if not isinstance(_param, dict):
raise TypeError(_param, 'mysql')
# 获取修改内容
lst = []
for k, v in _param.items():
if isinstance(k, int):
lst.append(v)
else:
if not v:
lst.append('`' + k + '`=NULL')
elif tools.is_array(v):
lst.append('`' + k + '`=\'' + json.dumps(v) + '\'')
else:
lst.append('`' + k + '`=\'' + str(v) + '\'')
_param = ','.join(lst)
_limit = ' LIMIT 1' if _limit else ''
self.__sql = 'UPDATE ' + _table + ' SET ' + _param + self.__where + _limit
res = self.query_sql('u')
return res
def py_log(self, _type, **kwargs):
"""
python 日志表
:param str _type: 日志类型
:param kwargs:
:return:
"""
# 报错信息
if 'error' in kwargs:
if not tools.is_array(kwargs['error']):
kwargs['error'] = [kwargs['error']]
else:
kwargs['error'] = None
# 请求参数
if 'params' in kwargs:
kwargs['params'] = tools.url_decode(kwargs['params'])
else:
kwargs['params'] = None
# 备注
if 'remark' not in kwargs:
kwargs['remark'] = None
elif isinstance(kwargs['remark'], str):
kwargs['remark'] = {'msg': kwargs['remark']}
# 记录日志
self.add('_log_python_request', {
'pid': os.getpid(),
'type': _type,
'params': kwargs['params'],
'error': kwargs['error'],
'remark': kwargs['remark'],
'create_time': glob.get_value('create_time'),
'update_time': tools.get_date('%Y-%m-%d %H:%M:%S'),
})
def get_where(self, _where = None, _alter = False):
"""
获取查询条件
:param dict|str _where: 条件
:param bool _alter: 是否记录日志
:return:
"""
if _where:
if tools.is_array(_where):
lst = []
for i, row in _where.items():
# 跳过数字
if tools.is_number(i): # 处理 i = 'id=1'
lst.append(row)
continue
else:
i = str(i)
if i.find('.') >= 0: # 处理 i = 'A.id'
columns = i.split('.')
key = columns[0] + '.' + '`' + str(columns[1]) + '` '
else: # 处理 i = 'id'
key = '`' + str(i) + '` '
if isinstance(row, dict): # 处理 in , like , > , < , >= , <= , != , <>
for j, col in row.items():
if j.lower() == 'in' and col: # 处理 in
if isinstance(col, list): # where['name']['in'] = []
new = []
for item in col:
new.append('\'' + str(item) + '\'')
value = j + ' (' + (','.join(new)) + ')'
elif isinstance(col, str): # where['name']['in'] = 'abc'
col = col.lstrip('(')
col = col.rstrip(')')
value = j + ' (' + col + ')'
elif tools.is_number(col): # where['name']['in'] = 1
value = j + ' (' + str(col) + ')'
else:
continue
elif j.lower() == 'like' and col and not tools.is_array(col): # 处理 like
value = j + ' \'' + str(col).strip("'") + '\''
elif j in ['>', '<', '=', '>=', '<=', '!=', '<>'] and not tools.is_array(col):
# 处理 > , < , = , >= , <= , != , <>
value = j + ' \'' + str(col) + '\''
else:
continue
lst.append(key + value)
else: # 处理 =
if isinstance(row, str): # str 类型
value = '= \'' + row + '\''
lst.append(key + value)
elif isinstance(row, int):
value = '= ' + str(row)
lst.append(key + value)
else:
continue
_where = ' && '.join(lst)
if _where:
self.__where = ' WHERE ' + _where
elif _alter:
raise EmptyError('Miss _where', 'mysql')