Author: cito
Date: Wed Feb 3 15:23:20 2016
New Revision: 814
Log:
Add typecasting of dates, times, timestamps, intervals
So far, PyGreSQL has returned these types only as strings (in various
formats depending on the DateStyle setting) and left it to the user
to parse and interpret the strings. These types are now properly cast
into the corresponding detetime types of Python, and this works with
any setting of DatesStyle, even if you change DateStyle in the middle
of a database session.
To implement this, a fast method for getting the datestyle (cached and
without roundtrip to the database) has been added. Also, the typecast
mechanism has been extended so that typecast functions can optionally
also take the connection as argument.
The date and time typecast functions have been implemented in Python
using the new typecast registry and added to both pg and pgdb. Some
duplication of code in the two modules was unavoidable, since we don't
want the modules to be dependent of each other or install additional
helper modules. One day we might want to change this, put everything
in one package and factor out some of the functionality.
Modified:
trunk/docs/contents/changelog.rst
trunk/docs/contents/pg/adaptation.rst
trunk/docs/contents/pg/connection.rst
trunk/docs/contents/pg/db_types.rst
trunk/docs/contents/pg/large_objects.rst
trunk/docs/contents/pg/module.rst
trunk/docs/contents/pgdb/adaptation.rst
trunk/docs/contents/pgdb/connection.rst
trunk/docs/contents/pgdb/cursor.rst
trunk/docs/contents/pgdb/module.rst
trunk/docs/contents/pgdb/typecache.rst
trunk/docs/contents/pgdb/types.rst
trunk/pg.py
trunk/pgdb.py
trunk/pgmodule.c
trunk/tests/test_classic_connection.py
trunk/tests/test_classic_dbwrapper.py
trunk/tests/test_classic_functions.py
trunk/tests/test_dbapi20.py
Modified: trunk/docs/contents/changelog.rst
==============================================================================
--- trunk/docs/contents/changelog.rst Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/docs/contents/changelog.rst Wed Feb 3 15:23:20 2016 (r814)
@@ -39,6 +39,7 @@
composite type, it is passed to Python as a named tuple. PyGreSQL uses
a new fast built-in parser to achieve this. Anonymous composite types are
also supported, but yield only an ordinary tuple containing text strings.
+ - A new type helper Interval() has been added.
- Changes in the classic PyGreSQL module (pg):
- The classic interface got two new methods get_as_list() and get_as_dict()
returning a database table as a Python list or dict. The amount of data
@@ -92,6 +93,7 @@
separately or formatted into the SQL.
- The methods for adapting and typecasting values pertaining to PostgreSQL
types have been refactored and swapped out to separate classes.
+ - A new type helper Bytea() has been added.
- Changes concerning both modules:
- The modules now provide get_typecast() and set_typecast() methods
allowing to control the typecasting on the global level. The connection
@@ -99,11 +101,16 @@
over the typecasting on the level of the current connection.
See the documentation on details about the type cache and the typecast
mechanisms provided by PyGreSQL.
+ - Dates, times, timestamps and time intervals are now returned as the
+ corresponding Python objects from the datetime module of the standard
+ library. In earlier versions of PyGreSQL they had been returned as
+ strings. You can restore the old behavior by deactivating the respective
+ typecast functions, e.g. set_typecast('date', None).
- PyGreSQL now supports the JSON and JSONB data types, converting such
columns automatically to and from Python objects. If you want to insert
Python objects as JSON data using DB-API 2, you should wrap them in the
new Json() type constructor as a hint to PyGreSQL.
- - New type helpers Literal(), Json() and Bytea() have been added.
+ - The new type helpers Literal() and Json() have been added.
- Fast parsers cast_array() and cast_record() for the input and output
syntax for PostgreSQL arrays and composite types have been added to the
C extension module. The array parser also allows using multi-dimensional
Modified: trunk/docs/contents/pg/adaptation.rst
==============================================================================
--- trunk/docs/contents/pg/adaptation.rst Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/docs/contents/pg/adaptation.rst Wed Feb 3 15:23:20 2016
(r814)
@@ -126,9 +126,9 @@
Lists are adapted as PostgreSQL arrays::
- >>> params = dict(array=[[1, 2],[3, 4]])
- >>> db.query_formatted("SELECT %(array)s::int[]", params).getresult()[0][0]
- [[1, 2], [3, 4]]
+ >>> params = dict(array=[[1, 2],[3, 4]])
+ >>> db.query_formatted("SELECT %(array)s::int[]", params).getresult()[0][0]
+ [[1, 2], [3, 4]]
Note that again we only need to cast the array parameter or use inline
parameters because this simple query does not provide enough context.
@@ -165,11 +165,11 @@
Using the automatic adaptation of Python tuples, an item can now be
inserted into the database and then read back as follows::
- >>> db.query_formatted("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
- ... dict(item=inventory_item('fuzzy dice', 42, 1.99), count=1000))
- >>> db.query("SELECT * FROM on_hand").getresult()[0][0]
- Row(item=inventory_item(name='fuzzy dice', supplier_id=42,
- price=Decimal('1.99')), count=1000)
+ >>> db.query_formatted("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
+ ... dict(item=inventory_item('fuzzy dice', 42, 1.99), count=1000))
+ >>> db.query("SELECT * FROM on_hand").getresult()[0][0]
+ Row(item=inventory_item(name='fuzzy dice', supplier_id=42,
+ price=Decimal('1.99')), count=1000)
The :meth:`DB.insert` method provides a simpler way to achieve the same::
Modified: trunk/docs/contents/pg/connection.rst
==============================================================================
--- trunk/docs/contents/pg/connection.rst Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/docs/contents/pg/connection.rst Wed Feb 3 15:23:20 2016
(r814)
@@ -134,7 +134,7 @@
.. method:: Connection.parameter(name)
- Looks up a current parameter setting of the server
+ Look up a current parameter setting of the server
:param str name: the name of the parameter to look up
:returns: the current setting of the specified parameter
@@ -157,17 +157,40 @@
.. versionadded:: 4.0
-fileno -- returns the socket used to connect to the database
-------------------------------------------------------------
+date_format -- get the currently used date format
+-------------------------------------------------
+
+.. method:: Connection.date_format()
+
+ Look up the date format currently being used by the database
+
+ :returns: the current date format
+ :rtype: str
+ :raises TypeError: too many (any) arguments
+ :raises TypeError: invalid connection
+
+This method returns the current date format used by the server. Note that
+it is cheap to call this method, since there is no database query involved
+and the setting is also cached internally. You will need the date format
+when you want to manually typecast dates and timestamps coming from the
+database instead of using the built-in typecast functions. The date format
+returned by this method can be directly used with date formatting functions
+such as :meth:`datetime.strptime`. It is derived from the current setting
+of the database parameter ``DateStyle``.
+
+.. versionadded:: 5.0
+
+fileno -- get the socket used to connect to the database
+--------------------------------------------------------
.. method:: Connection.fileno()
- Return the socket used to connect to the database
+ Get the socket used to connect to the database
- :returns: the socket id of the database connection
- :rtype: int
- :raises TypeError: too many (any) arguments
- :raises TypeError: invalid connection
+ :returns: the socket id of the database connection
+ :rtype: int
+ :raises TypeError: too many (any) arguments
+ :raises TypeError: invalid connection
This method returns the underlying socket id used to connect
to the database. This is useful for use in select calls, etc.
@@ -378,19 +401,19 @@
.. attribute:: Connection.host
- the host name of the server (str)
+ the host name of the server (str)
.. attribute:: Connection.port
- the port of the server (int)
+ the port of the server (int)
.. attribute:: Connection.db
- the selected database (str)
+ the selected database (str)
.. attribute:: Connection.options
- the connection options (str)
+ the connection options (str)
.. attribute:: Connection.user
@@ -398,20 +421,20 @@
.. attribute:: Connection.protocol_version
- the frontend/backend protocol being used (int)
+ the frontend/backend protocol being used (int)
.. versionadded:: 4.0
.. attribute:: Connection.server_version
- the backend version (int, e.g. 90305 for 9.3.5)
+ the backend version (int, e.g. 90305 for 9.3.5)
.. versionadded:: 4.0
.. attribute:: Connection.status
- the status of the connection (int: 1 = OK, 0 = bad)
+ the status of the connection (int: 1 = OK, 0 = bad)
.. attribute:: Connection.error
- the last warning/error message from the server (str)
+ the last warning/error message from the server (str)
Modified: trunk/docs/contents/pg/db_types.rst
==============================================================================
--- trunk/docs/contents/pg/db_types.rst Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/docs/contents/pg/db_types.rst Wed Feb 3 15:23:20 2016 (r814)
@@ -57,6 +57,12 @@
:param cast: the typecast function to be set for the specified type(s)
:type typ: str or int
+The typecast function must take one string object as argument and return a
+Python object into which the PostgreSQL type shall be casted. If the function
+takes another parameter named *connection*, then the current database
+connection will also be passed to the typecast function. This may sometimes
+be necessary to look up certain database settings.
+
.. method:: DbTypes.reset_typecast([typ])
Reset the typecasts for the specified (or all) type(s) to their defaults
Modified: trunk/docs/contents/pg/large_objects.rst
==============================================================================
--- trunk/docs/contents/pg/large_objects.rst Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/docs/contents/pg/large_objects.rst Wed Feb 3 15:23:20 2016
(r814)
@@ -163,15 +163,15 @@
.. attribute:: LargeObject.oid
- the OID associated with the large object (int)
+ the OID associated with the large object (int)
.. attribute:: LargeObject.pgcnx
- the :class:`Connection` object associated with the large object
+ the :class:`Connection` object associated with the large object
.. attribute:: LargeObject.error
- the last warning/error message of the connection (str)
+ the last warning/error message of the connection (str)
.. warning::
Modified: trunk/docs/contents/pg/module.rst
==============================================================================
--- trunk/docs/contents/pg/module.rst Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/docs/contents/pg/module.rst Wed Feb 3 15:23:20 2016 (r814)
@@ -252,10 +252,10 @@
.. note::
- It is especially important to do proper escaping when
- handling strings that were received from an untrustworthy source.
- Otherwise there is a security risk: you are vulnerable to "SQL injection"
- attacks wherein unwanted SQL commands are fed to your database.
+ It is especially important to do proper escaping when
+ handling strings that were received from an untrustworthy source.
+ Otherwise there is a security risk: you are vulnerable to "SQL injection"
+ attacks wherein unwanted SQL commands are fed to your database.
Example::
@@ -429,7 +429,7 @@
.. versionadded:: 4.2
.. versionchanged:: 5.0
- Boolean values had been returned as string by default in earlier versions.
+ Boolean values had been returned as string by default in earlier versions.
get/set_array -- whether arrays are returned as list objects
------------------------------------------------------------
@@ -462,7 +462,7 @@
.. versionadded:: 5.0
.. versionchanged:: 5.0
- Arrays had been always returned as text strings only in earlier versions.
+ Arrays had been always returned as text strings only in earlier versions.
get/set_bytea_escaped -- whether bytea data is returned escaped
---------------------------------------------------------------
@@ -495,7 +495,7 @@
.. versionadded:: 5.0
.. versionchanged:: 5.0
- Bytea data had been returned in escaped form by default in earlier versions.
+ Bytea data had been returned in escaped form by default in earlier
versions.
get/set_jsondecode -- decoding JSON format
------------------------------------------
@@ -522,7 +522,7 @@
.. versionadded:: 5.0
.. versionchanged:: 5.0
- JSON data had been always returned as text strings in earlier versions.
+ JSON data had been always returned as text strings in earlier versions.
get/set_cast_hook -- fallback typecast function
-----------------------------------------------
@@ -547,6 +547,42 @@
.. versionadded:: 5.0
+get/set_datestyle -- assume a fixed date style
+----------------------------------------------
+
+.. function:: get_datestyle()
+
+ Get the assumed date style for typecasting
+
+This returns the PostgreSQL date style that is silently assumed when
+typecasting dates or *None* if no fixed date style is assumed, in which case
+the date style is requested from the database when necessary (this is the
+default). Note that this method will *not* get the date style that is
+currently set in the session or in the database. You can get the current
+setting with the methods :meth:`DB.get_parameter` and
+:meth:`Connection.parameter`. You can also get the date format corresponding
+to the current date style by calling :meth:`Connection.date_format`.
+
+.. versionadded:: 5.0
+
+.. function:: set_datestyle(datestyle)
+
+ Set a fixed date style that shall be assumed when typecasting
+
+ :param str datestyle: the date style that shall be assumed,
+ or *None* if no fixed dat style shall be assumed
+
+PyGreSQL is able to automatically pick up the right date style for typecasting
+date values from the database, even if you change it for the current session
+with a ``SET DateStyle`` command. This is happens very effectively without
+an additional database request being involved. If you still want to have
+PyGreSQL always assume a fixed date style instead, then you can set one with
+this function. Note that calling this function will *not* alter the date
+style of the database or the current session. You can do that by calling
+the method :meth:`DB.set_parameter` instead.
+
+.. versionadded:: 5.0
+
get/set_typecast -- custom typecasting
--------------------------------------
@@ -578,6 +614,12 @@
:param cast: the typecast function to be set for the specified type(s)
:type typ: str or int
+The typecast function must take one string object as argument and return a
+Python object into which the PostgreSQL type shall be casted. If the function
+takes another parameter named *connection*, then the current database
+connection will also be passed to the typecast function. This may sometimes
+be necessary to look up certain database settings.
+
.. versionadded:: 5.0
Note that database connections cache types and their cast functions using
@@ -680,14 +722,20 @@
A wrapper for holding a bytea value
+.. versionadded:: 5.0
+
.. function:: Json(obj)
A wrapper for holding an object serializable to JSON
+.. versionadded:: 5.0
+
.. function:: Literal(sql)
A wrapper for holding a literal SQL string
+.. versionadded:: 5.0
+
Module constants
----------------
Modified: trunk/docs/contents/pgdb/adaptation.rst
==============================================================================
--- trunk/docs/contents/pgdb/adaptation.rst Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/docs/contents/pgdb/adaptation.rst Wed Feb 3 15:23:20 2016
(r814)
@@ -84,9 +84,9 @@
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]]
+ >>> 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.
@@ -151,11 +151,11 @@
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)
+ >>> 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::
@@ -174,9 +174,9 @@
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'>
+ >>> 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
@@ -233,7 +233,7 @@
reopen the database connection, or reset the cache of the connection to
make this effective, using the following command::
- >>> con.type_cache.reset_typecast()
+ >>> 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.
Modified: trunk/docs/contents/pgdb/connection.rst
==============================================================================
--- trunk/docs/contents/pgdb/connection.rst Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/docs/contents/pgdb/connection.rst Wed Feb 3 15:23:20 2016
(r814)
@@ -67,7 +67,7 @@
.. note::
- The following attributes are not part of the DB-API 2 standard.
+ The following attributes are not part of the DB-API 2 standard.
.. attribute:: Connection.cursor_type
Modified: trunk/docs/contents/pgdb/cursor.rst
==============================================================================
--- trunk/docs/contents/pgdb/cursor.rst Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/docs/contents/pgdb/cursor.rst Wed Feb 3 15:23:20 2016 (r814)
@@ -240,7 +240,7 @@
.. note::
- The following methods and attributes are not part of the DB-API 2 standard.
+ 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])
Modified: trunk/docs/contents/pgdb/module.rst
==============================================================================
--- trunk/docs/contents/pgdb/module.rst Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/docs/contents/pgdb/module.rst Wed Feb 3 15:23:20 2016 (r814)
@@ -72,6 +72,12 @@
:param cast: the typecast function to be set for the specified type(s)
:type typ: str or int
+The typecast function must take one string object as argument and return a
+Python object into which the PostgreSQL type shall be casted. If the function
+takes another parameter named *connection*, then the current database
+connection will also be passed to the typecast function. This may sometimes
+be necessary to look up certain database settings.
+
.. versionadded:: 5.0
.. method:: reset_typecast([typ])
@@ -110,8 +116,8 @@
.. data:: paramstyle
- The string constant ``pyformat``, stating that parameters should be passed
- using Python extended format codes, e.g. ``" ... WHERE name=%(name)s"``.
+ The string constant ``pyformat``, stating that parameters should be passed
+ using Python extended format codes, e.g. ``" ... WHERE name=%(name)s"``.
Errors raised by this module
----------------------------
Modified: trunk/docs/contents/pgdb/typecache.rst
==============================================================================
--- trunk/docs/contents/pgdb/typecache.rst Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/docs/contents/pgdb/typecache.rst Wed Feb 3 15:23:20 2016
(r814)
@@ -58,6 +58,12 @@
:param cast: the typecast function to be set for the specified type(s)
:type typ: str or int
+The typecast function must take one string object as argument and return a
+Python object into which the PostgreSQL type shall be casted. If the function
+takes another parameter named *connection*, then the current database
+connection will also be passed to the typecast function. This may sometimes
+be necessary to look up certain database settings.
+
.. method:: TypeCache.reset_typecast([typ])
Reset the typecasts for the specified (or all) type(s) to their defaults
Modified: trunk/docs/contents/pgdb/types.rst
==============================================================================
--- trunk/docs/contents/pgdb/types.rst Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/docs/contents/pgdb/types.rst Wed Feb 3 15:23:20 2016 (r814)
@@ -54,10 +54,18 @@
Additionally, PyGreSQL provides the following constructors for PostgreSQL
specific data types:
+.. function:: Interval(days, hours=0, minutes=0, seconds=0, microseconds=0)
+
+ Construct an object holding a time interval value
+
+.. versionadded:: 5.0
+
.. function:: Bytea(bytes)
Construct an object capable of holding a bytea value
+.. versionadded:: 5.0
+
.. function:: Json(obj, [encode])
Construct a wrapper for holding an object serializable to JSON
@@ -69,6 +77,8 @@
Construct a wrapper for holding a literal SQL string
+.. versionadded:: 5.0
+
Example for using a type constructor::
>>> cursor.execute("create table jsondata (data jsonb)")
@@ -168,14 +178,20 @@
Used to describe ``json`` and ``jsonb`` columns
+.. versionadded:: 5.0
+
.. object:: ARRAY
Used to describe columns containing PostgreSQL arrays
+.. versionadded:: 5.0
+
.. object:: RECORD
Used to describe columns containing PostgreSQL records
+.. versionadded:: 5.0
+
Example for using some type objects::
>>> cursor = con.cursor()
Modified: trunk/pg.py
==============================================================================
--- trunk/pg.py Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/pg.py Wed Feb 3 15:23:20 2016 (r814)
@@ -52,6 +52,9 @@
except NameError: # Python >= 3.0
basestring = (str, bytes)
+
+# Auxiliary classes and functions that are independent from a DB connection:
+
try:
from collections import OrderedDict
except ImportError: # Python 2.6 or 3.0
@@ -137,8 +140,26 @@
def _read_only_error(*args, **kw):
raise TypeError('This object is read-only')
+try:
+ from inspect import signature
+except ImportError: # Python < 3.3
+ from inspect import getargspec
+
+ get_args = lambda func: getargspec(func).args
+else:
+ get_args = lambda func: list(signature(func).parameters)
+
+try:
+ if datetime.strptime('+0100', '%z') is None:
+ raise ValueError
+except ValueError: # Python < 3.2
+ timezones = None
+else:
+ # time zones used in Postgres timestamptz output
+ timezones = dict(CET='+0100', EET='+0200', EST='-0500',
+ GMT='+0000', HST='-1000', MET='+0100', MST='-0700',
+ UCT='+0000', UTC='+0000', WET='+0000')
-# Auxiliary classes and functions that are independent from a DB connection:
def _oid_key(table):
"""Build oid key from a table name."""
@@ -587,6 +608,211 @@
return [int(v) for v in value.split()]
+def cast_date(value, connection):
+ """Cast a date value."""
+ # The output format depends on the server setting DateStyle. The default
+ # setting ISO and the setting for German are actually unambiguous. The
+ # order of days and months in the other two settings is however ambiguous,
+ # so at least here we need to consult the setting to properly parse values.
+ if value == '-infinity':
+ return date.min
+ if value == 'infinity':
+ return date.max
+ value = value.split()
+ if value[-1] == 'BC':
+ return date.min
+ value = value[0]
+ if len(value) > 10:
+ return date.max
+ fmt = connection.date_format()
+ return datetime.strptime(value, fmt).date()
+
+
+def cast_time(value):
+ """Cast a time value."""
+ fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
+ return datetime.strptime(value, fmt).time()
+
+
+_re_timezone = regex('(.*)([+-].*)')
+
+
+def cast_timetz(value):
+ """Cast a timetz value."""
+ tz = _re_timezone.match(value)
+ if tz:
+ value, tz = tz.groups()
+ else:
+ tz = '+0000'
+ fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
+ if timezones:
+ if tz.startswith(('+', '-')):
+ if len(tz) < 5:
+ tz += '00'
+ else:
+ tz = tz.replace(':', '')
+ elif tz in timezones:
+ tz = timezones[tz]
+ else:
+ tz = '+0000'
+ value += tz
+ fmt += '%z'
+ return datetime.strptime(value, fmt).timetz()
+
+
+def cast_timestamp(value, connection):
+ """Cast a timestamp value."""
+ if value == '-infinity':
+ return datetime.min
+ if value == 'infinity':
+ return datetime.max
+ value = value.split()
+ if value[-1] == 'BC':
+ return datetime.min
+ fmt = connection.date_format()
+ if fmt.endswith('-%Y') and len(value) > 2:
+ value = value[1:5]
+ if len(value[3]) > 4:
+ return datetime.max
+ fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
+ '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
+ else:
+ if len(value[0]) > 10:
+ return datetime.max
+ fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
+ return datetime.strptime(' '.join(value), ' '.join(fmt))
+
+
+def cast_timestamptz(value, connection):
+ """Cast a timestamptz value."""
+ if value == '-infinity':
+ return datetime.min
+ if value == 'infinity':
+ return datetime.max
+ value = value.split()
+ if value[-1] == 'BC':
+ return datetime.min
+ fmt = connection.date_format()
+ if fmt.endswith('-%Y') and len(value) > 2:
+ value = value[1:]
+ if len(value[3]) > 4:
+ return datetime.max
+ fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
+ '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
+ value, tz = value[:-1], value[-1]
+ else:
+ if fmt.startswith('%Y-'):
+ tz = _re_timezone.match(value[1])
+ if tz:
+ value[1], tz = tz.groups()
+ else:
+ tz = '+0000'
+ else:
+ value, tz = value[:-1], value[-1]
+ if len(value[0]) > 10:
+ return datetime.max
+ fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
+ if timezones:
+ if tz.startswith(('+', '-')):
+ if len(tz) < 5:
+ tz += '00'
+ else:
+ tz = tz.replace(':', '')
+ elif tz in timezones:
+ tz = timezones[tz]
+ else:
+ tz = '+0000'
+ value.append(tz)
+ fmt.append('%z')
+ return datetime.strptime(' '.join(value), ' '.join(fmt))
+
+_re_interval_sql_standard = regex(
+ '(?:([+-])?([0-9]+)-([0-9]+) ?)?'
+ '(?:([+-]?[0-9]+)(?!:) ?)?'
+ '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
+
+_re_interval_postgres = regex(
+ '(?:([+-]?[0-9]+) ?years? ?)?'
+ '(?:([+-]?[0-9]+) ?mons? ?)?'
+ '(?:([+-]?[0-9]+) ?days? ?)?'
+ '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
+
+_re_interval_postgres_verbose = regex(
+ '@ ?(?:([+-]?[0-9]+) ?years? ?)?'
+ '(?:([+-]?[0-9]+) ?mons? ?)?'
+ '(?:([+-]?[0-9]+) ?days? ?)?'
+ '(?:([+-]?[0-9]+) ?hours? ?)?'
+ '(?:([+-]?[0-9]+) ?mins? ?)?'
+ '(?:([+-])?([0-9]+)(?:\\.([0-9]+))? ?secs?)? ?(ago)?')
+
+_re_interval_iso_8601 = regex(
+ 'P(?:([+-]?[0-9]+)Y)?'
+ '(?:([+-]?[0-9]+)M)?'
+ '(?:([+-]?[0-9]+)D)?'
+ '(?:T(?:([+-]?[0-9]+)H)?'
+ '(?:([+-]?[0-9]+)M)?'
+ '(?:([+-])?([0-9]+)(?:\\.([0-9]+))?S)?)?')
+
+
+def cast_interval(value):
+ """Cast an interval value."""
+ # The output format depends on the server setting IntervalStyle, but it's
+ # not necessary to consult this setting to parse it. It's faster to just
+ # check all possible formats, and there is no ambiguity here.
+ m = _re_interval_iso_8601.match(value)
+ if m:
+ m = [d or '0' for d in m.groups()]
+ secs_ago = m.pop(5) == '-'
+ m = [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if secs_ago:
+ secs = -secs
+ usecs = -usecs
+ else:
+ m = _re_interval_postgres_verbose.match(value)
+ if m:
+ m, ago = [d or '0' for d in m.groups()[:8]], m.group(9)
+ secs_ago = m.pop(5) == '-'
+ m = [-int(d) for d in m] if ago else [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if secs_ago:
+ secs = - secs
+ usecs = -usecs
+ else:
+ m = _re_interval_postgres.match(value)
+ if m and any(m.groups()):
+ m = [d or '0' for d in m.groups()]
+ hours_ago = m.pop(3) == '-'
+ m = [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if hours_ago:
+ hours = -hours
+ mins = -mins
+ secs = -secs
+ usecs = -usecs
+ else:
+ m = _re_interval_sql_standard.match(value)
+ if m and any(m.groups()):
+ m = [d or '0' for d in m.groups()]
+ years_ago = m.pop(0) == '-'
+ hours_ago = m.pop(3) == '-'
+ m = [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if years_ago:
+ years = -years
+ mons = -mons
+ if hours_ago:
+ hours = -hours
+ mins = -mins
+ secs = -secs
+ usecs = -usecs
+ else:
+ raise ValueError('Cannot parse interval: %s' % value)
+ days += 365 * years + 30 * mons
+ return timedelta(days=days, hours=hours, minutes=mins,
+ seconds=secs, microseconds=usecs)
+
+
class Typecasts(dict):
"""Dictionary mapping database types to typecast functions.
@@ -609,9 +835,14 @@
'oid': long, 'oid8': long,
'float4': float, 'float8': float,
'numeric': cast_num, 'money': cast_money,
+ 'date': cast_date, 'interval': cast_interval,
+ 'time': cast_time, 'timetz': cast_timetz,
+ 'timestamp': cast_timestamp, 'timestamptz': cast_timestamptz,
'int2vector': cast_int2vector,
'anyarray': cast_array, 'record': cast_record}
+ connection = None # will be set in a connection specific instance
+
def __missing__(self, typ):
"""Create a cast function if it is not cached.
@@ -623,6 +854,7 @@
cast = self.defaults.get(typ)
if cast:
# store default for faster access
+ cast = self._add_connection(cast)
self[typ] = cast
elif typ.startswith('_'):
base_cast = self[typ[1:]]
@@ -637,6 +869,23 @@
self[typ] = cast
return cast
+ @staticmethod
+ def _needs_connection(func):
+ """Check if a typecast function needs a connection argument."""
+ try:
+ args = get_args(func)
+ except (TypeError, ValueError):
+ return False
+ else:
+ return 'connection' in args[1:]
+
+ def _add_connection(self, cast):
+ """Add a connection argument to the typecast function if necessary."""
+ if not self.connection or not self._needs_connection(cast):
+ return cast
+ connection = self.connection
+ return lambda value: cast(value, connection=connection)
+
def get(self, typ, default=None):
"""Get the typecast function for the given database type."""
return self[typ] or default
@@ -653,7 +902,7 @@
if not callable(cast):
raise TypeError("Cast parameter must be callable")
for t in typ:
- self[t] = cast
+ self[t] = self._add_connection(cast)
self.pop('_%s' % t, None)
def reset(self, typ=None):
@@ -698,6 +947,13 @@
"""
return {}
+ def dateformat(self):
+ """Return the current date format.
+
+ This method will be replaced with the dateformat() method of DbTypes.
+ """
+ return '%Y-%m-%d'
+
def create_array_cast(self, cast):
"""Create an array typecast for the given base cast."""
return lambda v: cast_array(v, cast)
@@ -757,13 +1013,14 @@
def __init__(self, db):
"""Initialize type cache for connection."""
super(DbTypes, self).__init__()
+ self._regtypes = False
self._get_attnames = db.get_attnames
+ self._typecasts = Typecasts()
+ self._typecasts.get_attnames = self.get_attnames
+ self._typecasts.connection = db
db = db.db
self.query = db.query
self.escape_string = db.escape_string
- self._typecasts = Typecasts()
- self._typecasts.get_attnames = self.get_attnames
- self._regtypes = False
def add(self, oid, pgtype, regtype,
typtype, category, delim, relid):
Modified: trunk/pgdb.py
==============================================================================
--- trunk/pgdb.py Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/pgdb.py Wed Feb 3 15:23:20 2016 (r814)
@@ -72,6 +72,7 @@
from decimal import Decimal
from math import isnan, isinf
from collections import namedtuple
+from re import compile as regex
from json import loads as jsondecode, dumps as jsonencode
try:
@@ -110,6 +111,27 @@
### Internal Type Handling
+try:
+ from inspect import signature
+except ImportError: # Python < 3.3
+ from inspect import getargspec
+
+ get_args = lambda func: getargspec(func).args
+else:
+ get_args = lambda func: list(signature(func).parameters)
+
+try:
+ if datetime.strptime('+0100', '%z') is None:
+ raise ValueError
+except ValueError: # Python < 3.2
+ timezones = None
+else:
+ # time zones used in Postgres timestamptz output
+ timezones = dict(CET='+0100', EET='+0200', EST='-0500',
+ GMT='+0000', HST='-1000', MET='+0100', MST='-0700',
+ UCT='+0000', UTC='+0000', WET='+0000')
+
+
def decimal_type(decimal_type=None):
"""Get or set global type to be used for decimal values.
@@ -141,6 +163,211 @@
return [int(v) for v in value.split()]
+def cast_date(value, connection):
+ """Cast a date value."""
+ # The output format depends on the server setting DateStyle. The default
+ # setting ISO and the setting for German are actually unambiguous. The
+ # order of days and months in the other two settings is however ambiguous,
+ # so at least here we need to consult the setting to properly parse values.
+ if value == '-infinity':
+ return date.min
+ if value == 'infinity':
+ return date.max
+ value = value.split()
+ if value[-1] == 'BC':
+ return date.min
+ value = value[0]
+ if len(value) > 10:
+ return date.max
+ fmt = connection.date_format()
+ return datetime.strptime(value, fmt).date()
+
+
+def cast_time(value):
+ """Cast a time value."""
+ fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
+ return datetime.strptime(value, fmt).time()
+
+
+_re_timezone = regex('(.*)([+-].*)')
+
+
+def cast_timetz(value):
+ """Cast a timetz value."""
+ tz = _re_timezone.match(value)
+ if tz:
+ value, tz = tz.groups()
+ else:
+ tz = '+0000'
+ fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
+ if timezones:
+ if tz.startswith(('+', '-')):
+ if len(tz) < 5:
+ tz += '00'
+ else:
+ tz = tz.replace(':', '')
+ elif tz in timezones:
+ tz = timezones[tz]
+ else:
+ tz = '+0000'
+ value += tz
+ fmt += '%z'
+ return datetime.strptime(value, fmt).timetz()
+
+
+def cast_timestamp(value, connection):
+ """Cast a timestamp value."""
+ if value == '-infinity':
+ return datetime.min
+ if value == 'infinity':
+ return datetime.max
+ value = value.split()
+ if value[-1] == 'BC':
+ return datetime.min
+ fmt = connection.date_format()
+ if fmt.endswith('-%Y') and len(value) > 2:
+ value = value[1:5]
+ if len(value[3]) > 4:
+ return datetime.max
+ fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
+ '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
+ else:
+ if len(value[0]) > 10:
+ return datetime.max
+ fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
+ return datetime.strptime(' '.join(value), ' '.join(fmt))
+
+
+def cast_timestamptz(value, connection):
+ """Cast a timestamptz value."""
+ if value == '-infinity':
+ return datetime.min
+ if value == 'infinity':
+ return datetime.max
+ value = value.split()
+ if value[-1] == 'BC':
+ return datetime.min
+ fmt = connection.date_format()
+ if fmt.endswith('-%Y') and len(value) > 2:
+ value = value[1:]
+ if len(value[3]) > 4:
+ return datetime.max
+ fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
+ '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
+ value, tz = value[:-1], value[-1]
+ else:
+ if fmt.startswith('%Y-'):
+ tz = _re_timezone.match(value[1])
+ if tz:
+ value[1], tz = tz.groups()
+ else:
+ tz = '+0000'
+ else:
+ value, tz = value[:-1], value[-1]
+ if len(value[0]) > 10:
+ return datetime.max
+ fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
+ if timezones:
+ if tz.startswith(('+', '-')):
+ if len(tz) < 5:
+ tz += '00'
+ else:
+ tz = tz.replace(':', '')
+ elif tz in timezones:
+ tz = timezones[tz]
+ else:
+ tz = '+0000'
+ value.append(tz)
+ fmt.append('%z')
+ return datetime.strptime(' '.join(value), ' '.join(fmt))
+
+_re_interval_sql_standard = regex(
+ '(?:([+-])?([0-9]+)-([0-9]+) ?)?'
+ '(?:([+-]?[0-9]+)(?!:) ?)?'
+ '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
+
+_re_interval_postgres = regex(
+ '(?:([+-]?[0-9]+) ?years? ?)?'
+ '(?:([+-]?[0-9]+) ?mons? ?)?'
+ '(?:([+-]?[0-9]+) ?days? ?)?'
+ '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
+
+_re_interval_postgres_verbose = regex(
+ '@ ?(?:([+-]?[0-9]+) ?years? ?)?'
+ '(?:([+-]?[0-9]+) ?mons? ?)?'
+ '(?:([+-]?[0-9]+) ?days? ?)?'
+ '(?:([+-]?[0-9]+) ?hours? ?)?'
+ '(?:([+-]?[0-9]+) ?mins? ?)?'
+ '(?:([+-])?([0-9]+)(?:\\.([0-9]+))? ?secs?)? ?(ago)?')
+
+_re_interval_iso_8601 = regex(
+ 'P(?:([+-]?[0-9]+)Y)?'
+ '(?:([+-]?[0-9]+)M)?'
+ '(?:([+-]?[0-9]+)D)?'
+ '(?:T(?:([+-]?[0-9]+)H)?'
+ '(?:([+-]?[0-9]+)M)?'
+ '(?:([+-])?([0-9]+)(?:\\.([0-9]+))?S)?)?')
+
+
+def cast_interval(value):
+ """Cast an interval value."""
+ # The output format depends on the server setting IntervalStyle, but it's
+ # not necessary to consult this setting to parse it. It's faster to just
+ # check all possible formats, and there is no ambiguity here.
+ m = _re_interval_iso_8601.match(value)
+ if m:
+ m = [d or '0' for d in m.groups()]
+ secs_ago = m.pop(5) == '-'
+ m = [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if secs_ago:
+ secs = -secs
+ usecs = -usecs
+ else:
+ m = _re_interval_postgres_verbose.match(value)
+ if m:
+ m, ago = [d or '0' for d in m.groups()[:8]], m.group(9)
+ secs_ago = m.pop(5) == '-'
+ m = [-int(d) for d in m] if ago else [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if secs_ago:
+ secs = - secs
+ usecs = -usecs
+ else:
+ m = _re_interval_postgres.match(value)
+ if m and any(m.groups()):
+ m = [d or '0' for d in m.groups()]
+ hours_ago = m.pop(3) == '-'
+ m = [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if hours_ago:
+ hours = -hours
+ mins = -mins
+ secs = -secs
+ usecs = -usecs
+ else:
+ m = _re_interval_sql_standard.match(value)
+ if m and any(m.groups()):
+ m = [d or '0' for d in m.groups()]
+ years_ago = m.pop(0) == '-'
+ hours_ago = m.pop(3) == '-'
+ m = [int(d) for d in m]
+ years, mons, days, hours, mins, secs, usecs = m
+ if years_ago:
+ years = -years
+ mons = -mons
+ if hours_ago:
+ hours = -hours
+ mins = -mins
+ secs = -secs
+ usecs = -usecs
+ else:
+ raise ValueError('Cannot parse interval: %s' % value)
+ days += 365 * years + 30 * mons
+ return timedelta(days=days, hours=hours, minutes=mins,
+ seconds=secs, microseconds=usecs)
+
+
class Typecasts(dict):
"""Dictionary mapping database types to typecast functions.
@@ -160,9 +387,14 @@
'oid': long, 'oid8': long,
'float4': float, 'float8': float,
'numeric': Decimal, 'money': cast_money,
+ 'date': cast_date, 'interval': cast_interval,
+ 'time': cast_time, 'timetz': cast_timetz,
+ 'timestamp': cast_timestamp, 'timestamptz': cast_timestamptz,
'int2vector': cast_int2vector,
'anyarray': cast_array, 'record': cast_record}
+ connection = None # will be set in local connection specific instances
+
def __missing__(self, typ):
"""Create a cast function if it is not cached.
@@ -174,6 +406,7 @@
cast = self.defaults.get(typ)
if cast:
# store default for faster access
+ cast = self._add_connection(cast)
self[typ] = cast
elif typ.startswith('_'):
# create array cast
@@ -184,6 +417,23 @@
self[typ] = cast
return cast
+ @staticmethod
+ def _needs_connection(func):
+ """Check if a typecast function needs a connection argument."""
+ try:
+ args = get_args(func)
+ except (TypeError, ValueError):
+ return False
+ else:
+ return 'connection' in args[1:]
+
+ def _add_connection(self, cast):
+ """Add a connection argument to the typecast function if necessary."""
+ if not self.connection or not self._needs_connection(cast):
+ return cast
+ connection = self.connection
+ return lambda value: cast(value, connection=connection)
+
def get(self, typ, default=None):
"""Get the typecast function for the given database type."""
return self[typ] or default
@@ -200,7 +450,7 @@
if not callable(cast):
raise TypeError("Cast parameter must be callable")
for t in typ:
- self[t] = cast
+ self[t] = self._add_connection(cast)
self.pop('_%s' % t, None)
def reset(self, typ=None):
@@ -218,11 +468,11 @@
for t in typ:
cast = defaults.get(t)
if cast:
- self[t] = cast
+ self[t] = self._add_connection(cast)
t = '_%s' % t
cast = defaults.get(t)
if cast:
- self[t] = cast
+ self[t] = self._add_connection(cast)
else:
self.pop(t, None)
else:
@@ -272,6 +522,8 @@
defaults = _typecasts
+ connection = None # will be set in a connection specific instance
+
def __missing__(self, typ):
"""Create a cast function if it is not cached."""
if typ.startswith('_'):
@@ -282,6 +534,7 @@
else:
cast = self.defaults.get(typ)
if cast:
+ cast = self._add_connection(cast)
self[typ] = cast
else:
fields = self.get_fields(typ)
@@ -337,6 +590,7 @@
self._src = cnx.source()
self._typecasts = LocalTypecasts()
self._typecasts.get_fields = self.get_fields
+ self._typecasts.connection = cnx
def __missing__(self, key):
"""Get the type info from the database if it is not cached."""
@@ -1298,6 +1552,11 @@
# Additional type helpers for PyGreSQL:
+def Interval(days, hours=0, minutes=0, seconds=0, microseconds=0):
+ """Construct an object holding a time inverval value."""
+ return timedelta(days, hours=hours, minutes=minutes, seconds=seconds,
+ microseconds=microseconds)
+
class Bytea(bytes):
"""Construct an object capable of holding a bytea value."""
Modified: trunk/pgmodule.c
==============================================================================
--- trunk/pgmodule.c Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/pgmodule.c Wed Feb 3 15:23:20 2016 (r814)
@@ -91,6 +91,7 @@
static PyObject *decimal = NULL, /* decimal type */
*namedresult = NULL, /* function for getting
named results */
*jsondecode = NULL; /* function for decoding
json strings */
+static const char *date_format = NULL; /* date format that is always assumed */
static char decimal_point = '.'; /* decimal point used in money values */
static int bool_as_text = 0; /* whether bool shall be returned as text */
static int array_as_text = 0; /* whether arrays shall be returned as text */
@@ -139,7 +140,8 @@
{
PyObject_HEAD
int valid; /* validity
flag */
- PGconn *cnx; /* PostGres connection
handle */
+ PGconn *cnx; /* Postgres connection
handle */
+ const char *date_format; /* date format derived from
datestyle */
PyObject *cast_hook; /* external typecast method */
PyObject *notice_receiver; /* current notice receiver */
} connObject;
@@ -294,12 +296,6 @@
case TEXTOID:
case VARCHAROID:
case NAMEOID:
- case DATEOID:
- case INTERVALOID:
- case TIMEOID:
- case TIMETZOID:
- case TIMESTAMPOID:
- case TIMESTAMPTZOID:
case REGTYPEOID:
t = PYGRES_TEXT;
break;
@@ -352,12 +348,6 @@
case TEXTARRAYOID:
case VARCHARARRAYOID:
case NAMEARRAYOID:
- case DATEARRAYOID:
- case INTERVALARRAYOID:
- case TIMEARRAYOID:
- case TIMETZARRAYOID:
- case TIMESTAMPARRAYOID:
- case TIMESTAMPTZARRAYOID:
case REGTYPEARRAYOID:
t = array_as_text ? PYGRES_TEXT : (PYGRES_TEXT |
PYGRES_ARRAY);
break;
@@ -2021,6 +2011,10 @@
return NULL;
}
+ /* this may have changed the datestyle, so we reset the date format
+ in order to force fetching it newly when next time requested */
+ self->date_format = date_format; /* this is normally NULL */
+
/* checks result status */
if ((status = PQresultStatus(result)) != PGRES_TUPLES_OK)
{
@@ -2469,6 +2463,98 @@
return Py_None;
}
+/* internal function converting a Postgres datestyles to date formats */
+static const char *
+date_style_to_format(const char *s)
+{
+ static const char *formats[] = {
+ "%Y-%m-%d", /* 0 = ISO */
+ "%m-%d-%Y", /* 1 = Postgres, MDY */
+ "%d-%m-%Y", /* 2 = Postgres, DMY */
+ "%m/%d/%Y", /* 3 = SQL, MDY */
+ "%d/%m/%Y", /* 4 = SQL, DMY */
+ "%d.%m.%Y"}; /* 5 = German */
+
+ switch (s ? *s : 'I')
+ {
+ case 'P': /* Postgres */
+ s = strchr(s + 1, ',');
+ if (s) do ++s; while (*s && *s == ' ');
+ return formats[s && *s == 'D' ? 2 : 1];
+ case 'S': /* SQL */
+ s = strchr(s + 1, ',');
+ if (s) do ++s; while (*s && *s == ' ');
+ return formats[s && *s == 'D' ? 4 : 3];
+ case 'G': /* German */
+ return formats[5];
+ default: /* ISO */
+ return formats[0]; /* ISO is the default */
+ }
+}
+
+/* internal function converting a date format to a Postgres datestyle */
+static const char *
+date_format_to_style(const char *s)
+{
+ static const char *datestyle[] = {
+ "ISO, YMD", /* 0 = %Y-%m-%d */
+ "Postgres, MDY", /* 1 = %m-%d-%Y */
+ "Postgres, DMY", /* 2 = %d-%m-%Y */
+ "SQL, MDY", /* 3 = %m/%d/%Y */
+ "SQL, DMY", /* 4 = %d/%m/%Y */
+ "German, DMY"}; /* 5 = %d.%m.%Y */
+
+ switch (s ? s[1] : 'Y')
+ {
+ case 'm':
+ switch (s[2])
+ {
+ case '/':
+ return datestyle[3]; /* SQL, MDY */
+ default:
+ return datestyle[1]; /* Postgres, MDY */
+ }
+ case 'd':
+ switch (s[2])
+ {
+ case '/':
+ return datestyle[4]; /* SQL, DMY */
+ case '.':
+ return datestyle[5]; /* German */
+ default:
+ return datestyle[2]; /* Postgres, DMY */
+ }
+ default:
+ return datestyle[0]; /* ISO */
+ }
+}
+
+/* get current date format */
+static char connDateFormat__doc__[] =
+"date_format() -- return the current date format";
+
+static PyObject *
+connDateFormat(connObject *self, PyObject *noargs)
+{
+ const char *fmt;
+
+ if (!self->cnx)
+ {
+ PyErr_SetString(PyExc_TypeError, "Connection is not valid");
+ return NULL;
+ }
+
+ /* check if the date format is cached in the connection */
+ fmt = self->date_format;
+ if (!fmt)
+ {
+ fmt = date_style_to_format(PQparameterStatus(self->cnx,
"DateStyle"));
+ self->date_format = fmt; /* cache the result */
+ }
+
+ return PyStr_FromString(fmt);
+}
+
#ifdef ESCAPING_FUNCS
/* escape literal */
@@ -3039,6 +3125,8 @@
connTransaction__doc__},
{"parameter", (PyCFunction) connParameter, METH_VARARGS,
connParameter__doc__},
+ {"date_format", (PyCFunction) connDateFormat, METH_NOARGS,
+ connDateFormat__doc__},
#ifdef ESCAPING_FUNCS
{"escape_literal", (PyCFunction) connEscapeLiteral, METH_O,
@@ -3296,6 +3384,10 @@
return NULL;
}
+ /* this may have changed the datestyle, so we reset the date format
+ in order to force fetching it newly when next time requested */
+ self->pgcnx->date_format = date_format; /* this is normally NULL */
+
/* checks result status */
switch (PQresultStatus(self->result))
{
@@ -4078,6 +4170,7 @@
npgobj->valid = 1;
npgobj->cnx = NULL;
+ npgobj->date_format = date_format;
npgobj->cast_hook = NULL;
npgobj->notice_receiver = NULL;
@@ -4723,6 +4816,45 @@
return to_obj;
}
+/* set fixed datestyle */
+static char pgSetDatestyle__doc__[] =
+"set_datestyle(style) -- set which style is assumed";
+
+static PyObject *
+pgSetDatestyle(PyObject *self, PyObject *args)
+{
+ const char *datestyle = NULL;
+
+ /* gets arguments */
+ if (!PyArg_ParseTuple(args, "z", &datestyle))
+ {
+ PyErr_SetString(PyExc_TypeError,
+ "Function set_datestyle() expects a string or None as
argument");
+ return NULL;
+ }
+
+ date_format = datestyle ? date_style_to_format(datestyle) : NULL;
+
+ Py_INCREF(Py_None); return Py_None;
+}
+
+/* get fixed datestyle */
+static char pgGetDatestyle__doc__[] =
+"get_datestyle() -- get which date style is assumed";
+
+static PyObject *
+pgGetDatestyle(PyObject *self, PyObject *noargs)
+{
+ if (date_format)
+ {
+ return PyStr_FromString(date_format_to_style(date_format));
+ }
+ else
+ {
+ Py_INCREF(Py_None); return Py_None;
+ }
+}
+
/* get decimal point */
static char pgGetDecimalPoint__doc__[] =
"get_decimal_point() -- get decimal point to be used for money values";
@@ -4798,28 +4930,24 @@
"set_decimal(cls) -- set a decimal type to be used for numeric values";
static PyObject *
-pgSetDecimal(PyObject *self, PyObject *args)
+pgSetDecimal(PyObject *self, PyObject *cls)
{
PyObject *ret = NULL;
- PyObject *cls;
- if (PyArg_ParseTuple(args, "O", &cls))
+ if (cls == Py_None)
{
- if (cls == Py_None)
- {
- Py_XDECREF(decimal); decimal = NULL;
- Py_INCREF(Py_None); ret = Py_None;
- }
- else if (PyCallable_Check(cls))
- {
- Py_XINCREF(cls); Py_XDECREF(decimal); decimal = cls;
- Py_INCREF(Py_None); ret = Py_None;
- }
- else
- PyErr_SetString(PyExc_TypeError,
- "Function set_decimal() expects"
- " a callable or None as argument");
+ Py_XDECREF(decimal); decimal = NULL;
+ Py_INCREF(Py_None); ret = Py_None;
}
+ else if (PyCallable_Check(cls))
+ {
+ Py_XINCREF(cls); Py_XDECREF(decimal); decimal = cls;
+ Py_INCREF(Py_None); ret = Py_None;
+ }
+ else
+ PyErr_SetString(PyExc_TypeError,
+ "Function set_decimal() expects"
+ " a callable or None as argument");
return ret;
}
@@ -4958,28 +5086,24 @@
"set_namedresult(func) -- set a function to be used for getting named results";
static PyObject *
-pgSetNamedresult(PyObject *self, PyObject *args)
+pgSetNamedresult(PyObject *self, PyObject *func)
{
PyObject *ret = NULL;
- PyObject *func;
- if (PyArg_ParseTuple(args, "O", &func))
+ if (func == Py_None)
{
- if (func == Py_None)
- {
- Py_XDECREF(namedresult); namedresult = NULL;
- Py_INCREF(Py_None); ret = Py_None;
- }
- else if (PyCallable_Check(func))
- {
- Py_XINCREF(func); Py_XDECREF(namedresult); namedresult
= func;
- Py_INCREF(Py_None); ret = Py_None;
- }
- else
- PyErr_SetString(PyExc_TypeError,
- "Function set_namedresult() expectst"
- " a callable or None as argument");
+ Py_XDECREF(namedresult); namedresult = NULL;
+ Py_INCREF(Py_None); ret = Py_None;
+ }
+ else if (PyCallable_Check(func))
+ {
+ Py_XINCREF(func); Py_XDECREF(namedresult); namedresult = func;
+ Py_INCREF(Py_None); ret = Py_None;
}
+ else
+ PyErr_SetString(PyExc_TypeError,
+ "Function set_namedresult() expects"
+ " a callable or None as argument");
return ret;
}
@@ -5003,31 +5127,27 @@
/* set json decode function */
static char pgSetJsondecode__doc__[] =
-"set_jsondecode() -- set a function to be used for decoding json results";
+"set_jsondecode(func) -- set a function to be used for decoding json results";
static PyObject *
-pgSetJsondecode(PyObject *self, PyObject *args)
+pgSetJsondecode(PyObject *self, PyObject *func)
{
PyObject *ret = NULL;
- PyObject *func;
- if (PyArg_ParseTuple(args, "O", &func))
+ if (func == Py_None)
{
- if (func == Py_None)
- {
- Py_XDECREF(jsondecode); jsondecode = NULL;
- Py_INCREF(Py_None); ret = Py_None;
- }
- else if (PyCallable_Check(func))
- {
- Py_XINCREF(func); Py_XDECREF(jsondecode); jsondecode =
func;
- Py_INCREF(Py_None); ret = Py_None;
- }
- else
- PyErr_SetString(PyExc_TypeError,
- "Function jsondecode() expects"
- " a callable or None as argument");
+ Py_XDECREF(jsondecode); jsondecode = NULL;
+ Py_INCREF(Py_None); ret = Py_None;
+ }
+ else if (PyCallable_Check(func))
+ {
+ Py_XINCREF(func); Py_XDECREF(jsondecode); jsondecode = func;
+ Py_INCREF(Py_None); ret = Py_None;
}
+ else
+ PyErr_SetString(PyExc_TypeError,
+ "Function jsondecode() expects"
+ " a callable or None as argument");
return ret;
}
@@ -5419,13 +5539,17 @@
pgEscapeBytea__doc__},
{"unescape_bytea", (PyCFunction) pgUnescapeBytea, METH_O,
pgUnescapeBytea__doc__},
+ {"get_datestyle", (PyCFunction) pgGetDatestyle, METH_NOARGS,
+ pgGetDatestyle__doc__},
+ {"set_datestyle", (PyCFunction) pgSetDatestyle, METH_VARARGS,
+ pgSetDatestyle__doc__},
{"get_decimal_point", (PyCFunction) pgGetDecimalPoint, METH_NOARGS,
pgGetDecimalPoint__doc__},
{"set_decimal_point", (PyCFunction) pgSetDecimalPoint, METH_VARARGS,
pgSetDecimalPoint__doc__},
{"get_decimal", (PyCFunction) pgGetDecimal, METH_NOARGS,
pgGetDecimal__doc__},
- {"set_decimal", (PyCFunction) pgSetDecimal, METH_VARARGS,
+ {"set_decimal", (PyCFunction) pgSetDecimal, METH_O,
pgSetDecimal__doc__},
{"get_bool", (PyCFunction) pgGetBool, METH_NOARGS, pgGetBool__doc__},
{"set_bool", (PyCFunction) pgSetBool, METH_VARARGS, pgSetBool__doc__},
@@ -5437,11 +5561,11 @@
pgSetByteaEscaped__doc__},
{"get_namedresult", (PyCFunction) pgGetNamedresult, METH_NOARGS,
pgGetNamedresult__doc__},
- {"set_namedresult", (PyCFunction) pgSetNamedresult, METH_VARARGS,
+ {"set_namedresult", (PyCFunction) pgSetNamedresult, METH_O,
pgSetNamedresult__doc__},
{"get_jsondecode", (PyCFunction) pgGetJsondecode, METH_NOARGS,
pgGetJsondecode__doc__},
- {"set_jsondecode", (PyCFunction) pgSetJsondecode, METH_VARARGS,
+ {"set_jsondecode", (PyCFunction) pgSetJsondecode, METH_O,
pgSetJsondecode__doc__},
{"cast_array", (PyCFunction) pgCastArray, METH_VARARGS|METH_KEYWORDS,
pgCastArray__doc__},
Modified: trunk/tests/test_classic_connection.py
==============================================================================
--- trunk/tests/test_classic_connection.py Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/tests/test_classic_connection.py Wed Feb 3 15:23:20 2016
(r814)
@@ -120,7 +120,7 @@
self.assertEqual(attributes, connection_attributes)
def testAllConnectMethods(self):
- methods = '''cancel close endcopy
+ methods = '''cancel close date_format endcopy
escape_bytea escape_identifier escape_literal escape_string
fileno get_cast_hook get_notice_receiver getline getlo getnotify
inserttable locreate loimport parameter putline query reset
@@ -914,14 +914,18 @@
q = 'select $1::%s' % (pgtype,)
r = self.c.query(q, (value,)).getresult()[0][0]
self.assertIsInstance(r, pytype)
- if isinstance(value, (bytes, str)):
- if not value or '{':
+ if isinstance(value, str):
+ if not value or ' ' in value or '{' in value:
value = '"%s"' % value
value = '{%s}' % value
r = self.c.query(q + '[]', (value,)).getresult()[0][0]
- self.assertIsInstance(r, list)
- self.assertEqual(len(r), 1)
- self.assertIsInstance(r[0], pytype)
+ if pgtype.startswith(('date', 'time', 'interval')):
+ # arrays of these are casted by the DB wrapper only
+ self.assertEqual(r, value)
+ else:
+ self.assertIsInstance(r, list)
+ self.assertEqual(len(r), 1)
+ self.assertIsInstance(r[0], pytype)
def testInt(self):
self.assert_proper_cast(0, 'int', int)
@@ -955,11 +959,11 @@
def testDate(self):
self.assert_proper_cast('1956-01-31', 'date', str)
- self.assert_proper_cast('0', 'interval', str)
- self.assert_proper_cast('08:42', 'time', str)
- self.assert_proper_cast('08:42', 'timetz', str)
- self.assert_proper_cast('1956-01-31 08:42', 'timestamp', str)
- self.assert_proper_cast('1956-01-31 08:42', 'timestamptz', str)
+ self.assert_proper_cast('10:20:30', 'interval', str)
+ self.assert_proper_cast('08:42:15', 'time', str)
+ self.assert_proper_cast('08:42:15+00', 'timetz', str)
+ self.assert_proper_cast('1956-01-31 08:42:15', 'timestamp', str)
+ self.assert_proper_cast('1956-01-31 08:42:15+00', 'timestamptz', str)
def testText(self):
self.assert_proper_cast('', 'text', str)
Modified: trunk/tests/test_classic_dbwrapper.py
==============================================================================
--- trunk/tests/test_classic_dbwrapper.py Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/tests/test_classic_dbwrapper.py Wed Feb 3 15:23:20 2016
(r814)
@@ -17,13 +17,14 @@
import os
import sys
-import tempfile
import json
+import tempfile
import pg # the module under test
from decimal import Decimal
-from datetime import date
+from datetime import date, time, datetime, timedelta
+from time import strftime
from operator import itemgetter
# We need a database to test against. If LOCAL_PyGreSQL.py exists we will
@@ -180,7 +181,7 @@
'abort', 'adapter',
'begin',
'cancel', 'clear', 'close', 'commit',
- 'db', 'dbname', 'dbtypes',
+ 'date_format', 'db', 'dbname', 'dbtypes',
'debug', 'decode_json', 'delete',
'encode_json', 'end', 'endcopy', 'error',
'escape_bytea', 'escape_identifier',
@@ -1505,18 +1506,15 @@
data = dict(item for item in data.items()
if item[0] in expect)
ts = expect.get('ts')
- if ts == 'current_timestamp':
- ts = expect['ts'] = data['ts']
- if len(ts) > 19:
- self.assertEqual(ts[19], '.')
- ts = ts[:19]
+ if ts:
+ if ts == 'current_timestamp':
+ ts = data['ts']
+ self.assertIsInstance(ts, datetime)
+ self.assertEqual(ts.strftime('%Y-%m-%d'),
+ strftime('%Y-%m-%d'))
else:
- self.assertEqual(len(ts), 19)
- self.assertTrue(ts[:4].isdigit())
- self.assertEqual(ts[4], '-')
- self.assertEqual(ts[10], ' ')
- self.assertTrue(ts[11:13].isdigit())
- self.assertEqual(ts[13], ':')
+ ts = datetime.strptime(ts, '%Y-%m-%d %H:%M:%S')
+ expect['ts'] = ts
self.assertEqual(data, expect)
data = query(
'select oid,* from "%s"' % table).dictresult()[0]
@@ -3523,6 +3521,175 @@
self.assertEqual(p.age, 61)
self.assertIsInstance(p.age, int)
+ def testDate(self):
+ query = self.db.query
+ for datestyle in ('ISO', 'Postgres, MDY', 'Postgres, DMY',
+ 'SQL, MDY', 'SQL, DMY', 'German'):
+ self.db.set_parameter('datestyle', datestyle)
+ d = date(2016, 3, 14)
+ q = "select '2016-03-14'::date"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, date)
+ self.assertEqual(r, d)
+ q = "select '10000-08-01'::date, '0099-01-08 BC'::date"
+ r = query(q).getresult()[0]
+ self.assertIsInstance(r[0], date)
+ self.assertIsInstance(r[1], date)
+ self.assertEqual(r[0], date.max)
+ self.assertEqual(r[1], date.min)
+ q = "select 'infinity'::date, '-infinity'::date"
+ r = query(q).getresult()[0]
+ self.assertIsInstance(r[0], date)
+ self.assertIsInstance(r[1], date)
+ self.assertEqual(r[0], date.max)
+ self.assertEqual(r[1], date.min)
+
+ def testTime(self):
+ query = self.db.query
+ d = time(15, 9, 26)
+ q = "select '15:09:26'::time"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, time)
+ self.assertEqual(r, d)
+ d = time(15, 9, 26, 535897)
+ q = "select '15:09:26.535897'::time"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, time)
+ self.assertEqual(r, d)
+
+ def testTimetz(self):
+ query = self.db.query
+ timezones = dict(CET=1, EET=2, EST=-5, UTC=0)
+ for timezone in sorted(timezones):
+ offset = timezones[timezone]
+ try:
+ tzinfo = datetime.strptime('%+03d00' % offset, '%z').tzinfo
+ except ValueError: # Python < 3.3
+ tzinfo = None
+ self.db.set_parameter('timezone', timezone)
+ d = time(15, 9, 26, tzinfo=tzinfo)
+ q = "select '15:09:26'::timetz"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, time)
+ self.assertEqual(r, d)
+ d = time(15, 9, 26, 535897, tzinfo)
+ q = "select '15:09:26.535897'::timetz"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, time)
+ self.assertEqual(r, d)
+
+ def testTimestamp(self):
+ query = self.db.query
+ for datestyle in ('ISO', 'Postgres, MDY', 'Postgres, DMY',
+ 'SQL, MDY', 'SQL, DMY', 'German'):
+ self.db.set_parameter('datestyle', datestyle)
+ d = datetime(2016, 3, 14)
+ q = "select '2016-03-14'::timestamp"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, datetime)
+ self.assertEqual(r, d)
+ d = datetime(2016, 3, 14, 15, 9, 26)
+ q = "select '2016-03-14 15:09:26'::timestamp"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, datetime)
+ self.assertEqual(r, d)
+ d = datetime(2016, 3, 14, 15, 9, 26, 535897)
+ q = "select '2016-03-14 15:09:26.535897'::timestamp"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, datetime)
+ self.assertEqual(r, d)
+ q = ("select '10000-08-01 AD'::timestamp,"
+ " '0099-01-08 BC'::timestamp")
+ r = query(q).getresult()[0]
+ self.assertIsInstance(r[0], datetime)
+ self.assertIsInstance(r[1], datetime)
+ self.assertEqual(r[0], datetime.max)
+ self.assertEqual(r[1], datetime.min)
+ q = "select 'infinity'::timestamp, '-infinity'::timestamp"
+ r = query(q).getresult()[0]
+ self.assertIsInstance(r[0], datetime)
+ self.assertIsInstance(r[1], datetime)
+ self.assertEqual(r[0], datetime.max)
+ self.assertEqual(r[1], datetime.min)
+
+ def testTimestamptz(self):
+ query = self.db.query
+ timezones = dict(CET=1, EET=2, EST=-5, UTC=0)
+ for timezone in sorted(timezones):
+ offset = timezones[timezone]
+ try:
+ tzinfo = datetime.strptime('%+03d00' % offset, '%z').tzinfo
+ except ValueError: # Python < 3.3
+ tzinfo = None
+ self.db.set_parameter('timezone', timezone)
+ for datestyle in ('ISO', 'Postgres, MDY', 'Postgres, DMY',
+ 'SQL, MDY', 'SQL, DMY', 'German'):
+ self.db.set_parameter('datestyle', datestyle)
+ d = datetime(2016, 3, 14, tzinfo=tzinfo)
+ q = "select '2016-03-14'::timestamptz"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, datetime)
+ self.assertEqual(r, d)
+ d = datetime(2016, 3, 14, 15, 9, 26, tzinfo=tzinfo)
+ q = "select '2016-03-14 15:09:26'::timestamptz"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, datetime)
+ self.assertEqual(r, d)
+ d = datetime(2016, 3, 14, 15, 9, 26, 535897, tzinfo)
+ q = "select '2016-03-14 15:09:26.535897'::timestamptz"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, datetime)
+ self.assertEqual(r, d)
+ q = ("select '10000-08-01 AD'::timestamptz,"
+ " '0099-01-08 BC'::timestamptz")
+ r = query(q).getresult()[0]
+ self.assertIsInstance(r[0], datetime)
+ self.assertIsInstance(r[1], datetime)
+ self.assertEqual(r[0], datetime.max)
+ self.assertEqual(r[1], datetime.min)
+ q = "select 'infinity'::timestamptz, '-infinity'::timestamptz"
+ r = query(q).getresult()[0]
+ self.assertIsInstance(r[0], datetime)
+ self.assertIsInstance(r[1], datetime)
+ self.assertEqual(r[0], datetime.max)
+ self.assertEqual(r[1], datetime.min)
+
+ def testInterval(self):
+ query = self.db.query
+ for intervalstyle in (
+ 'sql_standard', 'postgres', 'postgres_verbose', 'iso_8601'):
+ self.db.set_parameter('intervalstyle', intervalstyle)
+ q = "select '2016-03-14'::timestamp - '2016-03-11'::timestamp"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, timedelta)
+ d = timedelta(3)
+ self.assertEqual(r, d)
+ q = "select '2016-03-14'::timestamp - '2016-04-13'::timestamp"
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, timedelta)
+ d = timedelta(-30)
+ self.assertEqual(r, d)
+ q = ("select '2016-03-14 15:31:42.5678'::timestamp"
+ " - '2016-03-14 12:00:00'::timestamp")
+ r = query(q).getresult()[0][0]
+ self.assertIsInstance(r, timedelta)
+ d = timedelta(hours=3, minutes=31, seconds=42, microseconds=5678)
+ self.assertEqual(r, d)
+
+ def testDateAndTimeArrays(self):
+ q = "select ARRAY['2016-03-14'::date], ARRAY['15:09:26'::time]"
+ r = self.db.query(q).getresult()[0]
+ d = r[0]
+ self.assertIsInstance(d, list)
+ d = d[0]
+ self.assertIsInstance(d, date)
+ self.assertEqual(d, date(2016, 3, 14))
+ d = r[1]
+ self.assertIsInstance(d, list)
+ d = d[0]
+ self.assertIsInstance(d, time)
+ self.assertEqual(d, time(15, 9, 26))
+
def testDbTypesInfo(self):
dbtypes = self.db.dbtypes
self.assertIsInstance(dbtypes, dict)
Modified: trunk/tests/test_classic_functions.py
==============================================================================
--- trunk/tests/test_classic_functions.py Mon Feb 1 11:14:36 2016
(r813)
+++ trunk/tests/test_classic_functions.py Wed Feb 3 15:23:20 2016
(r814)
@@ -20,6 +20,8 @@
import pg # the module under test
+from datetime import timedelta
+
try:
long
except NameError: # Python >= 3.0
@@ -614,6 +616,179 @@
self.assertEqual(f(string, cast, b';'), expected)
+class TestCastInterval(unittest.TestCase):
+ """Test the interval typecast function."""
+
+ intervals = [
+ ((0, 0, 0, 1, 0, 0, 0),
+ ('1:00:00', '01:00:00', '@ 1 hour', 'PT1H')),
+ ((0, 0, 0, -1, 0, 0, 0),
+ ('-1:00:00', '-01:00:00', '@ -1 hour', 'PT-1H')),
+ ((0, 0, 0, 1, 0, 0, 0),
+ ('0-0 0 1:00:00', '0 years 0 mons 0 days 01:00:00',
+ '@ 0 years 0 mons 0 days 1 hour', 'P0Y0M0DT1H')),
+ ((0, 0, 0, -1, 0, 0, 0),
+ ('-0-0 -1:00:00', '0 years 0 mons 0 days -01:00:00',
+ '@ 0 years 0 mons 0 days -1 hour', 'P0Y0M0DT-1H')),
+ ((0, 0, 1, 0, 0, 0, 0),
+ ('1 0:00:00', '1 day', '@ 1 day', 'P1D')),
+ ((0, 0, -1, 0, 0, 0, 0),
+ ('-1 0:00:00', '-1 day', '@ -1 day', 'P-1D')),
+ ((0, 1, 0, 0, 0, 0, 0),
+ ('0-1', '1 mon', '@ 1 mon', 'P1M')),
+ ((1, 0, 0, 0, 0, 0, 0),
+ ('1-0', '1 year', '@ 1 year', 'P1Y')),
+ ((0, 0, 0, 2, 0, 0, 0),
+ ('2:00:00', '02:00:00', '@ 2 hours', 'PT2H')),
+ ((0, 0, 2, 0, 0, 0, 0),
+ ('2 0:00:00', '2 days', '@ 2 days', 'P2D')),
+ ((0, 2, 0, 0, 0, 0, 0),
+ ('0-2', '2 mons', '@ 2 mons', 'P2M')),
+ ((2, 0, 0, 0, 0, 0, 0),
+ ('2-0', '2 years', '@ 2 years', 'P2Y')),
+ ((0, 0, 0, -3, 0, 0, 0),
+ ('-3:00:00', '-03:00:00', '@ 3 hours ago', 'PT-3H')),
+ ((0, 0, -3, 0, 0, 0, 0),
+ ('-3 0:00:00', '-3 days', '@ 3 days ago', 'P-3D')),
+ ((0, -3, 0, 0, 0, 0, 0),
+ ('-0-3', '-3 mons', '@ 3 mons ago', 'P-3M')),
+ ((-3, 0, 0, 0, 0, 0, 0),
+ ('-3-0', '-3 years', '@ 3 years ago', 'P-3Y')),
+ ((0, 0, 0, 0, 1, 0, 0),
+ ('0:01:00', '00:01:00', '@ 1 min', 'PT1M')),
+ ((0, 0, 0, 0, 0, 1, 0),
+ ('0:00:01', '00:00:01', '@ 1 sec', 'PT1S')),
+ ((0, 0, 0, 0, 0, 0, 1),
+ ('0:00:00.000001', '00:00:00.000001',
+ '@ 0.000001 secs', 'PT0.000001S')),
+ ((0, 0, 0, 0, 2, 0, 0),
+ ('0:02:00', '00:02:00', '@ 2 mins', 'PT2M')),
+ ((0, 0, 0, 0, 0, 2, 0),
+ ('0:00:02', '00:00:02', '@ 2 secs', 'PT2S')),
+ ((0, 0, 0, 0, 0, 0, 2),
+ ('0:00:00.000002', '00:00:00.000002',
+ '@ 0.000002 secs', 'PT0.000002S')),
+ ((0, 0, 0, 0, -3, 0, 0),
+ ('-0:03:00', '-00:03:00', '@ 3 mins ago', 'PT-3M')),
+ ((0, 0, 0, 0, 0, -3, 0),
+ ('-0:00:03', '-00:00:03', '@ 3 secs ago', 'PT-3S')),
+ ((0, 0, 0, 0, 0, 0, -3),
+ ('-0:00:00.000003', '-00:00:00.000003',
+ '@ 0.000003 secs ago', 'PT-0.000003S')),
+ ((1, 2, 0, 0, 0, 0, 0),
+ ('1-2', '1 year 2 mons', '@ 1 year 2 mons', 'P1Y2M')),
+ ((0, 0, 3, 4, 5, 6, 0),
+ ('3 4:05:06', '3 days 04:05:06',
+ '@ 3 days 4 hours 5 mins 6 secs', 'P3DT4H5M6S')),
+ ((1, 2, 3, 4, 5, 6, 0),
+ ('+1-2 +3 +4:05:06', '1 year 2 mons 3 days 04:05:06',
+ '@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs',
+ 'P1Y2M3DT4H5M6S')),
+ ((1, 2, 3, -4, -5, -6, 0),
+ ('+1-2 +3 -4:05:06', '1 year 2 mons 3 days -04:05:06',
+ '@ 1 year 2 mons 3 days -4 hours -5 mins -6 secs',
+ 'P1Y2M3DT-4H-5M-6S')),
+ ((1, 2, 3, -4, 5, 6, 0),
+ ('+1-2 +3 -3:54:54', '1 year 2 mons 3 days -03:54:54',
+ '@ 1 year 2 mons 3 days -3 hours -54 mins -54 secs',
+ 'P1Y2M3DT-3H-54M-54S')),
+ ((-1, -2, 3, -4, -5, -6, 0),
+ ('-1-2 +3 -4:05:06', '-1 years -2 mons +3 days -04:05:06',
+ '@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago',
+ 'P-1Y-2M3DT-4H-5M-6S')),
+ ((1, 2, -3, 4, 5, 6, 0),
+ ('+1-2 -3 +4:05:06', '1 year 2 mons -3 days +04:05:06',
+ '@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs',
+ 'P1Y2M-3DT4H5M6S')),
+ ((0, 0, 0, 1, 30, 0, 0),
+ ('1:30:00', '01:30:00', '@ 1 hour 30 mins', 'PT1H30M')),
+ ((0, 0, 0, 3, 15, 45, 123456),
+ ('3:15:45.123456', '03:15:45.123456',
+ '@ 3 hours 15 mins 45.123456 secs', 'PT3H15M45.123456S')),
+ ((0, 0, 0, 3, 15, -5, 123),
+ ('3:14:55.000123', '03:14:55.000123',
+ '@ 3 hours 14 mins 55.000123 secs', 'PT3H14M55.000123S')),
+ ((0, 0, 0, 3, -5, 15, -12345),
+ ('2:55:14.987655', '02:55:14.987655',
+ '@ 2 hours 55 mins 14.987655 secs', 'PT2H55M14.987655S')),
+ ((0, 0, 0, 2, -1, 0, 0),
+ ('1:59:00', '01:59:00', '@ 1 hour 59 mins', 'PT1H59M')),
+ ((0, 0, 0, -1, 2, 0, 0),
+ ('-0:58:00', '-00:58:00', '@ 58 mins ago', 'PT-58M')),
+ ((1, 11, 0, 0, 0, 0, 0),
+ ('1-11', '1 year 11 mons', '@ 1 year 11 mons', 'P1Y11M')),
+ ((0, -10, 0, 0, 0, 0, 0),
+ ('-0-10', '-10 mons', '@ 10 mons ago', 'P-10M')),
+ ((0, 0, 2, -1, 0, 0, 0),
+ ('+0-0 +2 -1:00:00', '2 days -01:00:00',
+ '@ 2 days -1 hours', 'P2DT-1H')),
+ ((0, 0, -1, 2, 0, 0, 0),
+ ('+0-0 -1 +2:00:00', '-1 days +02:00:00',
+ '@ 1 day -2 hours ago', 'P-1DT2H')),
+ ((0, 0, 1, 0, 0, 0, 1),
+ ('1 0:00:00.000001', '1 day 00:00:00.000001',
+ '@ 1 day 0.000001 secs', 'P1DT0.000001S')),
+ ((0, 0, 1, 0, 0, 1, 0),
+ ('1 0:00:01', '1 day 00:00:01', '@ 1 day 1 sec', 'P1DT1S')),
+ ((0, 0, 1, 0, 1, 0, 0),
+ ('1 0:01:00', '1 day 00:01:00', '@ 1 day 1 min', 'P1DT1M')),
+ ((0, 0, 0, 0, 1, 0, -1),
+ ('0:00:59.999999', '00:00:59.999999',
+ '@ 59.999999 secs', 'PT59.999999S')),
+ ((0, 0, 0, 0, -1, 0, 1),
+ ('-0:00:59.999999', '-00:00:59.999999',
+ '@ 59.999999 secs ago', 'PT-59.999999S')),
+ ((0, 0, 0, 0, -1, 1, 1),
+ ('-0:00:58.999999', '-00:00:58.999999',
+ '@ 58.999999 secs ago', 'PT-58.999999S')),
+ ((0, 0, 42, 0, 0, 0, 0),
+ ('42 0:00:00', '42 days', '@ 42 days', 'P42D')),
+ ((0, 0, -7, 0, 0, 0, 0),
+ ('-7 0:00:00', '-7 days', '@ 7 days ago', 'P-7D')),
+ ((1, 1, 1, 1, 1, 0, 0),
+ ('+1-1 +1 +1:01:00', '1 year 1 mon 1 day 01:01:00',
+ '@ 1 year 1 mon 1 day 1 hour 1 min', 'P1Y1M1DT1H1M')),
+ ((0, -11, -1, -1, 1, 0, 0),
+ ('-0-11 -1 -0:59:00', '-11 mons -1 days -00:59:00',
+ '@ 11 mons 1 day 59 mins ago', 'P-11M-1DT-59M')),
+ ((-1, -1, -1, -1, -1, 0, 0),
+ ('-1-1 -1 -1:01:00', '-1 years -1 mons -1 days -01:01:00',
+ '@ 1 year 1 mon 1 day 1 hour 1 min ago', 'P-1Y-1M-1DT-1H-1M')),
+ ((-1, 0, -3, 1, 0, 0, 0),
+ ('-1-0 -3 +1:00:00', '-1 years -3 days +01:00:00',
+ '@ 1 year 3 days -1 hours ago', 'P-1Y-3DT1H')),
+ ((1, 0, 0, 0, 0, 0, 1),
+ ('+1-0 +0 +0:00:00.000001', '1 year 00:00:00.000001',
+ '@ 1 year 0.000001 secs', 'P1YT0.000001S')),
+ ((1, 0, 0, 0, 0, 0, -1),
+ ('+1-0 +0 -0:00:00.000001', '1 year -00:00:00.000001',
+ '@ 1 year -0.000001 secs', 'P1YT-0.000001S')),
+ ((1, 2, 3, 4, 5, 6, 7),
+ ('+1-2 +3 +4:05:06.000007',
+ '1 year 2 mons 3 days 04:05:06.000007',
+ '@ 1 year 2 mons 3 days 4 hours 5 mins 6.000007 secs',
+ 'P1Y2M3DT4H5M6.000007S')),
+ ((0, 10, 3, -4, 5, -6, 7),
+ ('+0-10 +3 -3:55:05.999993', '10 mons 3 days -03:55:05.999993',
+ '@ 10 mons 3 days -3 hours -55 mins -5.999993 secs',
+ 'P10M3DT-3H-55M-5.999993S')),
+ ((0, -10, -3, 4, -5, 6, -7),
+ ('-0-10 -3 +3:55:05.999993',
+ '-10 mons -3 days +03:55:05.999993',
+ '@ 10 mons 3 days -3 hours -55 mins -5.999993 secs ago',
+ 'P-10M-3DT3H55M5.999993S'))]
+
+ def testCastInterval(self):
+ for result, values in self.intervals:
+ f = pg.cast_interval
+ years, mons, days, hours, mins, secs, usecs = result
+ days += 365 * years + 30 * mons
+ interval = timedelta(days=days, hours=hours, minutes=mins,
+ seconds=secs, microseconds=usecs)
+ for value in values:
+ self.assertEqual(f(value), interval)
+
+
class TestEscapeFunctions(unittest.TestCase):
"""Test pg escape and unescape functions.
@@ -676,6 +851,29 @@
"""
+ def testGetDatestyle(self):
+ self.assertIsNone(pg.get_datestyle())
+
+ def testGetDatestyle(self):
+ datestyle = pg.get_datestyle()
+ try:
+ pg.set_datestyle('ISO, YMD')
+ self.assertEqual(pg.get_datestyle(), 'ISO, YMD')
+ pg.set_datestyle('Postgres, MDY')
+ self.assertEqual(pg.get_datestyle(), 'Postgres, MDY')
+ pg.set_datestyle('Postgres, DMY')
+ self.assertEqual(pg.get_datestyle(), 'Postgres, DMY')
+ pg.set_datestyle('SQL, MDY')
+ self.assertEqual(pg.get_datestyle(), 'SQL, MDY')
+ pg.set_datestyle('SQL, DMY')
+ self.assertEqual(pg.get_datestyle(), 'SQL, DMY')
+ pg.set_datestyle('German, DMY')
+ self.assertEqual(pg.get_datestyle(), 'German, DMY')
+ pg.set_datestyle(None)
+ self.assertIsNone(pg.get_datestyle())
+ finally:
+ pg.set_datestyle(datestyle)
+
def testGetDecimalPoint(self):
r = pg.get_decimal_point()
self.assertIsInstance(r, str)
Modified: trunk/tests/test_dbapi20.py
==============================================================================
--- trunk/tests/test_dbapi20.py Mon Feb 1 11:14:36 2016 (r813)
+++ trunk/tests/test_dbapi20.py Wed Feb 3 15:23:20 2016 (r814)
@@ -28,7 +28,12 @@
except ImportError:
pass
-from datetime import datetime
+from datetime import date, time, datetime, timedelta
+
+try:
+ from datetime import timezone
+except ImportError: # Python < 3.2
+ timezone = None
try:
long
@@ -401,13 +406,14 @@
Decimal = pgdb.decimal_type()
values = ('test', pgdb.Binary(b'\xff\x52\xb2'),
True, 5, 6, 5.7, Decimal('234.234234'), Decimal('75.45'),
- '2011-07-17', '15:47:42', '2008-10-20 15:25:35', '15:31:05',
- 7897234)
+ pgdb.Date(2011, 7, 17), pgdb.Time(15, 47, 42),
+ pgdb.Timestamp(2008, 10, 20, 15, 25, 35),
+ pgdb.Interval(15, 31, 5), 7897234)
table = self.table_prefix + 'booze'
con = self._connect()
try:
cur = con.cursor()
- cur.execute("set datestyle to 'iso'")
+ cur.execute("set datestyle to iso")
cur.execute("create table %s ("
"stringtest varchar,"
"binarytest bytea,"
@@ -443,10 +449,10 @@
self.assertIsInstance(row0[5], float)
self.assertIsInstance(row0[6], Decimal)
self.assertIsInstance(row0[7], Decimal)
- self.assertIsInstance(row0[8], str)
- self.assertIsInstance(row0[9], str)
- self.assertIsInstance(row0[10], str)
- self.assertIsInstance(row0[11], str)
+ self.assertIsInstance(row0[8], date)
+ self.assertIsInstance(row0[9], time)
+ self.assertIsInstance(row0[10], datetime)
+ self.assertIsInstance(row0[11], timedelta)
finally:
con.close()
@@ -503,29 +509,59 @@
self.assertEqual(inval, outval)
def test_datetime(self):
- values = ['2011-07-17 15:47:42', datetime(2016, 1, 20, 20, 15, 51)]
+ dt = datetime(2011, 7, 17, 15, 47, 42, 317509)
+ td = dt - datetime(1970, 1, 1)
table = self.table_prefix + 'booze'
con = self._connect()
try:
cur = con.cursor()
- cur.execute("set datestyle to 'iso'")
- cur.execute(
- "create table %s (n smallint, ts timestamp)" % table)
- params = enumerate(values)
- cur.executemany("insert into %s values (%%d,%%s)" % table, params)
- cur.execute("select ts from %s order by n" % table)
- rows = cur.fetchall()
- self.assertEqual(cur.description[0].type_code, pgdb.DATETIME)
- self.assertNotEqual(cur.description[0].type_code, pgdb.ARRAY)
- self.assertNotEqual(cur.description[0].type_code, pgdb.RECORD)
+ cur.execute("set datestyle to iso")
+ cur.execute("set datestyle to iso")
+ cur.execute("create table %s ("
+ "d date, t time, ts timestamp,"
+ "tz timetz, tsz timestamptz, i interval)" % table)
+ for n in range(3):
+ values = [dt.date(), dt.time(), dt,
+ dt.time(), dt, td]
+ if timezone:
+ values[3] = values[3].replace(tzinfo=timezone.utc)
+ values[4] = values[4].replace(tzinfo=timezone.utc)
+ if n == 0: # input as objects
+ params = values
+ if n == 1: # input as text
+ params = [v.isoformat() for v in values[:5]] # as text
+ params.append('%d days %d seconds %d microseconds '
+ % (td.days, td.seconds, td.microseconds))
+ elif n == 2: # input using type helpers
+ d = (dt.year, dt.month, dt.day)
+ t = (dt.hour, dt.minute, dt.second, dt.microsecond)
+ i = (td.days, 0, 0, td.seconds, td.microseconds)
+ params = [pgdb.Date(*d), pgdb.Time(*t),
+ pgdb.Timestamp(*(d + t)), pgdb.Time(*t),
+ pgdb.Timestamp(*(d + t)), pgdb.Interval(*i)]
+ cur.execute("insert into %s"
+ " values (%%s,%%s,%%s,%%s,%%s,%%s)" % table, params)
+ for datestyle in ('iso', 'postgres, mdy', 'postgres, dmy',
+ 'sql, mdy', 'sql, dmy', 'german'):
+ cur.execute("set datestyle to %s" % datestyle)
+ cur.execute("select * from %s" % table)
+ d = cur.description
+ for i in range(6):
+ self.assertEqual(d[i].type_code, pgdb.DATETIME)
+ self.assertNotEqual(d[i].type_code, pgdb.STRING)
+ self.assertNotEqual(d[i].type_code, pgdb.ARRAY)
+ self.assertNotEqual(d[i].type_code, pgdb.RECORD)
+ self.assertEqual(d[0].type_code, pgdb.DATE)
+ self.assertEqual(d[1].type_code, pgdb.TIME)
+ self.assertEqual(d[2].type_code, pgdb.TIMESTAMP)
+ self.assertEqual(d[3].type_code, pgdb.TIME)
+ self.assertEqual(d[4].type_code, pgdb.TIMESTAMP)
+ self.assertEqual(d[5].type_code, pgdb.INTERVAL)
+ row = cur.fetchone()
+ self.assertEqual(row, tuple(values))
+ cur.execute("delete from %s" % table)
finally:
con.close()
- self.assertEqual(len(rows), len(values))
- rows = [row[0] for row in rows]
- for inval, outval in zip(values, rows):
- if isinstance(inval, datetime):
- inval = inval.strftime('%Y-%m-%d %H:%M:%S')
- self.assertEqual(inval, outval)
def test_insert_array(self):
values = [(None, None), ([], []), ([None], [[None], ['null']]),
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
