def compose_order(self):
order = self._query_builder.g_order()
if order:
order_list = []
for field, sort in order.items():
order_list.append(str_f("{} {}", field, sort))
return str_f('ORDER BY {}', ','.join(order_list))
else:
return ''
def join(self, join_table, where, join_type="INNER"):
where_res = self.process_where(where)
if not where_res:
raise WhereArgStructErr(str_f('join 条件异常，{}', where))
if not join_table:
raise WhereArgStructErr(str_f('join table异常，table {}', join_table))
if join_type not in self._get_join_type():
raise WhereArgStructErr(str_f('join 类型异常，join_type {}', join_type))
self._join = [join_table, where_res, join_type]
return self
def to_query_sql(self):
tpl = "SELECT {} FROM {} {} {} {} {} {}"
return str_f(tpl, self.compose_field(), self._query_builder.g_table(),
self.compose_join(), self.compose_where(),
self.compose_order(), self.compose_group(),
self.compose_limit())
def compose_limit(self):
limit = self._query_builder.g_limit()
if limit:
self._params = self._params + limit
return str_f('LIMIT {}', ','.join(["%s" for x in limit]))
else:
return ''
def compose_join(self):
join = self._query_builder.g_join()
if join:
return str_f("{} JOIN {} ON {}", join[2].upper(), join[0],
self.parse_where(join[1]))
else:
return ''
def check_value(cls, value):
if not isinstance(value, list):
value = [value]
for x in value:
if not isinstance(x, (str, float, int)):
raise WhereArgStructErr(str_f('数据格式异常，无效的值：{}', x))
return True
def check_field(cls, field):
if not isinstance(field, list):
field = [field]
for x in field:
if not isinstance(x, (str, )):
raise WhereArgStructErr(str_f('数据格式异常，无效的字段：{}', x))
return True
def update(self, data):
if not data or not isinstance(data, dict):
raise WhereArgStructErr(str_f('更新数据为空或结构异常 {}', data))
self.check_value(data.values())
self.check_field(data.keys())
self._sql_type = self.SQL_TYPE_UPDATE
self._update_field = data
return self.__run()
def compose_update_data(self):
update_field = self._query_builder.g_update_field()
if update_field:
result_list = []
for field, value in update_field.items():
self._params.append(value)
result_list.append(str_f("{}=%s", field))
return ','.join(result_list)
else:
return ''
def parse_where(self, where):
if not where:
return ''
if not isinstance(where, list):
where = [where]
where_str = ''
for x in where:
where_type = x.get('type')
if where_type == QueryBuilder.TYPE_SIMPLE:
self._params.append(x.get('value'))
where_item_str = str_f("{} {} %s", x.get('field'),
x.get('operator'))
elif where_type == QueryBuilder.TYPE_NEST:
nest_where = self.parse_where(x.get('value'))
where_item_str = '(' + nest_where + ')'
elif where_type == QueryBuilder.TYPE_BETWEEN:
where_item_str = str_f("{} BETWEEN %s,%s", x.get('field'))
self._params = self._params + x.get('value')
elif where_type == QueryBuilder.TYPE_NOT_IN:
where_item_str = str_f(
"{} NOT IN ({})", ','.join(['%s' for i in x.get('value')]))
self._params = self._params + x.get('value')
elif where_type == QueryBuilder.TYPE_IN:
where_item_str = str_f(
"{} IN ({})", ','.join(['%s' for i in x.get('value')]))
self._params = self._params + x.get('value')
elif where_type == QueryBuilder.TYPE_NULL:
where_item_str = str_f("{} IS NULL", x.get('field'))
elif where_type == QueryBuilder.TYPE_NOT_NULL:
where_item_str = str_f("{} IS NOT NULL", x.get('field'))
else:
raise SqlBuilderError(
str_f('where type can\'t parse, type:{}', where_type))
and_str = ' '
if where_str:
and_str = ' AND ' if x.get('is_and') else ' OR '
where_str = where_str + and_str + where_item_str
return where_str
def insert(self, data):
if not isinstance(data, list):
data = [data]
fields = None
for item in data:
if not isinstance(item, dict):
raise WhereArgStructErr(str_f('新增数据异常 {}', item))
this_field = item.keys()
if fields is None:
fields = this_field
elif list_diff(fields, this_field) or list_diff(
this_field, fields):
raise WhereArgStructErr('新增数据格式异常，列必须一致')
else:
fields = this_field
self.check_value(item.values())
self.check_field(item.keys())
self._sql_type = self.SQL_TYPE_INSERT
self._insert_data = data
return self.__run()
def to_delete_sql(self):
tpl = "DELETE FROM {} {}"
return str_f(tpl, self._query_builder.g_table(), self.compose_where())
def to_insert_sql(self):
tpl = "INSERT INTO {} {} VALUES {}"
fields, data_str = self.compose_insert_data()
return str_f(tpl, self._query_builder.g_table(), fields, data_str)
def to_update_sql(self):
tpl = "UPDATE {} SET {} {}"
return str_f(tpl, self._query_builder.g_table(),
self.compose_update_data(), self.compose_where())
def process_where(cls, args):
"""
处理where
结构说明：
simple:
is_and, field, value, operator
:param args:
:return:
"""
if not isinstance(args, list) or len(args) < 3:
raise WhereArgStructErr(str_f("where 入参结构异常: {}", args))
where_type = args[0]
is_and, = args[-1],
params = args[1:-1]
if not isinstance(is_and, bool):
raise WhereArgStructErr(
str_f("and 类型必须为bool, 入参: {} and:{}", args, is_and))
result = {
"type": where_type,
"is_and": is_and,
}
if where_type == cls.TYPE_NEST:
if not params or not isinstance(params[0], list):
raise WhereArgStructErr(
str_f("嵌套类型，值必须是数组 入参: {} value:{} 类型：{}", args, params[0],
str(type(params[0]))))
result['value'] = []
for x in params[0]:
value = cls.process_where(x)
if value:
result['value'].append(value)
elif where_type == cls.TYPE_SIMPLE:
if len(params) != 3:
raise WhereArgStructErr(args)
field, operator, value = params
if not cls.check_field(field) or operator not in cls.operator:
raise WhereArgStructErr(
str_f("字段类型异常或操作符异常 入参: {} 字段:{} 操作符：{}", args, field,
operator))
result.update({
"field": field,
"operator": operator,
"value": value,
})
elif where_type in [cls.TYPE_BETWEEN, cls.TYPE_IN, cls.TYPE_NOT_IN]:
if len(params) != 2:
raise WhereArgStructErr(
str_f("value结构异常 入参: {} value:{}", args, params))
field, value = params
if not cls.check_field(field) or not isinstance(value, list):
raise WhereArgStructErr(
str_f("字段类型异常或值类型异常value结构异常 入参: {} 字段：{} value:{}", args,
field, params))
if value:
if where_type == cls.TYPE_BETWEEN:
if len(value) < 2:
raise WhereArgStructErr(
str_f("between value结构异常 入参: {} value:{}", args,
value))
value = value[0:2]
result.update({
"field": field,
"value": value,
})
else:
result = None
elif where_type in [cls.TYPE_NULL, cls.TYPE_NOT_NULL]:
if len(params) != 1:
raise WhereArgStructErr(
str_f("value结构异常 入参: {} value:{}", args, params))
field = params[0]
if not cls.check_field(field):
raise WhereArgStructErr(
str_f("字段结构异常 入参: {} value:{}", args, field))
result.update({
"field": field,
})
else:
raise WhereArgStructErr(
str_f("错误的where类型 入参: {} 类型:{}", args, where_type))
return result