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:
