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])