source: trunk/module/pgdb.py @ 553

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

Require at least Python 2.6 for the trunk (5.x)

Support for even older Python versions is maintained in the 4.x branch.
The goal for 5.x is to be a single-source code for both Python 2 and 3,
and this is only possible by dropping support for Python 2.5 and older.
For instance, the new except .. as syntax works only since Python 2.6.
Otherwise we would need to use 2to3 and things would be very ugly.
Note that Python 2.6 is now 7 years old. We may want to drop Python 2.6
as well at some point if it turns out to be a burden.

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