source: branches/4.x/pgdb.py @ 711

Last change on this file since 711 was 711, checked in by cito, 3 years ago

Flatten the directory structure of the project

Simplified the directory structure by flattening the "module" subdirectory out
to the root directory. That way, the setup.py script can now also access the
top-level docs subdirectory, so it could also install or build the docs.
There was nothing else in the root directory anyway, except the mkdocs and
mktar scripts which could be made unnecessary through setup.py.

Also made the setup script a bit clearer. Removed the note about MinGW for
Windows since the Microsoft compiler for Python 2.7 and Visual Studio Community
are now freely available including 64bit compilers, and produce less problems.

Note that the usual structure would have been to use a "pygresql" package
directory instead of the "module" directory. But since we install PyGreSQL
as two top-level modules "pg" and "pgdb" instead of a package "pygresql",
the flattened structure reflects that much better. For historical reasons
and people don't want to rewrite import statements, we will keep it that way.

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