Details:
I have a code, which does the following:
- Get data from CSV and insert it into MySQL table
- Get data from SQL Server and insert it into MySQL table
- Run this code every 240 seconds - to refresh the data, in order to have up to date infromation.
I'd like to know whether the code below, I am about to provide, can be improved? In terms of performance and whether the code is readable or anything can be changed.
import pyodbc
import csv
import mysql.connector
import time
#MySQL connection
MySQLdb = mysql.connector.connect(
host="SVR",
user="root",
passwd="",
database="production"
)
#SQL Sevrer
sqlServerData = pyodbc.connect(
"Driver={SQL Server Native Client 11.0};"
"Server=svr;"
"Database=SQL_Server;"
"Trusted_Connection=yes;")
input("Press Enter to continue...")
starttime=time.time()
while True:
try:
print("---START---")
#MySQL cursor
MySQLtruncateCSV = MySQLdb.cursor()
MySQLtruncateSQL_Server = MySQLdb.cursor()
MySQLcursor = MySQLdb.cursor()
MySQLcursor2 = MySQLdb.cursor()
#SQL Server Cursor
SqlData = sqlServerData.cursor()
#Truncate MySQL Tables
CSVTruncate = 'Truncate CSV;'
SQL_ServerTruncate = 'Truncate SQL_Server;'
print("Truncate Table CSV...")
#CSV table
MySQLtruncateCSV.execute(CSVTruncate)
MySQLtruncateCSV.close()
MySQLdb.commit()
print("Truncate Table SQL_Server...")
#SQL_Server table
MySQLtruncateSQL_Server.execute(SQL_ServerTruncate)
MySQLtruncateSQL_Server.close()
MySQLdb.commit()
print("Receiving data from CSV...")
print("Sending to MySQL..")
#Insert CSV file into MySQL database
with open('G:/Technical/Labels/Production/Data/CSVExport.csv', 'r') as f:
data = csv.reader(f)
next(data, None) #Skip header
for row in data:
MySQLcursor.execute('insert into CSV (customer_code,customer_logo, product_code,product_description,allergen_info, barcode_inner,barcode_outer) VALUES (%s,%s,%s,%s,%s,%s,%s);', row)
MySQLdb.commit()
MySQLcursor.close()
print("Receiving SQL_Server data...")
#Get data from SQL_Server
SqlData.execute("select p.id, p.code,p.description, p.searchRef1, so.number, c.code, c.name \
from salesorderline sol join \
salesorder so \
on sol.salesorderid = so.id join \
product p \
on sol.productid = p.id join \
customer c \
on so.customerid = c.id \
where so.orderdate > dateadd(dd,-10,cast(getdate() as date));")
print("Sending to MySQL..")
#Send SQL_Server data into MySQL
for x in SqlData.fetchall():
a,b,c,d,e,f,g = x
MySQLcursor2.execute("insert into SQL_Server (product_id, product_code, product_description, product_weight, \
salesorder_number, customer_code, customer_name) values (%s,%s,%s,%s,%s,%s,%s);", (a,b,c,d,e,f,g))
SqlData.close()
MySQLdb.commit()
MySQLcursor2.close()
print("---END---")
time.sleep(240 - ((time.time() - starttime) % 240))
except:
print("An error has occured.. Please contact Technical")
break