Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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()
- def execute_sql(self, file):
- self.f = open(file)
- self.sql_statement = self.f.read()
- self.cursor.execute(self.sql_statement)
- def execute_sql(self, file):
- with open(file) as fh:
- self.cursor.execute(fh.read())
- 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()
- ext = Extractor(plist[0], plist[1])
- ext.execute_sql_from_file()
- ext.extract_from_db_to_file()
- ext.transfer_file()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement