First of all, my code does work as expected at least to some extent. The intention of the program is to extract data from a DBMS in order to create a CSV dump and transfer it to an FTP server.
My two issues:
- The functions of the class seem to be bloated with
self.
-references. How can that be optimized? Or is that the way it ought to be? It looks overloaded to me. - The population of the plist-variable (first call of
if __name__ == '__main__':
) has been done very ineptly. The main function does not exit the program if the number of arguments does not equal 2. How can this be achieved respectively or be done better?
import cx_Oracle
import re
import os
import csv
import sys
import getopt
import ftplib
import ntpath
#os.environ['NLS_LANG'] = "en_US.ISO8859-1"
#os.environ['NLS_LANG'] = ".UTF8"
def main(argv):
inputfile = ''
outputfile = ''
usage = 'test.py -i <inputfile> -o <outputfile>'
flist = []
try:
opts, args = getopt.getopt(argv,"hi:o:",["ifile=","ofile="])
except getopt.GetoptError:
print usage
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print 'test.py -i <inputfile> -o <outputfile>'
sys.exit()
elif opt in ("-i", "--ifile"):
inputfile = arg
elif opt in ("-o", "--ofile"):
outputfile = arg
elif len(sys.argv) != 2:
print usage
sys.exit(2)
flist.append(inputfile)
flist.append(outputfile)
return flist
class Extractor(object):
def __init__(self, iput, oput):
self.oracle_server = "my_oracle_server"
self.oracle_password = "my_oracle_password"
self.oracle_port = "1521"
self.oracle_sid = "MYSID"
self.oracle_user = "MYUSER"
self.ftp_server = 'my_ftp_psw'
self.ftp_user = 'my_ftp_user'
self.ftp_passwort = 'my_ftp_passwort'
self.sql_file = os.path.join(os.getcwd(),iput)
self.csv_file = os.path.join(os.getcwd(),oput)
self.dsnStr = cx_Oracle.makedsn(self.oracle_server, self.oracle_port, self.oracle_sid)
self.connection = cx_Oracle.connect(user=self.oracle_user, password=self.oracle_password, dsn=self.dsnStr)
self.cursor = self.connection.cursor()
def execute_sql(self, file):
self.f = open(file)
self.sql_statement = self.f.read()
self.cursor.execute(self.sql_statement)
def extract_from_db(self):
self.column_list = [i[0] for i in self.cursor.description]
self.columns = ';'.join(self.column_list)
with open(self.csv_file,'w') as outfile:
outfile.write(self.columns+"\n")
writer = csv.writer(outfile, delimiter =";")
for row in self.cursor:
writer.writerow(row)
def transfer_file(self):
print "Transferring " + self.csv_file + " ..."
self.session = ftplib.FTP(self.ftp_server, self.ftp_user, self.ftp_passwort )
self.file = open(self.csv_file,'rb')
self.session.storlines('STOR ' + ntpath.basename(self.csv_file), self.file) # send the file
self.file.close()
self.session.quit()
print "Complete."
if __name__ == '__main__':
plist=main(sys.argv[1:]) # very inept, doesn't not exit as intended when number of arguments does not equal 2
ext = Extractor(plist[0], plist[1])
ext.execute_sql(ext.sql_file)
ext.extract_from_db()
ext.cursor.close()
ext.connection.close()
ext.transfer_file()