I have a folder where files are created all the time, once the file is the size of a 100 megabytes it means that it is ready to be loaded to the Database. The loading process should eventually be done in parallel. Five files at the same time.
In Powershell I did this easily by invoke-parallel command.
I wrote a script that opens a connection to the DB, runs procedures and executes a BULK INSERT command that uploads the file to the database.
import pymssql
import os
try:
# Open DB Connection
conn = pymssql.connect(server=server, database=Database, timeout=60)
c1 = conn.cursor(as_dict=True)
#Run First procedure with the File Name Variable
c1.callproc("First.procedure", (FileName ,))
# Recieve Variables from the First Procedure
for row in c1:
NewFileId= row['FileId']
DestinationTableName= row['DestinationTableName']
conn.commit()
# Upload the CSV file to the DB
string = "BULK INSERT DestinationTableName FROM '{CSV FILE}' WITH( FIELDTERMINATOR = ',', DATAFILETYPE = 'CHAR');"
c1.execute(string)
conn.commit()
#Run the Second procedure with the File ID and Table Name Variables
c1.callproc("2nd.procedure", (FileId, DestinationTableName,))
conn.commit()
conn.close()
f.close()
except pymssql.Error as e:
File = open("Error.txt", "a")
File.write(repr(e))
I want to run the script in parallel, by sending five filenames at a time. Any recommendations for implementation through Python?
