Author: cito
Date: Thu Jan 21 13:49:28 2016
New Revision: 774
Log:
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.
Modified:
trunk/docs/contents/changelog.rst
trunk/docs/contents/pg/db_wrapper.rst
trunk/docs/contents/pg/module.rst
trunk/docs/contents/pgdb/types.rst
trunk/pg.py
trunk/pgdb.py
trunk/pgmodule.c
trunk/pgtypes.h
trunk/tests/test_classic_dbwrapper.py
trunk/tests/test_classic_functions.py
trunk/tests/test_dbapi20.py
Modified: trunk/docs/contents/changelog.rst
==============================================================================
--- trunk/docs/contents/changelog.rst Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/docs/contents/changelog.rst Thu Jan 21 13:49:28 2016 (r774)
@@ -25,11 +25,24 @@
are now named tuples, i.e. their elements can be also accessed by name.
The column names and types can now also be requested through the
colnames and coltypes attributes, which are not part of DB-API 2 though.
+- If you pass a list as one of the parameters to a DB-API 2 cursor, it is
+ now automatically bound as PostgreSQL ARRAY. If you pass a tuple, then
+ it will be bound as a PostgreSQL ROW expression.
- Re-activated the shortcut methods of the DB-API connection since they
can be handy when doing experiments or writing quick scripts. We keep
them undocumented though and discourage using them in production.
- The tty parameter and attribute of database connections has been
removed since it is not supported any more since PostgreSQL 7.4.
+- The classic interface got two new methods get_as_list() and get_as_dict()
+ returning a database table as a Python list or dict. The amount of data
+ returned can be controlled with various parameters.
+- A method upsert() has been added to the DB wrapper class that exploits the
+ "upsert" feature that is new in PostgreSQL 9.5. The new method nicely
+ complements the existing get/insert/update/delete() methods.
+- PyGreSQL now supports the JSON and JSONB data types, converting such
+ columns automatically to and from Python objects. If you want to insert
+ Python objects as JSON data using DB-API 2, you should wrap them in the
+ new Json() type constructor as a hint to PyGreSQL.
- The pkey() method of the classic interface now returns tuples instead
of frozenset. The order of the tuples is like in the primary key index.
- The table name that is affixed to the name of the OID column returned
@@ -45,9 +58,6 @@
and use less memory. Also, overhead for quoting and escaping values in the
DB wrapper methods has been reduced and security has been improved by
passing the values to libpq separately as parameters instead of inline.
-- The classic interface got two new methods get_as_list() and get_as_dict()
- returning a database table as a Python list or dict. The amount of data
- returned can be controlled with various parameters.
Version 4.2
-----------
Modified: trunk/docs/contents/pg/db_wrapper.rst
==============================================================================
--- trunk/docs/contents/pg/db_wrapper.rst Thu Jan 21 08:04:52 2016
(r773)
+++ trunk/docs/contents/pg/db_wrapper.rst Thu Jan 21 13:49:28 2016
(r774)
@@ -599,8 +599,14 @@
if not specified. You can set *order* to *False* if you don't care about the
ordering. In this case the returned dictionary will be an ordinary one.
-escape_literal -- escape a literal string for use within SQL
-------------------------------------------------------------
+escape_literal/identifier/string/bytea -- escape for SQL
+--------------------------------------------------------
+
+The following methods escape text or binary strings so that they can be
+inserted directly into an SQL command. Except for :meth:`DB.escape_byte`,
+you don't need to call these methods for the strings passed as parameters
+to :meth:`DB.query`. You also don't need to call any of these methods
+when storing data using :meth:`DB.insert` and similar.
.. method:: DB.escape_literal(string)
@@ -617,9 +623,6 @@
.. versionadded:: 4.1
-escape_identifier -- escape an identifier string for use within SQL
--------------------------------------------------------------------
-
.. method:: DB.escape_identifier(string)
Escape a string for use within SQL as an identifier
@@ -636,9 +639,6 @@
.. versionadded:: 4.1
-escape_string -- escape a string for use within SQL
----------------------------------------------------
-
.. method:: DB.escape_string(string)
Escape a string for use within SQL
@@ -647,12 +647,9 @@
:returns: the escaped string
:rtype: str
-Similar to the module function with the same name, but the
-behavior of this method is adjusted depending on the connection properties
-(such as character encoding).
-
-escape_bytea -- escape binary data for use within SQL
------------------------------------------------------
+Similar to the module function :func:`pg.escape_string` with the same name,
+but the behavior of this method is adjusted depending on the connection
+properties (such as character encoding).
.. method:: DB.escape_bytea(datastring)
@@ -662,12 +659,17 @@
:returns: the escaped string
:rtype: str
-Similar to the module function with the same name, but the
-behavior of this method is adjusted depending on the connection properties
-(in particular, whether standard-conforming strings are enabled).
-
-unescape_bytea -- unescape data that has been retrieved as text
----------------------------------------------------------------
+Similar to the module function :func:`pg.escape_bytea` with the same name,
+but the behavior of this method is adjusted depending on the connection
+properties (in particular, whether standard-conforming strings are enabled).
+
+unescape_bytea -- unescape data retrieved from the database
+-----------------------------------------------------------
+
+The following method unescapes binary ``bytea`` data strings that
+have been retrieved from the database. You don't need to use this
+method on the data returned by :meth:`DB.get` and similar, only if
+you query the database directly with :meth:`DB.query`.
.. method:: DB.unescape_bytea(string)
@@ -677,7 +679,49 @@
:returns: byte string containing the binary data
:rtype: bytes
-See the module function with the same name.
+See the module function :func:`pg.unescape_bytea` with the same name.
+
+encode/decode_json -- encode and decode JSON data
+-------------------------------------------------
+
+The following methods can be used to encode end decode data in
+`JSON <http://www.json.org/>`_ format.
+
+.. method:: DB.encode_json(obj)
+
+ Encode a Python object for use within SQL as type ``json`` or ``jsonb``
+
+ :param obj: Python object that shall be encoded to JSON format
+ :type obj: dict, list or None
+ :returns: string representation of the Python object in JSON format
+ :rtype: str
+
+This method serializes a Python object into a JSON formatted string that can
+be used within SQL. You don't need to use this method on the data stored
+with :meth:`DB.insert` and similar, only if you store the data directly as
+part of an SQL command or parameter with :meth:`DB.query`. This is the same
+as the :func:`json.dumps` function from the standard library.
+
+.. versionadded:: 5.0
+
+.. method:: DB.decode_json(string)
+
+ Decode ``json`` or ``jsonb`` data that has been retrieved as text
+
+ :param string: JSON formatted string shall be decoded into a Python object
+ :type string: str
+ :returns: Python object representing the JSON formatted string
+ :rtype: dict, list or None
+
+This method deserializes a JSON formatted string retrieved as text from the
+database to a Python object. You normally don't need to use this method as
+JSON data is automatically decoded by PyGreSQL. If you don't want the data
+to be decoded, then you can cast ``json`` or ``jsonb`` columns to ``text``
+in PostgreSQL or you can set the decoding function to *None* or a different
+function using :func:`pg.set_jsondecode`. By default this is the same as
+the :func:`json.dumps` function from the standard library.
+
+.. versionadded:: 5.0
use_regtypes -- determine use of regular type names
---------------------------------------------------
Modified: trunk/docs/contents/pg/module.rst
==============================================================================
--- trunk/docs/contents/pg/module.rst Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/docs/contents/pg/module.rst Thu Jan 21 13:49:28 2016 (r774)
@@ -413,8 +413,10 @@
Get the function that converts to named tuples
-This function returns the function used by PyGreSQL to construct the
-result of the :meth:`Query.namedresult` method.
+This returns the function used by PyGreSQL to construct the result of the
+:meth:`Query.namedresult` method.
+
+.. versionadded:: 4.1
.. function:: set_namedresult(func)
@@ -423,7 +425,34 @@
:param func: the function to be used to convert results to named tuples
You can use this if you want to create different kinds of named tuples
-returned by the :meth:`Query.namedresult` method.
+returned by the :meth:`Query.namedresult` method. If you set this function
+to *None*, then it will become equal to :meth:`Query.getresult`.
+
+.. versionadded:: 4.1
+
+get/set_jsondecode -- decoding JSON format
+------------------------------------------
+
+.. function:: get_jsondecode()
+
+ Get the function that deserializes JSON formatted strings
+
+This returns the function used by PyGreSQL to construct Python objects
+from JSON formatted strings.
+
+.. function:: set_jsondecode(func)
+
+ Set a function that will deserialize JSON formatted strings
+
+ :param func: the function to be used for deserializing JSON strings
+
+You can use this if you do not want to deserialize JSON strings coming
+in from the database, or if want to use a different function than the
+standard function :meth:`json.loads` or if you want to use it with parameters
+different from the default ones. If you set this function to *None*, then
+the automatic deserialization of JSON strings will be deactivated.
+
+.. versionadded:: 5.0
Module constants
Modified: trunk/docs/contents/pgdb/types.rst
==============================================================================
--- trunk/docs/contents/pgdb/types.rst Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/docs/contents/pgdb/types.rst Thu Jan 21 13:49:28 2016 (r774)
@@ -3,15 +3,23 @@
.. py:currentmodule:: pgdb
-.. class:: Type
+Type constructors
+-----------------
-The :attr:`Cursor.description` attribute returns information about each
-of the result columns of a query. The *type_code* must compare equal to one
-of the :class:`Type` objects defined below. Type objects can be equal to
-more than one type code (e.g. :class:`DATETIME` is equal to the type codes
-for date, time and timestamp columns).
+For binding to an operation's input parameters, PostgreSQL needs to have
+the input in a particular format. However, from the parameters to the
+:meth:`Cursor.execute` and :meth:`Cursor.executemany` methods it is not
+always obvious as which PostgreSQL data types they shall be bound.
+For instance, a Python string could be bound as a simple ``char`` value,
+or also as a ``date`` or a ``time``. Or a list could be bound as a
+``array`` or a ``json`` object. To make the intention clear in such cases,
+you can wrap the parameters in type helper objects. PyGreSQL provides the
+constructors defined below to create such objects that can hold special values.
+When passed to the cursor methods, PyGreSQL can then detect the proper type
+of the input parameter and bind it accordingly.
-The :mod:`pgdb` module exports the following constructors and singletons:
+The :mod:`pgdb` module exports the following constructors that as part of
+the DB-API 2 standard:
.. function:: Date(year, month, day)
@@ -41,23 +49,58 @@
Construct an object capable of holding a (long) binary string value
-.. class:: STRING
+Additionally, PyGreSQL provides the following constructors for PostgreSQL
+specific data types:
+
+.. function:: Json(obj, [encode])
+
+ Construct a wrapper for holding an object serializable to JSON.
+
+ You can pass an optional serialization function as a parameter.
+ By default, PyGreSQL uses :func:`json.dumps` to serialize it.
+
+Example for using a type constructor::
+
+ >>> cursor.execute("create table jsondata (data jsonb)")
+ >>> data = {'id': 1, 'name': 'John Doe', 'kids': ['Johnnie', 'Janie']}
+ >>> cursor.execute("insert into jsondata values (%s)", [Json(data)])
+
+.. note::
+
+ SQL NULL values are always represented by the Python *None* singleton
+ on input and output.
+
+Type objects
+------------
+
+.. class:: Type
+
+The :attr:`Cursor.description` attribute returns information about each
+of the result columns of a query. The *type_code* must compare equal to one
+of the :class:`Type` objects defined below. Type objects can be equal to
+more than one type code (e.g. :class:`DATETIME` is equal to the type codes
+for ``date``, ``time`` and ``timestamp`` columns).
+
+The pgdb module exports the following :class:`Type` objects as part of the
+DB-API 2 standard:
+
+.. object:: STRING
Used to describe columns that are string-based (e.g. ``char``,
``varchar``, ``text``)
-.. class:: BINARY type
+.. object:: BINARY
Used to describe (long) binary columns (``bytea``)
-.. class:: NUMBER
+.. object:: NUMBER
Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``,
``money``)
-.. class:: DATETIME
+.. object:: DATETIME
Used to describe date/time columns (e.g. ``date``, ``time``,
``timestamp``, ``interval``)
-.. class:: ROWID
+.. object:: ROWID
Used to describe the ``oid`` column of PostgreSQL database tables
@@ -65,46 +108,67 @@
The following more specific types are not part of the DB-API 2 standard.
-.. class:: BOOL
+.. object:: BOOL
Used to describe ``boolean`` columns
-.. class:: SMALLINT
+.. object:: SMALLINT
Used to describe ``smallint`` columns
-.. class:: INTEGER
+.. object:: INTEGER
Used to describe ``integer`` columns
-.. class:: LONG
+.. object:: LONG
Used to describe ``bigint`` columns
-.. class:: FLOAT
+.. object:: FLOAT
Used to describe ``float`` columns
-.. class:: NUMERIC
+.. object:: NUMERIC
Used to describe ``numeric`` columns
-.. class:: MONEY
+.. object:: MONEY
Used to describe ``money`` columns
-.. class:: DATE
+.. object:: DATE
Used to describe ``date`` columns
-.. class:: TIME
+.. object:: TIME
Used to describe ``time`` columns
-.. class:: TIMESTAMP
+.. object:: TIMESTAMP
Used to describe ``timestamp`` columns
-.. class:: INTERVAL
+.. object:: INTERVAL
Used to describe date and time ``interval`` columns
+
+.. object:: JSON
+
+ Used to describe ``json`` and ``jsonb`` columns
+
+Example for using some type objects::
+
+ >>> cursor = con.cursor()
+ >>> cursor.execute("create table jsondata (created date, data jsonb)")
+ >>> cursor.execute("select * from jsondata")
+ >>> (created, data) = (d.type_code for d in cursor.description)
+ >>> created == DATE
+ True
+ >>> created == DATETIME
+ True
+ >>> created == TIME
+ False
+ >>> data == JSON
+ True
+ >>> data == STRING
+ False
Modified: trunk/pg.py
==============================================================================
--- trunk/pg.py Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/pg.py Thu Jan 21 13:49:28 2016 (r774)
@@ -38,14 +38,13 @@
from collections import namedtuple
from functools import partial
from operator import itemgetter
+from json import loads as jsondecode, dumps as jsonencode
try:
basestring
except NameError: # Python >= 3.0
basestring = (str, bytes)
-set_decimal(Decimal)
-
try:
from collections import OrderedDict
except ImportError: # Python 2.6 or 3.0
@@ -132,7 +131,6 @@
raise TypeError('This object is read-only')
-
# Auxiliary functions that are independent from a DB connection:
def _oid_key(table):
@@ -156,6 +154,8 @@
return 'money'
if typ.startswith('bytea'):
return 'bytea'
+ if typ.startswith('json'):
+ return 'json'
return 'text'
@@ -164,8 +164,6 @@
row = namedtuple('Row', q.listfields())
return [row(*r) for r in q.getresult()]
-set_namedresult(_namedresult)
-
class _MemoryQuery:
"""Class that embodies a given query result."""
@@ -201,6 +199,15 @@
return _db_error(msg, ProgrammingError)
+# Initialize the C module
+
+set_namedresult(_namedresult)
+set_decimal(Decimal)
+set_jsondecode(jsondecode)
+
+
+# The notification handler
+
class NotificationHandler(object):
"""A PostgreSQL client-side asynchronous notification handler."""
@@ -467,10 +474,14 @@
"""Prepare a bytea parameter."""
return self.escape_bytea(d)
+ def _prepare_json(self, d):
+ """Prepare a json parameter."""
+ return self.encode_json(d)
+
_prepare_funcs = dict( # quote methods for each type
bool=_prepare_bool, date=_prepare_date,
int=_prepare_num, num=_prepare_num, float=_prepare_num,
- money=_prepare_num, bytea=_prepare_bytea)
+ money=_prepare_num, bytea=_prepare_bytea, json=_prepare_json)
def _prepare_param(self, value, typ, params):
"""Prepare and add a parameter to the list."""
@@ -509,6 +520,14 @@
# so we define unescape_bytea as a method as well
unescape_bytea = staticmethod(unescape_bytea)
+ def decode_json(self, s):
+ """Decode a JSON string coming from the database."""
+ return (get_jsondecode() or jsondecode)(s)
+
+ def encode_json(self, d):
+ """Encode a JSON string for use within SQL."""
+ return jsonencode(d)
+
def close(self):
"""Close the database connection."""
# Wraps shared library function so we can track state.
@@ -1441,11 +1460,12 @@
rows = map(getrow, res)
if keytuple or rowtuple:
namedresult = get_namedresult()
- if keytuple:
- keys = namedresult(_MemoryQuery(keys, keyname))
- if rowtuple:
- fields = [f for f in fields if f not in keyset]
- rows = namedresult(_MemoryQuery(rows, fields))
+ if namedresult:
+ if keytuple:
+ keys = namedresult(_MemoryQuery(keys, keyname))
+ if rowtuple:
+ fields = [f for f in fields if f not in keyset]
+ rows = namedresult(_MemoryQuery(rows, fields))
return cls(zip(keys, rows))
def notification_handler(self,
Modified: trunk/pgdb.py
==============================================================================
--- trunk/pgdb.py Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/pgdb.py Thu Jan 21 13:49:28 2016 (r774)
@@ -72,6 +72,7 @@
from decimal import Decimal
from math import isnan, isinf
from collections import namedtuple
+from json import loads as jsondecode, dumps as jsonencode
try:
long
@@ -134,18 +135,11 @@
lambda v: v in '0123456789.-', value)))
-def _cast_bytea(value):
- return unescape_bytea(value)
-
-
-def _cast_float(value):
- return float(value) # this also works with NaN and Infinity
-
-
-_cast = {'bool': _cast_bool, 'bytea': _cast_bytea,
+_cast = {'bool': _cast_bool, 'bytea': unescape_bytea,
'int2': int, 'int4': int, 'serial': int,
- 'int8': long, 'oid': long, 'oid8': long,
- 'float4': _cast_float, 'float8': _cast_float,
+ 'int8': long, 'json': jsondecode, 'jsonb': jsondecode,
+ 'oid': long, 'oid8': long,
+ 'float4': float, 'float8': float,
'numeric': Decimal, 'money': _cast_money}
@@ -246,7 +240,7 @@
def _quote(self, val):
"""Quote value depending on its type."""
- if isinstance(val, (datetime, date, time, timedelta)):
+ if isinstance(val, (datetime, date, time, timedelta, Json)):
val = str(val)
if isinstance(val, basestring):
if isinstance(val, Binary):
@@ -265,9 +259,12 @@
return "'NaN'"
elif val is None:
val = 'NULL'
- elif isinstance(val, (list, tuple)):
+ elif isinstance(val, list):
q = self._quote
val = 'ARRAY[%s]' % ','.join(str(q(v)) for v in val)
+ elif isinstance(val, tuple):
+ q = self._quote
+ val = 'ROW(%s)' % ','.join(str(q(v)) for v in val)
elif Decimal is not float and isinstance(val, Decimal):
pass
elif hasattr(val, '__pg_repr__'):
@@ -299,12 +296,14 @@
def execute(self, operation, parameters=None):
"""Prepare and execute a database operation (query or command)."""
-
- # The parameters may also be specified as list of
- # tuples to e.g. insert multiple rows in a single
- # operation, but this kind of usage is deprecated:
- if (parameters and isinstance(parameters, list) and
- isinstance(parameters[0], tuple)):
+ # The parameters may also be specified as list of tuples to e.g.
+ # insert multiple rows in a single operation, but this kind of
+ # usage is deprecated. We make several plausibility checks because
+ # tuples can also be passed with the meaning of ROW constructors.
+ if (parameters and isinstance(parameters, list)
+ and len(parameters) > 1
+ and all(isinstance(p, tuple) for p in parameters)
+ and all(len(p) == len(parameters[0]) for p in parameters[1:])):
return self.executemany(operation, parameters)
else:
# not a list of tuples
@@ -331,10 +330,9 @@
raise _op_error("can't start transaction")
self._dbcnx._tnx = True
for parameters in seq_of_parameters:
+ sql = operation
if parameters:
- sql = self._quoteparams(operation, parameters)
- else:
- sql = operation
+ sql = self._quoteparams(sql, parameters)
rows = self._src.execute(sql)
if rows: # true if not DML
rowcount += rows
@@ -937,6 +935,7 @@
TIME = Type('time timetz')
TIMESTAMP = Type('timestamp timestamptz datetime abstime')
INTERVAL = Type('interval tinterval timespan reltime')
+JSON = Type('json jsonb')
# Mandatory type helpers defined by DB-API 2 specs:
@@ -952,7 +951,7 @@
def Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0):
- """construct an object holding a time stamp valu."""
+ """Construct an object holding a time stamp value."""
return datetime(year, month, day, hour, minute, second, microsecond)
@@ -962,17 +961,35 @@
def TimeFromTicks(ticks):
- """construct an object holding a time value from the given ticks value."""
+ """Construct an object holding a time value from the given ticks value."""
return Time(*localtime(ticks)[3:6])
def TimestampFromTicks(ticks):
- """construct an object holding a time stamp from the given ticks value."""
+ """Construct an object holding a time stamp from the given ticks value."""
return Timestamp(*localtime(ticks)[:6])
class Binary(bytes):
- """construct an object capable of holding a binary (long) string value."""
+ """Construct an object capable of holding a binary (long) string value."""
+
+
+# Additional type helpers for PyGreSQL:
+
+class Json:
+ """Construct a wrapper for holding an object serializable to JSON."""
+
+ def __init__(self, obj, encode=None):
+ self.obj = obj
+ self.encode = encode or jsonencode
+
+ def __str__(self):
+ obj = self.obj
+ if isinstance(obj, basestring):
+ return obj
+ return self.encode(obj)
+
+ __pg_repr__ = __str__
# If run as script, print some information:
Modified: trunk/pgmodule.c
==============================================================================
--- trunk/pgmodule.c Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/pgmodule.c Thu Jan 21 13:49:28 2016 (r774)
@@ -92,7 +92,8 @@
#endif /* DEFAULT_VARS */
static PyObject *decimal = NULL, /* decimal type */
- *namedresult = NULL; /* function for getting
named results */
+ *namedresult = NULL, /* function for getting
named results */
+ *jsondecode = NULL; /* function for decoding
json strings */
static char decimal_point = '.'; /* decimal point used in money values */
static int use_bool = 0; /* whether or not bool objects shall be returned */
@@ -188,13 +189,14 @@
/* define internal types */
+#define PYGRES_DEFAULT 0
#define PYGRES_INT 1
#define PYGRES_LONG 2
#define PYGRES_FLOAT 3
#define PYGRES_DECIMAL 4
#define PYGRES_MONEY 5
#define PYGRES_BOOL 6
-#define PYGRES_DEFAULT 7
+#define PYGRES_JSON 7
/* --------------------------------------------------------------------- */
/* Internal Functions
*/
@@ -234,52 +236,57 @@
static int *
get_type_array(PGresult *result, int nfields)
{
- int *typ;
+ int *array, *a;
int j;
- if (!(typ = PyMem_Malloc(sizeof(int) * nfields)))
+ if (!(array = PyMem_Malloc(sizeof(int) * nfields)))
{
PyErr_SetString(PyExc_MemoryError, "Memory error in
getresult()");
return NULL;
}
- for (j = 0; j < nfields; j++)
+ for (j = 0, a=array; j < nfields; j++)
{
switch (PQftype(result, j))
{
case INT2OID:
case INT4OID:
case OIDOID:
- typ[j] = PYGRES_INT;
+ *a++ = PYGRES_INT;
break;
case INT8OID:
- typ[j] = PYGRES_LONG;
+ *a++ = PYGRES_LONG;
break;
case FLOAT4OID:
case FLOAT8OID:
- typ[j] = PYGRES_FLOAT;
+ *a++ = PYGRES_FLOAT;
break;
case NUMERICOID:
- typ[j] = PYGRES_DECIMAL;
+ *a++ = PYGRES_DECIMAL;
break;
case CASHOID:
- typ[j] = PYGRES_MONEY;
+ *a++ = PYGRES_MONEY;
break;
case BOOLOID:
- typ[j] = PYGRES_BOOL;
+ *a++ = PYGRES_BOOL;
+ break;
+
+ case JSONOID:
+ case JSONBOID:
+ *a++ = PYGRES_JSON;
break;
default:
- typ[j] = PYGRES_DEFAULT;
+ *a++ = PYGRES_DEFAULT;
}
}
- return typ;
+ return array;
}
/* internal wrapper for the notice receiver callback */
@@ -3631,6 +3638,24 @@
switch (coltypes[j])
{
+ case PYGRES_JSON:
+ if (!jsondecode || /* no JSON
decoder available */
+ PQfformat(self->result,
j) != 0) /* not text */
+ goto default_case;
+ size =
PQgetlength(self->result, i, j);
+#if IS_PY3
+ val = get_decoded_string(s,
size, encoding);
+#else
+ val = get_decoded_string(s,
size, self->encoding);
+#endif
+ if (val) /* was able to decode
*/
+ {
+ tmp_obj =
Py_BuildValue("(O)", val);
+ val =
PyObject_CallObject(jsondecode, tmp_obj);
+ Py_DECREF(tmp_obj);
+ }
+ break;
+
case PYGRES_INT:
val = PyInt_FromString(s, NULL,
10);
break;
@@ -3802,6 +3827,24 @@
switch (coltypes[j])
{
+ case PYGRES_JSON:
+ if (!jsondecode || /* no JSON
decoder available */
+ PQfformat(self->result,
j) != 0) /* not text */
+ goto default_case;
+ size =
PQgetlength(self->result, i, j);
+#if IS_PY3
+ val = get_decoded_string(s,
size, encoding);
+#else
+ val = get_decoded_string(s,
size, self->encoding);
+#endif
+ if (val) /* was able to decode
*/
+ {
+ tmp_obj =
Py_BuildValue("(O)", val);
+ val =
PyObject_CallObject(jsondecode, tmp_obj);
+ Py_DECREF(tmp_obj);
+ }
+ break;
+
case PYGRES_INT:
val = PyInt_FromString(s, NULL,
10);
break;
@@ -3917,28 +3960,28 @@
PyObject *arglist,
*ret;
- /* checks args (args == NULL for an internal call) */
- if (args && !PyArg_ParseTuple(args, ""))
+ if (namedresult)
{
- PyErr_SetString(PyExc_TypeError,
- "Method namedresult() takes no parameters");
- return NULL;
- }
+ /* checks args (args == NULL for an internal call) */
+ if (args && !PyArg_ParseTuple(args, ""))
+ {
+ PyErr_SetString(PyExc_TypeError,
+ "Method namedresult() takes no parameters");
+ return NULL;
+ }
+
+ arglist = Py_BuildValue("(O)", self);
+ ret = PyObject_CallObject(namedresult, arglist);
+ Py_DECREF(arglist);
- if (!namedresult)
+ if (ret == NULL)
+ return NULL;
+ }
+ else
{
- PyErr_SetString(PyExc_TypeError,
- "Named tuples are not supported");
- return NULL;
+ ret = queryGetResult(self, args);
}
- arglist = Py_BuildValue("(O)", self);
- ret = PyObject_CallObject(namedresult, arglist);
- Py_DECREF(arglist);
-
- if (ret == NULL)
- return NULL;
-
return ret;
}
@@ -4439,7 +4482,12 @@
if (PyArg_ParseTuple(args, "O", &func))
{
- if (PyCallable_Check(func))
+ if (func == Py_None)
+ {
+ Py_XDECREF(namedresult); namedresult = NULL;
+ Py_INCREF(Py_None); ret = Py_None;
+ }
+ else if (PyCallable_Check(func))
{
Py_XINCREF(func); Py_XDECREF(namedresult); namedresult
= func;
Py_INCREF(Py_None); ret = Py_None;
@@ -4451,6 +4499,53 @@
return ret;
}
+/* get json decode function */
+static char pgGetJsondecode__doc__[] =
+"get_jsondecode(cls) -- get the function used for decoding json results";
+
+static PyObject *
+pgGetJsondecode(PyObject *self, PyObject *args)
+{
+ PyObject *ret = NULL;
+
+ if (PyArg_ParseTuple(args, ""))
+ {
+ ret = jsondecode ? jsondecode : Py_None;
+ Py_INCREF(ret);
+ }
+
+ return ret;
+}
+
+/* set json decode function */
+static char pgSetJsondecode__doc__[] =
+"set_jsondecode(cls) -- set a function to be used for decoding json results";
+
+static PyObject *
+pgSetJsondecode(PyObject *self, PyObject *args)
+{
+ PyObject *ret = NULL;
+ PyObject *func;
+
+ if (PyArg_ParseTuple(args, "O", &func))
+ {
+ if (func == Py_None)
+ {
+ Py_XDECREF(jsondecode); jsondecode = NULL;
+ Py_INCREF(Py_None); ret = Py_None;
+ }
+ else if (PyCallable_Check(func))
+ {
+ Py_XINCREF(func); Py_XDECREF(jsondecode); jsondecode =
func;
+ Py_INCREF(Py_None); ret = Py_None;
+ }
+ else
+ PyErr_SetString(PyExc_TypeError, "Parameter must be
callable");
+ }
+
+ return ret;
+}
+
#ifdef DEFAULT_VARS
/* gets default host */
@@ -4765,6 +4860,10 @@
pgGetNamedresult__doc__},
{"set_namedresult", (PyCFunction) pgSetNamedresult, METH_VARARGS,
pgSetNamedresult__doc__},
+ {"get_jsondecode", (PyCFunction) pgGetJsondecode, METH_VARARGS,
+ pgGetJsondecode__doc__},
+ {"set_jsondecode", (PyCFunction) pgSetJsondecode, METH_VARARGS,
+ pgSetJsondecode__doc__},
#ifdef DEFAULT_VARS
{"get_defhost", pgGetDefHost, METH_VARARGS, pgGetDefHost__doc__},
Modified: trunk/pgtypes.h
==============================================================================
--- trunk/pgtypes.h Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/pgtypes.h Thu Jan 21 13:49:28 2016 (r774)
@@ -1,8 +1,8 @@
/*
pgtypes - PostgreSQL type definitions
- These are the standard PostgreSQL built-in types,
- extracted from server/catalog/pg_type.h Revision 1.212,
+ These are the standard PostgreSQL 9.5 built-in types,
+ extracted from src/include/catalog/pg_type.h,
because that header file is sometimes not available
or needs other header files to get properly included.
You can also query pg_type to get this information.
@@ -25,7 +25,10 @@
#define XIDOID 28
#define CIDOID 29
#define OIDVECTOROID 30
+#define JSONOID 114
#define XMLOID 142
+#define PGNODETREEOID 194
+#define PGDDLCOMMANDOID 32
#define POINTOID 600
#define LSEGOID 601
#define PATHOID 602
@@ -43,8 +46,10 @@
#define MACADDROID 829
#define INETOID 869
#define CIDROID 650
+#define INT2ARRAYOID 1005
#define INT4ARRAYOID 1007
#define TEXTARRAYOID 1009
+#define OIDARRAYOID 1028
#define FLOAT4ARRAYOID 1021
#define ACLITEMOID 1033
#define CSTRINGARRAYOID 1263
@@ -65,12 +70,18 @@
#define REGOPERATOROID 2204
#define REGCLASSOID 2205
#define REGTYPEOID 2206
+#define REGROLEOID 4096
+#define REGNAMESPACEOID 4089
#define REGTYPEARRAYOID 2211
+#define UUIDOID 2950
+#define LSNOID 3220
#define TSVECTOROID 3614
#define GTSVECTOROID 3642
#define TSQUERYOID 3615
#define REGCONFIGOID 3734
#define REGDICTIONARYOID 3769
+#define JSONBOID 3802
+#define INT4RANGEOID 3904
#define RECORDOID 2249
#define RECORDARRAYOID 2287
#define CSTRINGOID 2275
@@ -78,11 +89,15 @@
#define ANYARRAYOID 2277
#define VOIDOID 2278
#define TRIGGEROID 2279
+#define EVTTRIGGEROID 3838
#define LANGUAGE_HANDLEROID 2280
#define INTERNALOID 2281
#define OPAQUEOID 2282
#define ANYELEMENTOID 2283
#define ANYNONARRAYOID 2776
#define ANYENUMOID 3500
+#define FDW_HANDLEROID 3115
+#define TSM_HANDLEROID 3310
+#define ANYRANGEOID 3831
#endif /* PG_TYPE_H */
Modified: trunk/tests/test_classic_dbwrapper.py
==============================================================================
--- trunk/tests/test_classic_dbwrapper.py Thu Jan 21 08:04:52 2016
(r773)
+++ trunk/tests/test_classic_dbwrapper.py Thu Jan 21 13:49:28 2016
(r774)
@@ -18,6 +18,7 @@
import os
import sys
import tempfile
+import json
import pg # the module under test
@@ -178,8 +179,8 @@
'abort',
'begin',
'cancel', 'clear', 'close', 'commit',
- 'db', 'dbname', 'debug', 'delete',
- 'end', 'endcopy', 'error',
+ 'db', 'dbname', 'debug', 'decode_json', 'delete',
+ 'encode_json', 'end', 'endcopy', 'error',
'escape_bytea', 'escape_identifier',
'escape_literal', 'escape_string',
'fileno',
@@ -283,6 +284,12 @@
def testMethodUnescapeBytea(self):
self.assertEqual(self.db.unescape_bytea(''), b'')
+ def testMethodDecodeJson(self):
+ self.assertEqual(self.db.decode_json('{}'), {})
+
+ def testMethodEncodeJson(self):
+ self.assertEqual(self.db.encode_json({}), '{}')
+
def testMethodQuery(self):
query = self.db.query
query("select 1+1")
@@ -532,6 +539,38 @@
b'\\x746861742773206be47365')
self.assertEqual(f(r'\\x4f007073ff21'), b'\\x4f007073ff21')
+ def testDecodeJson(self):
+ f = self.db.decode_json
+ self.assertIsNone(f('null'))
+ data = {
+ "id": 1, "name": "Foo", "price": 1234.5,
+ "new": True, "note": None,
+ "tags": ["Bar", "Eek"],
+ "stock": {"warehouse": 300, "retail": 20}}
+ text = json.dumps(data)
+ r = f(text)
+ self.assertIsInstance(r, dict)
+ self.assertEqual(r, data)
+ self.assertIsInstance(r['id'], int)
+ self.assertIsInstance(r['name'], unicode)
+ self.assertIsInstance(r['price'], float)
+ self.assertIsInstance(r['new'], bool)
+ self.assertIsInstance(r['tags'], list)
+ self.assertIsInstance(r['stock'], dict)
+
+ def testEncodeJson(self):
+ f = self.db.encode_json
+ self.assertEqual(f(None), 'null')
+ data = {
+ "id": 1, "name": "Foo", "price": 1234.5,
+ "new": True, "note": None,
+ "tags": ["Bar", "Eek"],
+ "stock": {"warehouse": 300, "retail": 20}}
+ text = json.dumps(data)
+ r = f(data)
+ self.assertIsInstance(r, str)
+ self.assertEqual(r, text)
+
def testGetParameter(self):
f = self.db.get_parameter
self.assertRaises(TypeError, f)
@@ -2771,7 +2810,6 @@
self.assertEqual(r, s)
def testUpsertBytea(self):
- query = self.db.query
self.createTable('bytea_test', 'n smallint primary key, data bytea')
s = b"It's all \\ kinds \x00 of\r nasty \xff stuff!\n"
r = dict(n=7, data=s)
@@ -2795,6 +2833,131 @@
self.assertIn('data', r)
self.assertIsNone(r['data'], bytes)
+ def testInsertGetJson(self):
+ try:
+ self.createTable('json_test', 'n smallint primary key, data json')
+ except pg.ProgrammingError as error:
+ if self.db.server_version < 90200:
+ self.skipTest('database does not support json')
+ self.fail(str(error))
+ jsondecode = pg.get_jsondecode()
+ # insert null value
+ r = self.db.insert('json_test', n=0, data=None)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 0)
+ self.assertIn('data', r)
+ self.assertIsNone(r['data'])
+ r = self.db.get('json_test', 0)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 0)
+ self.assertIn('data', r)
+ self.assertIsNone(r['data'])
+ # insert JSON object
+ data = {
+ "id": 1, "name": "Foo", "price": 1234.5,
+ "new": True, "note": None,
+ "tags": ["Bar", "Eek"],
+ "stock": {"warehouse": 300, "retail": 20}}
+ r = self.db.insert('json_test', n=1, data=data)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 1)
+ self.assertIn('data', r)
+ r = r['data']
+ if jsondecode is None:
+ self.assertIsInstance(r, str)
+ r = json.loads(r)
+ self.assertIsInstance(r, dict)
+ self.assertEqual(r, data)
+ self.assertIsInstance(r['id'], int)
+ self.assertIsInstance(r['name'], unicode)
+ self.assertIsInstance(r['price'], float)
+ self.assertIsInstance(r['new'], bool)
+ self.assertIsInstance(r['tags'], list)
+ self.assertIsInstance(r['stock'], dict)
+ r = self.db.get('json_test', 1)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 1)
+ self.assertIn('data', r)
+ r = r['data']
+ if jsondecode is None:
+ self.assertIsInstance(r, str)
+ r = json.loads(r)
+ self.assertIsInstance(r, dict)
+ self.assertEqual(r, data)
+ self.assertIsInstance(r['id'], int)
+ self.assertIsInstance(r['name'], unicode)
+ self.assertIsInstance(r['price'], float)
+ self.assertIsInstance(r['new'], bool)
+ self.assertIsInstance(r['tags'], list)
+ self.assertIsInstance(r['stock'], dict)
+
+ def testInsertGetJsonb(self):
+ try:
+ self.createTable('jsonb_test',
+ 'n smallint primary key, data jsonb')
+ except pg.ProgrammingError as error:
+ if self.db.server_version < 90400:
+ self.skipTest('database does not support jsonb')
+ self.fail(str(error))
+ jsondecode = pg.get_jsondecode()
+ # insert null value
+ r = self.db.insert('jsonb_test', n=0, data=None)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 0)
+ self.assertIn('data', r)
+ self.assertIsNone(r['data'])
+ r = self.db.get('jsonb_test', 0)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 0)
+ self.assertIn('data', r)
+ self.assertIsNone(r['data'])
+ # insert JSON object
+ data = {
+ "id": 1, "name": "Foo", "price": 1234.5,
+ "new": True, "note": None,
+ "tags": ["Bar", "Eek"],
+ "stock": {"warehouse": 300, "retail": 20}}
+ r = self.db.insert('jsonb_test', n=1, data=data)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 1)
+ self.assertIn('data', r)
+ r = r['data']
+ if jsondecode is None:
+ self.assertIsInstance(r, str)
+ r = json.loads(r)
+ self.assertIsInstance(r, dict)
+ self.assertEqual(r, data)
+ self.assertIsInstance(r['id'], int)
+ self.assertIsInstance(r['name'], unicode)
+ self.assertIsInstance(r['price'], float)
+ self.assertIsInstance(r['new'], bool)
+ self.assertIsInstance(r['tags'], list)
+ self.assertIsInstance(r['stock'], dict)
+ r = self.db.get('jsonb_test', 1)
+ self.assertIsInstance(r, dict)
+ self.assertIn('n', r)
+ self.assertEqual(r['n'], 1)
+ self.assertIn('data', r)
+ r = r['data']
+ if jsondecode is None:
+ self.assertIsInstance(r, str)
+ r = json.loads(r)
+ self.assertIsInstance(r, dict)
+ self.assertEqual(r, data)
+ self.assertIsInstance(r['id'], int)
+ self.assertIsInstance(r['name'], unicode)
+ self.assertIsInstance(r['price'], float)
+ self.assertIsInstance(r['new'], bool)
+ self.assertIsInstance(r['tags'], list)
+ self.assertIsInstance(r['stock'], dict)
+
def testNotificationHandler(self):
# the notification handler itself is tested separately
f = self.db.notification_handler
@@ -2882,13 +3045,14 @@
cls.set_option('decimal', float)
not_bool = not pg.get_bool()
cls.set_option('bool', not_bool)
- unnamed_result = lambda q: q.getresult()
- cls.set_option('namedresult', unnamed_result)
+ cls.set_option('namedresult', None)
+ cls.set_option('jsondecode', None)
super(TestDBClassNonStdOpts, cls).setUpClass()
@classmethod
def tearDownClass(cls):
super(TestDBClassNonStdOpts, cls).tearDownClass()
+ cls.reset_option('jsondecode')
cls.reset_option('namedresult')
cls.reset_option('bool')
cls.reset_option('decimal')
Modified: trunk/tests/test_classic_functions.py
==============================================================================
--- trunk/tests/test_classic_functions.py Thu Jan 21 08:04:52 2016
(r773)
+++ trunk/tests/test_classic_functions.py Thu Jan 21 13:49:28 2016
(r774)
@@ -15,6 +15,7 @@
except ImportError:
import unittest
+import json
import re
import pg # the module under test
@@ -181,11 +182,13 @@
def testSetDecimalPoint(self):
point = pg.get_decimal_point()
- pg.set_decimal_point('*')
- r = pg.get_decimal_point()
- pg.set_decimal_point(point)
- self.assertIsInstance(r, str)
- self.assertEqual(r, '*')
+ try:
+ pg.set_decimal_point('*')
+ r = pg.get_decimal_point()
+ self.assertIsInstance(r, str)
+ self.assertEqual(r, '*')
+ finally:
+ pg.set_decimal_point(point)
r = pg.get_decimal_point()
self.assertIsInstance(r, str)
self.assertEqual(r, point)
@@ -196,10 +199,12 @@
def testSetDecimal(self):
decimal_class = pg.Decimal
- pg.set_decimal(int)
- r = pg.get_decimal()
- pg.set_decimal(decimal_class)
- self.assertIs(r, int)
+ try:
+ pg.set_decimal(int)
+ r = pg.get_decimal()
+ self.assertIs(r, int)
+ finally:
+ pg.set_decimal(decimal_class)
r = pg.get_decimal()
self.assertIs(r, decimal_class)
@@ -210,16 +215,18 @@
def testSetBool(self):
use_bool = pg.get_bool()
- pg.set_bool(True)
- r = pg.get_bool()
- pg.set_bool(use_bool)
- self.assertIsInstance(r, bool)
- self.assertIs(r, True)
- pg.set_bool(False)
- r = pg.get_bool()
- pg.set_bool(use_bool)
- self.assertIsInstance(r, bool)
- self.assertIs(r, False)
+ try:
+ pg.set_bool(True)
+ r = pg.get_bool()
+ pg.set_bool(use_bool)
+ self.assertIsInstance(r, bool)
+ self.assertIs(r, True)
+ pg.set_bool(False)
+ r = pg.get_bool()
+ self.assertIsInstance(r, bool)
+ self.assertIs(r, False)
+ finally:
+ pg.set_bool(use_bool)
r = pg.get_bool()
self.assertIsInstance(r, bool)
self.assertIs(r, use_bool)
@@ -231,14 +238,40 @@
def testSetNamedresult(self):
namedresult = pg.get_namedresult()
- f = lambda q: q.getresult()
- pg.set_namedresult(f)
- r = pg.get_namedresult()
- pg.set_namedresult(namedresult)
- self.assertIs(r, f)
+ try:
+ pg.set_namedresult(None)
+ r = pg.get_namedresult()
+ self.assertIsNone(r)
+ f = lambda q: q.getresult()
+ pg.set_namedresult(f)
+ r = pg.get_namedresult()
+ self.assertIs(r, f)
+ self.assertRaises(TypeError, pg.set_namedresult, 'invalid')
+ finally:
+ pg.set_namedresult(namedresult)
r = pg.get_namedresult()
self.assertIs(r, namedresult)
+ def testGetJsondecode(self):
+ r = pg.get_jsondecode()
+ self.assertTrue(callable(r))
+ self.assertIs(r, json.loads)
+
+ def testSetJsondecode(self):
+ jsondecode = pg.get_jsondecode()
+ try:
+ pg.set_jsondecode(None)
+ r = pg.get_jsondecode()
+ self.assertIsNone(r)
+ pg.set_jsondecode(str)
+ r = pg.get_jsondecode()
+ self.assertIs(r, str)
+ self.assertRaises(TypeError, pg.set_jsondecode, 'invalid')
+ finally:
+ pg.set_jsondecode(jsondecode)
+ r = pg.get_jsondecode()
+ self.assertIs(r, jsondecode)
+
class TestModuleConstants(unittest.TestCase):
"""Test the existence of the documented module constants."""
Modified: trunk/tests/test_dbapi20.py
==============================================================================
--- trunk/tests/test_dbapi20.py Thu Jan 21 08:04:52 2016 (r773)
+++ trunk/tests/test_dbapi20.py Thu Jan 21 13:49:28 2016 (r774)
@@ -364,6 +364,7 @@
cur.executemany("insert into %s values (%%s,%%s)" % table, params)
cur.execute("select * from %s order by 1" % table)
rows = cur.fetchall()
+ self.assertEqual(cur.description[1].type_code, pgdb.FLOAT)
finally:
con.close()
self.assertEqual(len(rows), len(values))
@@ -399,6 +400,7 @@
cur.executemany("insert into %s values (%%s,%%s)" % table, params)
cur.execute("select * from %s order by 1" % table)
rows = cur.fetchall()
+ self.assertEqual(cur.description[1].type_code, pgdb.DATETIME)
finally:
con.close()
self.assertEqual(len(rows), len(values))
@@ -408,7 +410,7 @@
inval = inval.strftime('%Y-%m-%d %H:%M:%S')
self.assertEqual(inval, outval)
- def test_array(self):
+ def test_list_binds_as_array(self):
values = ([20000, 25000, 25000, 30000],
[['breakfast', 'consulting'], ['meeting', 'lunch']])
output = ('{20000,25000,25000,30000}',
@@ -425,6 +427,18 @@
con.close()
self.assertEqual(row, output)
+ def test_tuple_binds_as_row(self):
+ values = (1, 2.5, 'this is a test')
+ output = '(1,2.5,"this is a test")'
+ con = self._connect()
+ try:
+ cur = con.cursor()
+ cur.execute("select %s", [values])
+ outval = cur.fetchone()[0]
+ finally:
+ con.close()
+ self.assertEqual(outval, output)
+
def test_custom_type(self):
values = [3, 5, 65]
values = list(map(PgBitString, values))
@@ -458,11 +472,13 @@
cur = con.cursor()
self.assertTrue(pgdb.decimal_type(int) is int)
cur.execute('select 42')
+ self.assertEqual(cur.description[0].type_code, pgdb.INTEGER)
value = cur.fetchone()[0]
self.assertTrue(isinstance(value, int))
self.assertEqual(value, 42)
self.assertTrue(pgdb.decimal_type(float) is float)
cur.execute('select 4.25')
+ self.assertEqual(cur.description[0].type_code, pgdb.NUMBER)
value = cur.fetchone()[0]
self.assertTrue(isinstance(value, float))
self.assertEqual(value, 4.25)
@@ -549,6 +565,7 @@
cur.executemany("insert into %s values (%%s,%%s)" % table, params)
cur.execute("select * from %s order by 1" % table)
rows = cur.fetchall()
+ self.assertEqual(cur.description[1].type_code, pgdb.BOOL)
finally:
con.close()
rows = [row[1] for row in rows]
@@ -556,6 +573,46 @@
values[4] = values[6] = False
self.assertEqual(rows, values)
+ def test_json(self):
+ inval = {"employees":
+ [{"firstName": "John", "lastName": "Doe", "age": 61}]}
+ table = self.table_prefix + 'booze'
+ con = self._connect()
+ try:
+ cur = con.cursor()
+ try:
+ cur.execute("create table %s (jsontest json)" % table)
+ except pgdb.ProgrammingError:
+ self.skipTest('database does not support json')
+ params = (pgdb.Json(inval),)
+ cur.execute("insert into %s values (%%s)" % table, params)
+ cur.execute("select * from %s" % table)
+ outval = cur.fetchone()[0]
+ self.assertEqual(cur.description[0].type_code, pgdb.JSON)
+ finally:
+ con.close()
+ self.assertEqual(inval, outval)
+
+ def test_jsonb(self):
+ inval = {"employees":
+ [{"firstName": "John", "lastName": "Doe", "age": 61}]}
+ table = self.table_prefix + 'booze'
+ con = self._connect()
+ try:
+ cur = con.cursor()
+ try:
+ cur.execute("create table %s (jsonbtest jsonb)" % table)
+ except pgdb.ProgrammingError:
+ self.skipTest('database does not support jsonb')
+ params = (pgdb.Json(inval),)
+ cur.execute("insert into %s values (%%s)" % table, params)
+ cur.execute("select * from %s" % table)
+ outval = cur.fetchone()[0]
+ self.assertEqual(cur.description[0].type_code, pgdb.JSON)
+ finally:
+ con.close()
+ self.assertEqual(inval, outval)
+
def test_execute_edge_cases(self):
con = self._connect()
try:
@@ -563,8 +620,8 @@
sql = 'invalid' # should be ignored with empty parameter list
cur.executemany(sql, [])
sql = 'select %d + 1'
- cur.execute(sql, [(1,)]) # deprecated use of execute()
- self.assertEqual(cur.fetchone()[0], 2)
+ cur.execute(sql, [(1,), (2,)]) # deprecated use of execute()
+ self.assertEqual(cur.fetchone()[0], 3)
sql = 'select 1/0' # cannot be executed
self.assertRaises(pgdb.ProgrammingError, cur.execute, sql)
cur.close()
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
