Skip to content

Commit 8753465

Browse files
committed
added back support for queries
1 parent c253079 commit 8753465

3 files changed

Lines changed: 91 additions & 16 deletions

File tree

‎plsql/client.py‎

Lines changed: 57 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,10 @@
22

33
from collections import namedtuple
44
from enum import IntEnum
5-
from typing import Tuple, Iterator
5+
from itertools import starmap
6+
from operator import itemgetter
7+
from typing import Iterator
8+
from typing import Tuple
69

710
import cx_Oracle as oracle
811

@@ -144,9 +147,48 @@ def _exhaustive_name_resolution(
144147
raise NotFound(f"name {name} not found")
145148

146149

150+
class Schema:
151+
def __init__(self, plsql: Database, name: str):
152+
self._plsql, self._name = plsql, name
153+
154+
147155
class Function:
148-
def __init__(self, name: ResolvedName):
149-
self._name = name
156+
def __init__(self, plsql: Database, name: ResolvedName):
157+
self._plsql, self._name = plsql, name
158+
159+
160+
class Query:
161+
def __init__(
162+
self, connection: oracle.Connection, sql: str, *positional_binds, **named_binds
163+
):
164+
if all([positional_binds, named_binds]):
165+
raise ValueError("Can not bind both by position and name")
166+
167+
binds = positional_binds or named_binds
168+
169+
self._connection, self.sql, self.binds = (connection, sql, binds)
170+
171+
def _execute(self):
172+
with self._connection.cursor() as cursor:
173+
cursor.execute(self.sql, self.binds)
174+
175+
if len(cursor.description) > 1:
176+
record_type = namedtuple(
177+
"Record", (column[0].lower() for column in cursor.description)
178+
)
179+
records = starmap(record_type, cursor)
180+
else:
181+
records = map(itemgetter(0), cursor)
182+
183+
for record in records:
184+
yield record
185+
186+
@property
187+
def first(self):
188+
return next(self._execute())
189+
190+
def __iter__(self):
191+
return self._execute()
150192

151193

152194
class Database:
@@ -167,11 +209,17 @@ def __init__(
167209
def __getattr__(self, item):
168210
try:
169211
name = self._name_resolve(item)
170-
except oracle.DatabaseError:
171-
raise AttributeError
212+
except (oracle.DatabaseError, NotFound):
213+
if self.query(
214+
"""SELECT COUNT(*) FROM all_users WHERE username = UPPER(:username)""",
215+
username=item,
216+
).first:
217+
return Schema(plsql=self, name=item)
172218

173-
if name.object_type == ObjectTypes.function:
174-
return Function(name)
219+
raise AttributeError
220+
else:
221+
if name.object_type == ObjectTypes.function:
222+
return Function(plsql=self, name=name)
175223

176224
raise AttributeError
177225

@@ -198,8 +246,8 @@ def _name_resolve(self, name: str) -> ResolvedName:
198246
object_number,
199247
)
200248

201-
def query(self, sql: str, *args, **kwargs):
202-
pass
249+
def query(self, sql: str, *positional_binds, **named_binds) -> Query:
250+
return Query(self._connection, sql, *positional_binds, **named_binds)
203251

204252
def execute(self, statement: str, *args, **kwargs) -> None:
205253
with self._connection.cursor() as cursor:

‎tests/test_describe_procedure.py‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,16 +1,16 @@
11
# 0 placeholder for procedures with no arguments -- ------||------
2-
# 1 VARCHAR, VARCHAR, STRING -- string
2+
# 1 VARCHAR, VARCHAR, STRING -- str
33
# 2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL -- float
44
# 3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL -- int
5-
# 8 LONG -- ??? str or io ???
5+
# 8 LONG -- str
66
# 11 ROWID -- str
77
# 12 DATE -- datetime.datetime
88
# 23 RAW -- bytes
9-
# 24 LONG RAW -- ??? bytes or byte io ???
9+
# 24 LONG RAW -- bytes
1010
# 58 OPAQUE TYPE -- ??? XMLType in Oracle
1111
# 96 CHAR (ANSI FIXED CHAR), CHARACTER -- str
12-
# 106 MLSLABEL -- ??? only backwards compatibility
13-
# 121 OBJECT -- ???
12+
# 106 MLSLABEL -- ??? only backwards compatibility ???
13+
# 121 OBJECT -- cx_Oracle.Object ???
1414
# 122 NESTED TABLE -- list
1515
# 123 VARRAY -- list
1616
# 178 TIME -- datetime.datetime

‎tests/test_query.py‎

Lines changed: 29 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,32 @@
11
import pytest
22

33

4-
def test_with_no_binds(plsql):
5-
plsql.query("""SELECT 1 FROM dual""").first == 1
4+
def test_single_column_returns_just_the_value(plsql):
5+
assert plsql.query("""SELECT 1 FROM dual""").first == 1
6+
assert plsql.query("""SELECT 'a' FROM dual""").first == "a"
7+
assert plsql.query("""SELECT 3.5 FROM dual""").first == 3.5
8+
assert plsql.query("""SELECT NULL FROM dual""").first is None
9+
10+
11+
def test_select_with_multiple_columns_returns_named_tuples(plsql):
12+
result = plsql.query("""SELECT 1 AS first, 2 AS second FROM dual""").first
13+
assert (result.first, result.second) == (1, 2)
14+
15+
16+
def test_can_iterate_over_multiple_rows(plsql):
17+
assert sum(plsql.query("""SELECT level FROM dual CONNECT BY level < 10""")) == 45
18+
19+
20+
def test_can_use_positional_binds(plsql):
21+
bind_value = 6
22+
assert plsql.query("""SELECT :a FROM dual""", bind_value).first == bind_value
23+
24+
25+
def test_can_use_binds_by_name(plsql):
26+
bind_value = 6
27+
assert plsql.query("""SELECT :b FROM dual""", b=bind_value).first == bind_value
28+
29+
30+
def test_binding_by_position_and_name_raises_an_exception(plsql):
31+
with pytest.raises(ValueError, match="Can not bind both by position and name"):
32+
plsql.query("""SELECT :c, :d FROM dual""", 5, d=5)

0 commit comments

Comments
 (0)