Changeset 774 for trunk/docs


Ignore:
Timestamp:
Jan 21, 2016, 1:49:28 PM (3 years ago)
Author:
cito
Message:

Add support for JSON and JSONB to pg and pgdb

This adds all necessary functions to make PyGreSQL automatically
convert between JSON columns and Python objects representing them.

The documentation has also been updated, see there for the details.

Also, tuples automatically bind to ROW expressions in pgdb now.

Location:
trunk/docs/contents
Files:
4 edited

Legend:

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

    r772 r774  
    2626  The column names and types can now also be requested through the
    2727  colnames and coltypes attributes, which are not part of DB-API 2 though.
     28- If you pass a list as one of the parameters to a DB-API 2 cursor, it is
     29  now automatically bound as PostgreSQL ARRAY. If you pass a tuple, then
     30  it will be bound as a PostgreSQL ROW expression.
    2831- Re-activated the shortcut methods of the DB-API connection since they
    2932  can be handy when doing experiments or writing quick scripts. We keep
     
    3134- The tty parameter and attribute of database connections has been
    3235  removed since it is not supported any more since PostgreSQL 7.4.
     36- The classic interface got two new methods get_as_list() and get_as_dict()
     37  returning a database table as a Python list or dict. The amount of data
     38  returned can be controlled with various parameters.
     39- A method upsert() has been added to the DB wrapper class that exploits the
     40  "upsert" feature that is new in PostgreSQL 9.5. The new method nicely
     41  complements the existing get/insert/update/delete() methods.
     42- PyGreSQL now supports the JSON and JSONB data types, converting such
     43  columns automatically to and from Python objects. If you want to insert
     44  Python objects as JSON data using DB-API 2, you should wrap them in the
     45  new Json() type constructor as a hint to PyGreSQL.
    3346- The pkey() method of the classic interface now returns tuples instead
    3447  of frozenset. The order of the tuples is like in the primary key index.
     
    4659  DB wrapper methods has been reduced and security has been improved by
    4760  passing the values to libpq separately as parameters instead of inline.
    48 - The classic interface got two new methods get_as_list() and get_as_dict()
    49   returning a database table as a Python list or dict. The amount of data
    50   returned can be controlled with various parameters.
    5161
    5262Version 4.2
  • trunk/docs/contents/pg/db_wrapper.rst

    r770 r774  
    600600ordering.  In this case the returned dictionary will be an ordinary one.
    601601
    602 escape_literal -- escape a literal string for use within SQL
    603 ------------------------------------------------------------
     602escape_literal/identifier/string/bytea -- escape for SQL
     603--------------------------------------------------------
     604
     605The following methods escape text or binary strings so that they can be
     606inserted directly into an SQL command.  Except for :meth:`DB.escape_byte`,
     607you don't need to call these methods for the strings passed as parameters
     608to :meth:`DB.query`.  You also don't need to call any of these methods
     609when storing data using :meth:`DB.insert` and similar.
    604610
    605611.. method:: DB.escape_literal(string)
     
    617623
    618624.. versionadded:: 4.1
    619 
    620 escape_identifier -- escape an identifier string for use within SQL
    621 -------------------------------------------------------------------
    622625
    623626.. method:: DB.escape_identifier(string)
     
    637640.. versionadded:: 4.1
    638641
    639 escape_string -- escape a string for use within SQL
    640 ---------------------------------------------------
    641 
    642642.. method:: DB.escape_string(string)
    643643
     
    648648    :rtype: str
    649649
    650 Similar to the module function with the same name, but the
    651 behavior of this method is adjusted depending on the connection properties
    652 (such as character encoding).
    653 
    654 escape_bytea -- escape binary data for use within SQL
    655 -----------------------------------------------------
     650Similar to the module function :func:`pg.escape_string` with the same name,
     651but the behavior of this method is adjusted depending on the connection
     652properties (such as character encoding).
    656653
    657654.. method:: DB.escape_bytea(datastring)
     
    663660    :rtype: str
    664661
    665 Similar to the module function with the same name, but the
    666 behavior of this method is adjusted depending on the connection properties
    667 (in particular, whether standard-conforming strings are enabled).
    668 
    669 unescape_bytea -- unescape data that has been retrieved as text
    670 ---------------------------------------------------------------
     662Similar to the module function :func:`pg.escape_bytea` with the same name,
     663but the behavior of this method is adjusted depending on the connection
     664properties (in particular, whether standard-conforming strings are enabled).
     665
     666unescape_bytea -- unescape data retrieved from the database
     667-----------------------------------------------------------
     668
     669The following method unescapes binary ``bytea`` data strings that
     670have been retrieved from the database.  You don't need to use this
     671method on the data returned by :meth:`DB.get` and similar, only if
     672you query the database directly with :meth:`DB.query`.
    671673
    672674.. method:: DB.unescape_bytea(string)
     
    678680    :rtype: bytes
    679681
    680 See the module function with the same name.
     682See the module function :func:`pg.unescape_bytea` with the same name.
     683
     684encode/decode_json -- encode and decode JSON data
     685-------------------------------------------------
     686
     687The following methods can be used to encode end decode data in
     688`JSON <http://www.json.org/>`_ format.
     689
     690.. method:: DB.encode_json(obj)
     691
     692    Encode a Python object for use within SQL as type ``json`` or ``jsonb``
     693
     694    :param obj: Python object that shall be encoded to JSON format
     695    :type obj: dict, list or None
     696    :returns: string representation of the Python object in JSON format
     697    :rtype: str
     698
     699This method serializes a Python object into a JSON formatted string that can
     700be used within SQL.  You don't need to use this method on the data stored
     701with :meth:`DB.insert` and similar, only if you store the data directly as
     702part of an SQL command or parameter with :meth:`DB.query`.  This is the same
     703as the :func:`json.dumps` function from the standard library.
     704
     705.. versionadded:: 5.0
     706
     707.. method:: DB.decode_json(string)
     708
     709    Decode ``json`` or ``jsonb`` data that has been retrieved as text
     710
     711    :param string: JSON formatted string shall be decoded into a Python object
     712    :type string: str
     713    :returns: Python object representing the JSON formatted string
     714    :rtype: dict, list or None
     715
     716This method deserializes a JSON formatted string retrieved as text from the
     717database to a Python object.  You normally don't need to use this method as
     718JSON data is automatically decoded by PyGreSQL.  If you don't want the data
     719to be decoded, then you can cast ``json`` or ``jsonb`` columns to ``text``
     720in PostgreSQL or you can set the decoding function to *None* or a different
     721function using :func:`pg.set_jsondecode`.  By default this is the same as
     722the :func:`json.dumps` function from the standard library.
     723
     724.. versionadded:: 5.0
    681725
    682726use_regtypes -- determine use of regular type names
  • trunk/docs/contents/pg/module.rst

    r710 r774  
    414414    Get the function that converts to named tuples
    415415
    416 This function returns the function used by PyGreSQL to construct the
    417 result of the :meth:`Query.namedresult` method.
     416This returns the function used by PyGreSQL to construct the result of the
     417:meth:`Query.namedresult` method.
     418
     419.. versionadded:: 4.1
    418420
    419421.. function:: set_namedresult(func)
     
    424426
    425427You can use this if you want to create different kinds of named tuples
    426 returned by the :meth:`Query.namedresult` method.
     428returned by the :meth:`Query.namedresult` method.  If you set this function
     429to *None*, then it will become equal to :meth:`Query.getresult`.
     430
     431.. versionadded:: 4.1
     432
     433get/set_jsondecode -- decoding JSON format
     434------------------------------------------
     435
     436.. function:: get_jsondecode()
     437
     438    Get the function that deserializes JSON formatted strings
     439
     440This returns the function used by PyGreSQL to construct Python objects
     441from JSON formatted strings.
     442
     443.. function:: set_jsondecode(func)
     444
     445    Set a function that will deserialize JSON formatted strings
     446
     447    :param func: the function to be used for deserializing JSON strings
     448
     449You can use this if you do not want to deserialize JSON strings coming
     450in from the database, or if want to use a different function than the
     451standard function :meth:`json.loads` or if you want to use it with parameters
     452different from the default ones.  If you set this function to *None*, then
     453the automatic deserialization of JSON strings will be deactivated.
     454
     455.. versionadded:: 5.0
    427456
    428457
  • trunk/docs/contents/pgdb/types.rst

    r710 r774  
    44.. py:currentmodule:: pgdb
    55
    6 .. class:: Type
     6Type constructors
     7-----------------
    78
    8 The :attr:`Cursor.description` attribute returns information about each
    9 of the result columns of a query. The *type_code* must compare equal to one
    10 of the :class:`Type` objects defined below. Type objects can be equal to
    11 more than one type code (e.g. :class:`DATETIME` is equal to the type codes
    12 for date, time and timestamp columns).
     9For binding to an operation's input parameters, PostgreSQL needs to have
     10the input in a particular format.  However, from the parameters to the
     11:meth:`Cursor.execute` and :meth:`Cursor.executemany` methods it is not
     12always obvious as which PostgreSQL data types they shall be bound.
     13For instance, a Python string could be bound as a simple ``char`` value,
     14or also as a ``date`` or a ``time``.  Or a list could be bound as a
     15``array`` or a ``json`` object.  To make the intention clear in such cases,
     16you can wrap the parameters in type helper objects.  PyGreSQL provides the
     17constructors defined below to create such objects that can hold special values.
     18When passed to the cursor methods, PyGreSQL can then detect the proper type
     19of the input parameter and bind it accordingly.
    1320
    14 The :mod:`pgdb` module exports the following constructors and singletons:
     21The :mod:`pgdb` module exports the following constructors that as part of
     22the DB-API 2 standard:
    1523
    1624.. function:: Date(year, month, day)
     
    4250    Construct an object capable of holding a (long) binary string value
    4351
    44 .. class:: STRING
     52Additionally, PyGreSQL provides the following constructors for PostgreSQL
     53specific data types:
     54
     55.. function:: Json(obj, [encode])
     56
     57    Construct a wrapper for holding an object serializable to JSON.
     58
     59    You can pass an optional serialization function as a parameter.
     60    By default, PyGreSQL uses :func:`json.dumps` to serialize it.
     61
     62Example for using a type constructor::
     63
     64    >>> cursor.execute("create table jsondata (data jsonb)")
     65    >>> data = {'id': 1, 'name': 'John Doe', 'kids': ['Johnnie', 'Janie']}
     66    >>> cursor.execute("insert into jsondata values (%s)", [Json(data)])
     67
     68.. note::
     69
     70    SQL NULL values are always represented by the Python *None* singleton
     71    on input and output.
     72
     73Type objects
     74------------
     75
     76.. class:: Type
     77
     78The :attr:`Cursor.description` attribute returns information about each
     79of the result columns of a query. The *type_code* must compare equal to one
     80of the :class:`Type` objects defined below.  Type objects can be equal to
     81more than one type code (e.g. :class:`DATETIME` is equal to the type codes
     82for ``date``, ``time`` and ``timestamp`` columns).
     83
     84The pgdb module exports the following :class:`Type` objects as part of the
     85DB-API 2 standard:
     86
     87.. object:: STRING
    4588
    4689    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
    4790
    48 .. class:: BINARY type
     91.. object:: BINARY
    4992
    5093    Used to describe (long) binary columns (``bytea``)
    5194
    52 .. class:: NUMBER
     95.. object:: NUMBER
    5396
    5497    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
    5598
    56 .. class:: DATETIME
     99.. object:: DATETIME
    57100
    58101    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
    59102
    60 .. class:: ROWID
     103.. object:: ROWID
    61104
    62105    Used to describe the ``oid`` column of PostgreSQL database tables
     
    66109  The following more specific types are not part of the DB-API 2 standard.
    67110
    68 .. class:: BOOL
     111.. object:: BOOL
    69112
    70113    Used to describe ``boolean`` columns
    71114
    72 .. class:: SMALLINT
     115.. object:: SMALLINT
    73116
    74117    Used to describe ``smallint`` columns
    75118
    76 .. class:: INTEGER
     119.. object:: INTEGER
    77120
    78121    Used to describe ``integer`` columns
    79122
    80 .. class:: LONG
     123.. object:: LONG
    81124
    82125    Used to describe ``bigint`` columns
    83126
    84 .. class:: FLOAT
     127.. object:: FLOAT
    85128
    86129    Used to describe ``float`` columns
    87130
    88 .. class:: NUMERIC
     131.. object:: NUMERIC
    89132
    90133    Used to describe ``numeric`` columns
    91134
    92 .. class:: MONEY
     135.. object:: MONEY
    93136
    94137    Used to describe ``money`` columns
    95138
    96 .. class:: DATE
     139.. object:: DATE
    97140
    98141    Used to describe ``date`` columns
    99142
    100 .. class:: TIME
     143.. object:: TIME
    101144
    102145    Used to describe ``time`` columns
    103146
    104 .. class:: TIMESTAMP
     147.. object:: TIMESTAMP
    105148
    106149    Used to describe ``timestamp`` columns
    107150
    108 .. class:: INTERVAL
     151.. object:: INTERVAL
    109152
    110153    Used to describe date and time ``interval`` columns
     154
     155.. object:: JSON
     156
     157    Used to describe ``json`` and ``jsonb`` columns
     158
     159Example for using some type objects::
     160
     161    >>> cursor = con.cursor()
     162    >>> cursor.execute("create table jsondata (created date, data jsonb)")
     163    >>> cursor.execute("select * from jsondata")
     164    >>> (created, data) = (d.type_code for d in cursor.description)
     165    >>> created == DATE
     166    True
     167    >>> created == DATETIME
     168    True
     169    >>> created == TIME
     170    False
     171    >>> data == JSON
     172    True
     173    >>> data == STRING
     174    False
Note: See TracChangeset for help on using the changeset viewer.