Changeset 781 for trunk/docs


Ignore:
Timestamp:
Jan 25, 2016, 3:44:52 PM (4 years ago)
Author:
cito
Message:

Add full support for PostgreSQL array types

At the core of this patch is a fast parser for the peculiar syntax of
literal array expressions in PostgreSQL that was added to the C module.
This is not trivial, because PostgreSQL arrays can be multidimensional
and the syntax is different from Python and SQL expressions.

The Python pg and pgdb modules make use of this parser so that they can
return database columns containing PostgreSQL arrays to Python as lists.
Also added quoting methods that allow passing PostgreSQL arrays as lists
to insert()/update() and execute/executemany(). These methods are simpler
and were implemented in Python but needed support from the regex module.

The patch also adds makes getresult() in pg automatically return bytea
values in unescaped form as bytes strings. Before, it was necessary to
call unescape_bytea manually. The pgdb module did this already.

The patch includes some more refactorings and simplifications regarding
the quoting and casting in pg and pgdb.

Some references to antique PostgreSQL types that are not used any more
in the supported PostgreSQL versions have been removed.

Also added documentation and tests for the new features.

Location:
trunk/docs/contents
Files:
4 edited

Legend:

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

    r774 r781  
    4040  "upsert" feature that is new in PostgreSQL 9.5. The new method nicely
    4141  complements the existing get/insert/update/delete() methods.
     42- You can now insert() PostgreSQL arrays as lists in the classic module.
     43- A fast parser for PostgreSQL array output syntax has been added to the
     44  C module. Data in an array type column is now returned as a Python list,
     45  which can be nested if the array has more than one dimension.
    4246- PyGreSQL now supports the JSON and JSONB data types, converting such
    4347  columns automatically to and from Python objects. If you want to insert
     
    4650- The pkey() method of the classic interface now returns tuples instead
    4751  of frozenset. The order of the tuples is like in the primary key index.
     52- The classic module now also returns bytea columns fetched from the database
     53  as byte strings, you don't need to call unescape_bytea() any more.
    4854- The table name that is affixed to the name of the OID column returned
    4955  by the get() method of the classic interface will not automatically
  • trunk/docs/contents/pg/db_wrapper.rst

    r774 r781  
    311311munged as ``oid(table)`` using the actual name of the table.
    312312
     313Note that since PyGreSQL 5.0 this will return the value of an array
     314type column as a Python list.
     315
    313316insert -- insert a row into a database table
    314317--------------------------------------------
     
    332335The dictionary is then reloaded with the values actually inserted in order
    333336to pick up values modified by rules, triggers, etc.
     337
     338Note that since PyGreSQL 5.0 it is possible to insert a value for an
     339array type column by passing it as Python list.
    334340
    335341update -- update a row in a database table
     
    667673-----------------------------------------------------------
    668674
    669 The following method unescapes binary ``bytea`` data strings that
    670 have been retrieved from the database.  You don't need to use this
    671 method on the data returned by :meth:`DB.get` and similar, only if
    672 you query the database directly with :meth:`DB.query`.
    673 
    674675.. method:: DB.unescape_bytea(string)
    675676
     
    680681    :rtype: bytes
    681682
    682 See the module function :func:`pg.unescape_bytea` with the same name.
     683Converts an escaped string representation of binary data stored as ``bytea``
     684into the raw byte string representing the binary data  -- this is the reverse
     685of :meth:`DB.escape_bytea`.  Since the :class:`Query` results will already
     686return unescaped byte strings, you normally don't have to use this method.
    683687
    684688encode/decode_json -- encode and decode JSON data
  • trunk/docs/contents/pg/module.rst

    r774 r781  
    279279As with :func:`escape_string`, this is only used when inserting data directly
    280280into an SQL command string.
     281
    281282Note that there is also a :class:`Connection` method with the same name
    282283which takes connection properties into account.
     
    300301    :raises TypeError: bad argument type, or too many arguments
    301302
    302 Converts an escaped string representation of binary data into binary
    303 data -- the reverse of :func:`escape_bytea`. This is needed when retrieving
    304 ``bytea`` data with one of the :meth:`Query.getresult`,
    305 :meth:`Query.dictresult` or :meth:`Query.namedresult` methods.
    306 
    307 Example::
    308 
    309     picture = unescape_bytea(con.query(
    310           "select img from pictures where name='Garfield'").getresult[0][0])
    311     open('garfield.gif', 'wb').write(picture)
     303Converts an escaped string representation of binary data stored as ``bytea``
     304into the raw byte string representing the binary data  -- this is the reverse
     305of :func:`escape_bytea`.  Since the :class:`Query` results will already
     306return unescaped byte strings, you normally don't have to use this method.
     307
     308Note that there is also a :class:`DB` method with the same name
     309which does exactly the same.
    312310
    313311get/set_decimal -- decimal type to be used for numeric values
  • trunk/docs/contents/pg/query.rst

    r710 r781  
    2727and :meth:`Query.fieldnum` methods.
    2828
     29Note that since PyGreSQL 5.0 this will return the values of array type
     30columns as Python lists.
     31
    2932dictresult -- get query values as list of dictionaries
    3033------------------------------------------------------
     
    4346used as the dictionary index.
    4447
     48Note that since PyGreSQL 5.0 this will return the values of array type
     49columns as Python lists.
     50
    4551namedresult -- get query values as list of named tuples
    4652-------------------------------------------------------
     
    5864This method returns the list of the values returned by the query
    5965with each row returned as a named tuple with proper field names.
     66
     67Note that since PyGreSQL 5.0 this will return the values of array type
     68columns as Python lists.
    6069
    6170.. versionadded:: 4.1
Note: See TracChangeset for help on using the changeset viewer.