def insert_category_data(data):
mariadb_connection = db_params.get_mariadb_connection()
sql_command = "INSERT INTO stock_category (category_code, category_name) VALUES (%s, %s)"
cursor = mariadb_connection.cursor()
try:
for stock_data in data:
values = (stock_data["category_code"], stock_data["category_name"])
cursor.execute(sql_command, values)
mariadb_connection.commit()
except mysql.connector.Error as err:
print("Something went wrong: {}".format(err))
except:
print('Error Occured')
finally:
cursor.close()
mariadb_connection.close()
def get_stock():
mariadb_connection = db_params.get_mariadb_connection()
sql_command = "SELECT stock_id, stock_code FROM stock"
cursor = mariadb_connection.cursor()
stock_data = []
try:
cursor.execute(sql_command)
for result in cursor.fetchall():
data = {}
data["stock_id"] = result[0]
data["stock_code"] = result[1]
stock_data.append(data)
except mysql.connector.Error as err:
print("Something went wrong: {}".format(err))
except:
print('Error Occured')
finally:
cursor.close()
mariadb_connection.close()
return stock_data
def upsert_stock_proportion_data(data):
mariadb_connection = db_params.get_mariadb_connection()
delete_all_command = "DELETE FROM stock_proportion"
sql_command = "INSERT INTO stock_proportion (rank, stock_code, proportion, create_time) VALUES (%s, %s, %s, %s)"
mariadb_connection.autocommit = False
cursor = mariadb_connection.cursor()
try:
cursor.execute(delete_all_command)
for stock_proportion in data:
values = (stock_proportion["rank"], stock_proportion["stock_code"],
stock_proportion["proportion"],
stock_proportion["create_time"])
cursor.execute(sql_command, values)
mariadb_connection.commit()
except mysql.connector.Error as err:
print("Something went wrong: {}".format(err))
mariadb_connection.rollback()
except Exception as e:
print('Error Occured at insert statemnet')
mariadb_connection.rollback()
finally:
cursor.close()
mariadb_connection.close()