def update(self, table, data, filters, nonupdate=None):
update_dict = {k: v for k, v in data.items() if not_empty(v) and k not in (nonupdate or [])}
update_holders = ", ".join(map(lambda n: n + "=%s", update_dict.keys()))
where_dict = {k: v for k, v in filters.items() if not_empty(v)}
if where_dict:
where_holders = " AND ".join(map(lambda n: n + "=%s", where_dict.keys()))
request = "UPDATE %s SET %s WHERE %s" % (table, update_holders, where_holders)
self.execute(request, list(update_dict.values()) + list(where_dict.values()), commit=True)
def update(self, table, data, filters, nonupdate=None):
update_dict = {k: v for k, v in data.items()
if not_empty(v) and k not in (nonupdate or [])}
update_holders = ', '.join(map(lambda n: n + '=%s', update_dict.keys()))
where_dict = {k: v for k, v in filters.items() if not_empty(v)}
if where_dict:
where_holders = ' AND '.join(map(lambda n: n + '=%s', where_dict.keys()))
request = 'UPDATE %s SET %s WHERE %s' % (table, update_holders, where_holders)
self.execute(request, list(update_dict.values()) + list(where_dict.values()), commit=True)
def replace(self, table, data, noninsert=None, nonupdate=None):
insert_dict = {k: v for k, v in data.items()
if not_empty(v) and k not in (noninsert or [])}
columns = ', '.join(insert_dict.keys())
insert_holders = ', '.join(['%s'] * len(insert_dict))
update_dict = {k: v for k, v in insert_dict.items()
if not_empty(v) and k not in (nonupdate or [])}
update_holders = ', '.join(map(lambda n: n + '=%s', update_dict.keys()))
request = 'INSERT INTO %s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s' % (
table, columns, insert_holders, update_holders)
self.execute(request, list(insert_dict.values()) + list(update_dict.values()), commit=True)
def replace(self, table, data, noninsert=None, nonupdate=None):
insert_dict = {k: v for k, v in data.items() if not_empty(v) and k not in (noninsert or [])}
columns = ", ".join(insert_dict.keys())
insert_holders = ", ".join(["%s"] * len(insert_dict))
update_dict = {k: v for k, v in insert_dict.items() if not_empty(v) and k not in (nonupdate or [])}
update_holders = ", ".join(map(lambda n: n + "=%s", update_dict.keys()))
request = "INSERT INTO %s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s" % (
table,
columns,
insert_holders,
update_holders,
)
self.execute(request, list(insert_dict.values()) + list(update_dict.values()), commit=True)
def get_page_list(self, table, queries, page_no, page_size, orderby="", select_key="*"):
queries = queries or {}
equal_queries = {
k: v for k, v in queries.items() if not_empty(v) and type(v) is not tuple and type(v) is not list
}
open_range_queries = {k: v for k, v in queries.items() if type(v) is tuple and len(v) == 2}
close_range_queries = {k: v for k, v in queries.items() if type(v) is list and len(v) == 2}
conditions = (
list(map(lambda k: k + "=%s", equal_queries.keys()))
+ list(map(lambda k: "%s < " + k + " AND " + k + " < %s", open_range_queries.keys()))
+ list(map(lambda k: "%s <= " + k + " AND " + k + " <= %s", close_range_queries.keys()))
)
where_clause = (" WHERE " + " AND ".join(conditions)) if conditions else ""
values = list(equal_queries.values()) + list(open_range_queries.values()) + list(close_range_queries.values())
count_request = "SELECT count(1) AS total FROM %s %s" % (table, where_clause)
records = self.execute(count_request, values)
total = records["total"] if records else 0
if total:
limit_clause = (
" LIMIT %s,%s " % ((page_no - 1) * page_size, page_size) if page_no > 0 and page_size > 0 else ""
)
orderby_clause = " ORDER BY %s " % orderby if orderby else ""
request = "SELECT %s FROM %s %s %s %s" % (select_key, table, where_clause, orderby_clause, limit_clause)
return total, self.fetch_all(request, values)
else:
return 0, []
def get_left_join(self, table, joins, queries, select_key="*"):
queries = queries or {}
queries = {k: v for k, v in queries.items() if not_empty(v)}
where_clause = (" WHERE " + " AND ".join(map(lambda n: n + "=%s", queries.keys()))) if queries else ""
join_clause = "".join([" LEFT JOIN %s ON %s.%s = %s.%s " % (j[1], table, j[0], j[1], j[2]) for j in joins])
request = "SELECT %s FROM %s %s %s" % (select_key, table, join_clause, where_clause)
return self.fetch_all(request, queries.values())
def get_left_join(self, table, joins, queries, select_key='*'):
queries = queries or {}
queries = {k: v for k, v in queries.items() if not_empty(v)}
where_clause = (' WHERE ' + ' AND '.join(map(lambda n: n + '=%s', queries.keys()))) if queries else ''
join_clause = ''.join([' LEFT JOIN %s ON %s.%s = %s.%s ' % (j[1], table, j[0], j[1], j[2]) for j in joins])
request = 'SELECT %s FROM %s %s %s' % (
select_key, table, join_clause, where_clause)
return self.fetch_all(request, queries.values())
def insert(self, table, data, noninsert=None):
insert_dict = {k: v for k, v in data.items()
if not_empty(v) and k not in (noninsert or [])}
columns = ', '.join(insert_dict.keys())
insert_holders = ', '.join(['%s'] * len(insert_dict))
request = 'INSERT INTO %s (%s) VALUES (%s)' % (
table, columns, insert_holders)
self.execute(request, list(insert_dict.values()), commit=True)
def get(self, table, queries, select_key='*'):
queries = queries or {}
queries = {k: v for k, v in queries.items() if not_empty(v)}
where_clause = (' WHERE ' + ' AND '.join(map(lambda n: n + '=%s', queries.keys()))) if queries else ''
request = 'SELECT %s FROM %s %s LIMIT 1' % (select_key, table, where_clause)
records = self.execute(request, queries.values())
if not records:
return None
else:
return records if select_key == '*' else records.get(select_key)
def build_sign(data, key, method='md5'):
if method == 'md5':
p = [(k, str(v)) for k, v in sorted(data.items()) if not_empty(v) and k != 'sign']
return hashlib.md5(('&'.join([k + '=' + v for k, v in p]) + '&key=' + key).encode('utf8')).hexdigest().upper()
elif method == 'sha1':
p = [key, data.get('timestamp', ''), data.get('nonce', ''), data.get('encrypt', '')]
p.sort()
return hashlib.sha1((''.join(p)).encode('utf8')).hexdigest()
else:
return ''
def get(self, table, queries, select_key="*"):
queries = queries or {}
queries = {k: v for k, v in queries.items() if not_empty(v)}
where_clause = (" WHERE " + " AND ".join(map(lambda n: n + "=%s", queries.keys()))) if queries else ""
request = "SELECT %s FROM %s %s LIMIT 1" % (select_key, table, where_clause)
records = self.execute(request, queries.values())
if not records:
return None
else:
return records if select_key == "*" else records.get(select_key)
def build_sign(data, key, method='md5'):
if method == 'md5':
p = [(k, str(v)) for k, v in sorted(data.items())
if not_empty(v) and k != 'sign']
return hashlib.md5(('&'.join([k + '=' + v for k, v in p]) + '&key=' +
key).encode('utf8')).hexdigest().upper()
elif method == 'sha1':
p = [
key,
data.get('timestamp', ''),
data.get('nonce', ''),
data.get('encrypt', '')
]
p.sort()
return hashlib.sha1((''.join(p)).encode('utf8')).hexdigest()
else:
return ''
def get_page_list(self, table, queries, page_no, page_size, orderby='', select_key='*'):
queries = queries or {}
equal_queries = {k: v for k, v in queries.items() if
not_empty(v) and type(v) is not tuple and type(v) is not list}
open_range_queries = {k: v for k, v in queries.items() if type(v) is tuple and len(v) == 2}
close_range_queries = {k: v for k, v in queries.items() if type(v) is list and len(v) == 2}
conditions = \
list(map(lambda k: k + '=%s', equal_queries.keys())) + \
list(map(lambda k: '%s < ' + k + ' AND ' + k + ' < %s', open_range_queries.keys())) + \
list(map(lambda k: '%s <= ' + k + ' AND ' + k + ' <= %s', close_range_queries.keys()))
where_clause = (' WHERE ' + ' AND '.join(conditions)) if conditions else ''
values = list(equal_queries.values()) + list(open_range_queries.values()) + list(close_range_queries.values())
count_request = 'SELECT count(1) AS total FROM %s %s' % (table, where_clause)
records = self.execute(count_request, values)
total = records['total'] if records else 0
if total:
limit_clause = ' LIMIT %s,%s ' % (
(page_no - 1) * page_size, page_size) if page_no > 0 and page_size > 0 else ''
orderby_clause = ' ORDER BY %s ' % orderby if orderby else ''
request = 'SELECT %s FROM %s %s %s %s' % (
select_key, table, where_clause, orderby_clause, limit_clause)
return total, self.fetch_all(request, values)
else:
return 0, []
def insert(self, table, data, noninsert=None):
insert_dict = {k: v for k, v in data.items() if not_empty(v) and k not in (noninsert or [])}
columns = ", ".join(insert_dict.keys())
insert_holders = ", ".join(["%s"] * len(insert_dict))
request = "INSERT INTO %s (%s) VALUES (%s)" % (table, columns, insert_holders)
self.execute(request, list(insert_dict.values()), commit=True)