Changeset 774 for trunk/pgdb.py


Ignore:
Timestamp:
Jan 21, 2016, 1:49:28 PM (4 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.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/pgdb.py

    r773 r774  
    7373from math import isnan, isinf
    7474from collections import namedtuple
     75from json import loads as jsondecode, dumps as jsonencode
    7576
    7677try:
     
    135136
    136137
    137 def _cast_bytea(value):
    138     return unescape_bytea(value)
    139 
    140 
    141 def _cast_float(value):
    142     return float(value)  # this also works with NaN and Infinity
    143 
    144 
    145 _cast = {'bool': _cast_bool, 'bytea': _cast_bytea,
     138_cast = {'bool': _cast_bool, 'bytea': unescape_bytea,
    146139    'int2': int, 'int4': int, 'serial': int,
    147     'int8': long, 'oid': long, 'oid8': long,
    148     'float4': _cast_float, 'float8': _cast_float,
     140    'int8': long, 'json': jsondecode, 'jsonb': jsondecode,
     141    'oid': long, 'oid8': long,
     142    'float4': float, 'float8': float,
    149143    'numeric': Decimal, 'money': _cast_money}
    150144
     
    247241    def _quote(self, val):
    248242        """Quote value depending on its type."""
    249         if isinstance(val, (datetime, date, time, timedelta)):
     243        if isinstance(val, (datetime, date, time, timedelta, Json)):
    250244            val = str(val)
    251245        if isinstance(val, basestring):
     
    266260        elif val is None:
    267261            val = 'NULL'
    268         elif isinstance(val, (list, tuple)):
     262        elif isinstance(val, list):
    269263            q = self._quote
    270264            val = 'ARRAY[%s]' % ','.join(str(q(v)) for v in val)
     265        elif isinstance(val, tuple):
     266            q = self._quote
     267            val = 'ROW(%s)' % ','.join(str(q(v)) for v in val)
    271268        elif Decimal is not float and isinstance(val, Decimal):
    272269            pass
     
    300297    def execute(self, operation, parameters=None):
    301298        """Prepare and execute a database operation (query or command)."""
    302 
    303         # The parameters may also be specified as list of
    304         # tuples to e.g. insert multiple rows in a single
    305         # operation, but this kind of usage is deprecated:
    306         if (parameters and isinstance(parameters, list) and
    307                 isinstance(parameters[0], tuple)):
     299        # The parameters may also be specified as list of tuples to e.g.
     300        # insert multiple rows in a single operation, but this kind of
     301        # usage is deprecated.  We make several plausibility checks because
     302        # tuples can also be passed with the meaning of ROW constructors.
     303        if (parameters and isinstance(parameters, list)
     304                and len(parameters) > 1
     305                and all(isinstance(p, tuple) for p in parameters)
     306                and all(len(p) == len(parameters[0]) for p in parameters[1:])):
    308307            return self.executemany(operation, parameters)
    309308        else:
     
    332331                self._dbcnx._tnx = True
    333332            for parameters in seq_of_parameters:
     333                sql = operation
    334334                if parameters:
    335                     sql = self._quoteparams(operation, parameters)
    336                 else:
    337                     sql = operation
     335                    sql = self._quoteparams(sql, parameters)
    338336                rows = self._src.execute(sql)
    339337                if rows:  # true if not DML
     
    938936TIMESTAMP = Type('timestamp timestamptz datetime abstime')
    939937INTERVAL = Type('interval tinterval timespan reltime')
     938JSON = Type('json jsonb')
    940939
    941940
     
    953952
    954953def Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0):
    955     """construct an object holding a time stamp valu."""
     954    """Construct an object holding a time stamp value."""
    956955    return datetime(year, month, day, hour, minute, second, microsecond)
    957956
     
    963962
    964963def TimeFromTicks(ticks):
    965     """construct an object holding a time value from the given ticks value."""
     964    """Construct an object holding a time value from the given ticks value."""
    966965    return Time(*localtime(ticks)[3:6])
    967966
    968967
    969968def TimestampFromTicks(ticks):
    970     """construct an object holding a time stamp from the given ticks value."""
     969    """Construct an object holding a time stamp from the given ticks value."""
    971970    return Timestamp(*localtime(ticks)[:6])
    972971
    973972
    974973class Binary(bytes):
    975     """construct an object capable of holding a binary (long) string value."""
     974    """Construct an object capable of holding a binary (long) string value."""
     975
     976
     977# Additional type helpers for PyGreSQL:
     978
     979class Json:
     980    """Construct a wrapper for holding an object serializable to JSON."""
     981
     982    def __init__(self, obj, encode=None):
     983        self.obj = obj
     984        self.encode = encode or jsonencode
     985
     986    def __str__(self):
     987        obj = self.obj
     988        if isinstance(obj, basestring):
     989            return obj
     990        return self.encode(obj)
     991
     992    __pg_repr__ = __str__
    976993
    977994
Note: See TracChangeset for help on using the changeset viewer.