def select(self):
"""
@brief select all result from table
@param table String
@param field String
@param condition String
@return result Tuple
"""
sql = "SELECT %s FROM %s" % (self.fields[0], self.vtable)
# sql = "SELECT %s FROM %s"
# self.sql_values.extend([self.fields[0],self.vtable])
if len(self.join_tables) > 0:
sql = self.u_join(sql, 0)
if len(self.wheres) > 0:
sql = self.u_where(self.wheres[0], sql)
if len(self.orders) > 0:
sql = self.u_order(self.orders[0], sql)
if len(self.groups) > 0:
sql = self.u_groupby(self.groups[0], sql)
if len(self.limits) > 0:
sql = self.u_limit(self.limits[0], sql)
# if len(self.limits) > 0:
# sql=self.u_limit(self.limits[0],sql)
if self.union_count > 0:
sql += self.union_select()
# if field and condition:
# sql += " WHERE %s='%s'" % (field, condition)
# print sql
sql_values = tuple(self.sql_values)
dbsql = sql % sql_values
Log.debug('DB -> %s' % dbsql)
self.u_clear()
# print sql_values
return self.execute(sql, sql_values)
def union(self, union_type="all", table_name=""):
if table_name:
self.union_count += 1
self.union_tables[self.union_count] = table_name
self.union_types[self.union_count] = union_type
Log.debug('DB -> union %s table %s' % (union_type, table_name))
return self
def execute(self, sql, values=None, sqlhole=True):
"""
@brief execute sql commands, return result if it has
@param sql String
@param value Tuple
@return result Array
"""
c = self.conn.cursor()
self.lock.acquire()
hasReturn = sql.lstrip().upper().startswith("SELECT")
result = []
print values
try:
if values and not sqlhole:
c.executemany(sql, values)
else:
c.execute(sql, values)
# c.execute(sql,values)
if hasReturn:
result = c.fetchall()
except Exception, e:
Log.error(traceback.format_exc())
self.conn.rollback()
def close(self):
"""
@brief close connection to database
"""
Log.debug('DB -> close')
# 关闭数据库连接
self.conn.close()
def order(self, value=None):
# self.vorders=[]
if value:
# self.vorders.append(value)
self.orders[self.union_count] = value
Log.debug('DB -> %s' % value)
return self
def field(self, value="*"):
fieldlist = []
fieldsstr = ""
if value == "*":
for valu in self.table_cols[self.vtable]:
opstr = "CAST(%s AS CHAR) AS %s"
if valu['dataType'] == 'datetime' or valu['dataType'] == 'date':
opstr = opstr % (valu['field'], valu['field'])
fieldlist.append(opstr)
else:
fieldlist.append(valu['field'])
fieldsstr = ",".join(fieldlist)
else:
fieldsstr = value
# else:
# valuelist=value.split(",")
# print valuelist.index(u"id")
# for va in self.table_cols[self.vtable]:
# opstr="CAST(%s AS CHAR) AS %s"
# if ret_run("search index",valuelist.index,va['field']):
# if va['dataType']=='datetime' or va['dataType']=='date':
# opstr=opstr % (va['field'],va['field'])
# fieldlist.append(opstr)
# else:
# fieldlist.append(va['field'])
self.fields[self.union_count] = fieldsstr
Log.debug('DB -> %s' % fieldsstr)
return self
def table(self, value=None):
if value:
self.vtable = value
Log.debug('DB -> %s' % value)
return self
else:
return False
def delete_table(self, table):
"""
@brief Delete a table in database
@param table String
"""
sql = "DROP TABLE if exists %s;" % table
Log.debug('DB -> %s' % sql)
self.execute(sql)
def insert(self, table, value):
"""
@brief Insert a row in table
@param table String
@param value Tuple
"""
sql = ("INSERT INTO %s VALUES (" + ",".join(['?'] * len(value)) + ");") % table
Log.debug('DB -> %s' % sql)
self.execute(sql, value)
def ret_run(str, func, *args):
t = time.time()
r = False
try:
r = func(*args)
return r + 1
except:
Log.error(str + ":" + traceback.format_exc())
return False
def create_db(self, db_name):
"""
@brief Creates a database
@param db_name String
"""
if self.conf['database'] not in self.show_database():
sql = 'CREATE DATABASE IF NOT EXISTS %s CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' % db_name
Log.debug('DB -> %s' % sql)
self.execute(sql)
def create_table(self, table, cols):
"""
@brief Creates a table in database
@param table String
@param cols String, the cols in table
"""
sql = "CREATE TABLE if not exists %s (%s);" % (table, cols)
Log.debug('DB -> %s' % sql)
self.execute(sql)
def delete_table(self, table):
"""
@brief Delete a table in database
@param table String
"""
if table in self.table_cols:
sql = "DROP TABLE IF EXISTS %s" % table
Log.debug('DB -> %s' % sql)
self.execute(sql)
self.table_cols.pop(table)
def delete(self, table, field='', condition=''):
"""
@brief execute sql commands, return result if it has
@param table String
@param field String
@param condition String
"""
sql = "DELETE FROM %s WHERE %s=?" % (table, field)
Log.debug('DB -> %s' % sql)
cond = (condition,)
self.execute(sql, cond)
def insertmany(self, table, values):
"""
@brief Insert many rows in table
@param table String
@param values Array of tuple
"""
col_name = self.table_cols[table][1:]
sql = 'INSERT INTO %s(%s) VALUES (%s)' % (
table, ','.join(col_name), ','.join(['%s'] * len(values[0])))
Log.debug('DB -> %s' % sql)
self.execute(sql, values)
def update(self, table, dic, condition=''):
k_arr = []
v_arr = []
for (k, v) in dic.items():
k_arr.append('%s=?' % k)
v_arr.append(v)
sql = "UPDATE %s SET %s" % (table, ','.join(k_arr))
if condition:
sql += " WHERE %s" % condition
Log.debug('DB -> %s' % sql)
self.execute(sql, tuple(v_arr))
def run(self, str, func, *args):
t = time.time()
# echo(str)
r = False
try:
r = func(*args)
except:
Log.error(traceback.format_exc())
if r:
totalTime = int(time.time() - t)
# echo(Constant.RUN_RESULT_SUCCESS % totalTime)
else:
pass
def create_table(self, table, cols):
"""
@brief Creates a table in database
@param table String
@param cols String, the cols in table
"""
if table not in self.table_cols:
sql = 'CREATE TABLE IF NOT EXISTS %s(id int primary key auto_increment, %s) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' % (
table, cols)
Log.debug('DB -> %s' % sql)
self.execute(sql)
self.table_cols[table] = ['id'] + [
c.strip().split(' ')[0] for c in cols.split(',')
]
def insertmany(self, table, values):
"""
@brief Insert many rows in table
@param table String
@param values Array of tuple
"""
c = self.conn.cursor()
self.lock.acquire()
n = len(values[0])
sql = ("INSERT INTO %s VALUES (" + ",".join(['?'] * n) + ");") % table
Log.debug('DB -> %s' % sql)
try:
c.executemany(sql, values)
except Exception, e:
Log.error(traceback.format_exc())
def insert(self, table, value):
"""
@brief Insert a row in table
@param table String
@param value Tuple
"""
# col_name = self.table_cols[table][1:]
col_name = value.keys()
# print array_join(value.values(), ',')
# # print col_name
# # print value.values()
# return
sql = "INSERT INTO %s(%s) VALUES (%s)" % (table, str(
','.join(col_name)), array_join(value.values(), ','))
Log.debug('DB -> %s' % sql)
# print sql
self.execute(sql)
def save(self, value):
col_name = value.keys()
setfield = []
for key, valu in value.items():
if valu is None:
valu = ''
afield = "%s = %s" % (key, ("'" + valu + "'" if isinstance(
valu, basestring) else valu))
setfield.append(afield)
sql = "update %s set %s " % (self.vtable, str(','.join(setfield)))
if len(self.wheres) > 0:
sql = self.u_where(self.wheres[0], sql)
# print sql
Log.debug('DB -> %s' % sql)
sql_values = tuple(self.sql_values)
self.u_clear()
self.execute(sql, sql_values)
def select(self, table, field='', condition=''):
"""
@brief select all result from table
@param table String
@param field String
@param condition String
@return result Tuple
"""
result = []
if field and condition:
cond = (condition,)
sql = "SELECT * FROM %s WHERE %s=?" % (table, field)
Log.debug('DB -> %s' % sql)
result = self.execute(sql, cond)
else:
sql = "SELECT * FROM %s" % table
Log.debug('DB -> %s' % sql)
result = self.execute(sql)
return result
def delete(self, table, where):
"""
@brief execute sql commands, return result if it has
@param table String
@param field String
@param condition String
"""
sql = "DELETE FROM %s" % (table)
if isinstance(where, dict):
swhere = []
for wkey, wvalue in where.items():
awhree = "%s = %s" % (wkey, ("'" + wvalue + "'" if isinstance(
wvalue, basestring) else wvalue))
swhere.append(awhree)
sql = sql + " where %s" % (str(' and '.join(swhere)))
elif isinstance(where, basestring):
sql = sql + where
else:
return 0
Log.debug('DB -> %s' % sql)
self.execute(sql)
def update(self, table, value, where):
col_name = value.keys()
setfield = []
for key, valu in value.items():
if valu is None:
valu = ''
afield = "%s = %s" % (key, ("'" + valu + "'" if isinstance(
valu, basestring) else valu))
setfield.append(afield)
sql = "update %s set %s " % (table, str(','.join(setfield)))
if isinstance(where, dict):
swhere = []
for wkey, wvalue in where.items():
awhree = "%s = %s" % (wkey, ("'" + wvalue + "'" if isinstance(
wvalue, basestring) else wvalue))
swhere.append(awhree)
sql = sql + " where %s" % (str(' and '.join(swhere)))
elif isinstance(where, str):
sql = sql + where
else:
return 0
# print sql
Log.debug('DB -> %s' % sql)
self.execute(sql)
def limit(self, value=None):
if value:
self.limits[self.union_count] = value
Log.debug('DB -> %s' % value)
return self
def page(self, value):
self.vlimit = value
Log.debug('DB -> %s' % value)
return self
def show_database(self):
c = self.conn.cursor()
sql = 'SHOW DATABASES'
Log.debug('DB -> %s' % sql)
c.execute(sql)
return [r['Database'] for r in c.fetchall()]
def show_tables(self):
c = self.conn.cursor()
sql = 'SHOW TABLES'
Log.debug('DB -> %s' % sql)
c.execute(sql)
return [r['Tables_in_' + self.conf['database']] for r in c.fetchall()]
def __del__(self):
Log.debug('DB -> __del__')
self.close()
def show_table_struct(self, table):
c = self.conn.cursor()
sql = "desc %s" % table
Log.debug('DB -> %s' % sql)
c.execute(sql)
return c.fetchall()