source: trunk/pg.py @ 774

Last change on this file since 774 was 774, checked in by cito, 4 years ago

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.

  • Property svn:keywords set to Id
File size: 56.8 KB
Line 
1#! /usr/bin/python
2#
3# pg.py
4#
5# $Id: pg.py 774 2016-01-21 18:49:28Z cito $
6#
7
8"""PyGreSQL classic interface.
9
10This pg module implements some basic database management stuff.
11It includes the _pg module and builds on it, providing the higher
12level wrapper class named DB with additional functionality.
13This is known as the "classic" ("old style") PyGreSQL interface.
14For a DB-API 2 compliant interface use the newer pgdb module.
15"""
16
17# Copyright (c) 1997-2016 by D'Arcy J.M. Cain.
18#
19# Contributions made by Ch. Zwerschke and others.
20#
21# The notification handler is based on pgnotify which is
22# Copyright (c) 2001 Ng Pheng Siong. All rights reserved.
23#
24# Permission to use, copy, modify, and distribute this software and its
25# documentation for any purpose and without fee is hereby granted,
26# provided that the above copyright notice appear in all copies and that
27# both that copyright notice and this permission notice appear in
28# supporting documentation.
29
30from __future__ import print_function
31
32from _pg import *
33
34import select
35import warnings
36
37from decimal import Decimal
38from collections import namedtuple
39from functools import partial
40from operator import itemgetter
41from json import loads as jsondecode, dumps as jsonencode
42
43try:
44    basestring
45except NameError:  # Python >= 3.0
46    basestring = (str, bytes)
47
48try:
49    from collections import OrderedDict
50except ImportError:  # Python 2.6 or 3.0
51    OrderedDict = dict
52
53
54    class AttrDict(dict):
55        """Simple read-only ordered dictionary for storing attribute names."""
56
57        def __init__(self, *args, **kw):
58            if len(args) > 1 or kw:
59                raise TypeError
60            items = args[0] if args else []
61            if isinstance(items, dict):
62                raise TypeError
63            items = list(items)
64            self._keys = [item[0] for item in items]
65            dict.__init__(self, items)
66            self._read_only = True
67            error = self._read_only_error
68            self.clear = self.update = error
69            self.pop = self.setdefault = self.popitem = error
70
71        def __setitem__(self, key, value):
72            if self._read_only:
73                self._read_only_error()
74            dict.__setitem__(self, key, value)
75
76        def __delitem__(self, key):
77            if self._read_only:
78                self._read_only_error()
79            dict.__delitem__(self, key)
80
81        def __iter__(self):
82            return iter(self._keys)
83
84        def keys(self):
85            return list(self._keys)
86
87        def values(self):
88            return [self[key] for key in self]
89
90        def items(self):
91            return [(key, self[key]) for key in self]
92
93        def iterkeys(self):
94            return self.__iter__()
95
96        def itervalues(self):
97            return iter(self.values())
98
99        def iteritems(self):
100            return iter(self.items())
101
102        @staticmethod
103        def _read_only_error(*args, **kw):
104            raise TypeError('This object is read-only')
105
106else:
107
108     class AttrDict(OrderedDict):
109        """Simple read-only ordered dictionary for storing attribute names."""
110
111        def __init__(self, *args, **kw):
112            self._read_only = False
113            OrderedDict.__init__(self, *args, **kw)
114            self._read_only = True
115            error = self._read_only_error
116            self.clear = self.update = error
117            self.pop = self.setdefault = self.popitem = error
118
119        def __setitem__(self, key, value):
120            if self._read_only:
121                self._read_only_error()
122            OrderedDict.__setitem__(self, key, value)
123
124        def __delitem__(self, key):
125            if self._read_only:
126                self._read_only_error()
127            OrderedDict.__delitem__(self, key)
128
129        @staticmethod
130        def _read_only_error(*args, **kw):
131            raise TypeError('This object is read-only')
132
133
134# Auxiliary functions that are independent from a DB connection:
135
136def _oid_key(table):
137    """Build oid key from a table name."""
138    return 'oid(%s)' % table
139
140
141def _simpletype(typ):
142    """Determine a simplified name a pg_type name."""
143    if typ.startswith('bool'):
144        return 'bool'
145    if typ.startswith(('abstime', 'date', 'interval', 'timestamp')):
146        return 'date'
147    if typ.startswith(('cid', 'oid', 'int', 'xid')):
148        return 'int'
149    if typ.startswith('float'):
150        return 'float'
151    if typ.startswith('numeric'):
152        return 'num'
153    if typ.startswith('money'):
154        return 'money'
155    if typ.startswith('bytea'):
156        return 'bytea'
157    if typ.startswith('json'):
158        return 'json'
159    return 'text'
160
161
162def _namedresult(q):
163    """Get query result as named tuples."""
164    row = namedtuple('Row', q.listfields())
165    return [row(*r) for r in q.getresult()]
166
167
168class _MemoryQuery:
169    """Class that embodies a given query result."""
170
171    def __init__(self, result, fields):
172        """Create query from given result rows and field names."""
173        self.result = result
174        self.fields = fields
175
176    def listfields(self):
177        """Return the stored field names of this query."""
178        return self.fields
179
180    def getresult(self):
181        """Return the stored result of this query."""
182        return self.result
183
184
185def _db_error(msg, cls=DatabaseError):
186    """Return DatabaseError with empty sqlstate attribute."""
187    error = cls(msg)
188    error.sqlstate = None
189    return error
190
191
192def _int_error(msg):
193    """Return InternalError."""
194    return _db_error(msg, InternalError)
195
196
197def _prg_error(msg):
198    """Return ProgrammingError."""
199    return _db_error(msg, ProgrammingError)
200
201
202# Initialize the C module
203
204set_namedresult(_namedresult)
205set_decimal(Decimal)
206set_jsondecode(jsondecode)
207
208
209# The notification handler
210
211class NotificationHandler(object):
212    """A PostgreSQL client-side asynchronous notification handler."""
213
214    def __init__(self, db, event, callback=None,
215            arg_dict=None, timeout=None, stop_event=None):
216        """Initialize the notification handler.
217
218        You must pass a PyGreSQL database connection, the name of an
219        event (notification channel) to listen for and a callback function.
220
221        You can also specify a dictionary arg_dict that will be passed as
222        the single argument to the callback function, and a timeout value
223        in seconds (a floating point number denotes fractions of seconds).
224        If it is absent or None, the callers will never time out.  If the
225        timeout is reached, the callback function will be called with a
226        single argument that is None.  If you set the timeout to zero,
227        the handler will poll notifications synchronously and return.
228
229        You can specify the name of the event that will be used to signal
230        the handler to stop listening as stop_event. By default, it will
231        be the event name prefixed with 'stop_'.
232        """
233        self.db = db
234        self.event = event
235        self.stop_event = stop_event or 'stop_%s' % event
236        self.listening = False
237        self.callback = callback
238        if arg_dict is None:
239            arg_dict = {}
240        self.arg_dict = arg_dict
241        self.timeout = timeout
242
243    def __del__(self):
244        self.unlisten()
245
246    def close(self):
247        """Stop listening and close the connection."""
248        if self.db:
249            self.unlisten()
250            self.db.close()
251            self.db = None
252
253    def listen(self):
254        """Start listening for the event and the stop event."""
255        if not self.listening:
256            self.db.query('listen "%s"' % self.event)
257            self.db.query('listen "%s"' % self.stop_event)
258            self.listening = True
259
260    def unlisten(self):
261        """Stop listening for the event and the stop event."""
262        if self.listening:
263            self.db.query('unlisten "%s"' % self.event)
264            self.db.query('unlisten "%s"' % self.stop_event)
265            self.listening = False
266
267    def notify(self, db=None, stop=False, payload=None):
268        """Generate a notification.
269
270        Optionally, you can pass a payload with the notification.
271
272        If you set the stop flag, a stop notification will be sent that
273        will cause the handler to stop listening.
274
275        Note: If the notification handler is running in another thread, you
276        must pass a different database connection since PyGreSQL database
277        connections are not thread-safe.
278        """
279        if self.listening:
280            if not db:
281                db = self.db
282            q = 'notify "%s"' % (self.stop_event if stop else self.event)
283            if payload:
284                q += ", '%s'" % payload
285            return db.query(q)
286
287    def __call__(self):
288        """Invoke the notification handler.
289
290        The handler is a loop that listens for notifications on the event
291        and stop event channels.  When either of these notifications are
292        received, its associated 'pid', 'event' and 'extra' (the payload
293        passed with the notification) are inserted into its arg_dict
294        dictionary and the callback is invoked with this dictionary as
295        a single argument.  When the handler receives a stop event, it
296        stops listening to both events and return.
297
298        In the special case that the timeout of the handler has been set
299        to zero, the handler will poll all events synchronously and return.
300        If will keep listening until it receives a stop event.
301
302        Note: If you run this loop in another thread, don't use the same
303        database connection for database operations in the main thread.
304        """
305        self.listen()
306        poll = self.timeout == 0
307        if not poll:
308            rlist = [self.db.fileno()]
309        while self.listening:
310            if poll or select.select(rlist, [], [], self.timeout)[0]:
311                while self.listening:
312                    notice = self.db.getnotify()
313                    if not notice:  # no more messages
314                        break
315                    event, pid, extra = notice
316                    if event not in (self.event, self.stop_event):
317                        self.unlisten()
318                        raise _db_error(
319                            'Listening for "%s" and "%s", but notified of "%s"'
320                            % (self.event, self.stop_event, event))
321                    if event == self.stop_event:
322                        self.unlisten()
323                    self.arg_dict.update(pid=pid, event=event, extra=extra)
324                    self.callback(self.arg_dict)
325                if poll:
326                    break
327            else:   # we timed out
328                self.unlisten()
329                self.callback(None)
330
331
332def pgnotify(*args, **kw):
333    """Same as NotificationHandler, under the traditional name."""
334    warnings.warn("pgnotify is deprecated, use NotificationHandler instead",
335        DeprecationWarning, stacklevel=2)
336    return NotificationHandler(*args, **kw)
337
338
339# The actual PostGreSQL database connection interface:
340
341class DB(object):
342    """Wrapper class for the _pg connection type."""
343
344    def __init__(self, *args, **kw):
345        """Create a new connection
346
347        You can pass either the connection parameters or an existing
348        _pg or pgdb connection. This allows you to use the methods
349        of the classic pg interface with a DB-API 2 pgdb connection.
350        """
351        if not args and len(kw) == 1:
352            db = kw.get('db')
353        elif not kw and len(args) == 1:
354            db = args[0]
355        else:
356            db = None
357        if db:
358            if isinstance(db, DB):
359                db = db.db
360            else:
361                try:
362                    db = db._cnx
363                except AttributeError:
364                    pass
365        if not db or not hasattr(db, 'db') or not hasattr(db, 'query'):
366            db = connect(*args, **kw)
367            self._closeable = True
368        else:
369            self._closeable = False
370        self.db = db
371        self.dbname = db.db
372        self._regtypes = False
373        self._attnames = {}
374        self._pkeys = {}
375        self._privileges = {}
376        self._args = args, kw
377        self.debug = None  # For debugging scripts, this can be set
378            # * to a string format specification (e.g. in CGI set to "%s<BR>"),
379            # * to a file object to write debug statements or
380            # * to a callable object which takes a string argument
381            # * to any other true value to just print debug statements
382
383    def __getattr__(self, name):
384        # All undefined members are same as in underlying connection:
385        if self.db:
386            return getattr(self.db, name)
387        else:
388            raise _int_error('Connection is not valid')
389
390    def __dir__(self):
391        # Custom dir function including the attributes of the connection:
392        attrs = set(self.__class__.__dict__)
393        attrs.update(self.__dict__)
394        attrs.update(dir(self.db))
395        return sorted(attrs)
396
397    # Context manager methods
398
399    def __enter__(self):
400        """Enter the runtime context. This will start a transactio."""
401        self.begin()
402        return self
403
404    def __exit__(self, et, ev, tb):
405        """Exit the runtime context. This will end the transaction."""
406        if et is None and ev is None and tb is None:
407            self.commit()
408        else:
409            self.rollback()
410
411    # Auxiliary methods
412
413    def _do_debug(self, *args):
414        """Print a debug message"""
415        if self.debug:
416            s = '\n'.join(args)
417            if isinstance(self.debug, basestring):
418                print(self.debug % s)
419            elif hasattr(self.debug, 'write'):
420                self.debug.write(s + '\n')
421            elif callable(self.debug):
422                self.debug(s)
423            else:
424                print(s)
425
426    def _escape_qualified_name(self, s):
427        """Escape a qualified name.
428
429        Escapes the name for use as an SQL identifier, unless the
430        name contains a dot, in which case the name is ambiguous
431        (could be a qualified name or just a name with a dot in it)
432        and must be quoted manually by the caller.
433        """
434        if '.' not in s:
435            s = self.escape_identifier(s)
436        return s
437
438    @staticmethod
439    def _make_bool(d):
440        """Get boolean value corresponding to d."""
441        return bool(d) if get_bool() else ('t' if d else 'f')
442
443    _bool_true_values = frozenset('t true 1 y yes on'.split())
444
445    def _prepare_bool(self, d):
446        """Prepare a boolean parameter."""
447        if isinstance(d, basestring):
448            if not d:
449                return None
450            d = d.lower() in self._bool_true_values
451        return 't' if d else 'f'
452
453    _date_literals = frozenset('current_date current_time'
454        ' current_timestamp localtime localtimestamp'.split())
455
456    def _prepare_date(self, d):
457        """Prepare a date parameter."""
458        if not d:
459            return None
460        if isinstance(d, basestring) and d.lower() in self._date_literals:
461            raise ValueError
462        return d
463
464    _num_types = frozenset('int float num money'
465        ' int2 int4 int8 float4 float8 numeric money'.split())
466
467    def _prepare_num(self, d):
468        """Prepare a numeric parameter."""
469        if not d and d != 0:
470            return None
471        return d
472
473    def _prepare_bytea(self, d):
474        """Prepare a bytea parameter."""
475        return self.escape_bytea(d)
476
477    def _prepare_json(self, d):
478        """Prepare a json parameter."""
479        return self.encode_json(d)
480
481    _prepare_funcs = dict(  # quote methods for each type
482        bool=_prepare_bool, date=_prepare_date,
483        int=_prepare_num, num=_prepare_num, float=_prepare_num,
484        money=_prepare_num, bytea=_prepare_bytea, json=_prepare_json)
485
486    def _prepare_param(self, value, typ, params):
487        """Prepare and add a parameter to the list."""
488        if value is not None and typ != 'text':
489            prepare = self._prepare_funcs[typ]
490            try:
491                value = prepare(self, value)
492            except ValueError:
493                return value
494        params.append(value)
495        return '$%d' % len(params)
496
497    def _list_params(self, params):
498        """Create a human readable parameter list."""
499        return ', '.join('$%d=%r' % (n, v) for n, v in enumerate(params, 1))
500
501    @staticmethod
502    def _prepare_qualified_param(name, param):
503        """Quote parameter representing a qualified name.
504
505        Escapes the name for use as an SQL parameter, unless the
506        name contains a dot, in which case the name is ambiguous
507        (could be a qualified name or just a name with a dot in it)
508        and must be quoted manually by the caller.
509
510        """
511        if isinstance(param, int):
512            param = "$%d" % param
513        if '.' not in name:
514            param = 'quote_ident(%s)' % (param,)
515        return param
516
517    # Public methods
518
519    # escape_string and escape_bytea exist as methods,
520    # so we define unescape_bytea as a method as well
521    unescape_bytea = staticmethod(unescape_bytea)
522
523    def decode_json(self, s):
524        """Decode a JSON string coming from the database."""
525        return (get_jsondecode() or jsondecode)(s)
526
527    def encode_json(self, d):
528        """Encode a JSON string for use within SQL."""
529        return jsonencode(d)
530
531    def close(self):
532        """Close the database connection."""
533        # Wraps shared library function so we can track state.
534        if self._closeable:
535            if self.db:
536                self.db.close()
537                self.db = None
538            else:
539                raise _int_error('Connection already closed')
540
541    def reset(self):
542        """Reset connection with current parameters.
543
544        All derived queries and large objects derived from this connection
545        will not be usable after this call.
546
547        """
548        if self.db:
549            self.db.reset()
550        else:
551            raise _int_error('Connection already closed')
552
553    def reopen(self):
554        """Reopen connection to the database.
555
556        Used in case we need another connection to the same database.
557        Note that we can still reopen a database that we have closed.
558
559        """
560        # There is no such shared library function.
561        if self._closeable:
562            db = connect(*self._args[0], **self._args[1])
563            if self.db:
564                self.db.close()
565            self.db = db
566
567    def begin(self, mode=None):
568        """Begin a transaction."""
569        qstr = 'BEGIN'
570        if mode:
571            qstr += ' ' + mode
572        return self.query(qstr)
573
574    start = begin
575
576    def commit(self):
577        """Commit the current transaction."""
578        return self.query('COMMIT')
579
580    end = commit
581
582    def rollback(self, name=None):
583        """Roll back the current transaction."""
584        qstr = 'ROLLBACK'
585        if name:
586            qstr += ' TO ' + name
587        return self.query(qstr)
588
589    abort = rollback
590
591    def savepoint(self, name):
592        """Define a new savepoint within the current transaction."""
593        return self.query('SAVEPOINT ' + name)
594
595    def release(self, name):
596        """Destroy a previously defined savepoint."""
597        return self.query('RELEASE ' + name)
598
599    def get_parameter(self, parameter):
600        """Get the value of a run-time parameter.
601
602        If the parameter is a string, the return value will also be a string
603        that is the current setting of the run-time parameter with that name.
604
605        You can get several parameters at once by passing a list, set or dict.
606        When passing a list of parameter names, the return value will be a
607        corresponding list of parameter settings.  When passing a set of
608        parameter names, a new dict will be returned, mapping these parameter
609        names to their settings.  Finally, if you pass a dict as parameter,
610        its values will be set to the current parameter settings corresponding
611        to its keys.
612
613        By passing the special name 'all' as the parameter, you can get a dict
614        of all existing configuration parameters.
615        """
616        if isinstance(parameter, basestring):
617            parameter = [parameter]
618            values = None
619        elif isinstance(parameter, (list, tuple)):
620            values = []
621        elif isinstance(parameter, (set, frozenset)):
622            values = {}
623        elif isinstance(parameter, dict):
624            values = parameter
625        else:
626            raise TypeError(
627                'The parameter must be a string, list, set or dict')
628        if not parameter:
629            raise TypeError('No parameter has been specified')
630        params = {} if isinstance(values, dict) else []
631        for key in parameter:
632            param = key.strip().lower() if isinstance(
633                key, basestring) else None
634            if not param:
635                raise TypeError('Invalid parameter')
636            if param == 'all':
637                q = 'SHOW ALL'
638                values = self.db.query(q).getresult()
639                values = dict(value[:2] for value in values)
640                break
641            if isinstance(values, dict):
642                params[param] = key
643            else:
644                params.append(param)
645        else:
646            for param in params:
647                q = 'SHOW %s' % (param,)
648                value = self.db.query(q).getresult()[0][0]
649                if values is None:
650                    values = value
651                elif isinstance(values, list):
652                    values.append(value)
653                else:
654                    values[params[param]] = value
655        return values
656
657    def set_parameter(self, parameter, value=None, local=False):
658        """Set the value of a run-time parameter.
659
660        If the parameter and the value are strings, the run-time parameter
661        will be set to that value.  If no value or None is passed as a value,
662        then the run-time parameter will be restored to its default value.
663
664        You can set several parameters at once by passing a list of parameter
665        names, together with a single value that all parameters should be
666        set to or with a corresponding list of values.  You can also pass
667        the parameters as a set if you only provide a single value.
668        Finally, you can pass a dict with parameter names as keys.  In this
669        case, you should not pass a value, since the values for the parameters
670        will be taken from the dict.
671
672        By passing the special name 'all' as the parameter, you can reset
673        all existing settable run-time parameters to their default values.
674
675        If you set local to True, then the command takes effect for only the
676        current transaction.  After commit() or rollback(), the session-level
677        setting takes effect again.  Setting local to True will appear to
678        have no effect if it is executed outside a transaction, since the
679        transaction will end immediately.
680        """
681        if isinstance(parameter, basestring):
682            parameter = {parameter: value}
683        elif isinstance(parameter, (list, tuple)):
684            if isinstance(value, (list, tuple)):
685                parameter = dict(zip(parameter, value))
686            else:
687                parameter = dict.fromkeys(parameter, value)
688        elif isinstance(parameter, (set, frozenset)):
689            if isinstance(value, (list, tuple, set, frozenset)):
690                value = set(value)
691                if len(value) == 1:
692                    value = value.pop()
693            if not(value is None or isinstance(value, basestring)):
694                raise ValueError('A single value must be specified'
695                    ' when parameter is a set')
696            parameter = dict.fromkeys(parameter, value)
697        elif isinstance(parameter, dict):
698            if value is not None:
699                raise ValueError('A value must not be specified'
700                    ' when parameter is a dictionary')
701        else:
702            raise TypeError(
703                'The parameter must be a string, list, set or dict')
704        if not parameter:
705            raise TypeError('No parameter has been specified')
706        params = {}
707        for key, value in parameter.items():
708            param = key.strip().lower() if isinstance(
709                key, basestring) else None
710            if not param:
711                raise TypeError('Invalid parameter')
712            if param == 'all':
713                if value is not None:
714                    raise ValueError('A value must ot be specified'
715                        " when parameter is 'all'")
716                params = {'all': None}
717                break
718            params[param] = value
719        local = ' LOCAL' if local else ''
720        for param, value in params.items():
721            if value is None:
722                q = 'RESET%s %s' % (local, param)
723            else:
724                q = 'SET%s %s TO %s' % (local, param, value)
725            self._do_debug(q)
726            self.db.query(q)
727
728    def query(self, qstr, *args):
729        """Execute a SQL command string.
730
731        This method simply sends a SQL query to the database.  If the query is
732        an insert statement that inserted exactly one row into a table that
733        has OIDs, the return value is the OID of the newly inserted row.
734        If the query is an update or delete statement, or an insert statement
735        that did not insert exactly one row in a table with OIDs, then the
736        number of rows affected is returned as a string.  If it is a statement
737        that returns rows as a result (usually a select statement, but maybe
738        also an "insert/update ... returning" statement), this method returns
739        a Query object that can be accessed via getresult() or dictresult()
740        or simply printed.  Otherwise, it returns `None`.
741
742        The query can contain numbered parameters of the form $1 in place
743        of any data constant.  Arguments given after the query string will
744        be substituted for the corresponding numbered parameter.  Parameter
745        values can also be given as a single list or tuple argument.
746        """
747        # Wraps shared library function for debugging.
748        if not self.db:
749            raise _int_error('Connection is not valid')
750        self._do_debug(qstr)
751        return self.db.query(qstr, args)
752
753    def pkey(self, table, composite=False, flush=False):
754        """Get or set the primary key of a table.
755
756        Single primary keys are returned as strings unless you
757        set the composite flag.  Composite primary keys are always
758        represented as tuples.  Note that this raises a KeyError
759        if the table does not have a primary key.
760
761        If flush is set then the internal cache for primary keys will
762        be flushed.  This may be necessary after the database schema or
763        the search path has been changed.
764        """
765        pkeys = self._pkeys
766        if flush:
767            pkeys.clear()
768            self._do_debug('The pkey cache has been flushed')
769        try:  # cache lookup
770            pkey = pkeys[table]
771        except KeyError:  # cache miss, check the database
772            q = ("SELECT a.attname, a.attnum, i.indkey FROM pg_index i"
773                " JOIN pg_attribute a ON a.attrelid = i.indrelid"
774                " AND a.attnum = ANY(i.indkey)"
775                " AND NOT a.attisdropped"
776                " WHERE i.indrelid=%s::regclass"
777                " AND i.indisprimary ORDER BY a.attnum") % (
778                    self._prepare_qualified_param(table, 1),)
779            pkey = self.db.query(q, (table,)).getresult()
780            if not pkey:
781                raise KeyError('Table %s has no primary key' % table)
782            # we want to use the order defined in the primary key index here,
783            # not the order as defined by the columns in the table
784            if len(pkey) > 1:
785                indkey = [int(k) for k in pkey[0][2].split()]
786                pkey = sorted(pkey, key=lambda row: indkey.index(row[1]))
787                pkey = tuple(row[0] for row in pkey)
788            else:
789                pkey = pkey[0][0]
790            pkeys[table] = pkey  # cache it
791        if composite and not isinstance(pkey, tuple):
792            pkey = (pkey,)
793        return pkey
794
795    def get_databases(self):
796        """Get list of databases in the system."""
797        return [s[0] for s in
798            self.db.query('SELECT datname FROM pg_database').getresult()]
799
800    def get_relations(self, kinds=None):
801        """Get list of relations in connected database of specified kinds.
802
803        If kinds is None or empty, all kinds of relations are returned.
804        Otherwise kinds can be a string or sequence of type letters
805        specifying which kind of relations you want to list.
806        """
807        where = " AND r.relkind IN (%s)" % ','.join(
808            ["'%s'" % k for k in kinds]) if kinds else ''
809        q = ("SELECT quote_ident(s.nspname)||'.'||quote_ident(r.relname)"
810            " FROM pg_class r"
811            " JOIN pg_namespace s ON s.oid = r.relnamespace"
812            " WHERE s.nspname NOT SIMILAR"
813            " TO 'pg/_%%|information/_schema' ESCAPE '/' %s"
814            " ORDER BY s.nspname, r.relname") % where
815        return [r[0] for r in self.db.query(q).getresult()]
816
817    def get_tables(self):
818        """Return list of tables in connected database."""
819        return self.get_relations('r')
820
821    def get_attnames(self, table, flush=False):
822        """Given the name of a table, dig out the set of attribute names.
823
824        Returns a read-only dictionary of attribute names (the names are
825        the keys, the values are the names of the attributes' types)
826        with the column names in the proper order if you iterate over it.
827
828        If flush is set, then the internal cache for attribute names will
829        be flushed. This may be necessary after the database schema or
830        the search path has been changed.
831
832        By default, only a limited number of simple types will be returned.
833        You can get the regular types after calling use_regtypes(True).
834        """
835        attnames = self._attnames
836        if flush:
837            attnames.clear()
838            self._do_debug('The attnames cache has been flushed')
839        try:  # cache lookup
840            names = attnames[table]
841        except KeyError:  # cache miss, check the database
842            q = ("SELECT a.attname, t.typname%s"
843                " FROM pg_attribute a"
844                " JOIN pg_type t ON t.oid = a.atttypid"
845                " WHERE a.attrelid = %s::regclass"
846                " AND (a.attnum > 0 OR a.attname = 'oid')"
847                " AND NOT a.attisdropped ORDER BY a.attnum") % (
848                    '::regtype' if self._regtypes else '',
849                    self._prepare_qualified_param(table, 1))
850            names = self.db.query(q, (table,)).getresult()
851            if not self._regtypes:
852                names = ((name, _simpletype(typ)) for name, typ in names)
853            names = AttrDict(names)
854            attnames[table] = names  # cache it
855        return names
856
857    def use_regtypes(self, regtypes=None):
858        """Use regular type names instead of simplified type names."""
859        if regtypes is None:
860            return self._regtypes
861        else:
862            regtypes = bool(regtypes)
863            if regtypes != self._regtypes:
864                self._regtypes = regtypes
865                self._attnames.clear()
866            return regtypes
867
868    def has_table_privilege(self, table, privilege='select'):
869        """Check whether current user has specified table privilege."""
870        privilege = privilege.lower()
871        try:  # ask cache
872            return self._privileges[(table, privilege)]
873        except KeyError:  # cache miss, ask the database
874            q = "SELECT has_table_privilege(%s, $2)" % (
875                self._prepare_qualified_param(table, 1),)
876            q = self.db.query(q, (table, privilege))
877            ret = q.getresult()[0][0] == self._make_bool(True)
878            self._privileges[(table, privilege)] = ret  # cache it
879            return ret
880
881    def get(self, table, row, keyname=None):
882        """Get a row from a database table or view.
883
884        This method is the basic mechanism to get a single row.  It assumes
885        that the keyname specifies a unique row.  It must be the name of a
886        single column or a tuple of column names.  If the keyname is not
887        specified, then the primary key for the table is used.
888
889        If row is a dictionary, then the value for the key is taken from it.
890        Otherwise, the row must be a single value or a tuple of values
891        corresponding to the passed keyname or primary key.  The fetched row
892        from the table will be returned as a new dictionary or used to replace
893        the existing values when row was passed as aa dictionary.
894
895        The OID is also put into the dictionary if the table has one, but
896        in order to allow the caller to work with multiple tables, it is
897        munged as "oid(table)" using the actual name of the table.
898        """
899        if table.endswith('*'):  # hint for descendant tables can be ignored
900            table = table[:-1].rstrip()
901        attnames = self.get_attnames(table)
902        qoid = _oid_key(table) if 'oid' in attnames else None
903        if keyname and isinstance(keyname, basestring):
904            keyname = (keyname,)
905        if qoid and isinstance(row, dict) and qoid in row and 'oid' not in row:
906            row['oid'] = row[qoid]
907        if not keyname:
908            try:  # if keyname is not specified, try using the primary key
909                keyname = self.pkey(table, True)
910            except KeyError:  # the table has no primary key
911                # try using the oid instead
912                if qoid and isinstance(row, dict) and 'oid' in row:
913                    keyname = ('oid',)
914                else:
915                    raise _prg_error('Table %s has no primary key' % table)
916            else:  # the table has a primary key
917                # check whether all key columns have values
918                if isinstance(row, dict) and not set(keyname).issubset(row):
919                    # try using the oid instead
920                    if qoid and 'oid' in row:
921                        keyname = ('oid',)
922                    else:
923                        raise KeyError(
924                            'Missing value in row for specified keyname')
925        if not isinstance(row, dict):
926            if not isinstance(row, (tuple, list)):
927                row = [row]
928            if len(keyname) != len(row):
929                raise KeyError(
930                    'Differing number of items in keyname and row')
931            row = dict(zip(keyname, row))
932        params = []
933        param = partial(self._prepare_param, params=params)
934        col = self.escape_identifier
935        what = 'oid, *' if qoid else '*'
936        where = ' AND '.join('%s = %s' % (
937            col(k), param(row[k], attnames[k])) for k in keyname)
938        if 'oid' in row:
939            if qoid:
940                row[qoid] = row['oid']
941            del row['oid']
942        q = 'SELECT %s FROM %s WHERE %s LIMIT 1' % (
943            what, self._escape_qualified_name(table), where)
944        self._do_debug(q, params)
945        q = self.db.query(q, params)
946        res = q.dictresult()
947        if not res:
948            raise _db_error('No such record in %s\nwhere %s\nwith %s' % (
949                table, where, self._list_params(params)))
950        for n, value in res[0].items():
951            if qoid and n == 'oid':
952                n = qoid
953            elif value is not None and attnames.get(n) == 'bytea':
954                value = self.unescape_bytea(value)
955            row[n] = value
956        return row
957
958    def insert(self, table, row=None, **kw):
959        """Insert a row into a database table.
960
961        This method inserts a row into a table.  The name of the table must
962        be passed as the first parameter.  The other parameters are used for
963        providing the data of the row that shall be inserted into the table.
964        If a dictionary is supplied as the second parameter, it starts with
965        that.  Otherwise it uses a blank dictionary. Either way the dictionary
966        is updated from the keywords.
967
968        The dictionary is then reloaded with the values actually inserted in
969        order to pick up values modified by rules, triggers, etc.
970        """
971        if table.endswith('*'):  # hint for descendant tables can be ignored
972            table = table[:-1].rstrip()
973        if row is None:
974            row = {}
975        row.update(kw)
976        if 'oid' in row:
977            del row['oid']  # do not insert oid
978        attnames = self.get_attnames(table)
979        qoid = _oid_key(table) if 'oid' in attnames else None
980        params = []
981        param = partial(self._prepare_param, params=params)
982        col = self.escape_identifier
983        names, values = [], []
984        for n in attnames:
985            if n in row:
986                names.append(col(n))
987                values.append(param(row[n], attnames[n]))
988        names, values = ', '.join(names), ', '.join(values)
989        ret = 'oid, *' if qoid else '*'
990        q = 'INSERT INTO %s (%s) VALUES (%s) RETURNING %s' % (
991            self._escape_qualified_name(table), names, values, ret)
992        self._do_debug(q, params)
993        q = self.db.query(q, params)
994        res = q.dictresult()
995        if res:  # this should always be true
996            for n, value in res[0].items():
997                if qoid and n == 'oid':
998                    n = qoid
999                elif value is not None and attnames.get(n) == 'bytea':
1000                    value = self.unescape_bytea(value)
1001                row[n] = value
1002        return row
1003
1004    def update(self, table, row=None, **kw):
1005        """Update an existing row in a database table.
1006
1007        Similar to insert but updates an existing row.  The update is based
1008        on the primary key of the table or the OID value as munged by get
1009        or passed as keyword.
1010
1011        The dictionary is then modified to reflect any changes caused by the
1012        update due to triggers, rules, default values, etc.
1013        """
1014        if table.endswith('*'):
1015            table = table[:-1].rstrip()  # need parent table name
1016        attnames = self.get_attnames(table)
1017        qoid = _oid_key(table) if 'oid' in attnames else None
1018        if row is None:
1019            row = {}
1020        elif 'oid' in row:
1021            del row['oid']  # only accept oid key from named args for safety
1022        row.update(kw)
1023        if qoid and qoid in row and 'oid' not in row:
1024            row['oid'] = row[qoid]
1025        try:  # try using the primary key
1026            keyname = self.pkey(table, True)
1027        except KeyError:  # the table has no primary key
1028            # try using the oid instead
1029            if qoid and 'oid' in row:
1030                keyname = ('oid',)
1031            else:
1032                raise _prg_error('Table %s has no primary key' % table)
1033        else:  # the table has a primary key
1034            # check whether all key columns have values
1035            if not set(keyname).issubset(row):
1036                # try using the oid instead
1037                if qoid and 'oid' in row:
1038                    keyname = ('oid',)
1039                else:
1040                    raise KeyError('Missing primary key in row')
1041        params = []
1042        param = partial(self._prepare_param, params=params)
1043        col = self.escape_identifier
1044        where = ' AND '.join('%s = %s' % (
1045            col(k), param(row[k], attnames[k])) for k in keyname)
1046        if 'oid' in row:
1047            if qoid:
1048                row[qoid] = row['oid']
1049            del row['oid']
1050        values = []
1051        keyname = set(keyname)
1052        for n in attnames:
1053            if n in row and n not in keyname:
1054                values.append('%s = %s' % (col(n), param(row[n], attnames[n])))
1055        if not values:
1056            return row
1057        values = ', '.join(values)
1058        ret = 'oid, *' if qoid else '*'
1059        q = 'UPDATE %s SET %s WHERE %s RETURNING %s' % (
1060            self._escape_qualified_name(table), values, where, ret)
1061        self._do_debug(q, params)
1062        q = self.db.query(q, params)
1063        res = q.dictresult()
1064        if res:  # may be empty when row does not exist
1065            for n, value in res[0].items():
1066                if qoid and n == 'oid':
1067                    n = qoid
1068                elif value is not None and attnames.get(n) == 'bytea':
1069                    value = self.unescape_bytea(value)
1070                row[n] = value
1071        return row
1072
1073    def upsert(self, table, row=None, **kw):
1074        """Insert a row into a database table with conflict resolution
1075
1076        This method inserts a row into a table, but instead of raising a
1077        ProgrammingError exception in case a row with the same primary key
1078        already exists, an update will be executed instead.  This will be
1079        performed as a single atomic operation on the database, so race
1080        conditions can be avoided.
1081
1082        Like the insert method, the first parameter is the name of the
1083        table and the second parameter can be used to pass the values to
1084        be inserted as a dictionary.
1085
1086        Unlike the insert und update statement, keyword parameters are not
1087        used to modify the dictionary, but to specify which columns shall
1088        be updated in case of a conflict, and in which way:
1089
1090        A value of False or None means the column shall not be updated,
1091        a value of True means the column shall be updated with the value
1092        that has been proposed for insertion, i.e. has been passed as value
1093        in the dictionary.  Columns that are not specified by keywords but
1094        appear as keys in the dictionary are also updated like in the case
1095        keywords had been passed with the value True.
1096
1097        So if in the case of a conflict you want to update every column that
1098        has been passed in the dictionary row , you would call upsert(table, row).
1099        If you don't want to do anything in case of a conflict, i.e. leave
1100        the existing row as it is, call upsert(table, row, **dict.fromkeys(row)).
1101
1102        If you need more fine-grained control of what gets updated, you can
1103        also pass strings in the keyword parameters.  These strings will
1104        be used as SQL expressions for the update columns.  In these
1105        expressions you can refer to the value that already exists in
1106        the table by prefixing the column name with "included.", and to
1107        the value that has been proposed for insertion by prefixing the
1108        column name with the "excluded."
1109
1110        The dictionary is modified in any case to reflect the values in
1111        the database after the operation has completed.
1112
1113        Note: The method uses the PostgreSQL "upsert" feature which is
1114        only available since PostgreSQL 9.5.
1115        """
1116        if table.endswith('*'):  # hint for descendant tables can be ignored
1117            table = table[:-1].rstrip()
1118        if row is None:
1119            row = {}
1120        if 'oid' in row:
1121            del row['oid']  # do not insert oid
1122        if 'oid' in kw:
1123            del kw['oid']  # do not update oid
1124        attnames = self.get_attnames(table)
1125        qoid = _oid_key(table) if 'oid' in attnames else None
1126        params = []
1127        param = partial(self._prepare_param,params=params)
1128        col = self.escape_identifier
1129        names, values, updates = [], [], []
1130        for n in attnames:
1131            if n in row:
1132                names.append(col(n))
1133                values.append(param(row[n], attnames[n]))
1134        names, values = ', '.join(names), ', '.join(values)
1135        try:
1136            keyname = self.pkey(table, True)
1137        except KeyError:
1138            raise _prg_error('Table %s has no primary key' % table)
1139        target = ', '.join(col(k) for k in keyname)
1140        update = []
1141        keyname = set(keyname)
1142        keyname.add('oid')
1143        for n in attnames:
1144            if n not in keyname:
1145                value = kw.get(n, True)
1146                if value:
1147                    if not isinstance(value, basestring):
1148                        value = 'excluded.%s' % col(n)
1149                    update.append('%s = %s' % (col(n), value))
1150        if not values:
1151            return row
1152        do = 'update set %s' % ', '.join(update) if update else 'nothing'
1153        ret = 'oid, *' if qoid else '*'
1154        q = ('INSERT INTO %s AS included (%s) VALUES (%s)'
1155            ' ON CONFLICT (%s) DO %s RETURNING %s') % (
1156                self._escape_qualified_name(table), names, values,
1157                target, do, ret)
1158        self._do_debug(q, params)
1159        try:
1160            q = self.db.query(q, params)
1161        except ProgrammingError:
1162            if self.server_version < 90500:
1163                raise _prg_error(
1164                    'Upsert operation is not supported by PostgreSQL version')
1165            raise  # re-raise original error
1166        res = q.dictresult()
1167        if res:  # may be empty with "do nothing"
1168            for n, value in res[0].items():
1169                if qoid and n == 'oid':
1170                    n = qoid
1171                elif value is not None and attnames.get(n) == 'bytea':
1172                    value = self.unescape_bytea(value)
1173                row[n] = value
1174        else:
1175            self.get(table, row)
1176        return row
1177
1178    def clear(self, table, row=None):
1179        """Clear all the attributes to values determined by the types.
1180
1181        Numeric types are set to 0, Booleans are set to false, and everything
1182        else is set to the empty string.  If the row argument is present,
1183        it is used as the row dictionary and any entries matching attribute
1184        names are cleared with everything else left unchanged.
1185        """
1186        # At some point we will need a way to get defaults from a table.
1187        if row is None:
1188            row = {}  # empty if argument is not present
1189        attnames = self.get_attnames(table)
1190        for n, t in attnames.items():
1191            if n == 'oid':
1192                continue
1193            if t in self._num_types:
1194                row[n] = 0
1195            elif t == 'bool':
1196                row[n] = self._make_bool(False)
1197            else:
1198                row[n] = ''
1199        return row
1200
1201    def delete(self, table, row=None, **kw):
1202        """Delete an existing row in a database table.
1203
1204        This method deletes the row from a table.  It deletes based on the
1205        primary key of the table or the OID value as munged by get() or
1206        passed as keyword.
1207
1208        The return value is the number of deleted rows (i.e. 0 if the row
1209        did not exist and 1 if the row was deleted).
1210
1211        Note that if the row cannot be deleted because e.g. it is still
1212        referenced by another table, this method raises a ProgrammingError.
1213        """
1214        if table.endswith('*'):  # hint for descendant tables can be ignored
1215            table = table[:-1].rstrip()
1216        attnames = self.get_attnames(table)
1217        qoid = _oid_key(table) if 'oid' in attnames else None
1218        if row is None:
1219            row = {}
1220        elif 'oid' in row:
1221            del row['oid']  # only accept oid key from named args for safety
1222        row.update(kw)
1223        if qoid and qoid in row and 'oid' not in row:
1224            row['oid'] = row[qoid]
1225        try:  # try using the primary key
1226            keyname = self.pkey(table, True)
1227        except KeyError:  # the table has no primary key
1228            # try using the oid instead
1229            if qoid and 'oid' in row:
1230                keyname = ('oid',)
1231            else:
1232                raise _prg_error('Table %s has no primary key' % table)
1233        else:  # the table has a primary key
1234            # check whether all key columns have values
1235            if not set(keyname).issubset(row):
1236                # try using the oid instead
1237                if qoid and 'oid' in row:
1238                    keyname = ('oid',)
1239                else:
1240                    raise KeyError('Missing primary key in row')
1241        params = []
1242        param = partial(self._prepare_param, params=params)
1243        col = self.escape_identifier
1244        where = ' AND '.join('%s = %s' % (
1245            col(k), param(row[k], attnames[k])) for k in keyname)
1246        if 'oid' in row:
1247            if qoid:
1248                row[qoid] = row['oid']
1249            del row['oid']
1250        q = 'DELETE FROM %s WHERE %s' % (
1251            self._escape_qualified_name(table), where)
1252        self._do_debug(q, params)
1253        res = self.db.query(q, params)
1254        return int(res)
1255
1256    def truncate(self, table, restart=False, cascade=False, only=False):
1257        """Empty a table or set of tables.
1258
1259        This method quickly removes all rows from the given table or set
1260        of tables.  It has the same effect as an unqualified DELETE on each
1261        table, but since it does not actually scan the tables it is faster.
1262        Furthermore, it reclaims disk space immediately, rather than requiring
1263        a subsequent VACUUM operation. This is most useful on large tables.
1264
1265        If restart is set to True, sequences owned by columns of the truncated
1266        table(s) are automatically restarted.  If cascade is set to True, it
1267        also truncates all tables that have foreign-key references to any of
1268        the named tables.  If the parameter only is not set to True, all the
1269        descendant tables (if any) will also be truncated. Optionally, a '*'
1270        can be specified after the table name to explicitly indicate that
1271        descendant tables are included.
1272        """
1273        if isinstance(table, basestring):
1274            only = {table: only}
1275            table = [table]
1276        elif isinstance(table, (list, tuple)):
1277            if isinstance(only, (list, tuple)):
1278                only = dict(zip(table, only))
1279            else:
1280                only = dict.fromkeys(table, only)
1281        elif isinstance(table, (set, frozenset)):
1282            only = dict.fromkeys(table, only)
1283        else:
1284            raise TypeError('The table must be a string, list or set')
1285        if not (restart is None or isinstance(restart, (bool, int))):
1286            raise TypeError('Invalid type for the restart option')
1287        if not (cascade is None or isinstance(cascade, (bool, int))):
1288            raise TypeError('Invalid type for the cascade option')
1289        tables = []
1290        for t in table:
1291            u = only.get(t)
1292            if not (u is None or isinstance(u, (bool, int))):
1293                raise TypeError('Invalid type for the only option')
1294            if t.endswith('*'):
1295                if u:
1296                    raise ValueError(
1297                        'Contradictory table name and only options')
1298                t = t[:-1].rstrip()
1299            t = self._escape_qualified_name(t)
1300            if u:
1301                t = 'ONLY %s' % t
1302            tables.append(t)
1303        q = ['TRUNCATE', ', '.join(tables)]
1304        if restart:
1305            q.append('RESTART IDENTITY')
1306        if cascade:
1307            q.append('CASCADE')
1308        q = ' '.join(q)
1309        self._do_debug(q)
1310        return self.db.query(q)
1311
1312    def get_as_list(self, table, what=None, where=None,
1313            order=None, limit=None, offset=None, scalar=False):
1314        """Get a table as a list.
1315
1316        This gets a convenient representation of the table as a list
1317        of named tuples in Python.  You only need to pass the name of
1318        the table (or any other SQL expression returning rows).  Note that
1319        by default this will return the full content of the table which
1320        can be huge and overflow your memory.  However, you can control
1321        the amount of data returned using the other optional parameters.
1322
1323        The parameter 'what' can restrict the query to only return a
1324        subset of the table columns.  It can be a string, list or a tuple.
1325        The parameter 'where' can restrict the query to only return a
1326        subset of the table rows.  It can be a string, list or a tuple
1327        of SQL expressions that all need to be fulfilled.  The parameter
1328        'order' specifies the ordering of the rows.  It can also be a
1329        other string, list or a tuple.  If no ordering is specified,
1330        the result will be ordered by the primary key(s) or all columns
1331        if no primary key exists.  You can set 'order' to False if you
1332        don't care about the ordering.  The parameters 'limit' and 'offset'
1333        can be integers specifying the maximum number of rows returned
1334        and a number of rows skipped over.
1335
1336        If you set the 'scalar' option to True, then instead of the
1337        named tuples you will get the first items of these tuples.
1338        This is useful if the result has only one column anyway.
1339        """
1340        if not table:
1341            raise TypeError('The table name is missing')
1342        if what:
1343            if isinstance(what, (list, tuple)):
1344                what = ', '.join(map(str, what))
1345            if order is None:
1346                order = what
1347        else:
1348            what = '*'
1349        q = ['SELECT', what, 'FROM', table]
1350        if where:
1351            if isinstance(where, (list, tuple)):
1352                where = ' AND '.join(map(str, where))
1353            q.extend(['WHERE', where])
1354        if order is None:
1355            try:
1356                order = self.pkey(table, True)
1357            except (KeyError, ProgrammingError):
1358                try:
1359                    order = list(self.get_attnames(table))
1360                except (KeyError, ProgrammingError):
1361                    pass
1362        if order:
1363            if isinstance(order, (list, tuple)):
1364                order = ', '.join(map(str, order))
1365            q.extend(['ORDER BY', order])
1366        if limit:
1367            q.append('LIMIT %d' % limit)
1368        if offset:
1369            q.append('OFFSET %d' % offset)
1370        q = ' '.join(q)
1371        self._do_debug(q)
1372        q = self.db.query(q)
1373        res = q.namedresult()
1374        if res and scalar:
1375            res = [row[0] for row in res]
1376        return res
1377
1378    def get_as_dict(self, table, keyname=None, what=None, where=None,
1379            order=None, limit=None, offset=None, scalar=False):
1380        """Get a table as a dictionary.
1381
1382        This method is similar to get_as_list(), but returns the table
1383        as a Python dict instead of a Python list, which can be even
1384        more convenient. The primary key column(s) of the table will
1385        be used as the keys of the dictionary, while the other column(s)
1386        will be the corresponding values.  The keys will be named tuples
1387        if the table has a composite primary key.  The rows will be also
1388        named tuples unless the 'scalar' option has been set to True.
1389        With the optional parameter 'keyname' you can specify an alternative
1390        set of columns to be used as the keys of the dictionary.  It must
1391        be set as a string, list or a tuple.
1392
1393        If the Python version supports it, the dictionary will be an
1394        OrderedDict using the order specified with the 'order' parameter
1395        or the key column(s) if not specified.  You can set 'order' to False
1396        if you don't care about the ordering.  In this case the returned
1397        dictionary will be an ordinary one.
1398        """
1399        if not table:
1400            raise TypeError('The table name is missing')
1401        if not keyname:
1402            try:
1403                keyname = self.pkey(table, True)
1404            except (KeyError, ProgrammingError):
1405                raise _prg_error('Table %s has no primary key' % table)
1406        if isinstance(keyname, basestring):
1407            keyname = [keyname]
1408        elif not isinstance(keyname, (list, tuple)):
1409            raise KeyError('The keyname must be a string, list or tuple')
1410        if what:
1411            if isinstance(what, (list, tuple)):
1412                what = ', '.join(map(str, what))
1413            if order is None:
1414                order = what
1415        else:
1416            what = '*'
1417        q = ['SELECT', what, 'FROM', table]
1418        if where:
1419            if isinstance(where, (list, tuple)):
1420                where = ' AND '.join(map(str, where))
1421            q.extend(['WHERE', where])
1422        if order is None:
1423            order = keyname
1424        if order:
1425            if isinstance(order, (list, tuple)):
1426                order = ', '.join(map(str, order))
1427            q.extend(['ORDER BY', order])
1428        if limit:
1429            q.append('LIMIT %d' % limit)
1430        if offset:
1431            q.append('OFFSET %d' % offset)
1432        q = ' '.join(q)
1433        self._do_debug(q)
1434        q = self.db.query(q)
1435        res = q.getresult()
1436        cls = OrderedDict if order else dict
1437        if not res:
1438            return cls()
1439        keyset = set(keyname)
1440        fields = q.listfields()
1441        if not keyset.issubset(fields):
1442            raise KeyError('Missing keyname in row')
1443        keyind, rowind = [], []
1444        for i, f in enumerate(fields):
1445            (keyind if f in keyset else rowind).append(i)
1446        keytuple = len(keyind) > 1
1447        getkey = itemgetter(*keyind)
1448        keys = map(getkey, res)
1449        if scalar:
1450            rowind = rowind[:1]
1451            rowtuple = False
1452        else:
1453            rowtuple = len(rowind) > 1
1454        if scalar or rowtuple:
1455            getrow = itemgetter(*rowind)
1456        else:
1457            rowind = rowind[0]
1458            getrow = lambda row: (row[rowind],)
1459            rowtuple = True
1460        rows = map(getrow, res)
1461        if keytuple or rowtuple:
1462            namedresult = get_namedresult()
1463            if namedresult:
1464                if keytuple:
1465                    keys = namedresult(_MemoryQuery(keys, keyname))
1466                if rowtuple:
1467                    fields = [f for f in fields if f not in keyset]
1468                    rows = namedresult(_MemoryQuery(rows, fields))
1469        return cls(zip(keys, rows))
1470
1471    def notification_handler(self,
1472            event, callback, arg_dict=None, timeout=None, stop_event=None):
1473        """Get notification handler that will run the given callback."""
1474        return NotificationHandler(self,
1475            event, callback, arg_dict, timeout, stop_event)
1476
1477
1478# if run as script, print some information
1479
1480if __name__ == '__main__':
1481    print('PyGreSQL version' + version)
1482    print('')
1483    print(__doc__)
Note: See TracBrowser for help on using the repository browser.