def validator(sp_name, p_date, p_key):
try:
connection = db_connect("info_process")
cursor = connection.cursor()
args = (
p_date,
p_key,
)
cursor.callproc(sp_name, args)
result = cursor.fetchone()
connection.commit()
# print results
write_log("executing validator stored procedure: " + sp_name)
for result in cursor.stored_results():
write_log("Validation status: ", result.fetchone()[0])
except mysql.connector.Error as error:
write_log(
"Conclusion: Failed to execute stored procedure: {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
write_log("Conclusion: MySQL connection is closed")
def csv2db(import_code):
try:
connection = db_connect("info_extract")
sql_select_Query = csv_dict[import_code]
sql_select_Query = sql_select_Query.replace('#PATH#', CSV_IN_DIR)
print(sql_select_Query)
cursor = connection.cursor()
cursor.execute(sql_select_Query)
connection.commit()
write_log("Import csv2db key is: ", import_code)
write_log("Total number of imported rows is: ", cursor.rowcount)
except Error as e:
write_log("Error importing data to MySQL table: ", e)
finally:
if (connection.is_connected()):
connection.close()
cursor.close()
write_log("MySQL connection is closed")
def db2csv(export_code):
try:
connection = db_connect("info_report")
sql_select_Query = csv_dict[export_code]
sql_select_Query = sql_select_Query.replace('#PATH#', src_dir)
cursor = connection.cursor()
cursor.execute(sql_select_Query)
connection.commit()
write_log("Export db2csv key is: ", export_code)
write_log("Total number of Exported rows is: ", cursor.rowcount)
for root, dirnames, filenames in os.walk(src_dir):
for filename in fnmatch.filter(filenames, '*.csv'):
shutil.move(os.path.join(root, filename), dst_dir)
except Error as e:
write_log("Error exporting data to csv: ", e)
finally:
if (connection.is_connected()):
connection.close()
cursor.close()
write_log("MySQL connection is closed")
def sp_runner(sp_name, parameter):
try:
connection = db_connect("info_process")
cursor = connection.cursor()
args = (parameter,)
cursor.callproc(sp_name, args)
connection.commit()
# print results
write_log("executing stored procedure: " + sp_name)
for result in cursor.stored_results():
write_log(result.fetchall())
except mysql.connector.Error as error:
write_log("Conclusion: Failed to execute stored procedure: {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
write_log("Conclusion: MySQL connection is closed")
#test
#sp_runner('sp_t2p_customer_reference', '20190331')
def driver(process_name, truncate_key, import_code_list, sp_list,
process_date):
write_log("Start process: ", process_name)
write_log("Process_date: ", process_date)
write_log("Truncating extract and transform tables")
sp_runner('sp_truncate', truncate_key)
write_log("Importing CSV for: ", import_code_list)
for import_code in import_code_list:
csv2db(import_code)
write_log("Loading csv load key: ", import_code)
write_log("Running stored procedures from the list")
for sp_name in sp_list:
sp_runner(sp_name, process_date)
write_log("Running: ", sp_name, " for date: ", process_date)
write_log("Validating reports from the validation list")
for val_key in validation_list:
validator(validator_process, process_date, val_key)
write_log("Exporting data to csv files")
for export_code in export_list:
db2csv(export_code)
write_log("End of processing!")
from selenium import webdriver
import shutil, os, fnmatch, time
from python.setting import TMP_DIR, chromedriver, downloads
from python.helper.logger import write_log
from selenium.webdriver.chrome.options import Options
options = Options()
#options.add_argument("--start-minimized")
options.add_argument("window-size=10,10")
driver = webdriver.Chrome(options= options, executable_path=chromedriver)
driver.minimize_window()
write_log ("Opening TSETMC website for downloading stock price list.")
driver.get("http://members.tsetmc.com/tsev2/excel/MarketWatchPlus.aspx?d=0")
time.sleep(15)
driver.quit()
write_log ("Closing TSETMC website.")
for root, dirnames, filenames in os.walk(downloads):
for filename in fnmatch.filter(filenames, 'MarketWatchPlus*'):
write_log ("Moving file: ", filename, "to tmp directory.")
shutil.move(os.path.join(root, filename),TMP_DIR)
return filename
write_log ("End of web scraping for TSETMC")