CoCalc Public Fileswww / cgi-bin / mfd / pgdb.pyOpen with one click!
Author: William A. Stein
Compute Environment: Ubuntu 18.04 (Deprecated)
1
""" pgdb - DB-SIG compliant module for PygreSQL.
2
3
(c) 1999, Pascal Andre <[email protected]>.
4
See package documentation for further information on copyright.
5
6
Inline documentation is sparse. See DB-SIG 2.0 specification for
7
usage information.
8
9
basic usage:
10
11
pgdb.connect(connect_string) -> connection
12
connect_string = 'host:database:user:password:opt:tty'
13
All parts are optional. You may also pass host through
14
password as keyword arguments. To pass a port, pass it in
15
the host keyword parameter:
16
pgdb.connect(host='localhost:5432')
17
18
connection.cursor() -> cursor
19
20
connection.commit()
21
22
connection.close()
23
24
connection.rollback()
25
26
cursor.execute(query[, params])
27
execute a query, binding params (a dictionary) if it is
28
passed. The binding syntax is the same as the % operator
29
for dictionaries, and no quoting is done.
30
31
cursor.executemany(query, list of params)
32
execute a query many times, binding each param dictionary
33
from the list.
34
35
cursor.fetchone() -> [value, value, ...]
36
37
cursor.fetchall() -> [[value, value, ...], ...]
38
39
cursor.fetchmany([size]) -> [[value, value, ...], ...]
40
returns size or cursor.arraysize number of rows from result
41
set. Default cursor.arraysize is 1.
42
43
cursor.description -> [(column_name, type_name, display_size,
44
internal_size, precision, scale, null_ok), ...]
45
46
Note that precision, scale and null_ok are not implemented.
47
48
cursor.rowcount
49
number of rows available in the result set. Available after
50
a call to execute.
51
52
cursor.close()
53
54
"""
55
56
import _pg
57
import string
58
import exceptions
59
import types
60
import time
61
62
# Marc-Andre is changing where DateTime goes. This handles it either way.
63
try: from mx import DateTime
64
except ImportError: import DateTime
65
66
### module constants
67
68
# compliant with DB SIG 2.0
69
apilevel = '2.0'
70
71
# module may be shared, but not connections
72
threadsafety = 1
73
74
# this module use extended python format codes
75
paramstyle = 'pyformat'
76
77
### exception hierarchy
78
79
class Warning(StandardError):
80
pass
81
82
class Error(StandardError):
83
pass
84
85
class InterfaceError(Error):
86
pass
87
88
class DatabaseError(Error):
89
pass
90
91
class DataError(DatabaseError):
92
pass
93
94
class OperationalError(DatabaseError):
95
pass
96
97
class IntegrityError(DatabaseError):
98
pass
99
100
class InternalError(DatabaseError):
101
pass
102
103
class ProgrammingError(DatabaseError):
104
pass
105
106
class NotSupportedError(DatabaseError):
107
pass
108
109
### internal type handling class
110
class pgdbTypeCache:
111
112
def __init__(self, cnx):
113
self.__source = cnx.source()
114
self.__type_cache = {}
115
116
def typecast(self, typ, value):
117
# for NULL values, no typecast is necessary
118
if value == None:
119
return value
120
121
if typ == STRING:
122
pass
123
elif typ == BINARY:
124
pass
125
elif typ == BOOL:
126
value = (value[:1] in ['t','T'])
127
elif typ == INTEGER:
128
value = int(value)
129
elif typ == LONG:
130
value = long(value)
131
elif typ == FLOAT:
132
value = float(value)
133
elif typ == MONEY:
134
value = string.replace(value, "$", "")
135
value = string.replace(value, ",", "")
136
value = float(value)
137
elif typ == DATETIME:
138
# format may differ ... we'll give string
139
pass
140
elif typ == ROWID:
141
value = long(value)
142
return value
143
144
def getdescr(self, oid):
145
try:
146
return self.__type_cache[oid]
147
except:
148
self.__source.execute(
149
"SELECT typname, typlen "
150
"FROM pg_type WHERE oid = %s" % oid
151
)
152
res = self.__source.fetch(1)[0]
153
# column name is omitted from the return value. It will
154
# have to be prepended by the caller.
155
res = (
156
res[0],
157
None, string.atoi(res[1]),
158
None, None, None
159
)
160
self.__type_cache[oid] = res
161
return res
162
163
### cursor object
164
165
class pgdbCursor:
166
167
def __init__(self, src, cache):
168
self.__cache = cache
169
self.__source = src
170
self.description = None
171
self.rowcount = -1
172
self.arraysize = 1
173
174
def close(self):
175
self.__source.close()
176
self.description = None
177
self.rowcount = -1
178
179
def execute(self, operation, params = None):
180
# "The parameters may also be specified as list of
181
# tuples to e.g. insert multiple rows in a single
182
# operation, but this kind of usage is deprecated:
183
if params and isinstance(params, types.ListType) and \
184
isinstance(params[0], types.TupleType):
185
self.executemany(operation, params)
186
else:
187
# not a list of tuples
188
self.executemany(operation, (params,))
189
190
def executemany(self, operation, param_seq):
191
self.description = None
192
self.rowcount = -1
193
194
# first try to execute all queries
195
totrows = 0
196
sql = "INIT"
197
try:
198
for params in param_seq:
199
if params != None:
200
sql = _quoteparams(operation, params)
201
else:
202
sql = operation
203
rows = self.__source.execute(sql)
204
if rows != None: # true is __source is NOT a DQL
205
totrows = totrows + rows
206
except _pg.error, msg:
207
raise DatabaseError, "error '%s' in '%s'" % ( msg, sql )
208
except:
209
raise OperationalError, "internal error in '%s'" % sql
210
211
# then initialize result raw count and description
212
if self.__source.resulttype == _pg.RESULT_DQL:
213
self.rowcount = self.__source.ntuples
214
d = []
215
for typ in self.__source.listinfo():
216
# listinfo is a sequence of
217
# (index, column_name, type_oid)
218
# getdescr returns all items needed for a
219
# description tuple except the column_name.
220
desc = typ[1:2]+self.__cache.getdescr(typ[2])
221
d.append(desc)
222
self.description = d
223
else:
224
self.rowcount = totrows
225
self.description = None
226
227
def fetchone(self):
228
res = self.fetchmany(1, 0)
229
try:
230
return res[0]
231
except:
232
return None
233
234
def fetchall(self):
235
return self.fetchmany(-1, 0)
236
237
def fetchmany(self, size = None, keep = 1):
238
if size == None:
239
size = self.arraysize
240
if keep == 1:
241
self.arraysize = size
242
243
try: res = self.__source.fetch(size)
244
except _pg.error, e: raise DatabaseError, str(e)
245
246
result = []
247
for r in res:
248
row = []
249
for i in range(len(r)):
250
row.append(self.__cache.typecast(
251
self.description[i][1],
252
r[i]
253
)
254
)
255
result.append(row)
256
return result
257
258
def nextset(self):
259
raise NotSupportedError, "nextset() is not supported"
260
261
def setinputsizes(self, sizes):
262
pass
263
264
def setoutputsize(self, size, col = 0):
265
pass
266
267
268
try:
269
_quote = _pg.quote_fast
270
_quoteparams = _pg.quoteparams_fast
271
except (NameError, AttributeError):
272
def _quote(x):
273
if type(x) == DateTime.DateTimeType:
274
x = str(x)
275
if isinstance(x, types.StringType):
276
x = "'" + string.replace(
277
string.replace(str(x), '\\', '\\\\'), "'", "''") + "'"
278
279
elif type(x, (types.IntType, types.LongType, types.FloatType)):
280
pass
281
elif x is None:
282
x = 'NULL'
283
elif type(x, (types.ListType, types.TupleType)):
284
x = '(%s)' % string.join(map(lambda x: str(_quote(x)), x), ',')
285
elif hasattr(x, '__pg_repr__'):
286
x = x.__pg_repr__()
287
else:
288
raise InterfaceError, 'do not know how to handle type %s' % type(x)
289
290
return x
291
292
def _quoteparams(s, params):
293
if hasattr(params, 'has_key'):
294
x = {}
295
for k, v in params.items():
296
x[k] = _quote(v)
297
params = x
298
else:
299
params = tuple(map(_quote, params))
300
301
return s % params
302
303
### connection object
304
305
class pgdbCnx:
306
307
def __init__(self, cnx):
308
self.__cnx = cnx
309
self.__cache = pgdbTypeCache(cnx)
310
try:
311
src = self.__cnx.source()
312
src.execute("BEGIN")
313
except:
314
raise OperationalError, "invalid connection."
315
316
def close(self):
317
self.__cnx.close()
318
319
def commit(self):
320
try:
321
src = self.__cnx.source()
322
src.execute("COMMIT")
323
src.execute("BEGIN")
324
except:
325
raise OperationalError, "can't commit."
326
327
def rollback(self):
328
try:
329
src = self.__cnx.source()
330
src.execute("ROLLBACK")
331
src.execute("BEGIN")
332
except:
333
raise OperationalError, "can't rollback."
334
335
def cursor(self):
336
try:
337
src = self.__cnx.source()
338
return pgdbCursor(src, self.__cache)
339
except:
340
raise pgOperationalError, "invalid connection."
341
342
### module interface
343
344
# connects to a database
345
def connect(dsn = None, user = None, password = None, host = None, database = None):
346
# first get params from DSN
347
dbport = -1
348
dbhost = ""
349
dbbase = ""
350
dbuser = ""
351
dbpasswd = ""
352
dbopt = ""
353
dbtty = ""
354
try:
355
params = string.split(dsn, ":")
356
dbhost = params[0]
357
dbbase = params[1]
358
dbuser = params[2]
359
dbpasswd = params[3]
360
dbopt = params[4]
361
dbtty = params[5]
362
except:
363
pass
364
365
# override if necessary
366
if user != None:
367
dbuser = user
368
if password != None:
369
dbpasswd = password
370
if database != None:
371
dbbase = database
372
if host != None:
373
try:
374
params = string.split(host, ":")
375
dbhost = params[0]
376
dbport = int(params[1])
377
except:
378
pass
379
380
# empty host is localhost
381
if dbhost == "":
382
dbhost = None
383
if dbuser == "":
384
dbuser = None
385
386
# open the connection
387
cnx = _pg.connect(dbbase, dbhost, dbport, dbopt,
388
dbtty, dbuser, dbpasswd)
389
return pgdbCnx(cnx)
390
391
### types handling
392
393
# PostgreSQL is object-oriented: types are dynamic. We must thus use type names
394
# as internal type codes.
395
396
class pgdbType:
397
398
def __init__(self, *values):
399
self.values= values
400
401
def __cmp__(self, other):
402
if other in self.values:
403
return 0
404
if other < self.values:
405
return 1
406
else:
407
return -1
408
409
STRING = pgdbType(
410
'char', 'bpchar', 'name', 'text', 'varchar'
411
)
412
413
# BLOB support is pg specific
414
BINARY = pgdbType()
415
INTEGER = pgdbType('int2', 'int4', 'serial')
416
LONG = pgdbType('int8')
417
FLOAT = pgdbType('float4', 'float8', 'numeric')
418
BOOL = pgdbType('bool')
419
MONEY = pgdbType('money')
420
421
# this may be problematic as type are quite different ... I hope it won't hurt
422
DATETIME = pgdbType(
423
'abstime', 'reltime', 'tinterval', 'date', 'time', 'timespan', 'timestamp', 'timestamptz', 'interval'
424
)
425
426
# OIDs are used for everything (types, tables, BLOBs, rows, ...). This may cause
427
# confusion, but we are unable to find out what exactly is behind the OID (at
428
# least not easily enough). Should this be undefined as BLOBs ?
429
ROWID = pgdbType(
430
'oid', 'oid8'
431
)
432
433
# mandatory type helpers
434
def Date(year, month, day):
435
return DateTime.DateTime(year, month, day)
436
437
def Time(hour, minute, second):
438
return DateTime.TimeDelta(hour, minute, second)
439
440
def Timestamp(year, month, day, hour, minute, second):
441
return DateTime.DateTime(year, month, day, hour, minute, second)
442
443
def DateFromTicks(ticks):
444
return apply(Date, time.localtime(ticks)[:3])
445
446
def TimeFromTicks(ticks):
447
return apply(Time, time.localtime(ticks)[3:6])
448
449
def TimestampFromTicks(ticks):
450
return apply(Timestamp, time.localtime(ticks)[:6])
451
452