def store_into_db(results, table_items):
cur_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print('cur_time: {}'.format(cur_time))
conn, cur = db_util.get_cur()
update_cnt = 0
insert_cnt = 0
for result in results:
new_result = [ele if ele != '' else None for ele in result]
result = new_result
stock_code = result[0]
sql = "SELECT `{}` FROM {} WHERE `stock_code` = %s".format('`, `'.join(table_items), table_name)
db_data = db_util.execute_query(conn, cur, sql, [stock_code])
if len(db_data) == 0: # if not found in db, insert
sql = "INSERT INTO {}(`{}`, `insert_time`, `update_time`) VALUES(".format(
table_name, '`, `'.join(table_items)) + "%s, " * (len(table_items) + 1) + "%s)"
db_util.execute_query(conn, cur, sql, result + [cur_time, cur_time])
insert_cnt += 1
else: # check which item need to update
for item_idx in range(len(result)):
field = table_items[item_idx]
old_value = db_data[0][item_idx]
new_value = result[item_idx]
if str(new_value) == str(old_value): # no change
continue
if type(old_value) is float and new_value is not None and float(new_value) == old_value:
continue
update_item(conn, cur, stock_code, field, old_value, new_value, cur_time)
update_cnt += 1
print('insert row num: {}\t update item num: {}'.format(insert_cnt, update_cnt))
def saveformfields(formid, fieldid, value):
conn = make_conn()
#query_cmd = "UPDATE FieldDetails SET FieldValue = 'PANKAJ' WHERE FieldMasterID = 1 AND TemplateCustomerMappingID = 14"
query_cmd = "UPDATE FieldDetails SET FieldValue = '" + str(value) + "' WHERE " \
"FieldMasterID = " + str(fieldid) + " AND TemplateCustomerMappingID = " + str(formid)
execute_query(conn, query_cmd)
conn.close()
return "OK"
def update_item(conn, cur, stock_code, field, old_value, new_value, cur_time):
# update item
sql = "UPDATE {} SET `{}` = %s, update_time = %s WHERE `stock_code` = %s" .format(table_name, field)
db_util.execute_query(conn, cur, sql, [new_value, cur_time, stock_code])
# get update_log
sql = "SELECT `update_log` FROM {} WHERE `stock_code` = %s".format(table_name)
temp = db_util.execute_query(conn, cur, sql, [stock_code])
json_log = [] if temp[0][0] is None or temp[0][0] == '' else json.loads(temp[0][0])
json_log.insert(0, {'old_value': old_value, 'new_value': new_value,
'field': field, 'update_time': cur_time})
# add record into update_log
sql = "UPDATE {} SET `update_log` = %s WHERE `stock_code` = %s" .format(table_name)
db_util.execute_query(conn, cur, sql, [json.dumps(json_log, ensure_ascii=False), stock_code])
print('stock_code: {}, field: {}, old_value: {}, new_value: {}'.format(
stock_code, field, old_value, new_value))
def get_stock_code(exchange=None):
conn, cur = db_util.get_cur()
sql = "SELECT `stock_code`, `stock_name`, `board` FROM {} WHERE length(stock_code) = {}".\
format(table_name, 6 if exchange is None else 5)
results = db_util.execute_query(conn, cur, sql)
cur.close()
conn.close()
return results
def getformNumber(formnumber):
conn = make_conn()
TemplateCustomerMappingID = ''
query_cmd = "INSERT INTO TemplateCustomerMapping(UniqueFormNumber, TemplateMasterID) " \
"VALUES(0, " + str(formnumber) + ") RETURNING TemplateCustomerMappingID"
# query_cmd = "INSERT INTO TemplateCustomerMapping (UniqueFormNumber, TemplateMasterID) VALUES(0, 1) RETURNING TemplateCustomerMappingID "
TemplateCustomerMappingID = execute_query_value(conn, query_cmd)
query_cmd = "INSERT INTO FieldDetails(FieldValue, FieldMasterID, TemplateCustomerMappingID) " \
"SELECT '', FieldMasterID, " + str(TemplateCustomerMappingID) + \
" from FieldMaster WHERE TemplateMasterID = " + str(formnumber) + ";"
# query_cmd = "INSERT INTO FieldDetails(FieldValue, FieldMasterID, TemplateCustomerMappingID) SELECT '', FieldMasterID, " + str(TemplateCustomerMappingID) + " from FieldMaster WHERE TemplateMasterID = 1;"
result = execute_query(conn, query_cmd)
conn.close()
return TemplateCustomerMappingID
def updateOperationCompleted():
conn = make_conn()
query_cmd = "update currentoperation set operationid = 2;"
execute_query(conn, query_cmd)
conn.close()
return "OK"
def updateOperationFormDetails():
conn = make_conn()
query_cmd = "update currentoperation set operationid = 1;"
execute_query(conn, query_cmd)
conn.close()
return "OK"
def updateOperationWelcome():
conn = make_conn()
query_cmd = "update currentoperation set operationid = 0;"
execute_query(conn, query_cmd)
conn.close()
return "OK"