Author: cito
Date: Fri Jan 29 17:43:22 2016
New Revision: 797
Log:
Cache typecast functions and make them configurable
The typecast functions used by the pgdb module are now cached
using a local and a global Typecasts class. The local cache is
bound to the connection and knows how to cast composite types.
Also added functions that allow registering custom typecast
functions on the global and local level.
Also added a chapter on type adaptation and casting to the docs.
Added:
trunk/docs/contents/pgdb/adaptation.rst
trunk/docs/contents/pgdb/typecache.rst
- copied, changed from r786, trunk/docs/contents/pgdb/cursor.rst
Modified:
trunk/docs/contents/pgdb/connection.rst
trunk/docs/contents/pgdb/index.rst
trunk/docs/contents/pgdb/module.rst
trunk/docs/contents/pgdb/types.rst
trunk/pgdb.py
trunk/tests/test_dbapi20.py
Added: trunk/docs/contents/pgdb/adaptation.rst
==============================================================================
--- /dev/null 00:00:00 1970 (empty, because file is newly added)
+++ trunk/docs/contents/pgdb/adaptation.rst Fri Jan 29 17:43:22 2016
(r797)
@@ -0,0 +1,269 @@
+Remarks on Adaptation and Typecasting
+=====================================
+
+.. py:currentmodule:: pgdb
+
+Both PostgreSQL and Python have the concept of data types, but there
+are of course differences between the two type systems. Therefore PyGreSQL
+needs to adapt Python objects to the representation required by PostgreSQL
+when passing values as query parameters, and it needs to typecast the
+representation of PostgreSQL data types returned by database queries to
+Python objects. Here are some explanations about how this works in
+detail in case you want to better understand or change the default
+behavior of PyGreSQL.
+
+Adaptation of parameters
+------------------------
+
+PyGreSQL knows how to adapt the common Python types to get a suitable
+representation of their values for PostgreSQL when you pass parameters
+to a query. For example::
+
+ >>> con = pgdb.connect(...)
+ >>> cur = con.cursor()
+ >>> parameters = (144, 3.75, 'hello', None)
+ >>> tuple(cur.execute('SELECT %s, %s, %s, %s', parameters).fetchone()
+ (144, Decimal('3.75'), 'hello', None)
+
+This is the result we can expect, so obviously PyGreSQL has adapted the
+parameters and sent the following query to PostgreSQL:
+
+.. code-block:: sql
+
+ SELECT 144, 3.75, 'hello', NULL
+
+Note the subtle, but important detail that even though the SQL string passed
+to :meth:`cur.execute` contains conversion specifications normally used in
+Python with the ``%`` operator for formatting strings, we didn't use the ``%``
+operator to format the parameters, but passed them as the second argument to
+:meth:`cur.execute`. I.e. we **didn't** write the following::
+
+>>> tuple(cur.execute('SELECT %s, %s, %s, %s' % parameters).fetchone()
+
+If we had done this, PostgreSQL would have complained because the parameters
+were not adapted. Particularly, there would be no quotes around the value
+``'hello'``, so PostgreSQL would have interpreted this as a database column,
+which would have caused a :exc:`ProgrammingError`. Also, the Python value
+``None`` would have been included in the SQL command literally, instead of
+being converted to the SQL keyword ``NULL``, which would have been another
+reason for PostgreSQL to complain about our bad query:
+
+.. code-block:: sql
+
+ SELECT 144, 3.75, hello, None
+
+Even worse, building queries with the use of the ``%`` operator makes us
+vulnerable to so called "SQL injection" exploits, where an attacker inserts
+malicious SQL statements into our queries that we never intended to be
+executed. We could avoid this by carefully quoting and escaping the
+parameters, but this would be tedious and if we overlook something, our
+code will still be vulnerable. So please don't do this. This cannot be
+emphasized enough, because it is such a subtle difference and using the ``%``
+operator looks so natural:
+
+.. warning::
+
+ Remember to **never** insert parameters directly into your queries using
+ the ``%`` operator. Always pass the parameters separately.
+
+The good thing is that by letting PyGreSQL do the work for you, you can treat
+all your parameters equally and don't need to ponder where you need to put
+quotes or need to escape strings. You can and should also always use the
+general ``%s`` specification instead of e.g. using ``%d`` for integers.
+Actually, to avoid mistakes and make it easier to insert parameters at more
+than one location, you can and should use named specifications, like this::
+
+ >>> params = dict(greeting='Hello', name='HAL')
+ >>> sql = """SELECT %(greeting)s || ', ' || %(name)s
+ ... || '. Do you read me, ' || %(name)s || '?'"""
+ >>> cur.execute(sql, params).fetchone()[0]
+ 'Hello, HAL. Do you read me, HAL?'
+
+PyGreSQL does not only adapt the basic types like ``int``, ``float``,
+``bool`` and ``str``, but also tries to make sense of Python lists and tuples.
+
+Lists are adapted as PostgreSQL arrays::
+
+ >>> params = dict(array=[[1, 2],[3, 4]])
+ >>> cur.execute("SELECT %(array)s", params).fetchone()[0]
+ [[1, 2], [3, 4]]
+
+Note that the query gives the value back as Python lists again. This
+is achieved by the typecasting mechanism explained in the next section.
+The query that was actually executed was this:
+
+.. code-block:: sql
+
+ SELECT ARRAY[[1,2],[3,4]]
+
+Again, if we had inserted the list using the ``%`` operator without adaptation,
+the ``ARRAY`` keyword would have been missing in the query.
+
+Tuples are adapted as PostgreSQL composite types::
+
+ >>> params = dict(record=('Bond', 'James'))
+ >>> cur.execute("SELECT %(record)s", params).fetchone()[0]
+ ('Bond', 'James')
+
+You can also use this feature with the ``IN`` syntax of SQL::
+
+ >>> params = dict(what='needle', where=('needle', 'haystack'))
+ >>> cur.execute("SELECT %(what)s IN %(where)s", params).fetchone()[0]
+ True
+
+Sometimes a Python type can be ambiguous. For instance, you might want
+to insert a Python list not into an array column, but into a JSON column.
+Or you want to interpret a string as a date and insert it into a DATE column.
+In this case you can give PyGreSQL a hint by using :ref:`type_constructors`::
+
+ >>> cur.execute("CREATE TABLE json_data (data json, created date)")
+ >>> params = dict(
+ ... data=pgdb.Json([1, 2, 3]), created=pgdb.Date(2016, 1, 29))
+ >>> sql = ("INSERT INTO json_data VALUES (%(data)s, %(created)s)")
+ >>> cur.execute(sql, params)
+ >>> cur.execute("SELECT * FROM json_data").fetchone()
+ Row(data=[1, 2, 3], created='2016-01-29')
+
+Let's think of another example where we create a table with a composite
+type in PostgreSQL:
+
+.. code-block:: sql
+
+ CREATE TABLE on_hand (
+ item inventory_item,
+ count integer)
+
+We assume the composite type ``inventory_item`` has been created like this:
+
+.. code-block:: sql
+
+ CREATE TYPE inventory_item AS (
+ name text,
+ supplier_id integer,
+ price numeric)
+
+In Python we can use a named tuple as an equivalent to this PostgreSQL type::
+
+ >>> from collections import namedtuple
+ >>> inventory_item = namedtuple(
+ ... 'inventory_item', ['name', 'supplier_id', 'price'])
+
+Using the automatic adaptation of Python tuples, an item can now be
+inserted into the database and then read back as follows::
+
+ >>> cur.execute("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
+ ... dict(item=inventory_item('fuzzy dice', 42, 1.99), count=1000))
+ >>> cur.execute("SELECT * FROM on_hand").fetchone()
+ Row(item=inventory_item(name='fuzzy dice', supplier_id=42,
+ price=Decimal('1.99')), count=1000)
+
+However, we may not want to use named tuples, but custom Python classes
+to hold our values, like this one::
+
+ >>> class InventoryItem:
+ ...
+ ... def __init__(self, name, supplier_id, price):
+ ... self.name = name
+ ... self.supplier_id = supplier_id
+ ... self.price = price
+ ...
+ ... def __str__(self):
+ ... return '%s (from %s, at $%s)' % (
+ ... self.name, self.supplier_id, self.price)
+
+But when we try to insert an instance of this class in the same way, we
+will get an error::
+
+ >>> cur.execute("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
+ ... dict(item=InventoryItem('fuzzy dice', 42, 1.99), count=1000))
+ InterfaceError: Do not know how to adapt type <class 'InventoryItem'>
+
+While PyGreSQL knows how to adapt tuples, it does not know what to make out
+of our custom class. To simply convert the object to a string using the
+``str`` function is not a solution, since this yields a human readable string
+that is not useful for PostgreSQL. However, it is possible to make such
+custom classes adapt themselves to PostgreSQL by adding a "magic" method
+with the name ``__pg_repr__``, like this::
+
+ >>> class InventoryItem:
+ ...
+ ... ...
+ ...
+ ... def __str__(self):
+ ... return '%s (from %s, at $%s)' % (
+ ... self.name, self.supplier_id, self.price)
+ ...
+ ... def __pg_repr__(self):
+ ... return (self.name, self.supplier_id, self.price)
+
+Now you can insert class instances the same way as you insert named tuples.
+
+Note that PyGreSQL adapts the result of ``__pg_repr__`` again if it is a
+tuple or a list. Otherwise, it must be a properly escaped string.
+
+Typecasting to Python
+---------------------
+
+As you noticed, PyGreSQL automatically converted the PostgreSQL data to
+suitable Python objects when returning values via one of the "fetch" methods
+of a cursor. This is done by the use of built-in typecast functions.
+
+If you want to use different typecast functions or add your own if no
+built-in typecast function is available, then this is possible using
+the :func:`set_typecast` function. With the :func:`get_typecast` method
+you can check which function is currently set, and :func:`reset_typecast`
+allows you to reset the typecast function to its default. If no typecast
+function is set, then PyGreSQL will return the raw strings from the database.
+
+For instance, you will find that PyGreSQL uses the normal ``int`` function
+to cast PostgreSQL ``int4`` type values to Python::
+
+ >>> pgdb.get_typecast('int4')
+ int
+
+You can change this to return float values instead::
+
+ >>> pgdb.set_typecast('int4', float)
+ >>> con = pgdb.connect(...)
+ >>> cur = con.cursor()
+ >>> cur.execute('select 42::int4').fetchone()[0]
+ 42.0
+
+Note that the connections cache typecast functions, so you may need to
+reopen the database connection, or reset the cache of the connection to
+make this effective, using the following command::
+
+ >>> con.type_cache.reset_typecast()
+
+The :class:`TypeCache` of the connection can also be used to change typecast
+functions locally for one database connection only.
+
+As a more useful example, we can create a typecast function that casts
+items of the composite type used as example in the previous section
+to instances of the corresponding Python class::
+
+ >>> con.type_cache.reset_typecast()
+ >>> cast_tuple = con.type_cache.get_typecast('inventory_item')
+ >>> cast_item = lambda value: InventoryItem(*cast_tuple(value))
+ >>> con.type_cache.set_typecast('inventory_item', cast_item)
+ >>> str(cur.execute("SELECT * FROM on_hand").fetchone()[0])
+ 'fuzzy dice (from 42, at $1.99)'
+
+As you saw in the last section you, PyGreSQL also has a typecast function
+for JSON, which is the default JSON decoder from the standard library.
+Let's assume we want to use a slight variation of that decoder in which
+every integer in JSON is converted to a float in Python. This can be
+accomplished as follows::
+
+ >>> from json import loads
+ >>> cast_json = lambda v: loads(v, parse_int=float)
+ >>> pgdb.set_typecast('json', cast_json)
+ >>> cur.execute("SELECT data FROM json_data").fetchone()[0]
+ [1.0, 2.0, 3.0]
+
+Note again that you may need to ``type_cache.reset_typecast()`` to make
+this effective. Also note that the two types ``json`` and ``jsonb`` have
+their own typecast functions, so if you use ``jsonb`` instead of ``json``,
+you need to use this type name when setting the typecast function::
+
+ >>> pgdb.set_typecast('jsonb', cast_json)
Modified: trunk/docs/contents/pgdb/connection.rst
==============================================================================
--- trunk/docs/contents/pgdb/connection.rst Thu Jan 28 16:25:36 2016
(r796)
+++ trunk/docs/contents/pgdb/connection.rst Fri Jan 29 17:43:22 2016
(r797)
@@ -83,22 +83,8 @@
A dictionary with the various type codes for the PostgreSQL types
-You can request the dictionary either via a PostgreSQL type name (which
-(is equal to the DB-API 2 *type_code*) or via a PostgreSQL type OIDs.
-
-The values are *type_code* strings carrying additional attributes:
-
- - *oid* -- the OID of the type
- - *len* -- the internal size
- - *type* -- ``'b'`` = base, ``'c'`` = composite, ...
- - *category* -- ``'A'`` = Array, ``'B'`` = Boolean, ...
- - *delim* -- delimiter to be used when parsing arrays
- - *relid* -- the table OID for composite types
-
-For details, see the PostgreSQL documentation on `pg_type
-<http://www.postgresql.org/docs/current/static/catalog-pg-type.html>`_.
-
-The :attr:`Connection.type_cache` also provides a method :meth:`columns`
-that returns the names and type OIDs of the columns of composite types.
+This can be used for getting more information on the PostgreSQL database
+types or changing the typecast functions used for the connection. See the
+description of the :class:`TypeCache` class for details.
.. versionadded:: 5.0
Modified: trunk/docs/contents/pgdb/index.rst
==============================================================================
--- trunk/docs/contents/pgdb/index.rst Thu Jan 28 16:25:36 2016 (r796)
+++ trunk/docs/contents/pgdb/index.rst Fri Jan 29 17:43:22 2016 (r797)
@@ -13,3 +13,5 @@
connection
cursor
types
+ typecache
+ adaptation
Modified: trunk/docs/contents/pgdb/module.rst
==============================================================================
--- trunk/docs/contents/pgdb/module.rst Thu Jan 28 16:25:36 2016 (r796)
+++ trunk/docs/contents/pgdb/module.rst Fri Jan 29 17:43:22 2016 (r797)
@@ -37,6 +37,63 @@
con = connect(dsn='myhost:mydb', user='guido', password='234$')
+get/set/reset_typecast -- Control the global typecast functions
+---------------------------------------------------------------
+
+PyGreSQL uses typecast functions to cast the raw data coming from the
+database to Python objects suitable for the particular database type.
+These functions take a single string argument that represents the data
+to be casted and must return the casted value.
+
+PyGreSQL provides built-in typecast functions for the common database types,
+but if you want to change these or add more typecast functions, you can use
+the following functions.
+
+.. note::
+
+ The following functions are not part of the DB-API 2 standard.
+
+.. method:: get_typecast(typ)
+
+ Get the global cast function for the given database type
+
+ :param str typ: PostgreSQL type name or type code
+ :returns: the typecast function for the specified type
+ :rtype: function or None
+
+.. versionadded:: 5.0
+
+.. method:: set_typecast(typ, cast)
+
+ Set a global typecast function for the given database type(s)
+
+ :param typ: PostgreSQL type name or type code, or list of such
+ :type typ: str or list
+ :param cast: the typecast function to be set for the specified type(s)
+ :type typ: str or int
+
+.. versionadded:: 5.0
+
+.. method:: reset_typecast([typ])
+
+ Reset the typecasts for the specified (or all) type(s) to their defaults
+
+ :param str typ: PostgreSQL type name or type code, or list of such,
+ or None to reset all typecast functions
+ :type typ: str, list or None
+
+.. versionadded:: 5.0
+
+Note that database connections cache types and their cast functions using
+connection specific :class:`TypeCache` objects. You can also get, set and
+reset typecast functions on the connection level using the methods
+:meth:`TypeCache.get_typecast`, :meth:`TypeCache.set_typecast` and
+:meth:`TypeCache.reset_typecast` of the :attr:`Connection.type_cache`. This
+will not affect other connections or future connections. In order to be sure
+a global change is picked up by a running connection, you must reopen it or
+call :meth:`TypeCache.reset_typecast` on the :attr:`Connection.type_cache`.
+
+
Module constants
----------------
Copied and modified: trunk/docs/contents/pgdb/typecache.rst (from r786,
trunk/docs/contents/pgdb/cursor.rst)
==============================================================================
--- trunk/docs/contents/pgdb/cursor.rst Tue Jan 26 13:16:29 2016 (r786,
copy source)
+++ trunk/docs/contents/pgdb/typecache.rst Fri Jan 29 17:43:22 2016
(r797)
@@ -1,394 +1,85 @@
-Cursor -- The cursor object
-===========================
+TypeCache -- The internal cache for database types
+==================================================
.. py:currentmodule:: pgdb
-.. class:: Cursor
-
-These objects represent a database cursor, which is used to manage the context
-of a fetch operation. Cursors created from the same connection are not
-isolated, i.e., any changes done to the database by a cursor are immediately
-visible by the other cursors. Cursors created from different connections can
-or can not be isolated, depending on the level of transaction isolation.
-The default PostgreSQL transaction isolation level is "read committed".
-
-Cursor objects respond to the following methods and attributes.
-
-Note that ``Cursor`` objects also implement both the iterator and the
-context manager protocol, i.e. you can iterate over them and you can use them
-in a ``with`` statement.
-
-description -- details regarding the result columns
----------------------------------------------------
-
-.. attribute:: Cursor.description
-
- This read-only attribute is a sequence of 7-item named tuples.
-
- Each of these named tuples contains information describing
- one result column:
-
- - *name*
- - *type_code*
- - *display_size*
- - *internal_size*
- - *precision*
- - *scale*
- - *null_ok*
-
- The values for *precision* and *scale* are only set for numeric types.
- The values for *display_size* and *null_ok* are always ``None``.
-
- This attribute will be ``None`` for operations that do not return rows
- or if the cursor has not had an operation invoked via the
- :meth:`Cursor.execute` or :meth:`Cursor.executemany` method yet.
-
-.. versionchanged:: 5.0
- Before version 5.0, this attribute was an ordinary tuple.
-
-rowcount -- number of rows of the result
-----------------------------------------
-
-.. attribute:: Cursor.rowcount
-
- This read-only attribute specifies the number of rows that the last
- :meth:`Cursor.execute` or :meth:`Cursor.executemany` call produced
- (for DQL statements like SELECT) or affected (for DML statements like
- UPDATE or INSERT). It is also set by the :meth:`Cursor.copy_from` and
- :meth':`Cursor.copy_to` methods. The attribute is -1 in case no such
- method call has been performed on the cursor or the rowcount of the
- last operation cannot be determined by the interface.
-
-close -- close the cursor
--------------------------
-
-.. method:: Cursor.close()
-
- Close the cursor now (rather than whenever it is deleted)
-
- :rtype: None
-
-The cursor will be unusable from this point forward; an :exc:`Error`
-(or subclass) exception will be raised if any operation is attempted
-with the cursor.
-
-execute -- execute a database operation
----------------------------------------
-
-.. method:: Cursor.execute(operation, [parameters])
-
- Prepare and execute a database operation (query or command)
-
- :param str operation: the database operation
- :param parameters: a sequence or mapping of parameters
- :returns: the cursor, so you can chain commands
-
-Parameters may be provided as sequence or mapping and will be bound to
-variables in the operation. Variables are specified using Python extended
-format codes, e.g. ``" ... WHERE name=%(name)s"``.
-
-A reference to the operation will be retained by the cursor. If the same
-operation object is passed in again, then the cursor can optimize its behavior.
-This is most effective for algorithms where the same operation is used,
-but different parameters are bound to it (many times).
-
-The parameters may also be specified as list of tuples to e.g. insert multiple
-rows in a single operation, but this kind of usage is deprecated:
-:meth:`Cursor.executemany` should be used instead.
-
-Note that in case this method raises a :exc:`DatabaseError`, you can get
-information about the error condition that has occurred by introspecting
-its :attr:`DatabaseError.sqlstate` attribute, which will be the ``SQLSTATE``
-error code associated with the error. Applications that need to know which
-error condition has occurred should usually test the error code, rather than
-looking at the textual error message.
-
-executemany -- execute many similar database operations
--------------------------------------------------------
-
-.. method:: Cursor.executemany(operation, [seq_of_parameters])
-
- Prepare and execute many similar database operations (queries or commands)
-
- :param str operation: the database operation
- :param seq_of_parameters: a sequence or mapping of parameter tuples or
mappings
- :returns: the cursor, so you can chain commands
-
-Prepare a database operation (query or command) and then execute it against
-all parameter tuples or mappings found in the sequence *seq_of_parameters*.
-
-Parameters are bounded to the query using Python extended format codes,
-e.g. ``" ... WHERE name=%(name)s"``.
-
-callproc -- Call a stored procedure
------------------------------------
-
-.. method:: Cursor.callproc(self, procname, [parameters]):
-
- Call a stored database procedure with the given name
-
- :param str procname: the name of the database function
- :param parameters: a sequence of parameters (can be empty or omitted)
-
-This method calls a stored procedure (function) in the PostgreSQL database.
-
-The sequence of parameters must contain one entry for each input argument
-that the function expects. The result of the call is the same as this input
-sequence; replacement of output and input/output parameters in the return
-value is currently not supported.
-
-The function may also provide a result set as output. These can be requested
-through the standard fetch methods of the cursor.
+.. class:: TypeCache
.. versionadded:: 5.0
-fetchone -- fetch next row of the query result
-----------------------------------------------
-
-.. method:: Cursor.fetchone()
-
- Fetch the next row of a query result set
-
- :returns: the next row of the query result set
- :rtype: named tuple or None
-
-Fetch the next row of a query result set, returning a single named tuple,
-or ``None`` when no more data is available. The field names of the named
-tuple are the same as the column names of the database query as long as
-they are valid Python identifiers.
+The internal :class:`TypeCache` of PyGreSQL is not part of the DB-API 2
+standard, but is documented here in case you need full control and
+understanding of the internal handling of database types.
-An :exc:`Error` (or subclass) exception is raised if the previous call to
-:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
-any result set or no call was issued yet.
+The TypeCache is essentially a dictionary mapping PostgreSQL internal
+type names and type OIDs to DB-API 2 "type codes" (which are also returned
+as the *type_code* field of the :attr:`Cursor.description` attribute).
-.. versionchanged:: 5.0
- Before version 5.0, this method returned ordinary tuples.
+These type codes are strings which are equal to the PostgreSQL internal
+type name, but they are also carrying additional information about the
+associated PostgreSQL type in the following attributes:
-fetchmany -- fetch next set of rows of the query result
--------------------------------------------------------
+ - *oid* -- the OID of the type
+ - *len* -- the internal size
+ - *type* -- ``'b'`` = base, ``'c'`` = composite, ...
+ - *category* -- ``'A'`` = Array, ``'B'`` = Boolean, ...
+ - *delim* -- delimiter to be used when parsing arrays
+ - *relid* -- the table OID for composite types
-.. method:: Cursor.fetchmany([size=None], [keep=False])
+For details, see the PostgreSQL documentation on `pg_type
+<http://www.postgresql.org/docs/current/static/catalog-pg-type.html>`_.
- Fetch the next set of rows of a query result
+In addition to the dictionary methods, the :class:`TypeCache` provides
+the following methods:
- :param size: the number of rows to be fetched
- :type size: int or None
- :param keep: if set to true, will keep the passed arraysize
- :tpye keep: bool
- :returns: the next set of rows of the query result
- :rtype: list of named tuples
+.. method:: TypeCache.get_fields(typ)
-Fetch the next set of rows of a query result, returning a list of named
-tuples. An empty sequence is returned when no more rows are available.
-The field names of the named tuple are the same as the column names of
-the database query as long as they are valid Python identifiers.
+ Get the names and types of the fields of composite types
-The number of rows to fetch per call is specified by the *size* parameter.
-If it is not given, the cursor's :attr:`arraysize` determines the number of
-rows to be fetched. If you set the *keep* parameter to True, this is kept as
-new :attr:`arraysize`.
+ :param typ: PostgreSQL type name or OID of a composite type
+ :type typ: str or int
+ :returns: a list of pairs of field names and types
+ :rtype: list
-The method tries to fetch as many rows as indicated by the *size* parameter.
-If this is not possible due to the specified number of rows not being
-available, fewer rows may be returned.
+.. method:: TypeCache.get_typecast(typ)
-An :exc:`Error` (or subclass) exception is raised if the previous call to
-:meth:`Cursor.execute` or :meth:`Cursor.executemany` did not produce
-any result set or no call was issued yet.
+ Get the cast function for the given database type
-Note there are performance considerations involved with the *size* parameter.
-For optimal performance, it is usually best to use the :attr:`arraysize`
-attribute. If the *size* parameter is used, then it is best for it to retain
-the same value from one :meth:`Cursor.fetchmany` call to the next.
+ :param str typ: PostgreSQL type name or type code
+ :returns: the typecast function for the specified type
+ :rtype: function or None
-.. versionchanged:: 5.0
- Before version 5.0, this method returned ordinary tuples.
+.. method:: TypeCache.set_typecast(typ, cast)
-fetchall -- fetch all rows of the query result
-----------------------------------------------
+ Set a typecast function for the given database type(s)
-.. method:: Cursor.fetchall()
+ :param typ: PostgreSQL type name or type code, or list of such
+ :type typ: str or list
+ :param cast: the typecast function to be set for the specified type(s)
+ :type typ: str or int
- Fetch all (remaining) rows of a query result
+.. method:: TypeCache.reset_typecast([typ])
- :returns: the set of all rows of the query result
- :rtype: list of named tuples
+ Reset the typecasts for the specified (or all) type(s) to their defaults
-Fetch all (remaining) rows of a query result, returning them as list of
-named tuples. The field names of the named tuple are the same as the column
-names of the database query as long as they are valid Python identifiers.
+ :param str typ: PostgreSQL type name or type code, or list of such,
+ or None to reset all typecast functions
+ :type typ: str, list or None
-Note that the cursor's :attr:`arraysize` attribute can affect the performance
-of this operation.
+.. method:: TypeCache.typecast(typ, value)
-.. versionchanged:: 5.0
- Before version 5.0, this method returned ordinary tuples.
+ Cast the given value according to the given database type
-arraysize - the number of rows to fetch at a time
--------------------------------------------------
+ :param str typ: PostgreSQL type name or type code
+ :returns: the casted value
-.. attribute:: Cursor.arraysize
-
- The number of rows to fetch at a time
-
-This read/write attribute specifies the number of rows to fetch at a time with
-:meth:`Cursor.fetchmany`. It defaults to 1, meaning to fetch a single row
-at a time.
-
-Methods and attributes that are not part of the standard
---------------------------------------------------------
.. note::
- The following methods and attributes are not part of the DB-API 2 standard.
-
-.. method:: Cursor.copy_from(stream, table, [format], [sep], [null], [size],
[columns])
-
- Copy data from an input stream to the specified table
-
- :param stream: the input stream
- (must be a file-like object, a string or an iterable returning strings)
- :param str table: the name of a database table
- :param str format: the format of the data in the input stream,
- can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
- :param str sep: a single character separator
- (the default is ``'\t'`` for text and ``','`` for csv)
- :param str null: the textual representation of the ``NULL`` value,
- can also be an empty string (the default is ``'\\N'``)
- :param int size: the size of the buffer when reading file-like objects
- :param list column: an optional list of column names
- :returns: the cursor, so you can chain commands
-
- :raises TypeError: parameters with wrong types
- :raises ValueError: invalid parameters
- :raises IOError: error when executing the copy operation
-
-This method can be used to copy data from an input stream on the client side
-to a database table on the server side using the ``COPY FROM`` command.
-The input stream can be provided in form of a file-like object (which must
-have a ``read()`` method), a string, or an iterable returning one row or
-multiple rows of input data on each iteration.
-
-The format must be text, csv or binary. The sep option sets the column
-separator (delimiter) used in the non binary formats. The null option sets
-the textual representation of ``NULL`` in the input.
-
-The size option sets the size of the buffer used when reading data from
-file-like objects.
-
-The copy operation can be restricted to a subset of columns. If no columns are
-specified, all of them will be copied.
-
-.. versionadded:: 5.0
-
-.. method:: Cursor.copy_to(stream, table, [format], [sep], [null], [decode],
[columns])
-
- Copy data from the specified table to an output stream
-
- :param stream: the output stream (must be a file-like object or ``None``)
- :param str table: the name of a database table or a ``SELECT`` query
- :param str format: the format of the data in the input stream,
- can be ``'text'`` (the default), ``'csv'``, or ``'binary'``
- :param str sep: a single character separator
- (the default is ``'\t'`` for text and ``','`` for csv)
- :param str null: the textual representation of the ``NULL`` value,
- can also be an empty string (the default is ``'\\N'``)
- :param bool decode: whether decoded strings shall be returned
- for non-binary formats (the default is True in Python 3)
- :param list column: an optional list of column names
- :returns: a generator if stream is set to ``None``, otherwise the cursor
-
- :raises TypeError: parameters with wrong types
- :raises ValueError: invalid parameters
- :raises IOError: error when executing the copy operation
-
-This method can be used to copy data from a database table on the server side
-to an output stream on the client side using the ``COPY TO`` command.
-
-The output stream can be provided in form of a file-like object (which must
-have a ``write()`` method). Alternatively, if ``None`` is passed as the
-output stream, the method will return a generator yielding one row of output
-data on each iteration.
-
-Output will be returned as byte strings unless you set decode to true.
-
-Note that you can also use a ``SELECT`` query instead of the table name.
-
-The format must be text, csv or binary. The sep option sets the column
-separator (delimiter) used in the non binary formats. The null option sets
-the textual representation of ``NULL`` in the output.
-
-The copy operation can be restricted to a subset of columns. If no columns are
-specified, all of them will be copied.
-
-.. versionadded:: 5.0
-
-.. method:: Cursor.row_factory(row)
-
- Process rows before they are returned
-
- :param list row: the currently processed row of the result set
- :returns: the transformed row that the fetch methods shall return
-
-This method is used for processing result rows before returning them through
-one of the fetch methods. By default, rows are returned as named tuples.
-You can overwrite this method with a custom row factory if you want to
-return the rows as different kids of objects. This same row factory will then
-be used for all result sets. If you overwrite this method, the method
-:meth:`Cursor.build_row_factory` for creating row factories dynamically
-will be ignored.
-
-Note that named tuples are very efficient and can be easily converted to
-dicts (even OrderedDicts) by calling ``row._asdict()``. If you still want
-to return rows as dicts, you can create a custom cursor class like this::
-
- class DictCursor(pgdb.Cursor):
-
- def row_factory(self, row):
- return {key: value for key, value in zip(self.colnames, row)}
-
- cur = DictCursor(con) # get one DictCursor instance or
- con.cursor_type = DictCursor # always use DictCursor instances
-
-.. versionadded:: 4.0
-
-.. method:: Cursor.build_row_factory()
-
- Build a row factory based on the current description
-
- :returns: callable with the signature of :meth:`Cursor.row_factory`
-
-This method returns row factories for creating named tuples. It is called
-whenever a new result set is created, and :attr:`Cursor.row_factory` is
-then assigned the return value of this method. You can overwrite this method
-with a custom row factory builder if you want to use different row factories
-for different result sets. Otherwise, you can also simply overwrite the
-:meth:`Cursor.row_factory` method. This method will then be ignored.
-
-The default implementation that delivers rows as named tuples essentially
-looks like this::
-
- def build_row_factory(self):
- return namedtuple('Row', self.colnames, rename=True)._make
-
-.. versionadded:: 5.0
-
-.. attribute:: Cursor.colnames
-
- The list of columns names of the current result set
-
-The values in this list are the same values as the *name* elements
-in the :attr:`Cursor.description` attribute. Always use the latter
-if you want to remain standard compliant.
-
-.. versionadded:: 5.0
-
-.. attribute:: Cursor.coltypes
-
- The list of columns types of the current result set
-
-The values in this list are the same values as the *type_code* elements
-in the :attr:`Cursor.description` attribute. Always use the latter
-if you want to remain standard compliant.
-
-.. versionadded:: 5.0
+ Note that the :class:`TypeCache` is always bound to a database connection.
+ You can also get, set and reset typecast functions on a global level using
+ the functions :func:`pgdb.get_typecast`, :func:`pgdb.set_typecast` and
+ :func:`pgdb.reset_typecast`. If you do this, the current database
+ connections will continue to use their already cached typecast functions
+ unless you call the :meth:`TypeCache.reset_typecast` method on the
+ :attr:`Connection.type_cache` of the running connections.
Modified: trunk/docs/contents/pgdb/types.rst
==============================================================================
--- trunk/docs/contents/pgdb/types.rst Thu Jan 28 16:25:36 2016 (r796)
+++ trunk/docs/contents/pgdb/types.rst Fri Jan 29 17:43:22 2016 (r797)
@@ -3,6 +3,8 @@
.. py:currentmodule:: pgdb
+.. _type_constructors:
+
Type constructors
-----------------
@@ -70,6 +72,8 @@
SQL ``NULL`` values are always represented by the Python *None* singleton
on input and output.
+.. _type_objects:
+
Type objects
------------
Modified: trunk/pgdb.py
==============================================================================
--- trunk/pgdb.py Thu Jan 28 16:25:36 2016 (r796)
+++ trunk/pgdb.py Fri Jan 29 17:43:22 2016 (r797)
@@ -116,46 +116,179 @@
shortcutmethods = 1
-### Internal Types Handling
+### Internal Type Handling
def decimal_type(decimal_type=None):
- """Get or set global type to be used for decimal values."""
+ """Get or set global type to be used for decimal values.
+
+ Note that connections cache cast functions. To be sure a global change
+ is picked up by a running connection, call con.type_cache.reset_typecast().
+ """
global Decimal
if decimal_type is not None:
- _cast['numeric'] = Decimal = decimal_type
+ Decimal = decimal_type
+ set_typecast('numeric', decimal_type)
return Decimal
-def _cast_bool(value):
- return value[:1] in ('t', 'T')
+def cast_bool(value):
+ """Cast boolean value in database format to bool."""
+ if value:
+ return value[0] in ('t', 'T')
+
+
+def cast_money(value):
+ """Cast money value in database format to Decimal."""
+ if value:
+ value = value.replace('(', '-')
+ return Decimal(''.join(c for c in value if c.isdigit() or c in '.-'))
+
+
+class Typecasts(dict):
+ """Dictionary mapping database types to typecast functions.
+
+ The cast functions must accept one Python object as an argument and
+ convert that object to a string representation of the corresponding type
+ in the database. The Python None object is always converted to NULL,
+ so the cast functions can assume they never get passed None as argument.
+ However, they may get passed an empty string or a numeric null value.
+ """
+ # the default cast functions
+ # (str functions are ignored but have been added for faster access)
+ defaults = {'char': str, 'bpchar': str, 'name': str,
+ 'text': str, 'varchar': str,
+ 'bool': cast_bool, 'bytea': unescape_bytea,
+ 'int2': int, 'int4': int, 'serial': int,
+ 'int8': long, 'json': jsondecode, 'jsonb': jsondecode,
+ 'oid': long, 'oid8': long,
+ 'float4': float, 'float8': float,
+ 'numeric': Decimal, 'money': cast_money,
+ 'anyarray': cast_array, 'record': cast_record}
-def _cast_money(value):
- return Decimal(''.join(filter(
- lambda v: v in '0123456789.-', value)))
-
-
-_cast = {'char': str, 'bpchar': str, 'name': str,
- 'text': str, 'varchar': str,
- 'bool': _cast_bool, 'bytea': unescape_bytea,
- 'int2': int, 'int4': int, 'serial': int,
- 'int8': long, 'json': jsondecode, 'jsonb': jsondecode,
- 'oid': long, 'oid8': long,
- 'float4': float, 'float8': float,
- 'numeric': Decimal, 'money': _cast_money,
- 'record': cast_record}
+ def __missing__(self, typ):
+ """Create a cast function if it is not cached.
+ Note that this class never raises a KeyError,
+ but return None when no special cast function exists.
+ """
+ cast = self.defaults.get(typ)
+ if cast:
+ # store default for faster access
+ self[typ] = cast
+ elif typ.startswith('_'):
+ # create array cast
+ base_cast = self[typ[1:]]
+ cast = self.create_array_cast(base_cast)
+ if base_cast:
+ # store only if base type exists
+ self[typ] = cast
+ return cast
+
+ def get(self, typ, default=None):
+ """Get the typecast function for the given database type."""
+ return self[typ] or default
+
+ def set(self, typ, cast):
+ """Set a typecast function for the specified database type(s)."""
+ if isinstance(typ, basestring):
+ typ = [typ]
+ if cast is None:
+ for t in typ:
+ self.pop(t, None)
+ self.pop('_%s' % t, None)
+ else:
+ if not callable(cast):
+ raise TypeError("Cast parameter must be callable")
+ for t in typ:
+ self[t] = cast
+ self.pop('_%s % t', None)
-def _db_error(msg, cls=DatabaseError):
- """Return DatabaseError with empty sqlstate attribute."""
- error = cls(msg)
- error.sqlstate = None
- return error
+ def reset(self, typ=None):
+ """Reset the typecasts for the specified type(s) to their defaults.
+ When no type is specified, all typecasts will be reset.
+ """
+ defaults = self.defaults
+ if typ is None:
+ self.clear()
+ self.update(defaults)
+ else:
+ if isinstance(typ, basestring):
+ typ = [typ]
+ for t in typ:
+ self.set(t, defaults.get(t))
+
+ def create_array_cast(self, cast):
+ """Create an array typecast for the given base cast."""
+ return lambda v: cast_array(v, cast)
+
+ def create_record_cast(self, name, fields, casts):
+ """Create a named record typecast for the given fields and casts."""
+ record = namedtuple(name, fields)
+ return lambda v: record(*cast_record(v, casts))
-def _op_error(msg):
- """Return OperationalError."""
- return _db_error(msg, OperationalError)
+
+_typecasts = Typecasts() # this is the global typecast dictionary
+
+
+def get_typecast(typ):
+ """Get the global typecast function for the given database type(s)."""
+ return _typecasts.get(typ)
+
+
+def set_typecast(typ, cast):
+ """Set a global typecast function for the given database type(s).
+
+ Note that connections cache cast functions. To be sure a global change
+ is picked up by a running connection, call con.type_cache.reset_typecast().
+ """
+ _typecasts.set(typ, cast)
+
+
+def reset_typecast(typ=None):
+ """Reset the global typecasts for the given type(s) to their default.
+
+ When no type is specified, all typecasts will be reset.
+
+ Note that connections cache cast functions. To be sure a global change
+ is picked up by a running connection, call con.type_cache.reset_typecast().
+ """
+ _typecasts.reset(typ)
+
+
+class LocalTypecasts(Typecasts):
+ """Map typecasts, including local composite types, to cast functions."""
+
+ defaults = _typecasts
+
+ def __missing__(self, typ):
+ """Create a cast function if it is not cached."""
+ if typ.startswith('_'):
+ base_cast = self[typ[1:]]
+ cast = self.create_array_cast(base_cast)
+ if base_cast:
+ self[typ] = cast
+ else:
+ cast = self.defaults.get(typ)
+ if cast:
+ self[typ] = cast
+ else:
+ fields = self.get_fields(typ)
+ if fields:
+ casts = [self[field.type] for field in fields]
+ fields = [field.name for field in fields]
+ cast = self.create_record_cast(typ, fields, casts)
+ self[typ] = cast
+ return cast
+
+ def get_fields(self, typ):
+ """Return the fields for the given record type.
+
+ This method will be replaced with a method that looks up the fields
+ using the type cache of the connection.
+ """
+ return []
class TypeCode(str):
@@ -177,7 +310,7 @@
self.relid = relid
return self
-ColumnInfo = namedtuple('ColumnInfo', ['name', 'type'])
+FieldInfo = namedtuple('FieldInfo', ['name', 'type'])
class TypeCache(dict):
@@ -192,6 +325,8 @@
super(TypeCache, self).__init__()
self._escape_string = cnx.escape_string
self._src = cnx.source()
+ self._typecasts = LocalTypecasts()
+ self._typecasts.get_fields = self.get_fields
def __missing__(self, key):
"""Get the type info from the database if it is not cached."""
@@ -224,57 +359,42 @@
except KeyError:
return default
- def columns(self, key):
- """Get the names and types of the columns of composite types."""
- try:
- typ = self[key]
- except KeyError:
- return None # this type is not known
- if typ.type != 'c' or not typ.relid:
+ def get_fields(self, typ):
+ """Get the names and types of the fields of composite types."""
+ if not isinstance(typ, TypeCode):
+ typ = self.get(typ)
+ if not typ:
+ return None
+ if not typ.relid:
return None # this type is not composite
self._src.execute("SELECT attname, atttypid"
" FROM pg_attribute WHERE attrelid=%s AND attnum>0"
" AND NOT attisdropped ORDER BY attnum" % typ.relid)
- return [ColumnInfo(name, int(oid))
+ return [FieldInfo(name, self.get(int(oid)))
for name, oid in self._src.fetch(-1)]
+ def get_typecast(self, typ):
+ """Get the typecast function for the given database type."""
+ return self._typecasts.get(typ)
+
+ def set_typecast(self, typ, cast):
+ """Set a typecast function for the specified database type(s)."""
+ self._typecasts.set(typ, cast)
+
+ def reset_typecast(self, typ=None):
+ """Reset the typecast function for the specified database type(s)."""
+ self._typecasts.reset(typ)
+
def typecast(self, typ, value):
- """Cast value according to database type."""
+ """Cast the given value according to the given database type."""
if value is None:
# for NULL values, no typecast is necessary
return None
- cast = _cast.get(typ)
- if cast is str:
- return value # no typecast necessary
- if cast is None:
- if typ.startswith('_'):
- # cast as an array type
- cast = _cast.get(typ[1:])
- return cast_array(value, cast)
- # check whether this is a composite type
- cols = self.columns(typ)
- if cols:
- getcast = self.getcast
- cast = [getcast(col.type) for col in cols]
- value = cast_record(value, cast)
- fields = [col.name for col in cols]
- record = namedtuple(typ, fields)
- return record(*value)
- return value # no typecast available or necessary
- else:
- return cast(value)
-
- def getcast(self, key):
- """Get a cast function for the given database type."""
- if isinstance(key, int):
- try:
- typ = self[key]
- except KeyError:
- return None
- else:
- typ = key
- typecast = self.typecast
- return lambda value: typecast(typ, value)
+ cast = self.get_typecast(typ)
+ if not cast or cast is str:
+ # no typecast is necessary
+ return value
+ return cast(value)
class _quotedict(dict):
@@ -287,6 +407,20 @@
return self.quote(super(_quotedict, self).__getitem__(key))
+### Error messages
+
+def _db_error(msg, cls=DatabaseError):
+ """Return DatabaseError with empty sqlstate attribute."""
+ error = cls(msg)
+ error.sqlstate = None
+ return error
+
+
+def _op_error(msg):
+ """Return OperationalError."""
+ return _db_error(msg, OperationalError)
+
+
### Cursor Object
class Cursor(object):
@@ -321,50 +455,52 @@
"""Exit the runtime context for the cursor object."""
self.close()
- def _quote(self, val):
+ def _quote(self, value):
"""Quote value depending on its type."""
- if val is None:
+ if value is None:
return 'NULL'
- if isinstance(val, (datetime, date, time, timedelta, Json)):
- val = str(val)
- if isinstance(val, basestring):
- if isinstance(val, Binary):
- val = self._cnx.escape_bytea(val)
+ if isinstance(value, (datetime, date, time, timedelta, Json)):
+ value = str(value)
+ if isinstance(value, basestring):
+ if isinstance(value, Binary):
+ value = self._cnx.escape_bytea(value)
if bytes is not str: # Python >= 3.0
- val = val.decode('ascii')
+ value = value.decode('ascii')
else:
- val = self._cnx.escape_string(val)
- return "'%s'" % val
- if isinstance(val, float):
- if isinf(val):
- return "'-Infinity'" if val < 0 else "'Infinity'"
- if isnan(val):
+ value = self._cnx.escape_string(value)
+ return "'%s'" % value
+ if isinstance(value, float):
+ if isinf(value):
+ return "'-Infinity'" if value < 0 else "'Infinity'"
+ if isnan(value):
return "'NaN'"
- return val
- if isinstance(val, (int, long, Decimal)):
- return val
- if isinstance(val, list):
+ return value
+ if isinstance(value, (int, long, Decimal)):
+ return value
+ if isinstance(value, list):
# Quote value as an ARRAY constructor. This is better than using
# an array literal because it carries the information that this is
# an array and not a string. One issue with this syntax is that
- # you need to add an explicit type cast when passing empty arrays.
+ # you need to add an explicit typecast when passing empty arrays.
# The ARRAY keyword is actually only necessary at the top level.
q = self._quote
- return 'ARRAY[%s]' % ','.join(str(q(v)) for v in val)
- if isinstance(val, tuple):
+ return 'ARRAY[%s]' % ','.join(str(q(v)) for v in value)
+ if isinstance(value, tuple):
# Quote as a ROW constructor. This is better than using a record
# literal because it carries the information that this is a record
# and not a string. We don't use the keyword ROW in order to make
# this usable with the IN synntax as well. It is only necessary
# when the records has a single column which is not really useful.
q = self._quote
- return '(%s)' % ','.join(str(q(v)) for v in val)
+ return '(%s)' % ','.join(str(q(v)) for v in value)
try:
- return val.__pg_repr__()
+ value = value.__pg_repr__()
+ if isinstance(value, (tuple, list)):
+ value = self._quote(value)
+ return value
except AttributeError:
raise InterfaceError(
- 'do not know how to handle type %s' % type(val))
-
+ 'Do not know how to adapt type %s' % type(value))
def _quoteparams(self, string, parameters):
"""Quote parameters.
@@ -455,7 +591,7 @@
except DatabaseError:
raise # database provides error message
except Exception as err:
- raise _op_error("can't start transaction")
+ raise _op_error("Can't start transaction")
self._dbcnx._tnx = True
for parameters in seq_of_parameters:
sql = operation
@@ -470,9 +606,9 @@
raise # database provides error message
except Error as err:
raise _db_error(
- "error in '%s': '%s' " % (sql, err), InterfaceError)
+ "Error in '%s': '%s' " % (sql, err), InterfaceError)
except Exception as err:
- raise _op_error("internal error in '%s': %s" % (sql, err))
+ raise _op_error("Internal error in '%s': %s" % (sql, err))
# then initialize result raw count and description
if self._src.resulttype == RESULT_DQL:
self._description = True # fetch on demand
@@ -560,7 +696,7 @@
read = stream.read
except AttributeError:
if size:
- raise ValueError("size must only be set for file-like objects")
+ raise ValueError("Size must only be set for file-like objects")
if binary_format:
input_type = bytes
type_name = 'byte strings'
@@ -629,22 +765,24 @@
params = []
if format is not None:
if not isinstance(format, basestring):
- raise TypeError("format option must be be a string")
+ raise TypeError("The frmat option must be be a string")
if format not in ('text', 'csv', 'binary'):
- raise ValueError("invalid format")
+ raise ValueError("Invalid format")
options.append('format %s' % (format,))
if sep is not None:
if not isinstance(sep, basestring):
- raise TypeError("sep option must be a string")
+ raise TypeError("The sep option must be a string")
if format == 'binary':
- raise ValueError("sep is not allowed with binary format")
+ raise ValueError(
+ "The sep option is not allowed with binary format")
if len(sep) != 1:
- raise ValueError("sep must be a single one-byte character")
+ raise ValueError(
+ "The sep option must be a single one-byte character")
options.append('delimiter %s')
params.append(sep)
if null is not None:
if not isinstance(null, basestring):
- raise TypeError("null option must be a string")
+ raise TypeError("The null option must be a string")
options.append('null %s')
params.append(null)
if columns:
@@ -696,12 +834,12 @@
try:
write = stream.write
except AttributeError:
- raise TypeError("need an output stream to copy to")
+ raise TypeError("Need an output stream to copy to")
if not table or not isinstance(table, basestring):
- raise TypeError("need a table to copy to")
+ raise TypeError("Need a table to copy to")
if table.lower().startswith('select'):
if columns:
- raise ValueError("columns must be specified in the query")
+ raise ValueError("Columns must be specified in the query")
table = '(%s)' % (table,)
else:
table = '"%s"' % (table,)
@@ -710,22 +848,24 @@
params = []
if format is not None:
if not isinstance(format, basestring):
- raise TypeError("format option must be a string")
+ raise TypeError("The format option must be a string")
if format not in ('text', 'csv', 'binary'):
- raise ValueError("invalid format")
+ raise ValueError("Invalid format")
options.append('format %s' % (format,))
if sep is not None:
if not isinstance(sep, basestring):
- raise TypeError("sep option must be a string")
+ raise TypeError("The sep option must be a string")
if binary_format:
- raise ValueError("sep is not allowed with binary format")
+ raise ValueError(
+ "The sep option is not allowed with binary format")
if len(sep) != 1:
- raise ValueError("sep must be a single one-byte character")
+ raise ValueError(
+ "The sep option must be a single one-byte character")
options.append('delimiter %s')
params.append(sep)
if null is not None:
if not isinstance(null, basestring):
- raise TypeError("null option must be a string")
+ raise TypeError("The null option must be a string")
options.append('null %s')
params.append(null)
if decode is None:
@@ -735,9 +875,10 @@
decode = str is unicode
else:
if not isinstance(decode, (int, bool)):
- raise TypeError("decode option must be a boolean")
+ raise TypeError("The decode option must be a boolean")
if decode and binary_format:
- raise ValueError("decode is not allowed with binary format")
+ raise ValueError(
+ "The decode option is not allowed with binary format")
if columns:
if not isinstance(columns, basestring):
columns = ','.join('"%s"' % (col,) for col in columns)
@@ -787,7 +928,7 @@
@staticmethod
def nextset():
"""Not supported."""
- raise NotSupportedError("nextset() is not supported")
+ raise NotSupportedError("The nextset() method is not supported")
@staticmethod
def setinputsizes(sizes):
@@ -872,7 +1013,7 @@
try:
self._cnx.source()
except Exception:
- raise _op_error("invalid connection")
+ raise _op_error("Invalid connection")
def __enter__(self):
"""Enter the runtime context for the connection object.
@@ -902,7 +1043,7 @@
self._cnx.close()
self._cnx = None
else:
- raise _op_error("connection has been closed")
+ raise _op_error("Connection has been closed")
def commit(self):
"""Commit any pending transaction to the database."""
@@ -914,9 +1055,9 @@
except DatabaseError:
raise
except Exception:
- raise _op_error("can't commit")
+ raise _op_error("Can't commit")
else:
- raise _op_error("connection has been closed")
+ raise _op_error("Connection has been closed")
def rollback(self):
"""Roll back to the start of any pending transaction."""
@@ -928,9 +1069,9 @@
except DatabaseError:
raise
except Exception:
- raise _op_error("can't rollback")
+ raise _op_error("Can't rollback")
else:
- raise _op_error("connection has been closed")
+ raise _op_error("Connection has been closed")
def cursor(self):
"""Return a new cursor object using the connection."""
@@ -938,9 +1079,9 @@
try:
return self.cursor_type(self)
except Exception:
- raise _op_error("invalid connection")
+ raise _op_error("Invalid connection")
else:
- raise _op_error("connection has been closed")
+ raise _op_error("Connection has been closed")
if shortcutmethods: # otherwise do not implement and document this
Modified: trunk/tests/test_dbapi20.py
==============================================================================
--- trunk/tests/test_dbapi20.py Thu Jan 28 16:25:36 2016 (r796)
+++ trunk/tests/test_dbapi20.py Fri Jan 29 17:43:22 2016 (r797)
@@ -290,51 +290,106 @@
self.assertIsNone(d.scale)
self.assertIsNone(d.null_ok)
- def test_type_cache(self):
+ def test_type_cache_info(self):
con = self._connect()
- cur = con.cursor()
- type_cache = con.type_cache
- self.assertNotIn('numeric', type_cache)
- type_info = type_cache['numeric']
- self.assertIn('numeric', type_cache)
- self.assertEqual(type_info, 'numeric')
- self.assertEqual(type_info.oid, 1700)
- self.assertEqual(type_info.type, 'b') # base
- self.assertEqual(type_info.category, 'N') # numeric
- self.assertEqual(type_info.delim, ',')
- self.assertIs(con.type_cache[1700], type_info)
- self.assertNotIn('pg_type', type_cache)
- type_info = type_cache['pg_type']
- self.assertIn('numeric', type_cache)
- self.assertEqual(type_info.type, 'c') # composite
- self.assertEqual(type_info.category, 'C') # composite
- cols = type_cache.columns('pg_type')
- self.assertEqual(cols[0].name, 'typname')
- typname = type_cache[cols[0].type]
- self.assertEqual(typname, 'name')
- self.assertEqual(typname.type, 'b') # base
- self.assertEqual(typname.category, 'S') # string
- self.assertEqual(cols[3].name, 'typlen')
- typlen = type_cache[cols[3].type]
- self.assertEqual(typlen, 'int2')
- self.assertEqual(typlen.type, 'b') # base
- self.assertEqual(typlen.category, 'N') # numeric
- cur.close()
- cur = con.cursor()
- type_cache = con.type_cache
- self.assertIn('numeric', type_cache)
- cur.close()
- con.close()
+ try:
+ cur = con.cursor()
+ type_cache = con.type_cache
+ self.assertNotIn('numeric', type_cache)
+ type_info = type_cache['numeric']
+ self.assertIn('numeric', type_cache)
+ self.assertEqual(type_info, 'numeric')
+ self.assertEqual(type_info.oid, 1700)
+ self.assertEqual(type_info.len, -1)
+ self.assertEqual(type_info.type, 'b') # base
+ self.assertEqual(type_info.category, 'N') # numeric
+ self.assertEqual(type_info.delim, ',')
+ self.assertEqual(type_info.relid, 0)
+ self.assertIs(con.type_cache[1700], type_info)
+ self.assertNotIn('pg_type', type_cache)
+ type_info = type_cache['pg_type']
+ self.assertIn('numeric', type_cache)
+ self.assertEqual(type_info.type, 'c') # composite
+ self.assertEqual(type_info.category, 'C') # composite
+ cols = type_cache.get_fields('pg_type')
+ self.assertEqual(cols[0].name, 'typname')
+ typname = type_cache[cols[0].type]
+ self.assertEqual(typname, 'name')
+ self.assertEqual(typname.type, 'b') # base
+ self.assertEqual(typname.category, 'S') # string
+ self.assertEqual(cols[3].name, 'typlen')
+ typlen = type_cache[cols[3].type]
+ self.assertEqual(typlen, 'int2')
+ self.assertEqual(typlen.type, 'b') # base
+ self.assertEqual(typlen.category, 'N') # numeric
+ cur.close()
+ cur = con.cursor()
+ type_cache = con.type_cache
+ self.assertIn('numeric', type_cache)
+ cur.close()
+ finally:
+ con.close()
con = self._connect()
- cur = con.cursor()
- type_cache = con.type_cache
- self.assertNotIn('pg_type', type_cache)
- self.assertEqual(type_cache.get('pg_type'), type_info)
- self.assertIn('pg_type', type_cache)
- self.assertIsNone(type_cache.get(
- self.table_prefix + '_surely_does_not_exist'))
- cur.close()
- con.close()
+ try:
+ cur = con.cursor()
+ type_cache = con.type_cache
+ self.assertNotIn('pg_type', type_cache)
+ self.assertEqual(type_cache.get('pg_type'), type_info)
+ self.assertIn('pg_type', type_cache)
+ self.assertIsNone(type_cache.get(
+ self.table_prefix + '_surely_does_not_exist'))
+ cur.close()
+ finally:
+ con.close()
+
+ def test_type_cache_typecast(self):
+ con = self._connect()
+ try:
+ cur = con.cursor()
+ type_cache = con.type_cache
+ self.assertIs(type_cache.get_typecast('int4'), int)
+ cast_int = lambda v: 'int(%s)' % v
+ type_cache.set_typecast('int4', cast_int)
+ query = 'select 2::int2, 4::int4, 8::int8'
+ cur.execute(query)
+ i2, i4, i8 = cur.fetchone()
+ self.assertEqual(i2, 2)
+ self.assertEqual(i4, 'int(4)')
+ self.assertEqual(i8, 8)
+ self.assertEqual(type_cache.typecast('int4', 42), 'int(42)')
+ type_cache.set_typecast(['int2', 'int8'], cast_int)
+ cur.execute(query)
+ i2, i4, i8 = cur.fetchone()
+ self.assertEqual(i2, 'int(2)')
+ self.assertEqual(i4, 'int(4)')
+ self.assertEqual(i8, 'int(8)')
+ type_cache.reset_typecast('int4')
+ cur.execute(query)
+ i2, i4, i8 = cur.fetchone()
+ self.assertEqual(i2, 'int(2)')
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 'int(8)')
+ type_cache.reset_typecast(['int2', 'int8'])
+ cur.execute(query)
+ i2, i4, i8 = cur.fetchone()
+ self.assertEqual(i2, 2)
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 8)
+ type_cache.set_typecast(['int2', 'int8'], cast_int)
+ cur.execute(query)
+ i2, i4, i8 = cur.fetchone()
+ self.assertEqual(i2, 'int(2)')
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 'int(8)')
+ type_cache.reset_typecast()
+ cur.execute(query)
+ i2, i4, i8 = cur.fetchone()
+ self.assertEqual(i2, 2)
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 8)
+ cur.close()
+ finally:
+ con.close()
def test_cursor_iteration(self):
con = self._connect()
@@ -532,7 +587,7 @@
self.assertEqual(type_code, record)
self.assertEqual(type_code, pgdb.RECORD)
self.assertNotEqual(type_code, pgdb.ARRAY)
- columns = con.type_cache.columns(type_code)
+ columns = con.type_cache.get_fields(type_code)
self.assertEqual(columns[0].name, 'name')
self.assertEqual(columns[1].name, 'age')
self.assertEqual(con.type_cache[columns[0].type], 'varchar')
@@ -597,16 +652,27 @@
con = self._connect()
try:
cur = con.cursor()
- self.assertTrue(pgdb.decimal_type(int) is int)
- cur.execute('select 42')
- self.assertEqual(cur.description[0].type_code, pgdb.INTEGER)
+ # change decimal type globally to int
+ int_type = lambda v: int(float(v))
+ self.assertTrue(pgdb.decimal_type(int_type) is int_type)
+ cur.execute('select 4.25')
+ self.assertEqual(cur.description[0].type_code, pgdb.NUMBER)
value = cur.fetchone()[0]
self.assertTrue(isinstance(value, int))
- self.assertEqual(value, 42)
+ self.assertEqual(value, 4)
+ # change decimal type again to float
self.assertTrue(pgdb.decimal_type(float) is float)
cur.execute('select 4.25')
self.assertEqual(cur.description[0].type_code, pgdb.NUMBER)
value = cur.fetchone()[0]
+ # the connection still uses the old setting
+ self.assertTrue(isinstance(value, int))
+ # bust the cache for type functions for the connection
+ con.type_cache.reset_typecast()
+ cur.execute('select 4.25')
+ self.assertEqual(cur.description[0].type_code, pgdb.NUMBER)
+ value = cur.fetchone()[0]
+ # now the connection uses the new setting
self.assertTrue(isinstance(value, float))
self.assertEqual(value, 4.25)
finally:
@@ -614,6 +680,67 @@
pgdb.decimal_type(decimal_type)
self.assertTrue(pgdb.decimal_type() is decimal_type)
+ def test_global_typecast(self):
+ try:
+ query = 'select 2::int2, 4::int4, 8::int8'
+ self.assertIs(pgdb.get_typecast('int4'), int)
+ cast_int = lambda v: 'int(%s)' % v
+ pgdb.set_typecast('int4', cast_int)
+ con = self._connect()
+ try:
+ i2, i4, i8 = con.cursor().execute(query).fetchone()
+ finally:
+ con.close()
+ self.assertEqual(i2, 2)
+ self.assertEqual(i4, 'int(4)')
+ self.assertEqual(i8, 8)
+ pgdb.set_typecast(['int2', 'int8'], cast_int)
+ con = self._connect()
+ try:
+ i2, i4, i8 = con.cursor().execute(query).fetchone()
+ finally:
+ con.close()
+ self.assertEqual(i2, 'int(2)')
+ self.assertEqual(i4, 'int(4)')
+ self.assertEqual(i8, 'int(8)')
+ pgdb.reset_typecast('int4')
+ con = self._connect()
+ try:
+ i2, i4, i8 = con.cursor().execute(query).fetchone()
+ finally:
+ con.close()
+ self.assertEqual(i2, 'int(2)')
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 'int(8)')
+ pgdb.reset_typecast(['int2', 'int8'])
+ con = self._connect()
+ try:
+ i2, i4, i8 = con.cursor().execute(query).fetchone()
+ finally:
+ con.close()
+ self.assertEqual(i2, 2)
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 8)
+ pgdb.set_typecast(['int2', 'int8'], cast_int)
+ con = self._connect()
+ try:
+ i2, i4, i8 = con.cursor().execute(query).fetchone()
+ finally:
+ con.close()
+ self.assertEqual(i2, 'int(2)')
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 'int(8)')
+ finally:
+ pgdb.reset_typecast()
+ con = self._connect()
+ try:
+ i2, i4, i8 = con.cursor().execute(query).fetchone()
+ finally:
+ con.close()
+ self.assertEqual(i2, 2)
+ self.assertEqual(i4, 4)
+ self.assertEqual(i8, 8)
+
def test_unicode_with_utf8(self):
table = self.table_prefix + 'booze'
input = u"He wes Leovenaðes sone — liðe him be Drihten"
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
