source: trunk/module/pgdb.py @ 622

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

Use the ternary operator in modern Python

Since we require Python 2.6, we can and should use modern syntax
that makes the code a little easier to read.

  • Property svn:keywords set to Id
File size: 20.0 KB
Line 
1#! /usr/bin/python
2#
3# pgdb.py
4#
5# Written by D'Arcy J.M. Cain
6#
7# $Id: pgdb.py 622 2015-11-25 12:25:24Z cito $
8#
9
10"""pgdb - DB-API 2.0 compliant module for PygreSQL.
11
12(c) 1999, Pascal Andre <andre@via.ecp.fr>.
13See package documentation for further information on copyright.
14
15Inline documentation is sparse.
16See DB-API 2.0 specification for usage information:
17http://www.python.org/peps/pep-0249.html
18
19Basic usage:
20
21    pgdb.connect(connect_string) # open a connection
22    # connect_string = 'host:database:user:password:opt:tty'
23    # All parts are optional. You may also pass host through
24    # password as keyword arguments. To pass a port,
25    # pass it in the host keyword parameter:
26    connection = pgdb.connect(host='localhost:5432')
27
28    cursor = connection.cursor() # open a cursor
29
30    cursor.execute(query[, params])
31    # Execute a query, binding params (a dictionary) if they are
32    # passed. The binding syntax is the same as the % operator
33    # for dictionaries, and no quoting is done.
34
35    cursor.executemany(query, list of params)
36    # Execute a query many times, binding each param dictionary
37    # from the list.
38
39    cursor.fetchone() # fetch one row, [value, value, ...]
40
41    cursor.fetchall() # fetch all rows, [[value, value, ...], ...]
42
43    cursor.fetchmany([size])
44    # returns size or cursor.arraysize number of rows,
45    # [[value, value, ...], ...] from result set.
46    # Default cursor.arraysize is 1.
47
48    cursor.description # returns information about the columns
49    #   [(column_name, type_name, display_size,
50    #           internal_size, precision, scale, null_ok), ...]
51    # Note that precision, scale and null_ok are not implemented.
52
53    cursor.rowcount # number of rows available in the result set
54    # Available after a call to execute.
55
56    connection.commit() # commit transaction
57
58    connection.rollback() # or rollback transaction
59
60    cursor.close() # close the cursor
61
62    connection.close() # close the connection
63
64"""
65
66from _pg import *
67
68from datetime import date, time, datetime, timedelta
69from time import localtime
70from decimal import Decimal
71from math import isnan, isinf
72
73try:
74    long
75except NameError:  # Python >= 3.0
76    long = int
77
78try:
79    basestring
80except NameError:  # Python >= 3.0
81    basestring = (str, bytes)
82
83set_decimal(Decimal)
84
85
86### Module Constants
87
88# compliant with DB API 2.0
89apilevel = '2.0'
90
91# module may be shared, but not connections
92threadsafety = 1
93
94# this module use extended python format codes
95paramstyle = 'pyformat'
96
97# shortcut methods are not supported by default
98# since they have been excluded from DB API 2
99# and are not recommended by the DB SIG;
100
101shortcutmethods = 0
102
103
104### Internal Types Handling
105
106def decimal_type(decimal_type=None):
107    """Get or set global type to be used for decimal values."""
108    global Decimal
109    if decimal_type is not None:
110        _cast['numeric'] = Decimal = decimal_type
111        set_decimal(Decimal)
112    return Decimal
113
114
115def _cast_bool(value):
116    return value[:1] in ('t', 'T')
117
118
119def _cast_money(value):
120    return Decimal(''.join(filter(
121        lambda v: v in '0123456789.-', value)))
122
123
124def _cast_bytea(value):
125    return unescape_bytea(value)
126
127
128def _cast_float(value):
129    try:
130        return float(value)
131    except ValueError:
132        if value == 'NaN':
133            return nan
134        elif value == 'Infinity':
135            return inf
136        elif value == '-Infinity':
137            return -inf
138        raise
139
140
141_cast = {'bool': _cast_bool, 'bytea': _cast_bytea,
142    'int2': int, 'int4': int, 'serial': int,
143    'int8': long, 'oid': long, 'oid8': long,
144    'float4': _cast_float, 'float8': _cast_float,
145    'numeric': Decimal, 'money': _cast_money}
146
147
148def _db_error(msg, cls=DatabaseError):
149    """Returns DatabaseError with empty sqlstate attribute."""
150    error = cls(msg)
151    error.sqlstate = None
152    return error
153
154
155def _op_error(msg):
156    """Returns OperationalError."""
157    return _db_error(msg, OperationalError)
158
159
160class pgdbTypeCache(dict):
161    """Cache for database types."""
162
163    def __init__(self, cnx):
164        """Initialize type cache for connection."""
165        super(pgdbTypeCache, self).__init__()
166        self._src = cnx.source()
167
168    @staticmethod
169    def typecast(typ, value):
170        """Cast value to database type."""
171        if value is None:
172            # for NULL values, no typecast is necessary
173            return None
174        cast = _cast.get(typ)
175        if cast is None:
176            # no typecast available or necessary
177            return value
178        else:
179            return cast(value)
180
181    def getdescr(self, oid):
182        """Get name of database type with given oid."""
183        try:
184            return self[oid]
185        except KeyError:
186            self._src.execute(
187                "SELECT typname, typlen "
188                "FROM pg_type WHERE oid=%s" % oid)
189            res = self._src.fetch(1)[0]
190            # The column name is omitted from the return value.
191            # It will have to be prepended by the caller.
192            res = (res[0], None, int(res[1]),
193                None, None, None)
194            self[oid] = res
195            return res
196
197
198class _quotedict(dict):
199    """Dictionary with auto quoting of its items.
200
201    The quote attribute must be set to the desired quote function.
202
203    """
204
205    def __getitem__(self, key):
206        return self.quote(super(_quotedict, self).__getitem__(key))
207
208
209### Cursor Object
210
211class pgdbCursor(object):
212    """Cursor object."""
213
214    def __init__(self, dbcnx):
215        """Create a cursor object for the database connection."""
216        self.connection = self._dbcnx = dbcnx
217        self._cnx = dbcnx._cnx
218        self._type_cache = dbcnx._type_cache
219        self._src = self._cnx.source()
220        self.description = None
221        self.rowcount = -1
222        self.arraysize = 1
223        self.lastrowid = None
224
225    def __iter__(self):
226        """Make cursors compatible to the iteration protocol."""
227        return self
228
229    def __enter__(self):
230        """Enter the runtime context for the cursor object."""
231        return self
232
233    def __exit__(self, et, ev, tb):
234        """Exit the runtime context for the cursor object."""
235        self.close()
236
237    def _quote(self, val):
238        """Quote value depending on its type."""
239        if isinstance(val, (datetime, date, time, timedelta)):
240            val = str(val)
241        elif isinstance(val, str) and str is not bytes:
242            val = val.encode('utf8')
243        if isinstance(val, bytes):
244            if isinstance(val, Binary):
245                val = self._cnx.escape_bytea(val)
246            else:
247                val = self._cnx.escape_string(val)
248            val = "'%s'" % val
249        elif isinstance(val, (int, long)):
250            pass
251        elif isinstance(val, float):
252            if isinf(val):
253                return "'-Infinity'" if val < 0 else "'Infinity'"
254            elif isnan(val):
255                return "'NaN'"
256        elif val is None:
257            val = 'NULL'
258        elif isinstance(val, (list, tuple)):
259            val = '(%s)' % ','.join(map(lambda v: str(self._quote(v)), val))
260        elif Decimal is not float and isinstance(val, Decimal):
261            pass
262        elif hasattr(val, '__pg_repr__'):
263            val = val.__pg_repr__()
264        else:
265            raise InterfaceError(
266                'do not know how to handle type %s' % type(val))
267        return val
268
269    def _quoteparams(self, string, params):
270        """Quote parameters.
271
272        This function works for both mappings and sequences.
273
274        """
275        if isinstance(params, dict):
276            params = _quotedict(params)
277            params.quote = self._quote
278        else:
279            params = tuple(map(self._quote, params))
280        return string % params
281
282    def row_factory(row):
283        """Process rows before they are returned.
284
285        You can overwrite this with a custom row factory,
286        e.g. a dict factory:
287
288        class myCursor(pgdb.pgdbCursor):
289            def cursor.row_factory(self, row):
290                d = {}
291                for idx, col in enumerate(self.description):
292                    d[col[0]] = row[idx]
293                return d
294        cursor = myCursor(cnx)
295
296        """
297        return row
298    row_factory = staticmethod(row_factory)
299
300    def close(self):
301        """Close the cursor object."""
302        self._src.close()
303        self.description = None
304        self.rowcount = -1
305        self.lastrowid = None
306
307    def execute(self, operation, params=None):
308        """Prepare and execute a database operation (query or command)."""
309
310        # The parameters may also be specified as list of
311        # tuples to e.g. insert multiple rows in a single
312        # operation, but this kind of usage is deprecated:
313        if (params and isinstance(params, list)
314                and isinstance(params[0], tuple)):
315            return self.executemany(operation, params)
316        else:
317            # not a list of tuples
318            return self.executemany(operation, [params])
319
320    def executemany(self, operation, param_seq):
321        """Prepare operation and execute it against a parameter sequence."""
322        if not param_seq:
323            # don't do anything without parameters
324            return
325        self.description = None
326        self.rowcount = -1
327        # first try to execute all queries
328        totrows = 0
329        sql = "BEGIN"
330        try:
331            if not self._dbcnx._tnx:
332                try:
333                    self._cnx.source().execute(sql)
334                except DatabaseError:
335                    raise
336                except Exception:
337                    raise _op_error("can't start transaction")
338                self._dbcnx._tnx = True
339            for params in param_seq:
340                if params:
341                    sql = self._quoteparams(operation, params)
342                else:
343                    sql = operation
344                rows = self._src.execute(sql)
345                if rows:  # true if not DML
346                    totrows += rows
347                else:
348                    self.rowcount = -1
349        except DatabaseError:
350            raise
351        except Error as err:
352            raise _db_error("error '%s' in '%s'" % (err, sql))
353        except Exception as err:
354            raise _op_error("internal error in '%s': %s" % (sql, err))
355        # then initialize result raw count and description
356        if self._src.resulttype == RESULT_DQL:
357            self.rowcount = self._src.ntuples
358            getdescr = self._type_cache.getdescr
359            coltypes = self._src.listinfo()
360            self.description = [
361                typ[1:2] + getdescr(typ[2]) for typ in coltypes]
362            self.lastrowid = None
363        else:
364            self.rowcount = totrows
365            self.description = None
366            self.lastrowid = self._src.oidstatus()
367        # return the cursor object, so you can write statements such as
368        # "cursor.execute(...).fetchall()" or "for row in cursor.execute(...)"
369        return self
370
371    def fetchone(self):
372        """Fetch the next row of a query result set."""
373        res = self.fetchmany(1, False)
374        try:
375            return res[0]
376        except IndexError:
377            return None
378
379    def fetchall(self):
380        """Fetch all (remaining) rows of a query result."""
381        return self.fetchmany(-1, False)
382
383    def fetchmany(self, size=None, keep=False):
384        """Fetch the next set of rows of a query result.
385
386        The number of rows to fetch per call is specified by the
387        size parameter. If it is not given, the cursor's arraysize
388        determines the number of rows to be fetched. If you set
389        the keep parameter to true, this is kept as new arraysize.
390
391        """
392        if size is None:
393            size = self.arraysize
394        if keep:
395            self.arraysize = size
396        try:
397            result = self._src.fetch(size)
398        except DatabaseError:
399            raise
400        except Error as err:
401            raise _db_error(str(err))
402        row_factory = self.row_factory
403        typecast = self._type_cache.typecast
404        coltypes = [desc[1] for desc in self.description]
405        return [row_factory([typecast(*args)
406            for args in zip(coltypes, row)]) for row in result]
407
408    def __next__(self):
409        """Return the next row (support for the iteration protocol)."""
410        res = self.fetchone()
411        if res is None:
412            raise StopIteration
413        return res
414
415    # Note that since Python 2.6 the iterator protocol uses __next()__
416    # instead of next(), we keep it only for backward compatibility of pgdb.
417    next = __next__
418
419    @staticmethod
420    def nextset():
421        """Not supported."""
422        raise NotSupportedError("nextset() is not supported")
423
424    def setinputsizes(sizes):
425        """Not supported."""
426        pass  # unsupported, but silently passed
427    setinputsizes = staticmethod(setinputsizes)
428
429    def setoutputsize(size, column=0):
430        """Not supported."""
431        pass  # unsupported, but silently passed
432    setoutputsize = staticmethod(setoutputsize)
433
434
435### Connection Objects
436
437class pgdbCnx(object):
438    """Connection object."""
439
440    # expose the exceptions as attributes on the connection object
441    Error = Error
442    Warning = Warning
443    InterfaceError = InterfaceError
444    DatabaseError = DatabaseError
445    InternalError = InternalError
446    OperationalError = OperationalError
447    ProgrammingError = ProgrammingError
448    IntegrityError = IntegrityError
449    DataError = DataError
450    NotSupportedError = NotSupportedError
451
452    def __init__(self, cnx):
453        """Create a database connection object."""
454        self._cnx = cnx  # connection
455        self._tnx = False  # transaction state
456        self._type_cache = pgdbTypeCache(cnx)
457        try:
458            self._cnx.source()
459        except Exception:
460            raise _op_error("invalid connection")
461
462    def __enter__(self):
463        """Enter the runtime context for the connection object.
464
465        The runtime context can be used for running transactions.
466
467        """
468        return self
469
470    def __exit__(self, et, ev, tb):
471        """Exit the runtime context for the connection object.
472
473        This does not close the connection, but it ends a transaction.
474
475        """
476        if et is None and ev is None and tb is None:
477            self.commit()
478        else:
479            self.rollback()
480
481    def close(self):
482        """Close the connection object."""
483        if self._cnx:
484            if self._tnx:
485                try:
486                    self.rollback()
487                except DatabaseError:
488                    pass
489            self._cnx.close()
490            self._cnx = None
491        else:
492            raise _op_error("connection has been closed")
493
494    def commit(self):
495        """Commit any pending transaction to the database."""
496        if self._cnx:
497            if self._tnx:
498                self._tnx = False
499                try:
500                    self._cnx.source().execute("COMMIT")
501                except DatabaseError:
502                    raise
503                except Exception:
504                    raise _op_error("can't commit")
505        else:
506            raise _op_error("connection has been closed")
507
508    def rollback(self):
509        """Roll back to the start of any pending transaction."""
510        if self._cnx:
511            if self._tnx:
512                self._tnx = False
513                try:
514                    self._cnx.source().execute("ROLLBACK")
515                except DatabaseError:
516                    raise
517                except Exception:
518                    raise _op_error("can't rollback")
519        else:
520            raise _op_error("connection has been closed")
521
522    def cursor(self):
523        """Return a new cursor object using the connection."""
524        if self._cnx:
525            try:
526                return pgdbCursor(self)
527            except Exception:
528                raise _op_error("invalid connection")
529        else:
530            raise _op_error("connection has been closed")
531
532    if shortcutmethods:  # otherwise do not implement and document this
533
534        def execute(self, operation, params=None):
535            """Shortcut method to run an operation on an implicit cursor."""
536            cursor = self.cursor()
537            cursor.execute(operation, params)
538            return cursor
539
540        def executemany(self, operation, param_seq):
541            """Shortcut method to run an operation against a sequence."""
542            cursor = self.cursor()
543            cursor.executemany(operation, param_seq)
544            return cursor
545
546
547### Module Interface
548
549_connect_ = connect
550
551def connect(dsn=None,
552        user=None, password=None,
553        host=None, database=None):
554    """Connects to a database."""
555    # first get params from DSN
556    dbport = -1
557    dbhost = ""
558    dbbase = ""
559    dbuser = ""
560    dbpasswd = ""
561    dbopt = ""
562    dbtty = ""
563    try:
564        params = dsn.split(":")
565        dbhost = params[0]
566        dbbase = params[1]
567        dbuser = params[2]
568        dbpasswd = params[3]
569        dbopt = params[4]
570        dbtty = params[5]
571    except (AttributeError, IndexError, TypeError):
572        pass
573
574    # override if necessary
575    if user is not None:
576        dbuser = user
577    if password is not None:
578        dbpasswd = password
579    if database is not None:
580        dbbase = database
581    if host is not None:
582        try:
583            params = host.split(":")
584            dbhost = params[0]
585            dbport = int(params[1])
586        except (AttributeError, IndexError, TypeError, ValueError):
587            pass
588
589    # empty host is localhost
590    if dbhost == "":
591        dbhost = None
592    if dbuser == "":
593        dbuser = None
594
595    # open the connection
596    cnx = _connect_(dbbase, dbhost, dbport, dbopt,
597        dbtty, dbuser, dbpasswd)
598    return pgdbCnx(cnx)
599
600
601### Types Handling
602
603class pgdbType(frozenset):
604    """Type class for a couple of PostgreSQL data types.
605
606    PostgreSQL is object-oriented: types are dynamic.
607    We must thus use type names as internal type codes.
608
609    """
610
611    def __new__(cls, values):
612        if isinstance(values, basestring):
613            values = values.split()
614        return super(pgdbType, cls).__new__(cls, values)
615
616    def __eq__(self, other):
617        if isinstance(other, basestring):
618            return other in self
619        else:
620            return super(pgdbType, self).__eq__(other)
621
622    def __ne__(self, other):
623        if isinstance(other, basestring):
624            return other not in self
625        else:
626            return super(pgdbType, self).__ne__(other)
627
628
629# Mandatory type objects defined by DB-API 2 specs:
630
631STRING = pgdbType('char bpchar name text varchar')
632BINARY = pgdbType('bytea')
633NUMBER = pgdbType('int2 int4 serial int8 float4 float8 numeric money')
634DATETIME = pgdbType('date time timetz timestamp timestamptz datetime abstime'
635    ' interval tinterval timespan reltime')
636ROWID = pgdbType('oid oid8')
637
638
639# Additional type objects (more specific):
640
641BOOL = pgdbType('bool')
642SMALLINT = pgdbType('int2')
643INTEGER = pgdbType('int2 int4 int8 serial')
644LONG = pgdbType('int8')
645FLOAT = pgdbType('float4 float8')
646NUMERIC = pgdbType('numeric')
647MONEY = pgdbType('money')
648DATE = pgdbType('date')
649TIME = pgdbType('time timetz')
650TIMESTAMP = pgdbType('timestamp timestamptz datetime abstime')
651INTERVAL = pgdbType('interval tinterval timespan reltime')
652
653
654# Mandatory type helpers defined by DB-API 2 specs:
655
656def Date(year, month, day):
657    """Construct an object holding a date value."""
658    return date(year, month, day)
659
660
661def Time(hour, minute=0, second=0, microsecond=0):
662    """Construct an object holding a time value."""
663    return time(hour, minute, second, microsecond)
664
665
666def Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0):
667    """construct an object holding a time stamp value."""
668    return datetime(year, month, day, hour, minute, second, microsecond)
669
670
671def DateFromTicks(ticks):
672    """Construct an object holding a date value from the given ticks value."""
673    return Date(*localtime(ticks)[:3])
674
675
676def TimeFromTicks(ticks):
677    """construct an object holding a time value from the given ticks value."""
678    return Time(*localtime(ticks)[3:6])
679
680
681def TimestampFromTicks(ticks):
682    """construct an object holding a time stamp from the given ticks value."""
683    return Timestamp(*localtime(ticks)[:6])
684
685
686class Binary(str):
687    """construct an object capable of holding a binary (long) string value."""
688
689
690# If run as script, print some information:
691
692if __name__ == '__main__':
693    print('PyGreSQL version', version)
694    print('')
695    print(__doc__)
Note: See TracBrowser for help on using the repository browser.