Changeset 814 for trunk/docs


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/docs/contents
Files:
12 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::
Note: See TracChangeset for help on using the changeset viewer.