-
Notifications
You must be signed in to change notification settings - Fork 346
/
ExternalTable.py
159 lines (147 loc) · 6.97 KB
/
ExternalTable.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
#!/usr/bin/python
# -*- coding: utf-8 -*-
from DirectoryManagement import DirectoryManagement
import logging, random, string
from Utils import checkOptionsGivenByTheUser
from Constants import *
class ExternalTable (DirectoryManagement):
'''
Allow the user to read file thanks to external tables
'''
def __init__(self,args):
'''
Constructor
'''
logging.debug("ExternalTable object created")
DirectoryManagement.__init__(self,args)
self.tableName = self.__generateRandomString__()
self.__setDirectoryName__()
self.ERROR_EXTERNAL_TABLE_WITH_WRITE = "ORA-30653: "
self.ERROR_EXTERNAL_TABLE_READ ="ORA-29400: "
self.ERROR_ODCIEXTTABLEOPEN="ORA-29913: "
def __createTableForReadFile__(self,remoteNameFile):
'''
Create table name with, for exemple:
CREATE TABLE rf1 (id NUMBER PRIMARY KEY, path VARCHAR(255) UNIQUE, ot_format VARCHAR(6));
'''
logging.info('Create the table: {0}'.format(self.tableName))
query = "CREATE TABLE {0} (line varchar2(256)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY {1} ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE 'bad_data.bad' NOLOGFILE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS (line)) LOCATION ('{2}')) PARALLEL REJECT LIMIT 0 NOMONITORING".format(self.tableName, self.directoryName, remoteNameFile)
response = self.__execThisQuery__(query=query,isquery=False)
if isinstance(response,Exception) :
logging.info('Error with the SQL request {0}: {1}'.format(query,str(response)))
return response
else : return True
def __createTableForExec__(self,remoteNameFile):
'''
Create a table in order to execute a command
'''
logging.info('Create the table: {0}'.format(self.tableName))
query = """CREATE TABLE {0} ( line NUMBER , text VARCHAR2(4000)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY {1} ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE NOLOGFILE PREPROCESSOR {1}: '{2}' FIELDS TERMINATED BY WHITESPACE ( line RECNUM , text POSITION(1:4000)) ) LOCATION ('{2}') ) REJECT LIMIT UNLIMITED""".format(self.tableName, self.directoryName, remoteNameFile)
response = self.__execThisQuery__(query=query,isquery=False)
if isinstance(response,Exception) :
logging.info('Error with the SQL request {0}: {1}'.format(query,str(response)))
return response
else : return True
def __dropTable__(self):
'''
Drop the table with, for exemple
DROP TABLE my_table PURGE;
'''
logging.info('Drop the table: {0}'.format(self.tableName))
query = "DROP TABLE {0} PURGE".format(self.tableName)
response = self.__execThisQuery__(query=query,isquery=False)
if isinstance(response,Exception) :
logging.info('Error with the SQL request {0}: {1}'.format(query,str(response)))
return response
else : return True
def getFile (self,remotePath, remoteNameFile, localFile):
'''
Create the localFile file containing data stored on the remoteNameFile (stored in the remotePath)
'''
data = ""
logging.info("Copy the {0} remote file (stored in {1}) to {2}".format(remoteNameFile,remotePath,localFile))
status = self.__createOrRemplaceDirectory__(remotePath)
if isinstance(status,Exception): return status
status = self.__createTableForReadFile__(remoteNameFile)
if isinstance(status,Exception): return status
request = "select line from {0}".format(self.tableName)
response = self.__execThisQuery__(query=request,ld=['line'])
if isinstance(response,Exception):
logging.info('Error with the SQL request {0}: {1}'.format(request,response))
status = self.__dropDirectory__()
status = self.__dropTable__()
return response
else :
for l in response:
data += l['line']+'\n'
status = self.__dropDirectory__()
status = self.__dropTable__()
return data
def execute (self, remotePath, remoteNameFile):
'''
Execute a command
'''
logging.info("Execute the {0} command stored stored in {1}".format(remoteNameFile,remotePath))
status = self.__createOrRemplaceDirectory__(remotePath)
if isinstance(status,Exception): return status
status = self.__createTableForExec__(remoteNameFile)
if isinstance(status,Exception): return status
request = "select line from {0}".format(self.tableName)
response = self.__execThisQuery__(query=request, ld=['line'])
if isinstance(response,Exception):
logging.info('Error with the SQL request {0}: {1}'.format(request,response))
status = self.__dropDirectory__()
status = self.__dropTable__()
return response
else :
logging.info("{0} command executed without errors".format(remoteNameFile))
status = self.__dropDirectory__()
status = self.__dropTable__()
return response
def testAll(self):
'''
Test all functions
'''
folder = self.__generateRandomString__()
self.args['print'].subtitle("External table to read files ?")
logging.info("Simulate the file reading in the {0} folder thanks to an external table".format(folder))
status = self.getFile(remotePath=folder, remoteNameFile='data.txt', localFile="test.txt")
if (status == True or self.ERROR_EXTERNAL_TABLE_WITH_WRITE in str(status) or self.ERROR_EXTERNAL_TABLE_READ in str(status)):
self.args['print'].goodNews("OK")
else :
self.args['print'].badNews("KO")
self.args['print'].subtitle("External table to execute system commands ?")
logging.info("Simulate the file execution thanks to an external table")
status = self.execute (remotePath=folder, remoteNameFile='test')
if (status == True or self.ERROR_EXTERNAL_TABLE_WITH_WRITE in str(status) or self.ERROR_EXTERNAL_TABLE_READ in str(status)):
self.args['print'].goodNews("OK")
else :
self.args['print'].badNews("KO")
def runExternalTableModule (args):
'''
Run the External Table module
'''
status = True
if checkOptionsGivenByTheUser(args,["test-module","getFile","exec"]) == False : return EXIT_MISS_ARGUMENT
externalTable = ExternalTable(args)
status = externalTable.connection(stopIfError=True)
if args['test-module'] == True :
args['print'].title("Test if the External Table module can be used")
status = externalTable.testAll()
#Option 1: getFile
if args['getFile'] != None:
args['print'].title("Read the {0} file stored in the {1} path".format(args['getFile'][1],args['getFile'][0]))
data = externalTable.getFile (remotePath=args['getFile'][0], remoteNameFile=args['getFile'][1], localFile=args['getFile'][2])
if isinstance(data,Exception):
args['print'].badNews("There is an error: {0}".format(data))
else:
args['print'].goodNews("Data stored in the remote file {0} stored in {1}".format(args['getFile'][1],args['getFile'][0]))
print(data)
#Option 2: exec a script or command
if args['exec'] != None:
args['print'].title("Execute the {0} command stored in the {1} path".format(args['exec'][1],args['exec'][0]))
data = externalTable.execute (remotePath=args['exec'][0], remoteNameFile=args['exec'][1])
if isinstance(data,Exception):
args['print'].badNews("There is an error: {0}".format(data))
else:
args['print'].goodNews("The {0} command stored in {1} has been executed (normally)".format(args['exec'][1],args['exec'][0]))