Changeset 791 for trunk/pgdb.py


Ignore:
Timestamp:
Jan 27, 2016, 6:09:18 PM (3 years ago)
Author:
cito
Message:

Add support for composite types

Added a fast parser for the composite type input/output syntax, which is
similar to the already existing parser for the array input/output syntax.

The pgdb module now makes use of this parser, converting in both directions
between PostgreSQL records (composite types) and Python (named) tuples.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/pgdb.py

    r788 r791  
    137137
    138138
    139 _cast = {'bool': _cast_bool, 'bytea': unescape_bytea,
     139_cast = {'char': str, 'bpchar': str, 'name': str,
     140    'text': str, 'varchar': str,
     141    'bool': _cast_bool, 'bytea': unescape_bytea,
    140142    'int2': int, 'int4': int, 'serial': int,
    141143    'int8': long, 'json': jsondecode, 'jsonb': jsondecode,
    142144    'oid': long, 'oid8': long,
    143145    'float4': float, 'float8': float,
    144     'numeric': Decimal, 'money': _cast_money}
     146    'numeric': Decimal, 'money': _cast_money,
     147    'record': cast_record}
    145148
    146149
     
    184187                key = '"%s"' % key
    185188            oid = "'%s'::regtype" % self._escape_string(key)
    186         self._src.execute("SELECT oid, typname,"
    187              " typlen, typtype, typcategory, typdelim, typrelid"
    188             " FROM pg_type WHERE oid=%s" % oid)
    189         res = self._src.fetch(1)
     189        try:
     190            self._src.execute("SELECT oid, typname,"
     191                 " typlen, typtype, typcategory, typdelim, typrelid"
     192                " FROM pg_type WHERE oid=%s" % oid)
     193        except ProgrammingError:
     194            res = None
     195        else:
     196            res = self._src.fetch(1)
    190197        if not res:
    191198            raise KeyError('Type %s could not be found' % key)
     
    198205        return res
    199206
     207    def get(self, key, default=None):
     208        """Get the type even if it is not cached."""
     209        try:
     210            return self[key]
     211        except KeyError:
     212            return default
     213
    200214    def columns(self, key):
    201215        """Get the names and types of the columns of composite types."""
    202         typ = self[key]
     216        try:
     217            typ = self[key]
     218        except KeyError:
     219            return None  # this type is not known
    203220        if typ.type != 'c' or not typ.relid:
    204             return []  # this type is not composite
     221            return None  # this type is not composite
    205222        self._src.execute("SELECT attname, atttypid"
    206223            " FROM pg_attribute WHERE attrelid=%s AND attnum>0"
     
    209226            for name, oid in self._src.fetch(-1)]
    210227
    211     @staticmethod
    212     def typecast(typ, value):
     228    def typecast(self, typ, value):
    213229        """Cast value according to database type."""
    214230        if value is None:
     
    216232            return None
    217233        cast = _cast.get(typ)
     234        if cast is str:
     235            return value  # no typecast necessary
    218236        if cast is None:
    219237            if typ.startswith('_'):
     
    221239                cast = _cast.get(typ[1:])
    222240                return cast_array(value, cast)
    223             # no typecast available or necessary
    224             return value
     241            # check whether this is a composite type
     242            cols = self.columns(typ)
     243            if cols:
     244                getcast = self.getcast
     245                cast = [getcast(col.type) for col in cols]
     246                value = cast_record(value, cast)
     247                fields = [col.name for col in cols]
     248                record = namedtuple(typ, fields)
     249                return record(*value)
     250            return value  # no typecast available or necessary
    225251        else:
    226252            return cast(value)
    227253
    228 
    229 _re_array_escape = regex(r'(["\\])')
     254    def getcast(self, key):
     255        """Get a cast function for the given database type."""
     256        if isinstance(key, int):
     257            try:
     258                typ = self[key].name
     259            except KeyError:
     260                return None
     261        else:
     262            typ = key
     263        typecast = self.typecast
     264        return lambda value: typecast(typ, value)
     265
     266
    230267_re_array_quote = regex(r'[{},"\\\s]|^[Nn][Uu][Ll][Ll]$')
     268_re_record_quote = regex(r'[(,"\\]')
     269_re_array_escape = _re_record_escape = regex(r'(["\\])')
    231270
    232271
     
    300339            return "'%s'" % self._quote_array(val)
    301340        if isinstance(val, tuple):
    302             q = self._quote
    303             return 'ROW(%s)' % ','.join(str(q(v)) for v in val)
     341            return "'%s'" % self._quote_record(val)
    304342        try:
    305343            return val.__pg_repr__()
     
    310348    def _quote_array(self, val):
    311349        """Quote value as a literal constant for an array."""
    312         # We could also cast to an array constructor here, but that is more
    313         # verbose and you need to know the base type to build emtpy arrays.
     350        q = self._quote_array_element
     351        return '{%s}' % ','.join(q(v) for v in val)
     352
     353    def _quote_array_element(self, val):
     354        """Quote value using the output syntax for arrays."""
    314355        if isinstance(val, list):
    315             return '{%s}' % ','.join(self._quote_array(v) for v in val)
     356            return self._quote_array(val)
    316357        if val is None:
    317358            return 'null'
     
    320361        if isinstance(val, bool):
    321362            return 't' if val else 'f'
     363        if isinstance(val, tuple):
     364            val = self._quote_record(val)
    322365        if isinstance(val, basestring):
    323366            if not val:
     
    326369                return '"%s"' % _re_array_escape.sub(r'\\\1', val)
    327370            return val
    328         try:
    329             return val.__pg_repr__()
    330         except AttributeError:
    331             raise InterfaceError(
    332                 'do not know how to handle type %s' % type(val))
     371        raise InterfaceError(
     372            'do not know how to handle base type %s' % type(val))
     373
     374    def _quote_record(self, val):
     375        """Quote value as a literal constant for a record."""
     376        q = self._quote_record_element
     377        return '(%s)' % ','.join(q(v) for v in val)
     378
     379    def _quote_record_element(self, val):
     380        """Quote value using the output syntax for records."""
     381        if val is None:
     382            return ''
     383        if isinstance(val, (int, long, float)):
     384            return str(val)
     385        if isinstance(val, bool):
     386            return 't' if val else 'f'
     387        if isinstance(val, list):
     388            val = self._quote_array(val)
     389        if isinstance(val, basestring):
     390            if not val:
     391                return '""'
     392            if _re_record_quote.search(val):
     393                return '"%s"' % _re_record_escape.sub(r'\\\1', val)
     394            return val
     395        raise InterfaceError(
     396            'do not know how to handle component type %s' % type(val))
    333397
    334398    def _quoteparams(self, string, parameters):
Note: See TracChangeset for help on using the changeset viewer.