1+ from __future__ import annotations
2+
13import datetime
24from collections import namedtuple
35from itertools import starmap , chain
1012PACKAGE = "PACKAGE"
1113
1214
15+ def parse_in_out (parameters ):
16+ return {
17+ param_name : param_value .getvalue () for param_name , param_value in parameters
18+ }
19+
20+
21+ def oracle_plsql_record (connection , argument , mapping , record_type = None ):
22+ if record_type is None :
23+ record_type = connection .gettype (argument .extended_type .upper ())
24+ record = record_type .newobject ()
25+
26+ for key , value in mapping .items ():
27+ setattr (record , key .upper (), value )
28+ return record
29+
30+
31+ def oracle_plsql_table (connection , argument , mapping_of_recs ):
32+ table_type = connection .gettype (argument .extended_type )
33+ table = table_type .newobject ()
34+
35+ if table_type .elementType is not None :
36+ for key , value in mapping_of_recs .items ():
37+ rec = oracle_plsql_record (connection , None , value , table_type .elementType )
38+ table .setelement (key , rec )
39+ return table
40+
41+ return mapping_of_recs
42+
43+
44+ def translate_types (connection , arguments , parameters , conversion_func , data_type ):
45+ return {
46+ argument .argument_name .lower (): conversion_func (
47+ connection , argument , parameters [argument .argument_name .lower ()]
48+ )
49+ for argument in arguments
50+ if "IN" in argument .in_out
51+ if argument .data_type == data_type
52+ }
53+
54+
1355class Subprogram :
14- argument_sql = f"""
15- SELECT argument_name, data_type, defaulted, in_out, type_owner || '.' || type_name || '.' || type_subname extended_type
56+ argument_sql = """
57+ SELECT argument_name,
58+ data_type,
59+ defaulted,
60+ in_out,
61+ type_owner || '.' || type_name || '.' || type_subname extended_type
1662 FROM all_arguments
1763 WHERE owner = UPPER(:owner)
1864 AND object_name = :object_name
@@ -79,12 +125,6 @@ def __call__(self, **kwargs):
79125 pass
80126 kwargs [name ] = parameter
81127
82- def parse_in_out (parameters ):
83- return {
84- param_name : param_value .getvalue ()
85- for param_name , param_value in parameters
86- }
87-
88128 object_type = self .definition .object_type
89129
90130 if object_type == PACKAGE :
@@ -93,44 +133,13 @@ def parse_in_out(parameters):
93133 else :
94134 object_type = PROCEDURE
95135
96- def oracle_record (argument , mapping , record_type = None ):
97- if record_type is None :
98- record_type = self .plsql .connection .gettype (
99- argument .extended_type .upper ()
100- )
101- record = record_type .newobject ()
102-
103- for key , value in mapping .items ():
104- setattr (record , key .upper (), value )
105- return record
106-
107- def oracle_table (argument , list_of_recs ):
108- table_type = self .plsql .connection .gettype (argument .extended_type )
109- table = table_type .newobject ()
110-
111- if table_type .elementType is not None :
112- for mapping in list_of_recs :
113- rec = oracle_record (None , mapping , table_type .elementType )
114- table .append (rec )
115- return table
116-
117- return list_of_recs
118-
119- def translate_types (conversion_func , data_type ):
120- return {
121- argument .argument_name .lower (): conversion_func (
122- argument , kwargs [argument .argument_name .lower ()]
123- )
124- for argument in self .arguments
125- if "IN" in argument .in_out
126- if argument .data_type == data_type
127- }
128-
129136 translated_types = chain .from_iterable (
130- translate_types (func , data_type ).items ()
137+ translate_types (
138+ self .plsql .connection , self .arguments , kwargs , func , data_type
139+ ).items ()
131140 for func , data_type in [
132- (oracle_record , "PL/SQL RECORD" ),
133- (oracle_table , "PL/SQL TABLE" ),
141+ (oracle_plsql_record , "PL/SQL RECORD" ),
142+ (oracle_plsql_table , "PL/SQL TABLE" ),
134143 ]
135144 )
136145
@@ -152,6 +161,8 @@ def translate_types(conversion_func, data_type):
152161 attributes = result_list [0 ].type .attributes
153162 except IndexError :
154163 result = []
164+ except AttributeError :
165+ result = result_list
155166 else :
156167 result = [
157168 {
@@ -167,7 +178,6 @@ def translate_types(conversion_func, data_type):
167178 attribute .name .lower (): getattr (result , attribute .name )
168179 for attribute in attributes
169180 }
170-
171181 except AttributeError :
172182 pass
173183
@@ -187,10 +197,14 @@ def retrieve_subprograms(attributes, plsql):
187197 attribute_len = len (attributes )
188198
189199 base_query = """
190- SELECT owner, object_id, subprogram_id, NVL(procedure_name, object_name) object_name, object_type
191- FROM all_procedures
192- WHERE object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE') AND {extra_where_predicates}
193- """
200+ SELECT owner,
201+ object_id,
202+ subprogram_id,
203+ NVL(procedure_name, object_name) object_name,
204+ object_type
205+ FROM all_procedures
206+ WHERE object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE') AND {extra_where_predicates}
207+ """
194208
195209 def query_subprograms (** kwargs ):
196210 extra_where_predicates = (f"{ key } = UPPER(:{ key } )" for key in kwargs .keys ())
@@ -221,8 +235,12 @@ def query_subprograms(**kwargs):
221235
222236def match_parameters (subprograms , parameters , plsql ):
223237 # fixme: this sql is essentially the same as in when building the Subprogram
224- argument_sql = f"""
225- SELECT argument_name, data_type, defaulted, in_out, type_owner || '.' || type_name || '.' || type_subname extended_type
238+ argument_sql = """
239+ SELECT argument_name,
240+ data_type,
241+ defaulted,
242+ in_out,
243+ type_owner || '.' || type_name || '.' || type_subname extended_type
226244 FROM all_arguments
227245 WHERE owner = UPPER(:owner)
228246 AND object_name = :object_name
@@ -319,17 +337,25 @@ def __iter__(self):
319337
320338
321339class Database :
322- def __init__ (self , user , password , host , port , service_name , encoding ):
340+ def __init__ (
341+ self ,
342+ user : str ,
343+ password : str ,
344+ host : str ,
345+ port : int ,
346+ service_name : str ,
347+ encoding : str ,
348+ ):
323349 dsn = oracle .makedsn (host , port , service_name = service_name )
324350 self .connection = oracle .connect (
325351 user = user , password = password , dsn = dsn , encoding = encoding , nencoding = encoding
326352 )
327353
328- def execute_immediate (self , dynamic_string ):
354+ def execute_immediate (self , dynamic_string : str ):
329355 with self .connection .cursor () as cursor :
330356 cursor .execute (dynamic_string )
331357
332- def query (self , sql_query , binds = None ):
358+ def query (self , sql_query : str , binds = None ):
333359 return Query (self .connection , sql_query , binds )
334360
335361 def __getattr__ (self , item ):
0 commit comments