Skip to content

Commit fd3fe6b

Browse files
author
James William Pye
committed
Add statement and cursor metadata properties and do some documentation work.
This refactors how statements get their attribute maps using more general methods.
1 parent 49bba0b commit fd3fe6b

6 files changed

Lines changed: 373 additions & 99 deletions

File tree

‎postgresql/api.py‎

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -448,11 +448,46 @@ def cursor_id(self) -> str:
448448
The cursor's identifier.
449449
"""
450450

451+
@propertydoc
452+
@abstractproperty
453+
def sql_column_types(self) -> [str]:
454+
"""
455+
The type of the columns produced by the cursor.
456+
457+
A sequence of `str` objects stating the SQL type name::
458+
459+
['INTEGER', 'CHARACTER VARYING', 'INTERVAL']
460+
"""
461+
462+
@propertydoc
463+
@abstractproperty
464+
def pg_column_types(self) -> [int]:
465+
"""
466+
The type Oids of the columns produced by the cursor.
467+
468+
A sequence of `int` objects stating the SQL type name::
469+
470+
[27, 28]
471+
"""
472+
473+
@propertydoc
474+
@abstractproperty
475+
def column_names(self) -> [str]:
476+
"""
477+
The attribute names of the columns produced by the cursor.
478+
479+
A sequence of `str` objects stating the column name::
480+
481+
['column1', 'column2', 'emp_name']
482+
"""
483+
451484
@propertydoc
452485
@abstractproperty
453486
def parameters(self) -> (tuple, None):
454487
"""
455488
The parameters bound to the cursor. `None`, if unknown.
489+
490+
These *should* be the original parameters given to the invoked statement.
456491
"""
457492

458493
@propertydoc
@@ -575,6 +610,61 @@ def string(self) -> object:
575610
`statement` constructor.
576611
"""
577612

613+
@propertydoc
614+
@abstractproperty
615+
def sql_parameter_types(self) -> [str]:
616+
"""
617+
The type of the parameters required by the statement.
618+
619+
A sequence of `str` objects stating the SQL type name::
620+
621+
['INTEGER', 'VARCHAR', 'INTERVAL']
622+
"""
623+
624+
@propertydoc
625+
@abstractproperty
626+
def sql_column_types(self) -> [str]:
627+
"""
628+
The type of the columns produced by the statement.
629+
630+
A sequence of `str` objects stating the SQL type name::
631+
632+
['INTEGER', 'VARCHAR', 'INTERVAL']
633+
"""
634+
635+
@propertydoc
636+
@abstractproperty
637+
def pg_parameter_types(self) -> [int]:
638+
"""
639+
The type Oids of the parameters required by the statement.
640+
641+
A sequence of `int` objects stating the PostgreSQL type Oid::
642+
643+
[27, 28]
644+
"""
645+
646+
@propertydoc
647+
@abstractproperty
648+
def pg_column_types(self) -> [int]:
649+
"""
650+
The type Oids of the columns produced by the statement.
651+
652+
A sequence of `int` objects stating the SQL type name::
653+
654+
[27, 28]
655+
"""
656+
657+
@propertydoc
658+
@abstractproperty
659+
def column_names(self) -> [str]:
660+
"""
661+
The attribute names of the columns produced by the statement.
662+
663+
A sequence of `str` objects stating the column name::
664+
665+
['column1', 'column2', 'emp_name']
666+
"""
667+
578668
@abstractmethod
579669
def __call__(self,
580670
*args : "Positional Parameters",

‎postgresql/documentation/driver.py‎

Lines changed: 116 additions & 70 deletions
Original file line numberDiff line numberDiff line change
@@ -221,11 +221,11 @@
221221
server is desired, a new connection *must* be established.
222222
223223
224-
Database Interface Entry Points
225-
-------------------------------
224+
Database Interface Points
225+
-------------------------
226226
227-
After a connection is established, the primary interface entry points are ready
228-
for use. These entry points exist as properties and methods on the connection
227+
After a connection is established, the primary interface points are ready for
228+
use. These entry points exist as properties and methods on the connection
229229
object:
230230
231231
``prepare(sql_statement_string)``
@@ -273,7 +273,7 @@
273273
the connection is made:
274274
275275
``version``
276-
The results of ``SELECT version()``
276+
The results of ``SELECT version()``.
277277
``version_info``
278278
A ``sys.version_info`` form of the ``server_version`` setting. eg. ``(8, 1, 2,
279279
'final', 0)``.
@@ -302,18 +302,25 @@
302302
303303
The ``prepare`` entry point on the connection provides the standard method for
304304
creating a `postgersql.api.PreparedStatement` instance bound to the
305-
connection(``db``):
305+
connection(``db``) from an SQL statement.
306306
307-
>>> ps = db.prepare("SELECT 'hello, world!'")
307+
Statement objects may also be created from a statement identifier using the
308+
``statement_from_id`` method on the connection. When this method is used, the
309+
statement must have already been prepared or an error will be raised.
310+
311+
312+
Prepared Statement Interface Points
313+
-----------------------------------
308314
309315
Prepared statements are normally executed just like functions:
310316
317+
>>> ps = db.prepare("SELECT 'hello, world!'")
311318
>>> c = ps()
312319
>>> c.read()
313320
[('hello, world!',)]
314321
315-
``c``, the object returned by ``ps.__call__``, is a cursor with a
316-
`postgresql.api.Cursor` interface.
322+
``c``, the object returned by the invocation of ``ps.__call__``, is a cursor
323+
object providing a `postgresql.api.Cursor` interface.
317324
318325
.. note::
319326
Don't confuse PG-API cursors with DB-API 2.0 cursors.
@@ -365,12 +372,63 @@
365372
rules in mind.
366373
367374
375+
Statement Metadata
376+
------------------
377+
378+
In order to provide the appropriate type transformations, the driver must
379+
acquire metadata about the statement's parameters and results. This data is
380+
published via the following properties on the statement object:
381+
382+
``sql_parameter_types``
383+
A sequence of SQL type names specifying the types of the parameters used in
384+
the statement.
385+
386+
``sql_column_types``
387+
A sequence of SQL type names specifying the types of the columns produced by
388+
the statement. `None` if the statement does not return row-data.
389+
390+
``pg_parameter_types``
391+
A sequence of PostgreSQL type Oid's specifying the types of the parameters
392+
used in the statement.
393+
394+
``pg_column_types``
395+
A sequence of PostgreSQL type Oid's specifying the types of the columns produced by
396+
the statement. `None` if the statement does not return row-data.
397+
398+
``parameter_types``
399+
A sequence of Python types that the statement expects.
400+
401+
``column_types``
402+
A sequence of Python types that the statement will produce.
403+
404+
``column_names``
405+
A sequence of `str` objects specifying the names of the columns produced by
406+
the statement. `None` if the statement does not return row-data.
407+
408+
The indexes of the sequence correspond to the parameter's identifier, N+1.
409+
410+
In order for this information to be available, the statement must be fully
411+
prepared, so if the statement is closed, it will be re-prepared when this
412+
information is accessed.
413+
414+
>>> ps = db.prepare("SELECT $1::integer AS intname, $2::varchar AS chardata")
415+
>>> ps.sql_parameter_types
416+
('integer','varchar')
417+
>>> ps.sql_column_types
418+
('integer','varchar')
419+
>>> ps.column_names
420+
('intname','chardata')
421+
>>> ps.column_types
422+
(<class 'int'>, <class 'str'>)
423+
424+
368425
Parameterized Statements
369426
------------------------
370427
371-
Statements can take parameters. In order to do this, the statement must be
372-
defined using PostgreSQL's positional parameter notation. ``$1``, ``$2``,
373-
``$3``, etc:
428+
Statements can take parameters. Using statement parameters is the recommended
429+
way to interrogate the database when variable information is needed to formulate
430+
a complete request. In order to do this, the statement must be defined using
431+
PostgreSQL's positional parameter notation. ``$1``, ``$2``, ``$3``, etc:
374432
375433
>>> ps = db.prepare("SELECT $1")
376434
>>> c = ps('hello, world!')
@@ -399,8 +457,9 @@
399457
400458
Parameters are typed. PostgreSQL servers provide the driver with the
401459
type information about a positional parameter, and the driver will require that
402-
a given parameter is in the appropriate type. The Python types expected by the driver
403-
for a given SQL and PostgreSQL type are listed in `Type Support`_.
460+
a given parameter is in the appropriate type as required by the serialization
461+
routines. The Python types expected by the driver for a given SQL and PostgreSQL
462+
type are listed in `Type Support`_.
404463
405464
This usage of Python types that are included in the standard library is not always
406465
convenient. Notably, the `datetime` module does not provide a friendly way for a
@@ -428,56 +487,6 @@
428487
conversion.
429488
430489
431-
Statement Metadata
432-
------------------
433-
434-
In order to provide the appropriate type transformations, the driver must
435-
acquire metadata about the statement's parameters and results. This data is
436-
published via the following properties on the statement object:
437-
438-
``sql_parameter_types``
439-
A sequence of SQL type names specifying the types of the parameters used in
440-
the statement.
441-
442-
``sql_column_types``
443-
A sequence of SQL type names specifying the types of the columns produced by
444-
the statement. `None` if the statement does not return row-data.
445-
446-
``pg_parameter_types``
447-
A sequence of PostgreSQL type Oid's specifying the types of the parameters
448-
used in the statement.
449-
450-
``pg_column_types``
451-
A sequence of PostgreSQL type Oid's specifying the types of the columns produced by
452-
the statement. `None` if the statement does not return row-data.
453-
454-
``parameter_types``
455-
A sequence of Python types that the statement expects.
456-
457-
``column_types``
458-
A sequence of Python types that the statement will produce.
459-
460-
``column_names``
461-
A sequence of `str` objects specifying the names of the columns produced by
462-
the statement. `None` if the statement does not return row-data.
463-
464-
The indexes of the sequence correspond to the parameter's identifier, N+1.
465-
466-
In order for this information to be available, the statement must be fully
467-
prepared, so if the statement is closed, it will be re-prepared when this
468-
information is accessed.
469-
470-
>>> ps = db.prepare("SELECT $1::integer AS intname, $2::varchar AS chardata")
471-
>>> ps.sql_parameter_types
472-
('integer','varchar')
473-
>>> ps.sql_column_types
474-
('integer','varchar')
475-
>>> ps.column_names
476-
('intname','chardata')
477-
>>> ps.column_types
478-
(<class 'int'>, <class 'str'>)
479-
480-
481490
Inserting and DML
482491
-----------------
483492
@@ -519,12 +528,19 @@
519528
520529
521530
Cursors
522-
-------
531+
=======
523532
524533
When a prepared statement is called, a `postgresql.api.Cursor` is created and
525534
returned. The type of statement ultimately decides the kind of cursor used to
526535
manage the results.
527536
537+
Cursors can also be created directly from ``cursor_id``'s using the
538+
``cursor_from_id`` method on connection objects.
539+
540+
541+
Cursor Interface Points
542+
-----------------------
543+
528544
For cursors that return row data, these interfaces are provided for accessing
529545
those results:
530546
@@ -550,9 +566,38 @@
550566
with the ``chunksize`` attribute on the cursor object itself. This is
551567
normally the most efficient way to get rows out of the cursor.
552568
569+
``seek(position[, whence = 0])``
570+
When the cursor is scrollable, this seek interface can be used to move the
571+
position of the cursor. See `Scrollable Cursors`_ for more information.
572+
573+
574+
Cursor Metadata
575+
---------------
576+
577+
Cursors normally share metadata with the statements that create them, so it is
578+
usually unnecessary for referencing the cursor's column descriptions directly.
579+
However, when a cursor is opened from an identifier, the cursor interface must
580+
collect the metadata itself. These attributes provide the metadata in absence of
581+
a statement object:
582+
583+
``sql_column_types``
584+
A sequence of SQL type names specifying the types of the columns produced by
585+
the cursor. `None` if the cursor does not return row-data.
586+
587+
``pg_column_types``
588+
A sequence of PostgreSQL type Oid's specifying the types of the columns produced by
589+
the cursor. `None` if the cursor does not return row-data.
590+
591+
``column_types``
592+
A sequence of Python types that the cursor will produce.
593+
594+
``column_names``
595+
A sequence of `str` objects specifying the names of the columns produced by
596+
the cursor. `None` if the cursor does not return row-data.
597+
553598
554599
Scrollable Cursors
555-
^^^^^^^^^^^^^^^^^^
600+
------------------
556601
557602
By default, cursors are not scrollable. It is assumed, for performance reasons,
558603
that the user just wants the results in a linear fashion. However, scrollable
@@ -609,7 +654,7 @@
609654
610655
611656
COPY Cursors
612-
^^^^^^^^^^^^
657+
------------
613658
614659
`postgresql.driver` transparently supports PostgreSQL's COPY command. To the
615660
user, it will act exactly like a cursor that produces tuples; COPY tuples,
@@ -762,8 +807,9 @@
762807
The context manager interfaces are higher level interfaces to the explicit
763808
instruction methods provided by `postgresql.api.Transaction` objects.
764809
765-
Transaction Interface Entry Points
766-
----------------------------------
810+
811+
Transaction Interface Points
812+
----------------------------
767813
768814
The methods available on transaction objects manage the state of the transaction
769815
and relay any necessary instructions to the remote server in order to reflect
@@ -798,7 +844,7 @@
798844
* If the transaction is configured with a `gid`, the ``prepare()`` method
799845
*must* be invoked prior to the ``commit()``.
800846
* If the database is in an error state when the commit() is issued, an implicit
801-
rollback will occur. Usually, when the database is in an error state, an
847+
rollback will occur. Usually, when the database is in an error state,
802848
a database exception will have been thrown, so it is likely that it will be
803849
understood that a rollback should occur.
804850

0 commit comments

Comments
 (0)