1- import cx_Oracle as oracle
21from collections import namedtuple
3- from functools import lru_cache
4-
5-
6- class Schema :
7- sql_query = '''
8- SELECT COUNT(*)
9- FROM all_users
10- WHERE username = :name
11- '''
12-
13- def __init__ (self , name , plsql ):
14- self .name = name
15- self .plsql = plsql
16- result = list (plsql .query (sql_query = self .sql_query ,
17- bind_variables = {'name' : name .upper ()}))
18- if sum (row [0 ] for row in result ) == 0 :
19- raise ValueError ('Not a database schema!' )
202
3+ import cx_Oracle as oracle
214
5+ # class Schema:
6+ # sql_query = '''
7+ # SELECT COUNT(*)
8+ # FROM all_users
9+ # WHERE username = :name
10+ # '''
11+ #
12+ # def __init__(self, name, plsql):
13+ # self.name = name
14+ # self.plsql = plsql
15+ # result = list(plsql.query(sql_query=self.sql_query,
16+ # bind_variables={'name': name.upper()}))
17+ # if sum(row[0] for row in result) == 0:
18+ # raise ValueError('Not a database schema!')
2219# @lru_cache(maxsize=None)
2320# def get_schema(name, plsql):
2421# try:
@@ -38,18 +35,15 @@ def __init__(self, name, plsql):
3835
3936
4037class Subprogram :
41- ArgumentRecord = namedtuple ('ArgumentRecord' , 'argument_name, data_type, defaulted, in_out' )
42- SubprogramRecord = namedtuple ('SubprogramRecord' , 'object_type, object_name' )
43-
4438 subprogram_sql = f'''
45- SELECT { ',' . join ( SubprogramRecord . _fields ) }
39+ SELECT object_type, object_name
4640 FROM all_procedures
4741 WHERE owner = UPPER(:owner)
4842 AND object_name = UPPER(:name)
4943'''
5044
5145 argument_sql = f'''
52- SELECT { ',' . join ( ArgumentRecord . _fields ) }
46+ SELECT argument_name, data_type, defaulted, in_out
5347 FROM all_arguments
5448 WHERE owner = UPPER(:owner)
5549 AND object_name = UPPER(:name)
@@ -68,13 +62,13 @@ def __init__(self, name, plsql):
6862 schema , name = name .split ('.' )
6963
7064 binds = {'owner' : TEST_SCHEMA , 'name' : name }
71- self .definition = self . SubprogramRecord ( * next ( plsql .query (sql_query = self .subprogram_sql , bind_variables = binds )))
65+ self .definition = plsql .query (sql_query = self .subprogram_sql , bind_variables = binds ). first
7266
7367 binds = {'owner' : TEST_SCHEMA , 'name' : name }
7468 self .arguments = [
75- self . ArgumentRecord ( * argument )
69+ argument
7670 for argument
77- in plsql .query (sql_query = self .argument_sql , bind_variables = binds )
71+ in plsql .query (sql_query = self .argument_sql , bind_variables = binds ). all
7872 ]
7973
8074 if self .definition .object_type == FUNCTION :
@@ -139,6 +133,30 @@ def __call__(self, **kwargs):
139133 return subprogram (** kwargs )
140134
141135
136+ class Query :
137+
138+ def __init__ (self , connection , query , binds ):
139+ self .connection , self .query , self .binds = connection , query , binds
140+
141+ def execute (self ):
142+ with self .connection .cursor () as cursor :
143+ cursor .execute (self .query , self .binds )
144+ Result = namedtuple ('Result' , [column [0 ].lower () for column in cursor .description ])
145+ for rec in cursor :
146+ yield Result (* rec )
147+
148+ @property
149+ def first (self ):
150+ first_row = next (self .execute ())
151+ if len (first_row ) == 1 :
152+ return first_row [0 ]
153+ return first_row
154+
155+ @property
156+ def all (self ):
157+ return self .execute ()
158+
159+
142160class Database :
143161
144162 def __init__ (self , user , password , host , port , service_name , encoding ):
@@ -149,11 +167,8 @@ def execute_immediate(self, dynamic_string):
149167 with self .connection .cursor () as cursor :
150168 cursor .execute (dynamic_string )
151169
152- def query (self , sql_query , bind_variables ):
153- with self .connection .cursor () as cursor :
154- cursor .execute (sql_query , bind_variables )
155- for rec in cursor :
156- yield rec
170+ def query (self , sql_query , bind_variables = None ):
171+ return Query (self .connection , sql_query , bind_variables )
157172
158173 def __getattr__ (self , item ):
159174 return AttributeWalker (self , item )
0 commit comments