Skip to content

Commit 219e61c

Browse files
committed
added query class to make writing and using queries easier
1 parent 465cf09 commit 219e61c

3 files changed

Lines changed: 46 additions & 36 deletions

File tree

‎plsql/client.py‎

Lines changed: 46 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1,24 +1,21 @@
1-
import cx_Oracle as oracle
21
from 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

4037
class 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+
142160
class 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)

‎tests/test_functions.py‎

Lines changed: 0 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3,11 +3,6 @@ def test_simple_function(plsql, simple_function):
33
assert plsql.simple_function(p_int=None) is None
44

55

6-
def test_simple_function_indirection(plsql, simple_function):
7-
assert plsql.test_user.simple_function(p_int=5) == 5 + 5
8-
assert plsql.test_user.simple_function(p_int=None) is None
9-
10-
116
def test_function_with_defaults(plsql, function_with_defaults):
127
assert plsql.function_with_defaults() == 5
138
assert plsql.function_with_defaults(p_int_a=0) == 4

‎tests/test_query.py‎

Whitespace-only changes.

0 commit comments

Comments
 (0)