Changeset 814 for trunk


Ignore:
Timestamp:
Feb 3, 2016, 3:23:20 PM (4 years ago)
Author:
cito
Message:

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.

Location:
trunk
Files:
19 edited

Legend:

Unmodified
Added
Removed
  • trunk/docs/contents/changelog.rst

    r809 r814  
    4040      a new fast built-in parser to achieve this. Anonymous composite types are
    4141      also supported, but yield only an ordinary tuple containing text strings.
     42    - A new type helper Interval() has been added.
    4243- Changes in the classic PyGreSQL module (pg):
    4344    - The classic interface got two new methods get_as_list() and get_as_dict()
     
    9394    - The methods for adapting and typecasting values pertaining to PostgreSQL
    9495      types have been refactored and swapped out to separate classes.
     96    - A new type helper Bytea() has been added.
    9597- Changes concerning both modules:
    9698    - The modules now provide get_typecast() and set_typecast() methods
     
    100102      See the documentation on details about the type cache and the typecast
    101103      mechanisms provided by PyGreSQL.
     104    - Dates, times, timestamps and time intervals are now returned as the
     105      corresponding Python objects from the datetime module of the standard
     106      library.  In earlier versions of PyGreSQL they had been returned as
     107      strings.  You can restore the old behavior by deactivating the respective
     108      typecast functions, e.g. set_typecast('date', None).
    102109    - PyGreSQL now supports the JSON and JSONB data types, converting such
    103110      columns automatically to and from Python objects. If you want to insert
    104111      Python objects as JSON data using DB-API 2, you should wrap them in the
    105112      new Json() type constructor as a hint to PyGreSQL.
    106     - New type helpers Literal(), Json() and Bytea() have been added.
     113    - The new type helpers Literal() and Json() have been added.
    107114    - Fast parsers cast_array() and cast_record() for the input and output
    108115      syntax for PostgreSQL arrays and composite types have been added to the
  • trunk/docs/contents/pg/adaptation.rst

    r807 r814  
    127127Lists are adapted as PostgreSQL arrays::
    128128
    129    >>> params = dict(array=[[1, 2],[3, 4]])
    130    >>> db.query_formatted("SELECT %(array)s::int[]", params).getresult()[0][0]
    131    [[1, 2], [3, 4]]
     129    >>> params = dict(array=[[1, 2],[3, 4]])
     130    >>> db.query_formatted("SELECT %(array)s::int[]", params).getresult()[0][0]
     131    [[1, 2], [3, 4]]
    132132
    133133Note that again we only need to cast the array parameter or use inline
     
    166166inserted into the database and then read back as follows::
    167167
    168    >>> db.query_formatted("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
    169    ...     dict(item=inventory_item('fuzzy dice', 42, 1.99), count=1000))
    170    >>> db.query("SELECT * FROM on_hand").getresult()[0][0]
    171    Row(item=inventory_item(name='fuzzy dice', supplier_id=42,
    172            price=Decimal('1.99')), count=1000)
     168    >>> db.query_formatted("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
     169    ...     dict(item=inventory_item('fuzzy dice', 42, 1.99), count=1000))
     170    >>> db.query("SELECT * FROM on_hand").getresult()[0][0]
     171    Row(item=inventory_item(name='fuzzy dice', supplier_id=42,
     172            price=Decimal('1.99')), count=1000)
    173173
    174174The :meth:`DB.insert` method provides a simpler way to achieve the same::
  • trunk/docs/contents/pg/connection.rst

    r801 r814  
    135135.. method:: Connection.parameter(name)
    136136
    137     Looks up a current parameter setting of the server
     137    Look up a current parameter setting of the server
    138138
    139139    :param str name: the name of the parameter to look up
     
    158158.. versionadded:: 4.0
    159159
    160 fileno -- returns the socket used to connect to the database
    161 ------------------------------------------------------------
     160date_format -- get the currently used date format
     161-------------------------------------------------
     162
     163.. method:: Connection.date_format()
     164
     165    Look up the date format currently being used by the database
     166
     167    :returns: the current date format
     168    :rtype: str
     169    :raises TypeError: too many (any) arguments
     170    :raises TypeError: invalid connection
     171
     172This method returns the current date format used by the server.  Note that
     173it is cheap to call this method, since there is no database query involved
     174and the setting is also cached internally.  You will need the date format
     175when you want to manually typecast dates and timestamps coming from the
     176database instead of using the built-in typecast functions.  The date format
     177returned by this method can be directly used with date formatting functions
     178such as :meth:`datetime.strptime`.  It is derived from the current setting
     179of the database parameter ``DateStyle``.
     180
     181.. versionadded:: 5.0
     182
     183fileno -- get the socket used to connect to the database
     184--------------------------------------------------------
    162185
    163186.. method:: Connection.fileno()
    164187
    165    Return the socket used to connect to the database
    166 
    167    :returns: the socket id of the database connection
    168    :rtype: int
    169    :raises TypeError: too many (any) arguments
    170    :raises TypeError: invalid connection
     188    Get the socket used to connect to the database
     189
     190    :returns: the socket id of the database connection
     191    :rtype: int
     192    :raises TypeError: too many (any) arguments
     193    :raises TypeError: invalid connection
    171194
    172195This method returns the underlying socket id used to connect
     
    379402.. attribute:: Connection.host
    380403
    381    the host name of the server (str)
     404    the host name of the server (str)
    382405
    383406.. attribute:: Connection.port
    384407
    385    the port of the server (int)
     408    the port of the server (int)
    386409
    387410.. attribute:: Connection.db
    388411
    389    the selected database (str)
     412    the selected database (str)
    390413
    391414.. attribute:: Connection.options
    392415
    393    the connection options (str)
     416    the connection options (str)
    394417
    395418.. attribute:: Connection.user
     
    399422.. attribute:: Connection.protocol_version
    400423
    401    the frontend/backend protocol being used (int)
     424    the frontend/backend protocol being used (int)
    402425
    403426.. versionadded:: 4.0
     
    405428.. attribute:: Connection.server_version
    406429
    407    the backend version (int, e.g. 90305 for 9.3.5)
     430    the backend version (int, e.g. 90305 for 9.3.5)
    408431
    409432.. versionadded:: 4.0
     
    411434.. attribute:: Connection.status
    412435
    413    the status of the connection (int: 1 = OK, 0 = bad)
     436    the status of the connection (int: 1 = OK, 0 = bad)
    414437
    415438.. attribute:: Connection.error
    416439
    417    the last warning/error message from the server (str)
     440    the last warning/error message from the server (str)
  • trunk/docs/contents/pg/db_types.rst

    r798 r814  
    5858    :type typ: str or int
    5959
     60The typecast function must take one string object as argument and return a
     61Python object into which the PostgreSQL type shall be casted.  If the function
     62takes another parameter named *connection*, then the current database
     63connection will also be passed to the typecast function.  This may sometimes
     64be necessary to look up certain database settings.
     65
    6066.. method:: DbTypes.reset_typecast([typ])
    6167
  • trunk/docs/contents/pg/large_objects.rst

    r710 r814  
    164164.. attribute:: LargeObject.oid
    165165
    166    the OID associated with the large object (int)
     166    the OID associated with the large object (int)
    167167
    168168.. attribute:: LargeObject.pgcnx
    169169
    170    the :class:`Connection` object associated with the large object
     170    the :class:`Connection` object associated with the large object
    171171
    172172.. attribute:: LargeObject.error
    173173
    174    the last warning/error message of the connection (str)
     174    the last warning/error message of the connection (str)
    175175
    176176.. warning::
  • trunk/docs/contents/pg/module.rst

    r808 r814  
    253253.. note::
    254254
    255    It is especially important to do proper escaping when
    256    handling strings that were received from an untrustworthy source.
    257    Otherwise there is a security risk: you are vulnerable to "SQL injection"
    258    attacks wherein unwanted SQL commands are fed to your database.
     255    It is especially important to do proper escaping when
     256    handling strings that were received from an untrustworthy source.
     257    Otherwise there is a security risk: you are vulnerable to "SQL injection"
     258    attacks wherein unwanted SQL commands are fed to your database.
    259259
    260260Example::
     
    430430
    431431.. versionchanged:: 5.0
    432    Boolean values had been returned as string by default in earlier versions.
     432    Boolean values had been returned as string by default in earlier versions.
    433433
    434434get/set_array -- whether arrays are returned as list objects
     
    463463
    464464.. versionchanged:: 5.0
    465    Arrays had been always returned as text strings only in earlier versions.
     465    Arrays had been always returned as text strings only in earlier versions.
    466466
    467467get/set_bytea_escaped -- whether bytea data is returned escaped
     
    496496
    497497.. versionchanged:: 5.0
    498    Bytea data had been returned in escaped form by default in earlier versions.
     498    Bytea data had been returned in escaped form by default in earlier versions.
    499499
    500500get/set_jsondecode -- decoding JSON format
     
    523523
    524524.. versionchanged:: 5.0
    525    JSON data had been always returned as text strings in earlier versions.
     525    JSON data had been always returned as text strings in earlier versions.
    526526
    527527get/set_cast_hook -- fallback typecast function
     
    548548.. versionadded:: 5.0
    549549
     550get/set_datestyle -- assume a fixed date style
     551----------------------------------------------
     552
     553.. function:: get_datestyle()
     554
     555    Get the assumed date style for typecasting
     556
     557This returns the PostgreSQL date style that is silently assumed when
     558typecasting dates or *None* if no fixed date style is assumed, in which case
     559the date style is requested from the database when necessary (this is the
     560default).  Note that this method will *not* get the date style that is
     561currently set in the session or in the database.  You can get the current
     562setting with the methods :meth:`DB.get_parameter` and
     563:meth:`Connection.parameter`.  You can also get the date format corresponding
     564to the current date style by calling :meth:`Connection.date_format`.
     565
     566.. versionadded:: 5.0
     567
     568.. function:: set_datestyle(datestyle)
     569
     570    Set a fixed date style that shall be assumed when typecasting
     571
     572    :param str datestyle: the date style that shall be assumed,
     573      or *None* if no fixed dat style shall be assumed
     574
     575PyGreSQL is able to automatically pick up the right date style for typecasting
     576date values from the database, even if you change it for the current session
     577with a ``SET DateStyle`` command.  This is happens very effectively without
     578an additional database request being involved.  If you still want to have
     579PyGreSQL always assume a fixed date style instead, then you can set one with
     580this function.  Note that calling this function will *not* alter the date
     581style of the database or the current session.  You can do that by calling
     582the method :meth:`DB.set_parameter` instead.
     583
     584.. versionadded:: 5.0
     585
    550586get/set_typecast -- custom typecasting
    551587--------------------------------------
     
    578614    :param cast: the typecast function to be set for the specified type(s)
    579615    :type typ: str or int
     616
     617The typecast function must take one string object as argument and return a
     618Python object into which the PostgreSQL type shall be casted.  If the function
     619takes another parameter named *connection*, then the current database
     620connection will also be passed to the typecast function.  This may sometimes
     621be necessary to look up certain database settings.
    580622
    581623.. versionadded:: 5.0
     
    681723    A wrapper for holding a bytea value
    682724
     725.. versionadded:: 5.0
     726
    683727.. function:: Json(obj)
    684728
    685729    A wrapper for holding an object serializable to JSON
    686730
     731.. versionadded:: 5.0
     732
    687733.. function:: Literal(sql)
    688734
    689735    A wrapper for holding a literal SQL string
     736
     737.. versionadded:: 5.0
    690738
    691739Module constants
  • trunk/docs/contents/pgdb/adaptation.rst

    r807 r814  
    8585Lists are adapted as PostgreSQL arrays::
    8686
    87    >>> params = dict(array=[[1, 2],[3, 4]])
    88    >>> cur.execute("SELECT %(array)s", params).fetchone()[0]
    89    [[1, 2], [3, 4]]
     87    >>> params = dict(array=[[1, 2],[3, 4]])
     88    >>> cur.execute("SELECT %(array)s", params).fetchone()[0]
     89    [[1, 2], [3, 4]]
    9090
    9191Note that the query gives the value back as Python lists again.  This
     
    152152inserted into the database and then read back as follows::
    153153
    154    >>> cur.execute("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
    155    ...     dict(item=inventory_item('fuzzy dice', 42, 1.99), count=1000))
    156    >>> cur.execute("SELECT * FROM on_hand").fetchone()
    157    Row(item=inventory_item(name='fuzzy dice', supplier_id=42,
    158            price=Decimal('1.99')), count=1000)
     154    >>> cur.execute("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
     155    ...     dict(item=inventory_item('fuzzy dice', 42, 1.99), count=1000))
     156    >>> cur.execute("SELECT * FROM on_hand").fetchone()
     157    Row(item=inventory_item(name='fuzzy dice', supplier_id=42,
     158            price=Decimal('1.99')), count=1000)
    159159
    160160However, we may not want to use named tuples, but custom Python classes
     
    175175will get an error::
    176176
    177    >>> cur.execute("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
    178    ...     dict(item=InventoryItem('fuzzy dice', 42, 1.99), count=1000))
    179    InterfaceError: Do not know how to adapt type <class 'InventoryItem'>
     177    >>> cur.execute("INSERT INTO on_hand VALUES (%(item)s, %(count)s)",
     178    ...     dict(item=InventoryItem('fuzzy dice', 42, 1.99), count=1000))
     179    InterfaceError: Do not know how to adapt type <class 'InventoryItem'>
    180180
    181181While PyGreSQL knows how to adapt tuples, it does not know what to make out
     
    234234make this effective, using the following command::
    235235
    236    >>> con.type_cache.reset_typecast()
     236    >>> con.type_cache.reset_typecast()
    237237
    238238The :class:`TypeCache` of the connection can also be used to change typecast
  • trunk/docs/contents/pgdb/connection.rst

    r797 r814  
    6868.. note::
    6969
    70    The following attributes are not part of the DB-API 2 standard.
     70    The following attributes are not part of the DB-API 2 standard.
    7171
    7272.. attribute:: Connection.cursor_type
  • trunk/docs/contents/pgdb/cursor.rst

    r786 r814  
    241241.. note::
    242242
    243    The following methods and attributes are not part of the DB-API 2 standard.
     243    The following methods and attributes are not part of the DB-API 2 standard.
    244244
    245245.. method:: Cursor.copy_from(stream, table, [format], [sep], [null], [size], [columns])
  • trunk/docs/contents/pgdb/module.rst

    r798 r814  
    7373    :type typ: str or int
    7474
     75The typecast function must take one string object as argument and return a
     76Python object into which the PostgreSQL type shall be casted.  If the function
     77takes another parameter named *connection*, then the current database
     78connection will also be passed to the typecast function.  This may sometimes
     79be necessary to look up certain database settings.
     80
    7581.. versionadded:: 5.0
    7682
     
    111117.. data:: paramstyle
    112118
    113    The string constant ``pyformat``, stating that parameters should be passed
    114    using Python extended format codes, e.g. ``" ... WHERE name=%(name)s"``.
     119    The string constant ``pyformat``, stating that parameters should be passed
     120    using Python extended format codes, e.g. ``" ... WHERE name=%(name)s"``.
    115121
    116122Errors raised by this module
  • trunk/docs/contents/pgdb/typecache.rst

    r798 r814  
    5959    :type typ: str or int
    6060
     61The typecast function must take one string object as argument and return a
     62Python object into which the PostgreSQL type shall be casted.  If the function
     63takes another parameter named *connection*, then the current database
     64connection will also be passed to the typecast function.  This may sometimes
     65be necessary to look up certain database settings.
     66
    6167.. method:: TypeCache.reset_typecast([typ])
    6268
  • trunk/docs/contents/pgdb/types.rst

    r799 r814  
    5555specific data types:
    5656
     57.. function:: Interval(days, hours=0, minutes=0, seconds=0, microseconds=0)
     58
     59    Construct an object holding a time interval value
     60
     61.. versionadded:: 5.0
     62
    5763.. function:: Bytea(bytes)
    5864
    5965    Construct an object capable of holding a bytea value
     66
     67.. versionadded:: 5.0
    6068
    6169.. function:: Json(obj, [encode])
     
    6977
    7078    Construct a wrapper for holding a literal SQL string
     79
     80.. versionadded:: 5.0
    7181
    7282Example for using a type constructor::
     
    169179    Used to describe ``json`` and ``jsonb`` columns
    170180
     181.. versionadded:: 5.0
     182
    171183.. object:: ARRAY
    172184
    173185    Used to describe columns containing PostgreSQL arrays
    174186
     187.. versionadded:: 5.0
     188
    175189.. object:: RECORD
    176190
    177191    Used to describe columns containing PostgreSQL records
     192
     193.. versionadded:: 5.0
    178194
    179195Example for using some type objects::
  • trunk/pg.py

    r799 r814  
    5252except NameError:  # Python >= 3.0
    5353    basestring = (str, bytes)
     54
     55
     56# Auxiliary classes and functions that are independent from a DB connection:
    5457
    5558try:
     
    138141            raise TypeError('This object is read-only')
    139142
    140 
    141 # Auxiliary classes and functions that are independent from a DB connection:
     143try:
     144    from inspect import signature
     145except ImportError:  # Python < 3.3
     146    from inspect import getargspec
     147
     148    get_args = lambda func: getargspec(func).args
     149else:
     150    get_args = lambda func: list(signature(func).parameters)
     151
     152try:
     153    if datetime.strptime('+0100', '%z') is None:
     154        raise ValueError
     155except ValueError:  # Python < 3.2
     156    timezones = None
     157else:
     158    # time zones used in Postgres timestamptz output
     159    timezones = dict(CET='+0100', EET='+0200', EST='-0500',
     160        GMT='+0000', HST='-1000', MET='+0100', MST='-0700',
     161        UCT='+0000', UTC='+0000', WET='+0000')
     162
    142163
    143164def _oid_key(table):
     
    588609
    589610
     611def cast_date(value, connection):
     612    """Cast a date value."""
     613    # The output format depends on the server setting DateStyle.  The default
     614    # setting ISO and the setting for German are actually unambiguous.  The
     615    # order of days and months in the other two settings is however ambiguous,
     616    # so at least here we need to consult the setting to properly parse values.
     617    if value == '-infinity':
     618        return date.min
     619    if value == 'infinity':
     620        return date.max
     621    value = value.split()
     622    if value[-1] == 'BC':
     623        return date.min
     624    value = value[0]
     625    if len(value) > 10:
     626        return date.max
     627    fmt = connection.date_format()
     628    return datetime.strptime(value, fmt).date()
     629
     630
     631def cast_time(value):
     632    """Cast a time value."""
     633    fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
     634    return datetime.strptime(value, fmt).time()
     635
     636
     637_re_timezone = regex('(.*)([+-].*)')
     638
     639
     640def cast_timetz(value):
     641    """Cast a timetz value."""
     642    tz = _re_timezone.match(value)
     643    if tz:
     644        value, tz = tz.groups()
     645    else:
     646        tz = '+0000'
     647    fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
     648    if timezones:
     649        if tz.startswith(('+', '-')):
     650            if len(tz) < 5:
     651                tz += '00'
     652            else:
     653                tz = tz.replace(':', '')
     654        elif tz in timezones:
     655            tz = timezones[tz]
     656        else:
     657            tz = '+0000'
     658        value += tz
     659        fmt += '%z'
     660    return datetime.strptime(value, fmt).timetz()
     661
     662
     663def cast_timestamp(value, connection):
     664    """Cast a timestamp value."""
     665    if value == '-infinity':
     666        return datetime.min
     667    if value == 'infinity':
     668        return datetime.max
     669    value = value.split()
     670    if value[-1] == 'BC':
     671        return datetime.min
     672    fmt = connection.date_format()
     673    if fmt.endswith('-%Y') and len(value) > 2:
     674        value = value[1:5]
     675        if len(value[3]) > 4:
     676            return datetime.max
     677        fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
     678            '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
     679    else:
     680        if len(value[0]) > 10:
     681            return datetime.max
     682        fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
     683    return datetime.strptime(' '.join(value), ' '.join(fmt))
     684
     685
     686def cast_timestamptz(value, connection):
     687    """Cast a timestamptz value."""
     688    if value == '-infinity':
     689        return datetime.min
     690    if value == 'infinity':
     691        return datetime.max
     692    value = value.split()
     693    if value[-1] == 'BC':
     694        return datetime.min
     695    fmt = connection.date_format()
     696    if fmt.endswith('-%Y') and len(value) > 2:
     697        value = value[1:]
     698        if len(value[3]) > 4:
     699            return datetime.max
     700        fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
     701            '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
     702        value, tz = value[:-1], value[-1]
     703    else:
     704        if fmt.startswith('%Y-'):
     705            tz = _re_timezone.match(value[1])
     706            if tz:
     707                value[1], tz = tz.groups()
     708            else:
     709                tz = '+0000'
     710        else:
     711            value, tz = value[:-1], value[-1]
     712        if len(value[0]) > 10:
     713            return datetime.max
     714        fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
     715    if timezones:
     716        if tz.startswith(('+', '-')):
     717            if len(tz) < 5:
     718                tz += '00'
     719            else:
     720                tz = tz.replace(':', '')
     721        elif tz in timezones:
     722            tz = timezones[tz]
     723        else:
     724            tz = '+0000'
     725        value.append(tz)
     726        fmt.append('%z')
     727    return datetime.strptime(' '.join(value), ' '.join(fmt))
     728
     729_re_interval_sql_standard = regex(
     730    '(?:([+-])?([0-9]+)-([0-9]+) ?)?'
     731    '(?:([+-]?[0-9]+)(?!:) ?)?'
     732    '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
     733
     734_re_interval_postgres = regex(
     735    '(?:([+-]?[0-9]+) ?years? ?)?'
     736    '(?:([+-]?[0-9]+) ?mons? ?)?'
     737    '(?:([+-]?[0-9]+) ?days? ?)?'
     738    '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
     739
     740_re_interval_postgres_verbose = regex(
     741    '@ ?(?:([+-]?[0-9]+) ?years? ?)?'
     742    '(?:([+-]?[0-9]+) ?mons? ?)?'
     743    '(?:([+-]?[0-9]+) ?days? ?)?'
     744    '(?:([+-]?[0-9]+) ?hours? ?)?'
     745    '(?:([+-]?[0-9]+) ?mins? ?)?'
     746    '(?:([+-])?([0-9]+)(?:\\.([0-9]+))? ?secs?)? ?(ago)?')
     747
     748_re_interval_iso_8601 = regex(
     749    'P(?:([+-]?[0-9]+)Y)?'
     750    '(?:([+-]?[0-9]+)M)?'
     751    '(?:([+-]?[0-9]+)D)?'
     752    '(?:T(?:([+-]?[0-9]+)H)?'
     753    '(?:([+-]?[0-9]+)M)?'
     754    '(?:([+-])?([0-9]+)(?:\\.([0-9]+))?S)?)?')
     755
     756
     757def cast_interval(value):
     758    """Cast an interval value."""
     759    # The output format depends on the server setting IntervalStyle, but it's
     760    # not necessary to consult this setting to parse it.  It's faster to just
     761    # check all possible formats, and there is no ambiguity here.
     762    m = _re_interval_iso_8601.match(value)
     763    if m:
     764        m = [d or '0' for d in m.groups()]
     765        secs_ago = m.pop(5) == '-'
     766        m = [int(d) for d in m]
     767        years, mons, days, hours, mins, secs, usecs = m
     768        if secs_ago:
     769            secs = -secs
     770            usecs = -usecs
     771    else:
     772        m = _re_interval_postgres_verbose.match(value)
     773        if m:
     774            m, ago = [d or '0' for d in m.groups()[:8]], m.group(9)
     775            secs_ago = m.pop(5) == '-'
     776            m = [-int(d) for d in m] if ago else [int(d) for d in m]
     777            years, mons, days, hours, mins, secs, usecs = m
     778            if secs_ago:
     779                secs = - secs
     780                usecs = -usecs
     781        else:
     782            m = _re_interval_postgres.match(value)
     783            if m and any(m.groups()):
     784                m = [d or '0' for d in m.groups()]
     785                hours_ago = m.pop(3) == '-'
     786                m = [int(d) for d in m]
     787                years, mons, days, hours, mins, secs, usecs = m
     788                if hours_ago:
     789                    hours = -hours
     790                    mins = -mins
     791                    secs = -secs
     792                    usecs = -usecs
     793            else:
     794                m = _re_interval_sql_standard.match(value)
     795                if m and any(m.groups()):
     796                    m = [d or '0' for d in m.groups()]
     797                    years_ago = m.pop(0) == '-'
     798                    hours_ago = m.pop(3) == '-'
     799                    m = [int(d) for d in m]
     800                    years, mons, days, hours, mins, secs, usecs = m
     801                    if years_ago:
     802                        years = -years
     803                        mons = -mons
     804                    if hours_ago:
     805                        hours = -hours
     806                        mins = -mins
     807                        secs = -secs
     808                        usecs = -usecs
     809                else:
     810                    raise ValueError('Cannot parse interval: %s' % value)
     811    days += 365 * years + 30 * mons
     812    return timedelta(days=days, hours=hours, minutes=mins,
     813        seconds=secs, microseconds=usecs)
     814
     815
    590816class Typecasts(dict):
    591817    """Dictionary mapping database types to typecast functions.
     
    610836        'float4': float, 'float8': float,
    611837        'numeric': cast_num, 'money': cast_money,
     838        'date': cast_date, 'interval': cast_interval,
     839        'time': cast_time, 'timetz': cast_timetz,
     840        'timestamp': cast_timestamp, 'timestamptz': cast_timestamptz,
    612841        'int2vector': cast_int2vector,
    613842        'anyarray': cast_array, 'record': cast_record}
     843
     844    connection = None  # will be set in a connection specific instance
    614845
    615846    def __missing__(self, typ):
     
    624855        if cast:
    625856            # store default for faster access
     857            cast = self._add_connection(cast)
    626858            self[typ] = cast
    627859        elif typ.startswith('_'):
     
    637869                self[typ] = cast
    638870        return cast
     871
     872    @staticmethod
     873    def _needs_connection(func):
     874        """Check if a typecast function needs a connection argument."""
     875        try:
     876            args = get_args(func)
     877        except (TypeError, ValueError):
     878            return False
     879        else:
     880            return 'connection' in args[1:]
     881
     882    def _add_connection(self, cast):
     883        """Add a connection argument to the typecast function if necessary."""
     884        if not self.connection or not self._needs_connection(cast):
     885            return cast
     886        connection = self.connection
     887        return lambda value: cast(value, connection=connection)
    639888
    640889    def get(self, typ, default=None):
     
    654903                raise TypeError("Cast parameter must be callable")
    655904            for t in typ:
    656                 self[t] = cast
     905                self[t] = self._add_connection(cast)
    657906                self.pop('_%s' % t, None)
    658907
     
    699948        return {}
    700949
     950    def dateformat(self):
     951        """Return the current date format.
     952
     953        This method will be replaced with the dateformat() method of DbTypes.
     954        """
     955        return '%Y-%m-%d'
     956
    701957    def create_array_cast(self, cast):
    702958        """Create an array typecast for the given base cast."""
     
    7581014        """Initialize type cache for connection."""
    7591015        super(DbTypes, self).__init__()
     1016        self._regtypes = False
    7601017        self._get_attnames = db.get_attnames
     1018        self._typecasts = Typecasts()
     1019        self._typecasts.get_attnames = self.get_attnames
     1020        self._typecasts.connection = db
    7611021        db = db.db
    7621022        self.query = db.query
    7631023        self.escape_string = db.escape_string
    764         self._typecasts = Typecasts()
    765         self._typecasts.get_attnames = self.get_attnames
    766         self._regtypes = False
    7671024
    7681025    def add(self, oid, pgtype, regtype,
  • trunk/pgdb.py

    r812 r814  
    7373from math import isnan, isinf
    7474from collections import namedtuple
     75from re import compile as regex
    7576from json import loads as jsondecode, dumps as jsonencode
    7677
     
    110111
    111112### Internal Type Handling
     113
     114try:
     115    from inspect import signature
     116except ImportError:  # Python < 3.3
     117    from inspect import getargspec
     118
     119    get_args = lambda func: getargspec(func).args
     120else:
     121    get_args = lambda func: list(signature(func).parameters)
     122
     123try:
     124    if datetime.strptime('+0100', '%z') is None:
     125        raise ValueError
     126except ValueError:  # Python < 3.2
     127    timezones = None
     128else:
     129    # time zones used in Postgres timestamptz output
     130    timezones = dict(CET='+0100', EET='+0200', EST='-0500',
     131        GMT='+0000', HST='-1000', MET='+0100', MST='-0700',
     132        UCT='+0000', UTC='+0000', WET='+0000')
     133
    112134
    113135def decimal_type(decimal_type=None):
     
    140162    """Cast an int2vector value."""
    141163    return [int(v) for v in value.split()]
     164
     165
     166def cast_date(value, connection):
     167    """Cast a date value."""
     168    # The output format depends on the server setting DateStyle.  The default
     169    # setting ISO and the setting for German are actually unambiguous.  The
     170    # order of days and months in the other two settings is however ambiguous,
     171    # so at least here we need to consult the setting to properly parse values.
     172    if value == '-infinity':
     173        return date.min
     174    if value == 'infinity':
     175        return date.max
     176    value = value.split()
     177    if value[-1] == 'BC':
     178        return date.min
     179    value = value[0]
     180    if len(value) > 10:
     181        return date.max
     182    fmt = connection.date_format()
     183    return datetime.strptime(value, fmt).date()
     184
     185
     186def cast_time(value):
     187    """Cast a time value."""
     188    fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
     189    return datetime.strptime(value, fmt).time()
     190
     191
     192_re_timezone = regex('(.*)([+-].*)')
     193
     194
     195def cast_timetz(value):
     196    """Cast a timetz value."""
     197    tz = _re_timezone.match(value)
     198    if tz:
     199        value, tz = tz.groups()
     200    else:
     201        tz = '+0000'
     202    fmt = '%H:%M:%S.%f' if len(value) > 8 else '%H:%M:%S'
     203    if timezones:
     204        if tz.startswith(('+', '-')):
     205            if len(tz) < 5:
     206                tz += '00'
     207            else:
     208                tz = tz.replace(':', '')
     209        elif tz in timezones:
     210            tz = timezones[tz]
     211        else:
     212            tz = '+0000'
     213        value += tz
     214        fmt += '%z'
     215    return datetime.strptime(value, fmt).timetz()
     216
     217
     218def cast_timestamp(value, connection):
     219    """Cast a timestamp value."""
     220    if value == '-infinity':
     221        return datetime.min
     222    if value == 'infinity':
     223        return datetime.max
     224    value = value.split()
     225    if value[-1] == 'BC':
     226        return datetime.min
     227    fmt = connection.date_format()
     228    if fmt.endswith('-%Y') and len(value) > 2:
     229        value = value[1:5]
     230        if len(value[3]) > 4:
     231            return datetime.max
     232        fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
     233            '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
     234    else:
     235        if len(value[0]) > 10:
     236            return datetime.max
     237        fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
     238    return datetime.strptime(' '.join(value), ' '.join(fmt))
     239
     240
     241def cast_timestamptz(value, connection):
     242    """Cast a timestamptz value."""
     243    if value == '-infinity':
     244        return datetime.min
     245    if value == 'infinity':
     246        return datetime.max
     247    value = value.split()
     248    if value[-1] == 'BC':
     249        return datetime.min
     250    fmt = connection.date_format()
     251    if fmt.endswith('-%Y') and len(value) > 2:
     252        value = value[1:]
     253        if len(value[3]) > 4:
     254            return datetime.max
     255        fmt = ['%d %b' if fmt.startswith('%d') else '%b %d',
     256            '%H:%M:%S.%f' if len(value[2]) > 8 else '%H:%M:%S', '%Y']
     257        value, tz = value[:-1], value[-1]
     258    else:
     259        if fmt.startswith('%Y-'):
     260            tz = _re_timezone.match(value[1])
     261            if tz:
     262                value[1], tz = tz.groups()
     263            else:
     264                tz = '+0000'
     265        else:
     266            value, tz = value[:-1], value[-1]
     267        if len(value[0]) > 10:
     268            return datetime.max
     269        fmt = [fmt, '%H:%M:%S.%f' if len(value[1]) > 8 else '%H:%M:%S']
     270    if timezones:
     271        if tz.startswith(('+', '-')):
     272            if len(tz) < 5:
     273                tz += '00'
     274            else:
     275                tz = tz.replace(':', '')
     276        elif tz in timezones:
     277            tz = timezones[tz]
     278        else:
     279            tz = '+0000'
     280        value.append(tz)
     281        fmt.append('%z')
     282    return datetime.strptime(' '.join(value), ' '.join(fmt))
     283
     284_re_interval_sql_standard = regex(
     285    '(?:([+-])?([0-9]+)-([0-9]+) ?)?'
     286    '(?:([+-]?[0-9]+)(?!:) ?)?'
     287    '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
     288
     289_re_interval_postgres = regex(
     290    '(?:([+-]?[0-9]+) ?years? ?)?'
     291    '(?:([+-]?[0-9]+) ?mons? ?)?'
     292    '(?:([+-]?[0-9]+) ?days? ?)?'
     293    '(?:([+-])?([0-9]+):([0-9]+):([0-9]+)(?:\\.([0-9]+))?)?')
     294
     295_re_interval_postgres_verbose = regex(
     296    '@ ?(?:([+-]?[0-9]+) ?years? ?)?'
     297    '(?:([+-]?[0-9]+) ?mons? ?)?'
     298    '(?:([+-]?[0-9]+) ?days? ?)?'
     299    '(?:([+-]?[0-9]+) ?hours? ?)?'
     300    '(?:([+-]?[0-9]+) ?mins? ?)?'
     301    '(?:([+-])?([0-9]+)(?:\\.([0-9]+))? ?secs?)? ?(ago)?')
     302
     303_re_interval_iso_8601 = regex(
     304    'P(?:([+-]?[0-9]+)Y)?'
     305    '(?:([+-]?[0-9]+)M)?'
     306    '(?:([+-]?[0-9]+)D)?'
     307    '(?:T(?:([+-]?[0-9]+)H)?'
     308    '(?:([+-]?[0-9]+)M)?'
     309    '(?:([+-])?([0-9]+)(?:\\.([0-9]+))?S)?)?')
     310
     311
     312def cast_interval(value):
     313    """Cast an interval value."""
     314    # The output format depends on the server setting IntervalStyle, but it's
     315    # not necessary to consult this setting to parse it.  It's faster to just
     316    # check all possible formats, and there is no ambiguity here.
     317    m = _re_interval_iso_8601.match(value)
     318    if m:
     319        m = [d or '0' for d in m.groups()]
     320        secs_ago = m.pop(5) == '-'
     321        m = [int(d) for d in m]
     322        years, mons, days, hours, mins, secs, usecs = m
     323        if secs_ago:
     324            secs = -secs
     325            usecs = -usecs
     326    else:
     327        m = _re_interval_postgres_verbose.match(value)
     328        if m:
     329            m, ago = [d or '0' for d in m.groups()[:8]], m.group(9)
     330            secs_ago = m.pop(5) == '-'
     331            m = [-int(d) for d in m] if ago else [int(d) for d in m]
     332            years, mons, days, hours, mins, secs, usecs = m
     333            if secs_ago:
     334                secs = - secs
     335                usecs = -usecs
     336        else:
     337            m = _re_interval_postgres.match(value)
     338            if m and any(m.groups()):
     339                m = [d or '0' for d in m.groups()]
     340                hours_ago = m.pop(3) == '-'
     341                m = [int(d) for d in m]
     342                years, mons, days, hours, mins, secs, usecs = m
     343                if hours_ago:
     344                    hours = -hours
     345                    mins = -mins
     346                    secs = -secs
     347                    usecs = -usecs
     348            else:
     349                m = _re_interval_sql_standard.match(value)
     350                if m and any(m.groups()):
     351                    m = [d or '0' for d in m.groups()]
     352                    years_ago = m.pop(0) == '-'
     353                    hours_ago = m.pop(3) == '-'
     354                    m = [int(d) for d in m]
     355                    years, mons, days, hours, mins, secs, usecs = m
     356                    if years_ago:
     357                        years = -years
     358                        mons = -mons
     359                    if hours_ago:
     360                        hours = -hours
     361                        mins = -mins
     362                        secs = -secs
     363                        usecs = -usecs
     364                else:
     365                    raise ValueError('Cannot parse interval: %s' % value)
     366    days += 365 * years + 30 * mons
     367    return timedelta(days=days, hours=hours, minutes=mins,
     368        seconds=secs, microseconds=usecs)
    142369
    143370
     
    161388        'float4': float, 'float8': float,
    162389        'numeric': Decimal, 'money': cast_money,
     390        'date': cast_date, 'interval': cast_interval,
     391        'time': cast_time, 'timetz': cast_timetz,
     392        'timestamp': cast_timestamp, 'timestamptz': cast_timestamptz,
    163393        'int2vector': cast_int2vector,
    164394        'anyarray': cast_array, 'record': cast_record}
     395
     396    connection = None  # will be set in local connection specific instances
    165397
    166398    def __missing__(self, typ):
     
    175407        if cast:
    176408            # store default for faster access
     409            cast = self._add_connection(cast)
    177410            self[typ] = cast
    178411        elif typ.startswith('_'):
     
    185418        return cast
    186419
     420    @staticmethod
     421    def _needs_connection(func):
     422        """Check if a typecast function needs a connection argument."""
     423        try:
     424            args = get_args(func)
     425        except (TypeError, ValueError):
     426            return False
     427        else:
     428            return 'connection' in args[1:]
     429
     430    def _add_connection(self, cast):
     431        """Add a connection argument to the typecast function if necessary."""
     432        if not self.connection or not self._needs_connection(cast):
     433            return cast
     434        connection = self.connection
     435        return lambda value: cast(value, connection=connection)
     436
    187437    def get(self, typ, default=None):
    188438        """Get the typecast function for the given database type."""
     
    201451                raise TypeError("Cast parameter must be callable")
    202452            for t in typ:
    203                 self[t] = cast
     453                self[t] = self._add_connection(cast)
    204454                self.pop('_%s' % t, None)
    205455
     
    219469                cast = defaults.get(t)
    220470                if cast:
    221                     self[t] = cast
     471                    self[t] = self._add_connection(cast)
    222472                    t = '_%s' % t
    223473                    cast = defaults.get(t)
    224474                    if cast:
    225                         self[t] = cast
     475                        self[t] = self._add_connection(cast)
    226476                    else:
    227477                        self.pop(t, None)
     
    272522
    273523    defaults = _typecasts
     524
     525    connection = None  # will be set in a connection specific instance
    274526
    275527    def __missing__(self, typ):
     
    283535            cast = self.defaults.get(typ)
    284536            if cast:
     537                cast = self._add_connection(cast)
    285538                self[typ] = cast
    286539            else:
     
    338591        self._typecasts = LocalTypecasts()
    339592        self._typecasts.get_fields = self.get_fields
     593        self._typecasts.connection = cnx
    340594
    341595    def __missing__(self, key):
     
    12991553# Additional type helpers for PyGreSQL:
    13001554
     1555def Interval(days, hours=0, minutes=0, seconds=0, microseconds=0):
     1556    """Construct an object holding a time inverval value."""
     1557    return timedelta(days, hours=hours, minutes=minutes, seconds=seconds,
     1558        microseconds=microseconds)
     1559
    13011560class Bytea(bytes):
    13021561    """Construct an object capable of holding a bytea value."""
  • trunk/pgmodule.c

    r813 r814  
    9292                                *namedresult = NULL, /* function for getting named results */
    9393                                *jsondecode = NULL; /* function for decoding json strings */
     94static const char *date_format = NULL; /* date format that is always assumed */
    9495static char decimal_point = '.'; /* decimal point used in money values */
    9596static int bool_as_text = 0; /* whether bool shall be returned as text */
     
    140141        PyObject_HEAD
    141142        int                     valid;                          /* validity flag */
    142         PGconn     *cnx;                                /* PostGres connection handle */
     143        PGconn     *cnx;                                /* Postgres connection handle */
     144        const char *date_format;                /* date format derived from datestyle */
    143145        PyObject   *cast_hook;                  /* external typecast method */
    144146        PyObject   *notice_receiver;    /* current notice receiver */
     
    295297                case VARCHAROID:
    296298                case NAMEOID:
    297                 case DATEOID:
    298                 case INTERVALOID:
    299                 case TIMEOID:
    300                 case TIMETZOID:
    301                 case TIMESTAMPOID:
    302                 case TIMESTAMPTZOID:
    303299                case REGTYPEOID:
    304300                        t = PYGRES_TEXT;
     
    353349                case VARCHARARRAYOID:
    354350                case NAMEARRAYOID:
    355                 case DATEARRAYOID:
    356                 case INTERVALARRAYOID:
    357                 case TIMEARRAYOID:
    358                 case TIMETZARRAYOID:
    359                 case TIMESTAMPARRAYOID:
    360                 case TIMESTAMPTZARRAYOID:
    361351                case REGTYPEARRAYOID:
    362352                        t = array_as_text ? PYGRES_TEXT : (PYGRES_TEXT | PYGRES_ARRAY);
     
    20222012        }
    20232013
     2014        /* this may have changed the datestyle, so we reset the date format
     2015           in order to force fetching it newly when next time requested */
     2016        self->date_format = date_format; /* this is normally NULL */
     2017
    20242018        /* checks result status */
    20252019        if ((status = PQresultStatus(result)) != PGRES_TUPLES_OK)
     
    24702464}
    24712465
     2466/* internal function converting a Postgres datestyles to date formats */
     2467static const char *
     2468date_style_to_format(const char *s)
     2469{
     2470        static const char *formats[] = {
     2471                "%Y-%m-%d",             /* 0 = ISO */
     2472                "%m-%d-%Y",             /* 1 = Postgres, MDY */
     2473                "%d-%m-%Y",             /* 2 = Postgres, DMY */
     2474                "%m/%d/%Y",             /* 3 = SQL, MDY */
     2475                "%d/%m/%Y",             /* 4 = SQL, DMY */
     2476                "%d.%m.%Y"};    /* 5 = German */
     2477
     2478        switch (s ? *s : 'I')
     2479        {
     2480                case 'P': /* Postgres */
     2481                        s = strchr(s + 1, ',');
     2482                        if (s) do ++s; while (*s && *s == ' ');
     2483                        return formats[s && *s == 'D' ? 2 : 1];
     2484                case 'S': /* SQL */
     2485                        s = strchr(s + 1, ',');
     2486                        if (s) do ++s; while (*s && *s == ' ');
     2487                        return formats[s && *s == 'D' ? 4 : 3];
     2488                case 'G': /* German */
     2489                        return formats[5];
     2490                default: /* ISO */
     2491                        return formats[0]; /* ISO is the default */
     2492        }
     2493}
     2494
     2495/* internal function converting a date format to a Postgres datestyle */
     2496static const char *
     2497date_format_to_style(const char *s)
     2498{
     2499        static const char *datestyle[] = {
     2500                "ISO, YMD",                     /* 0 = %Y-%m-%d */
     2501                "Postgres, MDY",        /* 1 = %m-%d-%Y */
     2502                "Postgres, DMY",        /* 2 = %d-%m-%Y */
     2503                "SQL, MDY",             /* 3 = %m/%d/%Y */
     2504                "SQL, DMY",             /* 4 = %d/%m/%Y */
     2505                "German, DMY"};         /* 5 = %d.%m.%Y */
     2506
     2507        switch (s ? s[1] : 'Y')
     2508        {
     2509                case 'm':
     2510                        switch (s[2])
     2511                        {
     2512                                case '/':
     2513                                        return datestyle[3]; /* SQL, MDY */
     2514                                default:
     2515                                        return datestyle[1]; /* Postgres, MDY */
     2516                        }
     2517                case 'd':
     2518                        switch (s[2])
     2519                        {
     2520                                case '/':
     2521                                        return datestyle[4]; /* SQL, DMY */
     2522                                case '.':
     2523                                        return datestyle[5]; /* German */
     2524                                default:
     2525                                        return datestyle[2]; /* Postgres, DMY */
     2526                        }
     2527                default:
     2528                        return datestyle[0]; /* ISO */
     2529        }
     2530}
     2531
     2532/* get current date format */
     2533static char connDateFormat__doc__[] =
     2534"date_format() -- return the current date format";
     2535
     2536static PyObject *
     2537connDateFormat(connObject *self, PyObject *noargs)
     2538{
     2539        const char *fmt;
     2540
     2541        if (!self->cnx)
     2542        {
     2543                PyErr_SetString(PyExc_TypeError, "Connection is not valid");
     2544                return NULL;
     2545        }
     2546
     2547        /* check if the date format is cached in the connection */
     2548        fmt = self->date_format;
     2549        if (!fmt)
     2550        {
     2551                fmt = date_style_to_format(PQparameterStatus(self->cnx, "DateStyle"));
     2552                self->date_format = fmt; /* cache the result */
     2553        }
     2554
     2555        return PyStr_FromString(fmt);
     2556}
     2557
    24722558#ifdef ESCAPING_FUNCS
    24732559
     
    30403126        {"parameter", (PyCFunction) connParameter, METH_VARARGS,
    30413127                        connParameter__doc__},
     3128        {"date_format", (PyCFunction) connDateFormat, METH_NOARGS,
     3129                        connDateFormat__doc__},
    30423130
    30433131#ifdef ESCAPING_FUNCS
     
    32963384                return NULL;
    32973385        }
     3386
     3387        /* this may have changed the datestyle, so we reset the date format
     3388           in order to force fetching it newly when next time requested */
     3389        self->pgcnx->date_format = date_format; /* this is normally NULL */
    32983390
    32993391        /* checks result status */
     
    40794171        npgobj->valid = 1;
    40804172        npgobj->cnx = NULL;
     4173        npgobj->date_format = date_format;
    40814174        npgobj->cast_hook = NULL;
    40824175        npgobj->notice_receiver = NULL;
     
    47244817}
    47254818
     4819/* set fixed datestyle */
     4820static char pgSetDatestyle__doc__[] =
     4821"set_datestyle(style) -- set which style is assumed";
     4822
     4823static PyObject *
     4824pgSetDatestyle(PyObject *self, PyObject *args)
     4825{
     4826        const char         *datestyle = NULL;
     4827
     4828        /* gets arguments */
     4829        if (!PyArg_ParseTuple(args, "z", &datestyle))
     4830        {
     4831                PyErr_SetString(PyExc_TypeError,
     4832                        "Function set_datestyle() expects a string or None as argument");
     4833                return NULL;
     4834        }
     4835
     4836        date_format = datestyle ? date_style_to_format(datestyle) : NULL;
     4837
     4838        Py_INCREF(Py_None); return Py_None;
     4839}
     4840
     4841/* get fixed datestyle */
     4842static char pgGetDatestyle__doc__[] =
     4843"get_datestyle() -- get which date style is assumed";
     4844
     4845static PyObject *
     4846pgGetDatestyle(PyObject *self, PyObject *noargs)
     4847{
     4848        if (date_format)
     4849        {
     4850                return PyStr_FromString(date_format_to_style(date_format));
     4851        }
     4852        else
     4853        {
     4854                Py_INCREF(Py_None); return Py_None;
     4855        }
     4856}
     4857
    47264858/* get decimal point */
    47274859static char pgGetDecimalPoint__doc__[] =
     
    47994931
    48004932static PyObject *
    4801 pgSetDecimal(PyObject *self, PyObject *args)
     4933pgSetDecimal(PyObject *self, PyObject *cls)
    48024934{
    48034935        PyObject *ret = NULL;
    4804         PyObject *cls;
    4805 
    4806         if (PyArg_ParseTuple(args, "O", &cls))
    4807         {
    4808                 if (cls == Py_None)
    4809                 {
    4810                         Py_XDECREF(decimal); decimal = NULL;
    4811                         Py_INCREF(Py_None); ret = Py_None;
    4812                 }
    4813                 else if (PyCallable_Check(cls))
    4814                 {
    4815                         Py_XINCREF(cls); Py_XDECREF(decimal); decimal = cls;
    4816                         Py_INCREF(Py_None); ret = Py_None;
    4817                 }
    4818                 else
    4819                         PyErr_SetString(PyExc_TypeError,
    4820                                 "Function set_decimal() expects"
    4821                                  " a callable or None as argument");
    4822         }
     4936
     4937        if (cls == Py_None)
     4938        {
     4939                Py_XDECREF(decimal); decimal = NULL;
     4940                Py_INCREF(Py_None); ret = Py_None;
     4941        }
     4942        else if (PyCallable_Check(cls))
     4943        {
     4944                Py_XINCREF(cls); Py_XDECREF(decimal); decimal = cls;
     4945                Py_INCREF(Py_None); ret = Py_None;
     4946        }
     4947        else
     4948                PyErr_SetString(PyExc_TypeError,
     4949                        "Function set_decimal() expects"
     4950                         " a callable or None as argument");
    48234951
    48244952        return ret;
     
    49595087
    49605088static PyObject *
    4961 pgSetNamedresult(PyObject *self, PyObject *args)
     5089pgSetNamedresult(PyObject *self, PyObject *func)
    49625090{
    49635091        PyObject *ret = NULL;
    4964         PyObject *func;
    4965 
    4966         if (PyArg_ParseTuple(args, "O", &func))
    4967         {
    4968                 if (func == Py_None)
    4969                 {
    4970                         Py_XDECREF(namedresult); namedresult = NULL;
    4971                         Py_INCREF(Py_None); ret = Py_None;
    4972                 }
    4973                 else if (PyCallable_Check(func))
    4974                 {
    4975                         Py_XINCREF(func); Py_XDECREF(namedresult); namedresult = func;
    4976                         Py_INCREF(Py_None); ret = Py_None;
    4977                 }
    4978                 else
    4979                         PyErr_SetString(PyExc_TypeError,
    4980                                 "Function set_namedresult() expectst"
    4981                                  " a callable or None as argument");
    4982         }
     5092
     5093        if (func == Py_None)
     5094        {
     5095                Py_XDECREF(namedresult); namedresult = NULL;
     5096                Py_INCREF(Py_None); ret = Py_None;
     5097        }
     5098        else if (PyCallable_Check(func))
     5099        {
     5100                Py_XINCREF(func); Py_XDECREF(namedresult); namedresult = func;
     5101                Py_INCREF(Py_None); ret = Py_None;
     5102        }
     5103        else
     5104                PyErr_SetString(PyExc_TypeError,
     5105                        "Function set_namedresult() expects"
     5106                         " a callable or None as argument");
    49835107
    49845108        return ret;
     
    50045128/* set json decode function */
    50055129static char pgSetJsondecode__doc__[] =
    5006 "set_jsondecode() -- set a function to be used for decoding json results";
    5007 
    5008 static PyObject *
    5009 pgSetJsondecode(PyObject *self, PyObject *args)
     5130"set_jsondecode(func) -- set a function to be used for decoding json results";
     5131
     5132static PyObject *
     5133pgSetJsondecode(PyObject *self, PyObject *func)
    50105134{
    50115135        PyObject *ret = NULL;
    5012         PyObject *func;
    5013 
    5014         if (PyArg_ParseTuple(args, "O", &func))
    5015         {
    5016                 if (func == Py_None)
    5017                 {
    5018                         Py_XDECREF(jsondecode); jsondecode = NULL;
    5019                         Py_INCREF(Py_None); ret = Py_None;
    5020                 }
    5021                 else if (PyCallable_Check(func))
    5022                 {
    5023                         Py_XINCREF(func); Py_XDECREF(jsondecode); jsondecode = func;
    5024                         Py_INCREF(Py_None); ret = Py_None;
    5025                 }
    5026                 else
    5027                         PyErr_SetString(PyExc_TypeError,
    5028                                 "Function jsondecode() expects"
    5029                                  " a callable or None as argument");
    5030         }
     5136
     5137        if (func == Py_None)
     5138        {
     5139                Py_XDECREF(jsondecode); jsondecode = NULL;
     5140                Py_INCREF(Py_None); ret = Py_None;
     5141        }
     5142        else if (PyCallable_Check(func))
     5143        {
     5144                Py_XINCREF(func); Py_XDECREF(jsondecode); jsondecode = func;
     5145                Py_INCREF(Py_None); ret = Py_None;
     5146        }
     5147        else
     5148                PyErr_SetString(PyExc_TypeError,
     5149                        "Function jsondecode() expects"
     5150                         " a callable or None as argument");
    50315151
    50325152        return ret;
     
    54205540        {"unescape_bytea", (PyCFunction) pgUnescapeBytea, METH_O,
    54215541                        pgUnescapeBytea__doc__},
     5542        {"get_datestyle", (PyCFunction) pgGetDatestyle, METH_NOARGS,
     5543                        pgGetDatestyle__doc__},
     5544        {"set_datestyle", (PyCFunction) pgSetDatestyle, METH_VARARGS,
     5545                        pgSetDatestyle__doc__},
    54225546        {"get_decimal_point", (PyCFunction) pgGetDecimalPoint, METH_NOARGS,
    54235547                        pgGetDecimalPoint__doc__},
     
    54265550        {"get_decimal", (PyCFunction) pgGetDecimal, METH_NOARGS,
    54275551                        pgGetDecimal__doc__},
    5428         {"set_decimal", (PyCFunction) pgSetDecimal, METH_VARARGS,
     5552        {"set_decimal", (PyCFunction) pgSetDecimal, METH_O,
    54295553                        pgSetDecimal__doc__},
    54305554        {"get_bool", (PyCFunction) pgGetBool, METH_NOARGS, pgGetBool__doc__},
     
    54385562        {"get_namedresult", (PyCFunction) pgGetNamedresult, METH_NOARGS,
    54395563                        pgGetNamedresult__doc__},
    5440         {"set_namedresult", (PyCFunction) pgSetNamedresult, METH_VARARGS,
     5564        {"set_namedresult", (PyCFunction) pgSetNamedresult, METH_O,
    54415565                        pgSetNamedresult__doc__},
    54425566        {"get_jsondecode", (PyCFunction) pgGetJsondecode, METH_NOARGS,
    54435567                        pgGetJsondecode__doc__},
    5444         {"set_jsondecode", (PyCFunction) pgSetJsondecode, METH_VARARGS,
     5568        {"set_jsondecode", (PyCFunction) pgSetJsondecode, METH_O,
    54455569                        pgSetJsondecode__doc__},
    54465570        {"cast_array", (PyCFunction) pgCastArray, METH_VARARGS|METH_KEYWORDS,
  • trunk/tests/test_classic_connection.py

    r813 r814  
    121121
    122122    def testAllConnectMethods(self):
    123         methods = '''cancel close endcopy
     123        methods = '''cancel close date_format endcopy
    124124            escape_bytea escape_identifier escape_literal escape_string
    125125            fileno get_cast_hook get_notice_receiver getline getlo getnotify
     
    915915        r = self.c.query(q, (value,)).getresult()[0][0]
    916916        self.assertIsInstance(r, pytype)
    917         if isinstance(value, (bytes, str)):
    918             if not value or '{':
     917        if isinstance(value, str):
     918            if not value or ' ' in value or '{' in value:
    919919                value = '"%s"' % value
    920920        value = '{%s}' % value
    921921        r = self.c.query(q + '[]', (value,)).getresult()[0][0]
    922         self.assertIsInstance(r, list)
    923         self.assertEqual(len(r), 1)
    924         self.assertIsInstance(r[0], pytype)
     922        if pgtype.startswith(('date', 'time', 'interval')):
     923            # arrays of these are casted by the DB wrapper only
     924            self.assertEqual(r, value)
     925        else:
     926            self.assertIsInstance(r, list)
     927            self.assertEqual(len(r), 1)
     928            self.assertIsInstance(r[0], pytype)
    925929
    926930    def testInt(self):
     
    956960    def testDate(self):
    957961        self.assert_proper_cast('1956-01-31', 'date', str)
    958         self.assert_proper_cast('0', 'interval', str)
    959         self.assert_proper_cast('08:42', 'time', str)
    960         self.assert_proper_cast('08:42', 'timetz', str)
    961         self.assert_proper_cast('1956-01-31 08:42', 'timestamp', str)
    962         self.assert_proper_cast('1956-01-31 08:42', 'timestamptz', str)
     962        self.assert_proper_cast('10:20:30', 'interval', str)
     963        self.assert_proper_cast('08:42:15', 'time', str)
     964        self.assert_proper_cast('08:42:15+00', 'timetz', str)
     965        self.assert_proper_cast('1956-01-31 08:42:15', 'timestamp', str)
     966        self.assert_proper_cast('1956-01-31 08:42:15+00', 'timestamptz', str)
    963967
    964968    def testText(self):
  • trunk/tests/test_classic_dbwrapper.py

    r804 r814  
    1818import os
    1919import sys
     20import json
    2021import tempfile
    21 import json
    2222
    2323import pg  # the module under test
    2424
    2525from decimal import Decimal
    26 from datetime import date
     26from datetime import date, time, datetime, timedelta
     27from time import strftime
    2728from operator import itemgetter
    2829
     
    181182            'begin',
    182183            'cancel', 'clear', 'close', 'commit',
    183             'db', 'dbname', 'dbtypes',
     184            'date_format', 'db', 'dbname', 'dbtypes',
    184185            'debug', 'decode_json', 'delete',
    185186            'encode_json', 'end', 'endcopy', 'error',
     
    15061507                        if item[0] in expect)
    15071508            ts = expect.get('ts')
    1508             if ts == 'current_timestamp':
    1509                 ts = expect['ts'] = data['ts']
    1510                 if len(ts) > 19:
    1511                     self.assertEqual(ts[19], '.')
    1512                     ts = ts[:19]
     1509            if ts:
     1510                if ts == 'current_timestamp':
     1511                    ts = data['ts']
     1512                    self.assertIsInstance(ts, datetime)
     1513                    self.assertEqual(ts.strftime('%Y-%m-%d'),
     1514                        strftime('%Y-%m-%d'))
    15131515                else:
    1514                     self.assertEqual(len(ts), 19)
    1515                 self.assertTrue(ts[:4].isdigit())
    1516                 self.assertEqual(ts[4], '-')
    1517                 self.assertEqual(ts[10], ' ')
    1518                 self.assertTrue(ts[11:13].isdigit())
    1519                 self.assertEqual(ts[13], ':')
     1516                    ts = datetime.strptime(ts, '%Y-%m-%d %H:%M:%S')
     1517                expect['ts'] = ts
    15201518            self.assertEqual(data, expect)
    15211519            data = query(
     
    35243522        self.assertIsInstance(p.age, int)
    35253523
     3524    def testDate(self):
     3525        query = self.db.query
     3526        for datestyle in ('ISO', 'Postgres, MDY', 'Postgres, DMY',
     3527                'SQL, MDY', 'SQL, DMY', 'German'):
     3528            self.db.set_parameter('datestyle', datestyle)
     3529            d = date(2016, 3, 14)
     3530            q = "select '2016-03-14'::date"
     3531            r = query(q).getresult()[0][0]
     3532            self.assertIsInstance(r, date)
     3533            self.assertEqual(r, d)
     3534            q = "select '10000-08-01'::date, '0099-01-08 BC'::date"
     3535            r = query(q).getresult()[0]
     3536            self.assertIsInstance(r[0], date)
     3537            self.assertIsInstance(r[1], date)
     3538            self.assertEqual(r[0], date.max)
     3539            self.assertEqual(r[1], date.min)
     3540        q = "select 'infinity'::date, '-infinity'::date"
     3541        r = query(q).getresult()[0]
     3542        self.assertIsInstance(r[0], date)
     3543        self.assertIsInstance(r[1], date)
     3544        self.assertEqual(r[0], date.max)
     3545        self.assertEqual(r[1], date.min)
     3546
     3547    def testTime(self):
     3548        query = self.db.query
     3549        d = time(15, 9, 26)
     3550        q = "select '15:09:26'::time"
     3551        r = query(q).getresult()[0][0]
     3552        self.assertIsInstance(r, time)
     3553        self.assertEqual(r, d)
     3554        d = time(15, 9, 26, 535897)
     3555        q = "select '15:09:26.535897'::time"
     3556        r = query(q).getresult()[0][0]
     3557        self.assertIsInstance(r, time)
     3558        self.assertEqual(r, d)
     3559
     3560    def testTimetz(self):
     3561        query = self.db.query
     3562        timezones = dict(CET=1, EET=2, EST=-5, UTC=0)
     3563        for timezone in sorted(timezones):
     3564            offset = timezones[timezone]
     3565            try:
     3566                tzinfo = datetime.strptime('%+03d00' % offset, '%z').tzinfo
     3567            except ValueError:  # Python < 3.3
     3568                tzinfo = None
     3569            self.db.set_parameter('timezone', timezone)
     3570            d = time(15, 9, 26, tzinfo=tzinfo)
     3571            q = "select '15:09:26'::timetz"
     3572            r = query(q).getresult()[0][0]
     3573            self.assertIsInstance(r, time)
     3574            self.assertEqual(r, d)
     3575            d = time(15, 9, 26, 535897, tzinfo)
     3576            q = "select '15:09:26.535897'::timetz"
     3577            r = query(q).getresult()[0][0]
     3578            self.assertIsInstance(r, time)
     3579            self.assertEqual(r, d)
     3580
     3581    def testTimestamp(self):
     3582        query = self.db.query
     3583        for datestyle in ('ISO', 'Postgres, MDY', 'Postgres, DMY',
     3584                'SQL, MDY', 'SQL, DMY', 'German'):
     3585            self.db.set_parameter('datestyle', datestyle)
     3586            d = datetime(2016, 3, 14)
     3587            q = "select '2016-03-14'::timestamp"
     3588            r = query(q).getresult()[0][0]
     3589            self.assertIsInstance(r, datetime)
     3590            self.assertEqual(r, d)
     3591            d = datetime(2016, 3, 14, 15, 9, 26)
     3592            q = "select '2016-03-14 15:09:26'::timestamp"
     3593            r = query(q).getresult()[0][0]
     3594            self.assertIsInstance(r, datetime)
     3595            self.assertEqual(r, d)
     3596            d = datetime(2016, 3, 14, 15, 9, 26, 535897)
     3597            q = "select '2016-03-14 15:09:26.535897'::timestamp"
     3598            r = query(q).getresult()[0][0]
     3599            self.assertIsInstance(r, datetime)
     3600            self.assertEqual(r, d)
     3601            q = ("select '10000-08-01 AD'::timestamp,"
     3602                " '0099-01-08 BC'::timestamp")
     3603            r = query(q).getresult()[0]
     3604            self.assertIsInstance(r[0], datetime)
     3605            self.assertIsInstance(r[1], datetime)
     3606            self.assertEqual(r[0], datetime.max)
     3607            self.assertEqual(r[1], datetime.min)
     3608        q = "select 'infinity'::timestamp, '-infinity'::timestamp"
     3609        r = query(q).getresult()[0]
     3610        self.assertIsInstance(r[0], datetime)
     3611        self.assertIsInstance(r[1], datetime)
     3612        self.assertEqual(r[0], datetime.max)
     3613        self.assertEqual(r[1], datetime.min)
     3614
     3615    def testTimestamptz(self):
     3616        query = self.db.query
     3617        timezones = dict(CET=1, EET=2, EST=-5, UTC=0)
     3618        for timezone in sorted(timezones):
     3619            offset = timezones[timezone]
     3620            try:
     3621                tzinfo = datetime.strptime('%+03d00' % offset, '%z').tzinfo
     3622            except ValueError:  # Python < 3.3
     3623                tzinfo = None
     3624            self.db.set_parameter('timezone', timezone)
     3625            for datestyle in ('ISO', 'Postgres, MDY', 'Postgres, DMY',
     3626                    'SQL, MDY', 'SQL, DMY', 'German'):
     3627                self.db.set_parameter('datestyle', datestyle)
     3628                d = datetime(2016, 3, 14, tzinfo=tzinfo)
     3629                q = "select '2016-03-14'::timestamptz"
     3630                r = query(q).getresult()[0][0]
     3631                self.assertIsInstance(r, datetime)
     3632                self.assertEqual(r, d)
     3633                d = datetime(2016, 3, 14, 15, 9, 26, tzinfo=tzinfo)
     3634                q = "select '2016-03-14 15:09:26'::timestamptz"
     3635                r = query(q).getresult()[0][0]
     3636                self.assertIsInstance(r, datetime)
     3637                self.assertEqual(r, d)
     3638                d = datetime(2016, 3, 14, 15, 9, 26, 535897, tzinfo)
     3639                q = "select '2016-03-14 15:09:26.535897'::timestamptz"
     3640                r = query(q).getresult()[0][0]
     3641                self.assertIsInstance(r, datetime)
     3642                self.assertEqual(r, d)
     3643                q = ("select '10000-08-01 AD'::timestamptz,"
     3644                    " '0099-01-08 BC'::timestamptz")
     3645                r = query(q).getresult()[0]
     3646                self.assertIsInstance(r[0], datetime)
     3647                self.assertIsInstance(r[1], datetime)
     3648                self.assertEqual(r[0], datetime.max)
     3649                self.assertEqual(r[1], datetime.min)
     3650        q = "select 'infinity'::timestamptz, '-infinity'::timestamptz"
     3651        r = query(q).getresult()[0]
     3652        self.assertIsInstance(r[0], datetime)
     3653        self.assertIsInstance(r[1], datetime)
     3654        self.assertEqual(r[0], datetime.max)
     3655        self.assertEqual(r[1], datetime.min)
     3656
     3657    def testInterval(self):
     3658        query = self.db.query
     3659        for intervalstyle in (
     3660                'sql_standard', 'postgres', 'postgres_verbose', 'iso_8601'):
     3661            self.db.set_parameter('intervalstyle', intervalstyle)
     3662            q = "select '2016-03-14'::timestamp - '2016-03-11'::timestamp"
     3663            r = query(q).getresult()[0][0]
     3664            self.assertIsInstance(r, timedelta)
     3665            d = timedelta(3)
     3666            self.assertEqual(r, d)
     3667            q = "select '2016-03-14'::timestamp - '2016-04-13'::timestamp"
     3668            r = query(q).getresult()[0][0]
     3669            self.assertIsInstance(r, timedelta)
     3670            d = timedelta(-30)
     3671            self.assertEqual(r, d)
     3672            q = ("select '2016-03-14 15:31:42.5678'::timestamp"
     3673                 " - '2016-03-14 12:00:00'::timestamp")
     3674            r = query(q).getresult()[0][0]
     3675            self.assertIsInstance(r, timedelta)
     3676            d = timedelta(hours=3, minutes=31, seconds=42, microseconds=5678)
     3677            self.assertEqual(r, d)
     3678
     3679    def testDateAndTimeArrays(self):
     3680        q = "select ARRAY['2016-03-14'::date], ARRAY['15:09:26'::time]"
     3681        r = self.db.query(q).getresult()[0]
     3682        d = r[0]
     3683        self.assertIsInstance(d, list)
     3684        d = d[0]
     3685        self.assertIsInstance(d, date)
     3686        self.assertEqual(d, date(2016, 3, 14))
     3687        d = r[1]
     3688        self.assertIsInstance(d, list)
     3689        d = d[0]
     3690        self.assertIsInstance(d, time)
     3691        self.assertEqual(d, time(15, 9, 26))
     3692
    35263693    def testDbTypesInfo(self):
    35273694        dbtypes = self.db.dbtypes
  • trunk/tests/test_classic_functions.py

    r813 r814  
    2020
    2121import pg  # the module under test
     22
     23from datetime import timedelta
    2224
    2325try:
     
    615617
    616618
     619class TestCastInterval(unittest.TestCase):
     620    """Test the interval typecast function."""
     621
     622    intervals = [
     623        ((0, 0, 0, 1, 0, 0, 0),
     624            ('1:00:00', '01:00:00', '@ 1 hour', 'PT1H')),
     625        ((0, 0, 0, -1, 0, 0, 0),
     626            ('-1:00:00', '-01:00:00', '@ -1 hour', 'PT-1H')),
     627        ((0, 0, 0, 1, 0, 0, 0),
     628            ('0-0 0 1:00:00', '0 years 0 mons 0 days 01:00:00',
     629            '@ 0 years 0 mons 0 days 1 hour', 'P0Y0M0DT1H')),
     630        ((0, 0, 0, -1, 0, 0, 0),
     631            ('-0-0 -1:00:00', '0 years 0 mons 0 days -01:00:00',
     632            '@ 0 years 0 mons 0 days -1 hour', 'P0Y0M0DT-1H')),
     633        ((0, 0, 1, 0, 0, 0, 0),
     634            ('1 0:00:00', '1 day', '@ 1 day', 'P1D')),
     635        ((0, 0, -1, 0, 0, 0, 0),
     636            ('-1 0:00:00', '-1 day', '@ -1 day', 'P-1D')),
     637        ((0, 1, 0, 0, 0, 0, 0),
     638            ('0-1', '1 mon', '@ 1 mon', 'P1M')),
     639        ((1, 0, 0, 0, 0, 0, 0),
     640            ('1-0', '1 year', '@ 1 year', 'P1Y')),
     641        ((0, 0, 0, 2, 0, 0, 0),
     642            ('2:00:00', '02:00:00', '@ 2 hours', 'PT2H')),
     643        ((0, 0, 2, 0, 0, 0, 0),
     644            ('2 0:00:00', '2 days', '@ 2 days', 'P2D')),
     645        ((0, 2, 0, 0, 0, 0, 0),
     646            ('0-2', '2 mons', '@ 2 mons', 'P2M')),
     647        ((2, 0, 0, 0, 0, 0, 0),
     648            ('2-0', '2 years', '@ 2 years', 'P2Y')),
     649        ((0, 0, 0, -3, 0, 0, 0),
     650            ('-3:00:00', '-03:00:00', '@ 3 hours ago', 'PT-3H')),
     651        ((0, 0, -3, 0, 0, 0, 0),
     652            ('-3 0:00:00', '-3 days', '@ 3 days ago', 'P-3D')),
     653        ((0, -3, 0, 0, 0, 0, 0),
     654            ('-0-3', '-3 mons', '@ 3 mons ago', 'P-3M')),
     655        ((-3, 0, 0, 0, 0, 0, 0),
     656            ('-3-0', '-3 years', '@ 3 years ago', 'P-3Y')),
     657        ((0, 0, 0, 0, 1, 0, 0),
     658            ('0:01:00', '00:01:00', '@ 1 min', 'PT1M')),
     659        ((0, 0, 0, 0, 0, 1, 0),
     660            ('0:00:01', '00:00:01', '@ 1 sec', 'PT1S')),
     661        ((0, 0, 0, 0, 0, 0, 1),
     662            ('0:00:00.000001', '00:00:00.000001',
     663             '@ 0.000001 secs', 'PT0.000001S')),
     664        ((0, 0, 0, 0, 2, 0, 0),
     665            ('0:02:00', '00:02:00', '@ 2 mins', 'PT2M')),
     666        ((0, 0, 0, 0, 0, 2, 0),
     667            ('0:00:02', '00:00:02', '@ 2 secs', 'PT2S')),
     668        ((0, 0, 0, 0, 0, 0, 2),
     669            ('0:00:00.000002', '00:00:00.000002',
     670             '@ 0.000002 secs', 'PT0.000002S')),
     671        ((0, 0, 0, 0, -3, 0, 0),
     672            ('-0:03:00', '-00:03:00', '@ 3 mins ago', 'PT-3M')),
     673        ((0, 0, 0, 0, 0, -3, 0),
     674            ('-0:00:03', '-00:00:03', '@ 3 secs ago', 'PT-3S')),
     675        ((0, 0, 0, 0, 0, 0, -3),
     676            ('-0:00:00.000003', '-00:00:00.000003',
     677             '@ 0.000003 secs ago', 'PT-0.000003S')),
     678        ((1, 2, 0, 0, 0, 0, 0),
     679            ('1-2', '1 year 2 mons', '@ 1 year 2 mons', 'P1Y2M')),
     680        ((0, 0, 3, 4, 5, 6, 0),
     681            ('3 4:05:06', '3 days 04:05:06',
     682             '@ 3 days 4 hours 5 mins 6 secs', 'P3DT4H5M6S')),
     683        ((1, 2, 3, 4, 5, 6, 0),
     684            ('+1-2 +3 +4:05:06', '1 year 2 mons 3 days 04:05:06',
     685             '@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs',
     686             'P1Y2M3DT4H5M6S')),
     687        ((1, 2, 3, -4, -5, -6, 0),
     688            ('+1-2 +3 -4:05:06', '1 year 2 mons 3 days -04:05:06',
     689             '@ 1 year 2 mons 3 days -4 hours -5 mins -6 secs',
     690             'P1Y2M3DT-4H-5M-6S')),
     691        ((1, 2, 3, -4, 5, 6, 0),
     692            ('+1-2 +3 -3:54:54', '1 year 2 mons 3 days -03:54:54',
     693             '@ 1 year 2 mons 3 days -3 hours -54 mins -54 secs',
     694             'P1Y2M3DT-3H-54M-54S')),
     695        ((-1, -2, 3, -4, -5, -6, 0),
     696            ('-1-2 +3 -4:05:06', '-1 years -2 mons +3 days -04:05:06',
     697             '@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago',
     698             'P-1Y-2M3DT-4H-5M-6S')),
     699        ((1, 2, -3, 4, 5, 6, 0),
     700            ('+1-2 -3 +4:05:06', '1 year 2 mons -3 days +04:05:06',
     701             '@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs',
     702             'P1Y2M-3DT4H5M6S')),
     703        ((0, 0, 0, 1, 30, 0, 0),
     704            ('1:30:00', '01:30:00', '@ 1 hour 30 mins', 'PT1H30M')),
     705        ((0, 0, 0, 3, 15, 45, 123456),
     706            ('3:15:45.123456', '03:15:45.123456',
     707             '@ 3 hours 15 mins 45.123456 secs', 'PT3H15M45.123456S')),
     708        ((0, 0, 0, 3, 15, -5, 123),
     709            ('3:14:55.000123', '03:14:55.000123',
     710             '@ 3 hours 14 mins 55.000123 secs', 'PT3H14M55.000123S')),
     711        ((0, 0, 0, 3, -5, 15, -12345),
     712            ('2:55:14.987655', '02:55:14.987655',
     713             '@ 2 hours 55 mins 14.987655 secs', 'PT2H55M14.987655S')),
     714        ((0, 0, 0, 2, -1, 0, 0),
     715            ('1:59:00', '01:59:00', '@ 1 hour 59 mins', 'PT1H59M')),
     716        ((0, 0, 0, -1, 2, 0, 0),
     717            ('-0:58:00', '-00:58:00', '@ 58 mins ago', 'PT-58M')),
     718        ((1, 11, 0, 0, 0, 0, 0),
     719            ('1-11', '1 year 11 mons', '@ 1 year 11 mons', 'P1Y11M')),
     720        ((0, -10, 0, 0, 0, 0, 0),
     721            ('-0-10', '-10 mons', '@ 10 mons ago', 'P-10M')),
     722        ((0, 0, 2, -1, 0, 0, 0),
     723            ('+0-0 +2 -1:00:00', '2 days -01:00:00',
     724             '@ 2 days -1 hours', 'P2DT-1H')),
     725        ((0, 0, -1, 2, 0, 0, 0),
     726            ('+0-0 -1 +2:00:00', '-1 days +02:00:00',
     727             '@ 1 day -2 hours ago', 'P-1DT2H')),
     728        ((0, 0, 1, 0, 0, 0, 1),
     729            ('1 0:00:00.000001', '1 day 00:00:00.000001',
     730             '@ 1 day 0.000001 secs', 'P1DT0.000001S')),
     731        ((0, 0, 1, 0, 0, 1, 0),
     732            ('1 0:00:01', '1 day 00:00:01', '@ 1 day 1 sec', 'P1DT1S')),
     733        ((0, 0, 1, 0, 1, 0, 0),
     734            ('1 0:01:00', '1 day 00:01:00', '@ 1 day 1 min', 'P1DT1M')),
     735        ((0, 0, 0, 0, 1, 0, -1),
     736            ('0:00:59.999999', '00:00:59.999999',
     737             '@ 59.999999 secs', 'PT59.999999S')),
     738        ((0, 0, 0, 0, -1, 0, 1),
     739            ('-0:00:59.999999', '-00:00:59.999999',
     740             '@ 59.999999 secs ago', 'PT-59.999999S')),
     741        ((0, 0, 0, 0, -1, 1, 1),
     742            ('-0:00:58.999999', '-00:00:58.999999',
     743             '@ 58.999999 secs ago', 'PT-58.999999S')),
     744        ((0, 0, 42, 0, 0, 0, 0),
     745            ('42 0:00:00', '42 days', '@ 42 days', 'P42D')),
     746        ((0, 0, -7, 0, 0, 0, 0),
     747            ('-7 0:00:00', '-7 days', '@ 7 days ago', 'P-7D')),
     748        ((1, 1, 1, 1, 1, 0, 0),
     749            ('+1-1 +1 +1:01:00', '1 year 1 mon 1 day 01:01:00',
     750             '@ 1 year 1 mon 1 day 1 hour 1 min', 'P1Y1M1DT1H1M')),
     751        ((0, -11, -1, -1, 1, 0, 0),
     752            ('-0-11 -1 -0:59:00', '-11 mons -1 days -00:59:00',
     753             '@ 11 mons 1 day 59 mins ago', 'P-11M-1DT-59M')),
     754        ((-1, -1, -1, -1, -1, 0, 0),
     755            ('-1-1 -1 -1:01:00', '-1 years -1 mons -1 days -01:01:00',
     756             '@ 1 year 1 mon 1 day 1 hour 1 min ago', 'P-1Y-1M-1DT-1H-1M')),
     757        ((-1, 0, -3, 1, 0, 0, 0),
     758            ('-1-0 -3 +1:00:00', '-1 years -3 days +01:00:00',
     759             '@ 1 year 3 days -1 hours ago', 'P-1Y-3DT1H')),
     760        ((1, 0, 0, 0, 0, 0, 1),
     761            ('+1-0 +0 +0:00:00.000001', '1 year 00:00:00.000001',
     762             '@ 1 year 0.000001 secs', 'P1YT0.000001S')),
     763        ((1, 0, 0, 0, 0, 0, -1),
     764            ('+1-0 +0 -0:00:00.000001', '1 year -00:00:00.000001',
     765             '@ 1 year -0.000001 secs', 'P1YT-0.000001S')),
     766        ((1, 2, 3, 4, 5, 6, 7),
     767            ('+1-2 +3 +4:05:06.000007',
     768             '1 year 2 mons 3 days 04:05:06.000007',
     769             '@ 1 year 2 mons 3 days 4 hours 5 mins 6.000007 secs',
     770             'P1Y2M3DT4H5M6.000007S')),
     771        ((0, 10, 3, -4, 5, -6, 7),
     772            ('+0-10 +3 -3:55:05.999993', '10 mons 3 days -03:55:05.999993',
     773             '@ 10 mons 3 days -3 hours -55 mins -5.999993 secs',
     774             'P10M3DT-3H-55M-5.999993S')),
     775        ((0, -10, -3, 4, -5, 6, -7),
     776            ('-0-10 -3 +3:55:05.999993',
     777             '-10 mons -3 days +03:55:05.999993',
     778             '@ 10 mons 3 days -3 hours -55 mins -5.999993 secs ago',
     779             'P-10M-3DT3H55M5.999993S'))]
     780
     781    def testCastInterval(self):
     782        for result, values in self.intervals:
     783            f = pg.cast_interval
     784            years, mons, days, hours, mins, secs, usecs = result
     785            days += 365 * years + 30 * mons
     786            interval = timedelta(days=days, hours=hours, minutes=mins,
     787                seconds=secs, microseconds=usecs)
     788            for value in values:
     789                self.assertEqual(f(value), interval)
     790
     791
    617792class TestEscapeFunctions(unittest.TestCase):
    618793    """Test pg escape and unescape functions.
     
    677852    """
    678853
     854    def testGetDatestyle(self):
     855        self.assertIsNone(pg.get_datestyle())
     856
     857    def testGetDatestyle(self):
     858        datestyle = pg.get_datestyle()
     859        try:
     860            pg.set_datestyle('ISO, YMD')
     861            self.assertEqual(pg.get_datestyle(), 'ISO, YMD')
     862            pg.set_datestyle('Postgres, MDY')
     863            self.assertEqual(pg.get_datestyle(), 'Postgres, MDY')
     864            pg.set_datestyle('Postgres, DMY')
     865            self.assertEqual(pg.get_datestyle(), 'Postgres, DMY')
     866            pg.set_datestyle('SQL, MDY')
     867            self.assertEqual(pg.get_datestyle(), 'SQL, MDY')
     868            pg.set_datestyle('SQL, DMY')
     869            self.assertEqual(pg.get_datestyle(), 'SQL, DMY')
     870            pg.set_datestyle('German, DMY')
     871            self.assertEqual(pg.get_datestyle(), 'German, DMY')
     872            pg.set_datestyle(None)
     873            self.assertIsNone(pg.get_datestyle())
     874        finally:
     875            pg.set_datestyle(datestyle)
     876
    679877    def testGetDecimalPoint(self):
    680878        r = pg.get_decimal_point()
  • trunk/tests/test_dbapi20.py

    r799 r814  
    2929        pass
    3030
    31 from datetime import datetime
     31from datetime import date, time, datetime, timedelta
     32
     33try:
     34    from datetime import timezone
     35except ImportError:  # Python < 3.2
     36    timezone = None
    3237
    3338try:
     
    402407        values = ('test', pgdb.Binary(b'\xff\x52\xb2'),
    403408            True, 5, 6, 5.7, Decimal('234.234234'), Decimal('75.45'),
    404             '2011-07-17', '15:47:42', '2008-10-20 15:25:35', '15:31:05',
    405             7897234)
    406         table = self.table_prefix + 'booze'
    407         con = self._connect()
    408         try:
    409             cur = con.cursor()
    410             cur.execute("set datestyle to 'iso'")
     409            pgdb.Date(2011, 7, 17), pgdb.Time(15, 47, 42),
     410            pgdb.Timestamp(2008, 10, 20, 15, 25, 35),
     411            pgdb.Interval(15, 31, 5), 7897234)
     412        table = self.table_prefix + 'booze'
     413        con = self._connect()
     414        try:
     415            cur = con.cursor()
     416            cur.execute("set datestyle to iso")
    411417            cur.execute("create table %s ("
    412418                "stringtest varchar,"
     
    444450            self.assertIsInstance(row0[6], Decimal)
    445451            self.assertIsInstance(row0[7], Decimal)
    446             self.assertIsInstance(row0[8], str)
    447             self.assertIsInstance(row0[9], str)
    448             self.assertIsInstance(row0[10], str)
    449             self.assertIsInstance(row0[11], str)
     452            self.assertIsInstance(row0[8], date)
     453            self.assertIsInstance(row0[9], time)
     454            self.assertIsInstance(row0[10], datetime)
     455            self.assertIsInstance(row0[11], timedelta)
    450456        finally:
    451457            con.close()
     
    504510
    505511    def test_datetime(self):
    506         values = ['2011-07-17 15:47:42', datetime(2016, 1, 20, 20, 15, 51)]
    507         table = self.table_prefix + 'booze'
    508         con = self._connect()
    509         try:
    510             cur = con.cursor()
    511             cur.execute("set datestyle to 'iso'")
    512             cur.execute(
    513                 "create table %s (n smallint, ts timestamp)" % table)
    514             params = enumerate(values)
    515             cur.executemany("insert into %s values (%%d,%%s)" % table, params)
    516             cur.execute("select ts from %s order by n" % table)
    517             rows = cur.fetchall()
    518             self.assertEqual(cur.description[0].type_code, pgdb.DATETIME)
    519             self.assertNotEqual(cur.description[0].type_code, pgdb.ARRAY)
    520             self.assertNotEqual(cur.description[0].type_code, pgdb.RECORD)
    521         finally:
    522             con.close()
    523         self.assertEqual(len(rows), len(values))
    524         rows = [row[0] for row in rows]
    525         for inval, outval in zip(values, rows):
    526             if isinstance(inval, datetime):
    527                 inval = inval.strftime('%Y-%m-%d %H:%M:%S')
    528             self.assertEqual(inval, outval)
     512        dt = datetime(2011, 7, 17, 15, 47, 42, 317509)
     513        td = dt - datetime(1970, 1, 1)
     514        table = self.table_prefix + 'booze'
     515        con = self._connect()
     516        try:
     517            cur = con.cursor()
     518            cur.execute("set datestyle to iso")
     519            cur.execute("set datestyle to iso")
     520            cur.execute("create table %s ("
     521                "d date, t time,  ts timestamp,"
     522                "tz timetz, tsz timestamptz, i interval)" % table)
     523            for n in range(3):
     524                values = [dt.date(), dt.time(), dt,
     525                    dt.time(), dt, td]
     526                if timezone:
     527                    values[3] = values[3].replace(tzinfo=timezone.utc)
     528                    values[4] = values[4].replace(tzinfo=timezone.utc)
     529                if n == 0:  # input as objects
     530                    params = values
     531                if n == 1:  # input as text
     532                    params = [v.isoformat() for v in values[:5]]  # as text
     533                    params.append('%d days %d seconds %d microseconds '
     534                        % (td.days, td.seconds, td.microseconds))
     535                elif n == 2:  # input using type helpers
     536                    d = (dt.year, dt.month, dt.day)
     537                    t = (dt.hour, dt.minute, dt.second, dt.microsecond)
     538                    i = (td.days, 0, 0, td.seconds, td.microseconds)
     539                    params = [pgdb.Date(*d), pgdb.Time(*t),
     540                            pgdb.Timestamp(*(d + t)), pgdb.Time(*t),
     541                            pgdb.Timestamp(*(d + t)), pgdb.Interval(*i)]
     542                cur.execute("insert into %s"
     543                    " values (%%s,%%s,%%s,%%s,%%s,%%s)" % table, params)
     544                for datestyle in ('iso', 'postgres, mdy', 'postgres, dmy',
     545                        'sql, mdy', 'sql, dmy', 'german'):
     546                    cur.execute("set datestyle to %s" % datestyle)
     547                    cur.execute("select * from %s" % table)
     548                    d = cur.description
     549                    for i in range(6):
     550                        self.assertEqual(d[i].type_code, pgdb.DATETIME)
     551                        self.assertNotEqual(d[i].type_code, pgdb.STRING)
     552                        self.assertNotEqual(d[i].type_code, pgdb.ARRAY)
     553                        self.assertNotEqual(d[i].type_code, pgdb.RECORD)
     554                    self.assertEqual(d[0].type_code, pgdb.DATE)
     555                    self.assertEqual(d[1].type_code, pgdb.TIME)
     556                    self.assertEqual(d[2].type_code, pgdb.TIMESTAMP)
     557                    self.assertEqual(d[3].type_code, pgdb.TIME)
     558                    self.assertEqual(d[4].type_code, pgdb.TIMESTAMP)
     559                    self.assertEqual(d[5].type_code, pgdb.INTERVAL)
     560                    row = cur.fetchone()
     561                    self.assertEqual(row, tuple(values))
     562                cur.execute("delete from %s" % table)
     563        finally:
     564            con.close()
    529565
    530566    def test_insert_array(self):
Note: See TracChangeset for help on using the changeset viewer.