def add_new_expense_record(expense_info): try: conn = DBPOOL.connection() cursor = conn.cursor() cursor.execute("SET AUTOCOMMIT = 0") sql_stmt = "INSERT INTO interest_group_expense (group_id, act_member_count, act_total_cost, act_date) VALUES (?, ?, ?, ?)" params = ( expense_info.get("group_id"), expense_info.get("act_member_count"), expense_info.get("act_total_cost"), expense_info.get("act_date"), ) cursor.execute(sql_stmt, params) comment_id = cursor.lastrowid conn.commit() cursor.execute("SET AUTOCOMMIT = 1") return comment_id except Exception, e: conn.rollback() tornado.log.logging.error(e) raise SQLError
def select_query(sql_statment, sql_params = None, dict_flag = False, sql_key = None, conn_close_flag = True): #server_log.debug("try to execute query %s with parameters %s " % (sql_statment, sql_params)) try: conn = DBPOOL.connection() if (dict_flag == True): cursor = conn.cursor(oursql.DictCursor) else: cursor = conn.cursor() if (sql_params is not None): cursor.execute(sql_statment, sql_params) else: cursor.execute(sql_statment) if (sql_key is not None): result = {} for row in cursor: result[row[sql_key]] = row else: result = cursor.fetchall() return result except Exception, e: tornado.log.logging.error(e) raise SQLError
def get_record_fromdb(record_id, tab): if record_id is None: return None if (tab not in ['alipay_payment_tracking']): record_id = long(record_id) sql_strs = {'user': '******', } keys = {'user': '******'} try: # get connection from connection pool conn = DBPOOL.connection() cursor = conn.cursor(oursql.DictCursor) rec = {} cursor.execute(sql_strs[tab], (record_id,)) for row in cursor: rec = row if rec: rec[keys[tab]] = record_id except Exception,e: tornado.log.logging.error(e) raise SQLError
def insert_or_update_query(sql_statment, sql_params, conn_close_flag = True): try: conn = DBPOOL.connection() cursor = conn.cursor() cursor.execute(sql_statment, sql_params) conn.commit() return True except Exception, e: tornado.log.logging.error(e) raise SQLError
def get_bundle_fromdb(res_list, tab, *dyn_params): res_list = res_list[:] sql_strs = {'user': '******', } keys = {'user': '******'} # concat the in clause in_clause = utils.in_clause(res_list) if in_clause == '': return res_list else: try: # get connection from connection pool conn = DBPOOL.connection() cursor = conn.cursor(oursql.DictCursor) if dyn_params: cursor.execute(sql_strs[tab] % in_clause, dyn_params) else: cursor.execute(sql_strs[tab] % in_clause) result_set = cursor.fetchall() rec_set = {} for row in result_set: # put into result set rec_set[row[keys[tab]]] = row # update the return list for i in xrange(len(res_list)): if type(res_list[i]) in (int, long): res_list[i] = rec_set.get(res_list[i]) except Exception,e: tornado.log.logging.error(e) raise SQLError else: