I am running a python script (actually a module which uses pymssql library of python 2.7) in a remote linux machine which is use to extract tabular data from a SQL Server and write it into CSV file on the remote machine. Which we later on pull to HDF using GetSFTP processor. I am running the python script daily once in order to generate daily load from SQL server.
This is taking more time than expectation and i am looking for improvement in execution time.
Below are the parameters i am using in ExecuteProcess processor:
Property
Value
Command
ssh
Command Arguments
-o StrictHostKeyChecking=no -i "<private key path>" <USERNAME>@<REMOTE_MACHINE_HOST> <Command to run python script>
Batch Duration
No
value set
Redirect
Error Stream
FALSE
Argument
Delimiter And the snippet of python code that i am running :
# -*- coding: utf-8 -*-import pymssql
import csv
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
server ="<MS SQL Server IP>"
user ="<SQL User Username>"
password ="<SQL User password>"
header=True
conn = pymssql.connect(server, user, password,"<DATABASE_NAME>")
cursor = conn.cursor()
query='select * from <TABLE_NAME>'
cursor.execute(query)
file_r= open('/tmp/sample.csv','w+')
file_1 = csv.writer(file_r, delimiter=',',quoting=csv.QUOTE_ALL)if header ==True:
file_1.writerow([ i[0]for i in cursor.description ])
csv_writer.writerows(cursor)
logging.info(table_name +" Transfered Successfully to"+ output_path)
conn.close()
... View more
