class MySQLHelper: def __init__(self, host, user, password, database): self._db = Connection(host, database, user=user, password=password, max_idle_time=10) if not self._db._db: raise Exception('%s' % self._db.error_msg) def query(self, sql, *parameters): return self._db.query(sql, *parameters) def query_one(self, sql, *parameters): res = self._db.query(sql, *parameters) return res.pop() if res else {} def write(self, sql, *parameters): return self._db.execute(sql, *parameters) def gen_insert(self, tablename, rowdict, replace=False): return self._db.gen_insert_sql(tablename, rowdict, replace) def insert_dict(self, tablename, rowdict): key_strs = ", ".join(["""`%s`""" % key for key in rowdict.keys()]) value_strs = ", ".join(["""'%s'""" % rowdict.get(key) for key in rowdict.keys()]) sql = """INSERT INTO %s (%s) VALUES (%s)""" % (tablename, key_strs, value_strs) return self._db.execute(sql) def insert_batch(self, tablename, batch_params): value_batch = [] for param in batch_params: keys = param.keys() key_strs = ", ".join(["""`%s`""" % key for key in keys]) value_strs = "(%s)" % ", ".join( ["""'%s'""" % "%s" % param.get(key) for key in keys]) value_batch.append(value_strs) sql = """INSERT INTO %s (%s) VALUES %s""" % (tablename, key_strs, ",".join(value_batch)) return self._db.execute(sql) def update_dict(self, tablename, rowdict, where): sql = """UPDATE %s SET %s WHERE %s""" % ( tablename, self._formatter(rowdict, ', '), self._formatter(where, " AND ")) return self._db.execute(sql) def transaction(self, query, parameters): return self._db.transaction(query, parameters) def get(self, tablename, conds, cols='', extra_conds={}): if not tablename: return False cols = "%s" % ','.join(cols) if cols else '*' wheres = [] values = [] if conds and isinstance(conds, dict): for key, value in conds.items(): if isinstance(value, (list, tuple)): wheres.append("`%s` IN (%s)" % (key, "'%s'" % "','".join([str(v) for v in value]))) else: wheres.append("`%s`=%%s" % key) values.append("%s" % value) where_str = ' AND '.join(wheres) sql = """ SELECT %s FROM `%s` """ % (cols, tablename) if where_str: sql += """ WHERE %s """ % (where_str) if extra_conds.get('group_by'): sql += """ GROUP by %s """ % ','.join(extra_conds['group_by']) if extra_conds.get('order_by'): sql += """ ORDER by %s """ % ','.join(extra_conds['order_by']) if extra_conds.get('limit'): sql += """ LIMIT %s """ % ','.join(map(str, extra_conds['limit'])) return self._db.query(sql, *values) def _serialize(self, value): if isinstance(value, (dict, list, set)): value = json.dumps(value) else: value = "%s" % value return value def _formatter(self, pairs, delimiter): values = [] for key, value in pairs.items(): if not isinstance(value, list): value = self._serialize(value) values.append("""`%s`='%s'""" % (key, value)) else: values.append("""`%s` in ("%s")""" % (key, '","'.join([self._serialize(val) for val in value]))) return delimiter.join(values) def __del__(self): self._db.close()