Skip to content

Commit 49e8e10

Browse files
author
James William Pye
committed
Implement the one-shot query interface: Connection.query[.<method>](*params)
While the implementation is currently mere syntactic convenience, the driver does have the ability to be more intelligent about statement disposal. Optimizations are possible. ("query" *is* in verb form, like "prepare")
1 parent 5fc26c3 commit 49e8e10

6 files changed

Lines changed: 212 additions & 138 deletions

File tree

‎postgresql/api.py‎

Lines changed: 138 additions & 123 deletions
Original file line numberDiff line numberDiff line change
@@ -306,130 +306,10 @@ def seek(self, offset, whence = 'ABSOLUTE'):
306306
FROM_END will effectively be ABSOLUTE.
307307
"""
308308

309-
class Statement(
310-
Element,
311-
collections.Callable,
312-
collections.Iterable,
313-
):
309+
class Execution(metaclass = abc.ABCMeta):
314310
"""
315-
Instances of `Statement` are returned by the `prepare` method of
316-
`Database` instances.
317-
318-
A Statement is an Iterable as well as Callable.
319-
320-
The Iterable interface is supported for queries that take no arguments at
321-
all. It allows the syntax::
322-
323-
>>> for x in db.prepare('select * FROM table'):
324-
... pass
311+
The abstract class of execution methods.
325312
"""
326-
_e_label = 'STATEMENT'
327-
_e_factors = ('database', 'statement_id', 'string',)
328-
329-
@property
330-
@abc.abstractmethod
331-
def statement_id(self) -> str:
332-
"""
333-
The statment's identifier.
334-
"""
335-
336-
@property
337-
@abc.abstractmethod
338-
def string(self) -> object:
339-
"""
340-
The SQL string of the prepared statement.
341-
342-
`None` if not available. This can happen in cases where a statement is
343-
prepared on the server and a reference to the statement is sent to the
344-
client which subsequently uses the statement via the `Database`'s
345-
`statement` constructor.
346-
"""
347-
348-
@property
349-
@abc.abstractmethod
350-
def sql_parameter_types(self) -> [str]:
351-
"""
352-
The type of the parameters required by the statement.
353-
354-
A sequence of `str` objects stating the SQL type name::
355-
356-
['INTEGER', 'VARCHAR', 'INTERVAL']
357-
"""
358-
359-
@property
360-
@abc.abstractmethod
361-
def sql_column_types(self) -> [str]:
362-
"""
363-
The type of the columns produced by the statement.
364-
365-
A sequence of `str` objects stating the SQL type name::
366-
367-
['INTEGER', 'VARCHAR', 'INTERVAL']
368-
"""
369-
370-
@property
371-
@abc.abstractmethod
372-
def pg_parameter_types(self) -> [int]:
373-
"""
374-
The type Oids of the parameters required by the statement.
375-
376-
A sequence of `int` objects stating the PostgreSQL type Oid::
377-
378-
[27, 28]
379-
"""
380-
381-
@property
382-
@abc.abstractmethod
383-
def pg_column_types(self) -> [int]:
384-
"""
385-
The type Oids of the columns produced by the statement.
386-
387-
A sequence of `int` objects stating the SQL type name::
388-
389-
[27, 28]
390-
"""
391-
392-
@property
393-
@abc.abstractmethod
394-
def column_names(self) -> [str]:
395-
"""
396-
The attribute names of the columns produced by the statement.
397-
398-
A sequence of `str` objects stating the column name::
399-
400-
['column1', 'column2', 'emp_name']
401-
"""
402-
403-
@property
404-
@abc.abstractmethod
405-
def column_types(self) -> [type]:
406-
"""
407-
The Python types of the columns produced by the statement.
408-
409-
A sequence of type objects::
410-
411-
[<class 'int'>, <class 'str'>]
412-
"""
413-
414-
@property
415-
@abc.abstractmethod
416-
def parameter_types(self) -> [type]:
417-
"""
418-
The Python types expected of parameters given to the statement.
419-
420-
A sequence of type objects::
421-
422-
[<class 'int'>, <class 'str'>]
423-
"""
424-
425-
@abc.abstractmethod
426-
def clone(self) -> "Statement":
427-
"""
428-
Create a new statement object using the same factors as `self`.
429-
430-
When used for refreshing plans, the new clone should replace references to
431-
the original.
432-
"""
433313

434314
@abc.abstractmethod
435315
def __call__(self, *parameters : "Positional Parameters") -> ["Row"]:
@@ -585,11 +465,137 @@ def load_chunks(self,
585465
that the operation can be optimized.
586466
"""
587467

468+
class Statement(
469+
Element,
470+
collections.Callable,
471+
collections.Iterable,
472+
):
473+
"""
474+
Instances of `Statement` are returned by the `prepare` method of
475+
`Database` instances.
476+
477+
A Statement is an Iterable as well as Callable.
478+
479+
The Iterable interface is supported for queries that take no arguments at
480+
all. It allows the syntax::
481+
482+
>>> for x in db.prepare('select * FROM table'):
483+
... pass
484+
"""
485+
_e_label = 'STATEMENT'
486+
_e_factors = ('database', 'statement_id', 'string',)
487+
488+
@property
489+
@abc.abstractmethod
490+
def statement_id(self) -> str:
491+
"""
492+
The statment's identifier.
493+
"""
494+
495+
@property
496+
@abc.abstractmethod
497+
def string(self) -> object:
498+
"""
499+
The SQL string of the prepared statement.
500+
501+
`None` if not available. This can happen in cases where a statement is
502+
prepared on the server and a reference to the statement is sent to the
503+
client which subsequently uses the statement via the `Database`'s
504+
`statement` constructor.
505+
"""
506+
507+
@property
508+
@abc.abstractmethod
509+
def sql_parameter_types(self) -> [str]:
510+
"""
511+
The type of the parameters required by the statement.
512+
513+
A sequence of `str` objects stating the SQL type name::
514+
515+
['INTEGER', 'VARCHAR', 'INTERVAL']
516+
"""
517+
518+
@property
519+
@abc.abstractmethod
520+
def sql_column_types(self) -> [str]:
521+
"""
522+
The type of the columns produced by the statement.
523+
524+
A sequence of `str` objects stating the SQL type name::
525+
526+
['INTEGER', 'VARCHAR', 'INTERVAL']
527+
"""
528+
529+
@property
530+
@abc.abstractmethod
531+
def pg_parameter_types(self) -> [int]:
532+
"""
533+
The type Oids of the parameters required by the statement.
534+
535+
A sequence of `int` objects stating the PostgreSQL type Oid::
536+
537+
[27, 28]
538+
"""
539+
540+
@property
541+
@abc.abstractmethod
542+
def pg_column_types(self) -> [int]:
543+
"""
544+
The type Oids of the columns produced by the statement.
545+
546+
A sequence of `int` objects stating the SQL type name::
547+
548+
[27, 28]
549+
"""
550+
551+
@property
552+
@abc.abstractmethod
553+
def column_names(self) -> [str]:
554+
"""
555+
The attribute names of the columns produced by the statement.
556+
557+
A sequence of `str` objects stating the column name::
558+
559+
['column1', 'column2', 'emp_name']
560+
"""
561+
562+
@property
563+
@abc.abstractmethod
564+
def column_types(self) -> [type]:
565+
"""
566+
The Python types of the columns produced by the statement.
567+
568+
A sequence of type objects::
569+
570+
[<class 'int'>, <class 'str'>]
571+
"""
572+
573+
@property
574+
@abc.abstractmethod
575+
def parameter_types(self) -> [type]:
576+
"""
577+
The Python types expected of parameters given to the statement.
578+
579+
A sequence of type objects::
580+
581+
[<class 'int'>, <class 'str'>]
582+
"""
583+
584+
@abc.abstractmethod
585+
def clone(self) -> "Statement":
586+
"""
587+
Create a new statement object using the same factors as `self`.
588+
589+
When used for refreshing plans, the new clone should replace references to
590+
the original.
591+
"""
592+
588593
@abc.abstractmethod
589594
def close(self) -> None:
590595
"""
591596
Close the prepared statement releasing resources associated with it.
592597
"""
598+
Execution.register(Statement)
593599
PreparedStatement = Statement
594600

595601
class StoredProcedure(
@@ -1169,10 +1175,19 @@ class Connection(Database):
11691175
@abc.abstractmethod
11701176
def connector(self) -> Connector:
11711177
"""
1172-
The `Connector` instance facilitating the `Connection` object's
1178+
The :py:class:`Connector` instance facilitating the `Connection` object's
11731179
communication and initialization.
11741180
"""
11751181

1182+
@property
1183+
@abc.abstractmethod
1184+
def query(self) -> Execution:
1185+
"""
1186+
The :py:class:`Execution` instance providing a one-shot query interface::
1187+
1188+
connection.query.<method>(sql, *parameters) == connection.prepare(sql).<method>(*parameters)
1189+
"""
1190+
11761191
@property
11771192
@abc.abstractmethod
11781193
def closed(self) -> bool:

‎postgresql/documentation/changes-v1.1.rst‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,4 +9,5 @@ Changes in v1.1
99
the implementation did nothing to accommodate for.
1010
* Add support for unpacking anonymous records (Elvis)
1111
* Support PostgreSQL 9.2 (Elvis)
12-
* Add column execution method.
12+
* Add column execution method. (jwp)
13+
* Add one-shot statement interface. Connection.query (jwp)

‎postgresql/documentation/driver.rst‎

Lines changed: 23 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,6 @@ databases using a locator string and optional connection keywords. The string
5959
taken by `postgresql.open` is a URL whose components make up the client
6060
parameters::
6161

62-
>>> import postgresql
6362
>>> db = postgresql.open("pq://localhost/postgres")
6463

6564
This will connect to the host, ``localhost`` and to the database named
@@ -283,10 +282,6 @@ interfaces:
283282
``sslrootcrlfile``
284283
Revocation list file path. [Currently not checked.]
285284

286-
``category``
287-
A `postgresql.api.Category` instance used to further initialize
288-
the database.
289-
290285

291286
Connections
292287
===========
@@ -1017,7 +1012,7 @@ direction keyword argument::
10171012
>>> c.read(1, 'BACKWARD')
10181013

10191014

1020-
Cursor Direction
1015+
Cursor Direction
10211016
----------------
10221017

10231018
The ``direction`` property on the cursor states the default direction for read
@@ -1200,6 +1195,28 @@ And finally, `functools.partial` can be used to create a simple callable::
12001195
[(9301423, 2, Decimal('5.412'))]
12011196

12021197

1198+
Queries
1199+
=======
1200+
1201+
Queries in `py-postgresql` are single use prepared statements. They exist primarily for
1202+
syntactic convenience, but they also allow the driver to recognize the short lifetime of
1203+
the statement.
1204+
1205+
Single use statements are supported using the ``query`` property on connection
1206+
objects, :py:class:`postgresql.api.Connection.query`. The statement object is not
1207+
available when using queries as the results, or handle to the results, are directly returned.
1208+
1209+
Queries have access to all execution methods:
1210+
1211+
``Connection.query(sql, *parameters)``
1212+
``Connection.query.rows(sql, *parameters)``
1213+
``Connection.query.column(sql, *parameters)``
1214+
``Connection.query.first(sql, *parameters)``
1215+
``Connection.query.chunks(sql, *parameters)``
1216+
``Connection.query.declare(sql, *parameters)``
1217+
``Connection.query.load_rows(sql, collections.Iterable(parameters))``
1218+
``Connection.query.load_chunks(collections.Iterable(collections.Iterable(parameters)))``
1219+
12031220
Stored Procedures
12041221
=================
12051222

‎postgresql/documentation/reference.rst‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,14 @@ Reference
99
.. autodata:: version_info
1010
.. autofunction:: open
1111

12+
:mod:`postgresql.api`
13+
---------------------
14+
15+
.. automodule::
16+
postgresql.api
17+
:members:
18+
:show-inheritance:
19+
1220
:mod:`postgresql.sys`
1321
---------------------
1422

@@ -72,11 +80,3 @@ Reference
7280
postgresql.alock
7381
:members:
7482
:show-inheritance:
75-
76-
:mod:`postgresql.api`
77-
---------------------
78-
79-
.. automodule::
80-
postgresql.api
81-
:members:
82-
:show-inheritance:

0 commit comments

Comments
 (0)