def populate_lmp_table(db: sq.connect):
global lmr
# print(lmr)
# lmr = pd.read_csv(lmr, skiprows=4)
# lmr.drop(lmr.tail(1).index, inplace=True)
# lmr.drop(0, inplace=True)
with db.cursor() as cursor:
data = lmr[[
'Location ID', 'Hour Ending', 'Date', 'Locational Marginal Price',
'Energy Component', 'Congestion Component',
'Marginal Loss Component'
]]
for row in data.itertuples(index=False):
l_id = row[0]
hour_ending = row[1]
m, d, y = row[2].split('/')
date = y + '-' + m + '-' + d
lmp = row[3]
ene_comp = row[4]
cog_comp = row[5]
ml_comp = row[6]
try:
sql = 'INSERT INTO `day_ahead_market` (L_ID, Hour_Ending, Date, LMP, Energy_Comp, Cog_Comp, ML_Comp) ' \
'VALUES (%s, %s, \'%s\', %s, %s, %s, %s)' % (
l_id, hour_ending, date, lmp, ene_comp, cog_comp, ml_comp)
cursor.execute(sql)
db.commit()
except:
# print(sql)
db.rollback()
def populate_aucresult_table(fl: pd.DataFrame, db: sq.connect):
global auc_name2ID, result_id
with db.cursor() as cursor:
auc_result = fl[[
'Auction Name', 'Customer ID', 'Source Location ID',
'Sink Location ID', 'Buy/Sell', 'ClassType', 'Award FTR MW',
'Award FTR Price'
]]
for row in auc_result.itertuples(index=False):
auc_id = auc_name2ID[row[0]]
c_id = row[1]
source_id = row[2]
sink_id = row[3]
b_s = row[4]
class_type = row[5]
ftr_mw = float(row[6])
ftr_price = float(row[7])
try:
sql = 'INSERT INTO `auction_results` (Auc_Res_ID, Auc_ID, C_ID, Sou_ID, Sin_ID, Buy_Sell, ' \
'Class_Type, Award_FTR_MW, Award_FTR_Price ) VALUES (%s, %s, %s, %s, %s, \'%s\',' \
' \'%s\', %s, %s)' % (
result_id, auc_id, c_id, source_id, sink_id, b_s, class_type, ftr_mw, ftr_price)
result_id += 1
cursor.execute(sql)
db.commit()
except:
db.rollback()
def run_given_database_query(self, _db_name: str, _sql: str,
_conn: pymysql.connect) -> list:
"""Run given sql statement"""
try:
with _conn.cursor() as _cursor:
_cursor.execute(_sql)
return _cursor.fetchall()
except:
_conn.rollback()
def create_database_utf8(self, _conn: pymysql.connect, _name: str):
"""Create a new database in utf-8 format
:param _conn: database connection
:param _name: database name
:return:
"""
try:
with _conn.cursor() as _cursor:
_sql = f"CREATE DATABASE IF NOT EXISTS `{_name}` CHARACTER SET utf8 COLLATE utf8_general_ci"
_cursor.execute(_sql)
except:
print(f'[Error]: Create database {_name} failed')
_conn.rollback()
def insert_random_data_into_data2_table(self, _conn: pymysql.connect):
"""Insert data into data1 table
:param _conn: database connection
"""
with open('./data2.txt', 'r') as f_in:
_all_sql_statements = f_in.readlines()
try:
with _conn.cursor() as _cursor:
for _sql in _all_sql_statements:
_cursor.execute(_sql)
_conn.commit()
except:
_conn.rollback()
def populate_customer_table(fl: pd.DataFrame, db: sq.connect):
with db.cursor() as cursor:
customer = fl[['Customer ID', 'Customer Name']].drop_duplicates()
for row in customer.itertuples(index=False):
c_id = row[0]
c_name = row[1]
try:
sql = 'INSERT INTO `customer` (C_ID, C_Name) VALUES (%s, \'%s\')' % (
c_id, c_name)
cursor.execute(sql)
# print('1 row inserted')
db.commit()
except:
# print('Failed to insert into Customer table, PK already exists.')
db.rollback()
def populate_auction_table(fl: pd.DataFrame, db: sq.connect):
global auc_name2ID, auction_id
with db.cursor() as cursor:
auction_name = fl['Auction Name'].unique()
for name in auction_name:
period = 365
if len(name.split(' ')) == 3:
period = 30
try:
sql = 'INSERT INTO `auction` (Auc_ID, Auc_Name, Period) ' \
'VALUES (%s, \'%s\', %s)' % (auction_id, name, period)
auc_name2ID[name] = auction_id
cursor.execute(sql)
auction_id += 1
db.commit()
except:
db.rollback()
def create_data1_table(self,
_conn: pymysql.connect,
_table_name: str = 'data1'):
""" Create data1 table for testing
:param _conn: database connection
:param _table_name: default data1
:return:
"""
try:
with _conn.cursor() as _cursor:
_sql = f'CREATE TABLE IF NOT EXISTS {_table_name}(' \
f'id TEXT, order_id TEXT, cost FLOAT)'
_cursor.execute(_sql)
except:
print(f'[Error]: Create table {_table_name} failed')
_conn.rollback()
def insert_random_data_into_data_table(self, _conn: pymysql.connect):
"""Insert some random data into data table
:param _conn: database connection
"""
try:
with _conn.cursor() as _cursor:
for _ in range(100):
# Create a new record
_letters = string.ascii_lowercase
_name = ''.join(random.choice(_letters) for i in range(6))
_age = random.randint(20, 50)
_sql = "INSERT INTO `data` (`name`, `age`) VALUES (%s, %s)"
_cursor.execute(_sql, [_name, _age])
_conn.commit()
except:
_conn.rollback()
def create_data_table(self,
_conn: pymysql.connect,
_table_name: str = 'data'):
"""Create the data table
:param _conn: Database connection
:param _table_name: table name, default 'data'
:return:
"""
try:
with _conn.cursor() as _cursor:
_sql = f'CREATE TABLE IF NOT EXISTS {_table_name}(' \
f'id INT AUTO_INCREMENT PRIMARY KEY, ' \
f'name TEXT, age INT)'
_cursor.execute(_sql)
except:
print(f'[Error]: Create table {_table_name} failed')
_conn.rollback()
def populate_location_table(fl: pd.DataFrame, db: sq.connect):
global lmr
with db.cursor() as cursor:
lmr = pd.read_csv(lmr, skiprows=4)
lmr.drop(lmr.tail(1).index, inplace=True)
lmr.drop(0, inplace=True)
# insert location id, location name, location type into location table
# l_id, l_name and l_type are parsed from the data frame
location = lmr[['Location ID', 'Location Name',
'Location Type']].drop_duplicates()
for row in location.itertuples(index=False):
l_id = row[0]
l_name = row[1]
l_type = row[2]
try:
sql = 'INSERT INTO `location` (L_ID, L_Name, L_Type) VALUES (%s, \'%s\', \'%s\')' % (
l_id, l_name, l_type)
cursor.execute(sql)
db.commit()
except:
db.rollback()
# Insert location information again parsed from auction result data frame, this is for the case that something
# appeared in the result table was not in the LMR table, doing this could help cover these missing locations
location = fl[[
'Source Location ID', 'Source Location Name',
'Source Location Type'
]].drop_duplicates()
for row in location.itertuples(index=False):
l_id = row[0]
l_name = row[1]
l_type = row[2]
try:
sql = 'INSERT INTO `location` (L_ID, L_Name, L_Type) VALUES (%s, \'%s\', \'%s\')' % (
l_id, l_name, l_type)
cursor.execute(sql)
db.commit()
except:
db.rollback()
# do the same thing for sink location
location = fl[[
'Sink Location ID', 'Sink Location Name', 'Sink Location Type'
]].drop_duplicates()
for row in location.itertuples(index=False):
l_id = row[0]
l_name = row[1]
l_type = row[2]
try:
sql = 'INSERT INTO `location` (L_ID, L_Name, L_Type) VALUES (%s, \'%s\', \'%s\')' % (
l_id, l_name, l_type)
cursor.execute(sql)
db.commit()
except:
db.rollback()
def set_fk(db: sq.connect):
with db.cursor() as cursor:
# populate fk in auction result tables
try:
sql = 'ALTER TABLE `proj`.`auction_results` ' \
'ADD CONSTRAINT `auction_fk` FOREIGN KEY (`Auc_ID`) REFERENCES `proj`.`auction` (`Auc_ID`) ' \
'ON DELETE NO ACTION ON UPDATE NO ACTION;'
cursor.execute(sql)
db.commit()
except:
db.rollback()
try:
sql = 'ALTER TABLE `proj`.`auction_results` ' \
'ADD CONSTRAINT `source_fk` FOREIGN KEY (`Sou_ID`) REFERENCES `proj`.`location` (`L_ID`) ' \
'ON DELETE NO ACTION ON UPDATE NO ACTION;'
cursor.execute(sql)
db.commit()
except:
db.rollback()
try:
sql = 'ALTER TABLE `proj`.`auction_results` ' \
'ADD CONSTRAINT `sink_fk` FOREIGN KEY (`Sin_ID`) REFERENCES `proj`.`location` (`L_ID`) ' \
'ON DELETE NO ACTION ON UPDATE NO ACTION;'
cursor.execute(sql)
db.commit()
except:
db.rollback()
try:
sql = 'ALTER TABLE `proj`.`auction_results` ' \
'ADD CONSTRAINT `sou_lmp_fk` FOREIGN KEY (`Sou_ID`) REFERENCES `proj`.`day_ahead_market` (`L_ID`) ' \
'ON DELETE NO ACTION ON UPDATE NO ACTION;'
cursor.execute(sql)
db.commit()
except:
db.rollback()
try:
sql = 'ALTER TABLE `proj`.`auction_results` ' \
'ADD CONSTRAINT `sin_lmp_fk` FOREIGN KEY (`Sin_ID`) REFERENCES `proj`.`day_ahead_market` (`L_ID`) ' \
'ON DELETE NO ACTION ON UPDATE NO ACTION;'
cursor.execute(sql)
db.commit()
except:
db.rollback()
try:
sql = 'ALTER TABLE `proj`.`day_ahead_market` ADD CONSTRAINT `location_fk` FOREIGN KEY (`L_ID`) ' \
'REFERENCES `proj`.`location` (`L_ID`) ' \
'ON DELETE NO ACTION ON UPDATE NO ACTION;'
cursor.execute(sql)
db.commit()
except:
db.rollback()