def query(self, table, showcol, con_args):
if len(showcol) == 0:
showcol = '*'
else:
showcol = ','.join(showcol)
if isinstance(con_args, {}):
con_args = reduce(lambda x, y: str(x) + ',' + str(y[0]) + '=' + str(y[1]), con_args.items(), 'WHERE 1=1')
else:
con_args = 'WHERE 1=1' + con_args
try:
self.db.set_character_set('utf8')
sql = "SELECT %s FROM %s %s" % (showcol, table, con_args)
try:
result = self.cur.execute(sql)
if not result:
return []
return self.cur.fetchall()
except MySQLdb.Error, e:
# 发生错误时回滚
self.db.rollback()
print BasicTool.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])
except MySQLdb.Error, e:
print BasicTool.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])
def __init__(self):
try:
self.db = MySQLdb.connect(IPADDRESS, USERNAME, PASSWORD, DBNAME)
self.cur = self.db.cursor()
except MySQLdb.Error, e:
print BasicTool.getCurrentTime(), "连接数据库错误，原因%d: %s" % (e.args[0], e.args[1])
def update(self, table, change_col, con_args):
change_col = reduce(lambda x, y: str(x) + ',' + str(y[0]) + '=' + str(y[1]), change_col.items(), '')[1:]
if isinstance(con_args, {}):
con_args = reduce(lambda x, y: str(x) + ',' + str(y[0]) + '=' + str(y[1]), con_args.items(), 'WHERE 1=1')
else:
con_args = 'WHERE 1=1' + con_args
try:
self.db.set_character_set('utf8')
sql = "UPDATE %s SET %s %s" % (table, change_col, con_args)
try:
return self.cur.execute(sql)
except MySQLdb.Error, e:
# 发生错误时回滚
self.db.rollback()
print BasicTool.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])
except MySQLdb.Error, e:
print BasicTool.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])
def delete(self, table, con_args):
if isinstance(con_args, {}):
condition = reduce(lambda x, y: str(x) + ',' + str(y[0]) + '=' + str(y[1]), con_args.items(), 'WHERE 1=1')
else:
condition = 'WHERE 1=1' + con_args
try:
self.db.set_character_set('utf8')
sql = "DELETE FROM %s %s" % (table, condition)
try:
return self.cur.execute(sql)
except MySQLdb.Error, e:
# 发生错误时回滚
self.db.rollback()
print BasicTool.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])
except MySQLdb.Error, e:
print BasicTool.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])
def insert(self, table, my_dict):
try:
self.db.set_character_set('utf8')
cols = ', '.join(my_dict.keys())
values = '"," '.join(my_dict.values())
sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, cols, '"' + values + '"')
try:
result = self.cur.execute(sql)
insert_id = self.db.insert_id()
self.db.commit()
# 判断是否执行成功
if result:
return insert_id
else:
return 0
except MySQLdb.Error, e:
# 发生错误时回滚
self.db.rollback()
# 主键唯一，无法插入
if "key 'PRIMARY'" in e.args[1]:
print BasicTool.getCurrentTime(), "数据已存在，未插入数据"
else:
print BasicTool.getCurrentTime(), "插入数据失败，原因 %d: %s" % (e.args[0], e.args[1])
except MySQLdb.Error, e:
print BasicTool.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])