?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
del net-oscar
[learning-git.git] / pgworksheet_1.9 / pgw / MyPgSQL.py
bloba9f3cd48b227a5da708643d8b212c535f6bc38b2
1 #ident "@(#) $Id: MyPgSQL.py,v 1.2 2004/11/24 10:31:07 hmichelon Exp $"
2 # vi:set sw=4 ts=8 showmode ai:
3 #--(H+)-----------------------------------------------------------------+
4 # Name: PgSQL.py |
5 # |
6 # Description: This file implements a Python DB-API 2.0 interface to |
7 # PostgreSQL. |
8 #=======================================================================|
9 # Copyright 2000 by Billy G. Allie. |
10 # All rights reserved. |
11 # |
12 # Permission to use, copy, modify, and distribute this software and its |
13 # documentation for any purpose and without fee is hereby granted, pro- |
14 # vided that the above copyright notice appear in all copies and that |
15 # both that copyright notice and this permission notice appear in sup- |
16 # porting documentation, and that the copyright owner's name not be |
17 # used in advertising or publicity pertaining to distribution of the |
18 # software without specific, written prior permission. |
19 # |
20 # THE AUTHOR(S) DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, |
21 # INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN |
22 # NO EVENT SHALL THE AUTHOR(S) BE LIABLE FOR ANY SPECIAL, INDIRECT OR |
23 # CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS |
24 # OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE |
25 # OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE |
26 # USE OR PERFORMANCE OF THIS SOFTWARE. |
27 #=======================================================================|
28 # Revision History: |
29 # |
30 # Date Ini Description |
31 # --------- --- ------------------------------------------------------- |
32 # 05JUL2003 bga - Fixed a problem with PgNumeric where an exception can |
33 # be thrown if the stated scale and precision of the |
34 # returned in the first result row does not match later |
35 # rows. |
36 # 26JUN2003 bga - Applied patch from Laurent Pinchart to allow _quote |
37 # to correctly process objects that are sub-classed |
38 # from String and Long types. |
39 # 05JUN2003 bga - Change the name of the quoting function back to |
40 # _quote. Variables named like __*__ should be restrict |
41 # to system names. |
42 # 02JUN2003 bga - PgTypes is now hashable. repr() of a PgType will now |
43 # return the repr() of the underlying OID. |
44 # - Connection.binary() will now fail if autocommit is |
45 # enabled. |
46 # - Connection.binary() will no longer commit the trans- |
47 # action after creating the large object. The applica- |
48 # tion developer is now responsible for commiting (or |
49 # for rolling back) the transaction [Bug #747525]. |
50 # - Added PG_TIMETZ to the mix [Patch #708013]. |
51 # - Pg_Money will now accept a string as a parameter. |
52 # - PostgreSQL int2, int, int4 will now be cast into |
53 # Python ints. Int8 will be cast into a Python long. |
54 # Float4, float8, and money types will be cast into |
55 # a Python float. |
56 # 07MAR2003 bga - Correct problem with the PgNumeric.__radd__ method. |
57 # [Bug #694358] |
58 # - Correct problem with conversion of negitive integers |
59 # (with a given scale and precision) to PgNumerics. |
60 # [Bug #694358] |
61 # - Work around a problem where the precision and scale |
62 # of a query result can be different from the first re- |
63 # sult in the result set. |
64 # [Bug #697221] |
65 # 12JAN2003 bga - Change the code so that the display length in the |
66 # cursor.description attribute is always None instead |
67 # of '-1'. |
68 # - Fixed another problem with interval <-> DateTimeDelta |
69 # casting. |
70 # 23DEC2002 bga - Corrected a problem that caused the close of a portal |
71 # (ie. PostgreSQL cursor) to fail. |
72 # 13DEC2002 bga - Corrected a problem with interval <-> DateTimeDelta |
73 # casting. [Bug #653044] |
74 # 06DEC2002 bga - Corrected problem found by Adam Buraczewski in the |
75 # __setupTransaction function. |
76 # - Allow both 'e' and 'E' to signify an exponet in the |
77 # PgNumeric constructor. |
78 # 04DEC2002 bga - Correct some problems that were missed in yesterday's |
79 # fixes (Thanks, Adam, for the help with the problems) |
80 # 03DEC2002 bga - Fixed various problems with the constructor and the |
81 # formatting routine. These correct the problems re- |
82 # ported by Adam Buraczewski. |
83 # 01DEC2002 bga - Fixed problems with new __setupTransaction function: |
84 # 1. Made it a method of Connection, not Cursor. |
85 # 2. inTransaction was only set if TransactionLevel was |
86 # set. |
87 # - Fixed instances where method name was incorrect: |
88 # Connection__gcCursor -> _Connection__gcCursor |
89 # Connection__closeCursor -> _Connection__closeCursor |
90 # - Cleaned up code where there was unneeded references |
91 # to conn in Connection class. |
92 # 01DEC2002 gh - Handle the new '__quote__' method for arrays, too. |
93 # - Still handle '_quote' methods for backwards compati- |
94 # bility. This will will avoid complaints by users |
95 # who have code depending on this. Like me. |
96 # 28NOV2002 bga - Fixed changed PG_TIMESTAMP oid, added PG_TIMESTAMPTZ |
97 # and PG_REFCURSOR oids. [Bug #845360] |
98 # - Reference cursors are now type-casted into cursor ob- |
99 # jects. |
100 # 27NOV2002 bga - Completed the emulation of a String object for the |
101 # PgBytea and PgOther classes. This corrects several |
102 # problems with PgBytea concerning comparisons, using |
103 # PgBytea types as keys in dictionaries, etc. |
104 # 10NOV2002 bga - Added the __hash__ function to the PgNumeric class. |
105 # Cleaned up the code in PgNumeric class and made some |
106 # small improvments to it. |
107 # 02NOV2002 bga - Added the PgArray class. This is a wrapper around a |
108 # Python list and is used for all PostgreSQL arrays. |
109 # This change was made so that lists and tuples no |
110 # longer have a special meaning in the Cursor.execute() |
111 # method. |
112 # - Changed the quoting methods defined in the various |
113 # classes defining PostgreSQL support types to __quote__|
114 # 27OCT2002 gh - Merged the Unicode patch. Closes #484468. |
115 # - Convert ROWID to PgInt8 instead of PgInt4 (the origi- |
116 # nal behaviour led to overflow errors.) |
117 # - Always set the displaysize field of |
118 # cursor.description to -1. PostgreSQL 7.3 doesn't |
119 # provide this information any more and it's pretty |
120 # useless nowadays that we've mostly left line printers |
121 # beyond us. |
122 # 26OCT2002 bga - Column access by name (attribute and dictionary) now |
123 # supports mixed-case column name. Be aware that if |
124 # you define mixed-case column names in the database, |
125 # you have to use the mixed-case name to access the co- |
126 # lumn or it will not be found. For column names that |
127 # were not defined with mixed-case in the database, the |
128 # column access by name is case insensitive. |
129 # 02OCT2002 gh - Only support mxDateTime 2.x and give useful error |
130 # message if import fails. |
131 # - Cosmetic changes: use cmp builtin where appropriate. |
132 # - Fixed typo where PgTypes.__str__ was spelled |
133 # incorrectly. Compare to None using "is" operator. |
134 # - Take into account that libpq.PgInt8Type might not be |
135 # available. |
136 # - Add support for the INTERVAL type. |
137 # 08SEP2002 gh Fixed various problems with the PgResultSet: |
138 # - Column (attribute and dictionary) access is now case- |
139 # insensitive. |
140 # - Added __contains__ method. |
141 # - Added default value parameter to get method. |
142 # - Made setattr actually work. |
143 # 11AUG2002 bga Fixed various problems with the PgNumeric type: |
144 # - Added code to allow a float as an argument to the |
145 # PgNumeric constructor. |
146 # - You can now change the precision/scale of a PgNumeric |
147 # by: a = PgNumeric(pgnumeric, new prec, new scale). |
148 # This can be used to 'cast' a PgNumeric to the proper |
149 # precision and scale before storing it in a field. |
150 # - The arithmatic routines (__add__, __radd__, etc) now |
151 # ensure that the arguments are properly coerced to the |
152 # correct types. |
153 # - Added support for the augmented arithmatic operations |
154 # (__iadd__, etc). |
155 # - The math routines would lose precision becuase the |
156 # precision/scale were set to be the same as the first |
157 # operand. This is no longer the case all precision is |
158 # retained for the +, -, and * operations. |
159 # 03AUG2002 gh - Fixed problem that occurs when a query on an OID |
160 # field doesn't return any rows. [Bug #589370]. |
161 # 29JUL2002 gh - Applied patch #569203 and also added __pos__ and |
162 # __abs__ special methods to PgNumeric. |
163 # 15MAY2002 gh - Got rid of redundant building and storing of the |
164 # mapping of column names to column positions in the |
165 # PgResultSet class. Now, rows of the same query are |
166 # instances of a dynamically created class, which has |
167 # this mapping as a class attribute instead of an at- |
168 # tribute of the instance. This saves a lot of space, |
169 # and also slightly increases performance of cursor |
170 # fetches. |
171 # 21APR2002 gh - Improved the array parsing, so that it now passes all |
172 # the new mean testcases. Added support for parsing |
173 # multidimensional arrays. Eventually, the array par- |
174 # sing code should go as a support function into libpq. |
175 # - Replaced all typechecks with "is" operators instead |
176 # of equals. Mark McEahern had a problem with using |
177 # pyPgSQL in combination with a FixedPoint class where |
178 # the reason was that the FixedPoint class was not com- |
179 # parable to None. The consensus on python-list was |
180 # that None and all types are singletons, so they |
181 # should be checked using "is", which is also faster, |
182 # because it only checks for object identity. |
183 # --------- bga Remove prior comments to reduce the size of the flower |
184 # box. See revision 1.22 for earlier comments. |
185 #--(H-)-----------------------------------------------------------------+
186 """
187 PgSQL - A PyDB-SIG 2.0 compliant module for PostgreSQL.
188
189 Copyright 2000 by Billy G. Allie <Bill.Allie@mug.org>
190 See package documentation for further information on copyright.
191
192 Inline documentation is sparse.
193 See the Python DB-SIG 2.0 specification for usage information.
194
195 basic usage:
196
197 PgSQL.connect(connect_string) -> connection
198 connect_string = 'host:port:database:user:password:options:tty'
199 All parts are optional. You may also pass the information in as
200 keyword arguments with the following keywords: 'host', 'port',
201 'database', 'user', 'password', 'options', and 'tty'. The port
202 may also be passed in as part of the host keyword parameter,
203 ie. host='localhost:5432'. Other optional parameters are
204 client_encoding and unicode_results. If unicode_results is true,
205 all strings from the backend are returned as Unicode strings.
206
207 client_encoding accepts the same parameters as the encode method
208 of Unicode strings. If you also want to set a policy for encoding
209 errors, set client_encoding to a tuple, like ("koi8-r", "replace")
210
211 Note that you still must make sure that the PostgreSQL client is
212 using the same encoding as set with the client_encoding parameter.
213 This is typically done by issuing a "SET CLIENT_ENCODING TO ..."
214 SQL statement immediately after creating the connection.
215
216 connection.cursor() -> cursor
217 Create a new cursor object. A connection can support multiple
218 cursors at the same time.
219
220 connection.close()
221 Closes the connection now (instead of when __del__ is called).
222 The connection will be unusable from this point forward.
223 NOTE: Any uncommited transactions will be rolled back and any
224 open cursors for this connection will be closed.
225
226 connection.commit()
227 Commit any pending transactions for this connection.
228
229 NOTE: This will reset any open cursors for this connection to their
230 inital state. Any PostgreSQL portals in use by cursors will
231 be closed and any remaining query results will be discarded.
232
233 connection.rollback()
234 Rollback any pending transactions for this connection.
235
236 NOTE: This will reset any open cursors for this connection to their
237 inital state. Any PostgreSQL portals in use by cursors will
238 be closed and any remaining query results will be discarded.
239
240 connection.binary(string) -> PgLargeObject
241 Create a new PostgreSQL large object. If string is present, it is
242 written to the new large object. The returned large object will
243 not be opened (i.e. it will be closed).
244
245 connection.un_link(OID|PgLargeObject)
246 Un-links (removes) the PgLargeObject from the database.
247
248 NOTE: This is a PostgreSQL extension to the Connection object.
249 It is not safe to un-link PgLargeObjects while in a trans-
250 action (in versions prior to 7.1) since this action can not
251 be rollbacked completely. Therefore, an attempt to un-link
252 while in a transaction (in versions prior to 7.1) will raise
253 an exception.
254
255 connection.version
256 This instance of the PgVersion class contains information about
257 the version of the PostgreSQL backend to which the connection
258 object is connected to.
259
260 NOTE: This is a PgSQL extension to the Connection object.
261
262 cursor.execute(query[, param1[, param2, ..., paramN])
263 Execute a query, binding the parameters if they are passed. The
264 binding syntax is the same as the '%' operator except that only %s
265 [or %(name)s] should be used and the %s [or %(name)s] should not be
266 quoted. Any necessary quoting will be performed by the execute
267 method.
268
269 cursor.execute(query[, sequence])
270 Execute a query, binding the contents of the sequence as parameters.
271 The binding syntax is the same as the '%' operator except that only
272 %s should be used and the %s should not be quoted. Any necessary
273 quoting will be performed by the execute method.
274
275 cursor.execute(query[, dictionary])
276 Execute a query, binding the contents of the dictionary as para-
277 meters. The binding syntax is the same as the '%' operator except
278 that only %s [or %(name)s] should be used and the %s [or %(name)s]
279 should not be quoted. Any necessary quoting will be performed by
280 the execute method.
281
282 NOTE: In order to use a PostgreSQL portal (i.e. DECLARE ... CURSOR
283 FOR ...), the word SELECT must be the first word in the query,
284 and can only be be proceeded by spaces and tabs. If this is not
285 the case, a cursor will be simulated by reading the entire result
286 set into memory and handing out the results as needed.
287
288 NOTE: PostgreSQL cursors are read-only. SELECT ... FOR UPDATE queries
289 will not use PostgreSQL cursors, but will simulate a cursor by
290 reading the entire result set into memory and handing out the
291 results as needed.
292
293 NOTE: Setting the variable, PgSQL.noPostgresCursor, to 1 will cause
294 PgSQL to NOT use PostgreSQL cursor (DECLARE ... CURSOR FOR ...),
295 even if all the conditions for doing so are met. PgSQL will
296 simulate a cursor by reading the entire result set into memory
297 and handing out the results as needed.
298
299 cursor.executemany(query, sequence_of_params)
300 Execute a query many times, once for each params in the sequence.
301
302 NOTE: The restriction on the use of PostgreSQL cursors described in
303 the cursor.execute() note also applies to cursor.executemany().
304 The params in the sequence of params can be a list, tuple or
305 dictionary.
306
307 cursor.fetchone() -> PgResultSet
308 Fetch the next row of the query result set as a single PgResultSet
309 containing the column data. Returns None when no more rows are
310 available. A PgResultSet is a sequence that can be indexed by
311 a column name in addition to an integer.
312
313 cursor.fetchmany([size]) -> [PgResultSet, ...]
314 Fetch the next set of rows from the query result, returning a
315 sequence of PgResultSets. An empty sequence is returned when
316 no more rows are available.
317
318 The number of rows returned is determined by the size parameter.
319 If the size parameter is ommited, the value of cursor.arraysize
320 is used. If size is given, cursor.arraysize is set to that value.
321
322 cursor.fetchall() -> [PgResultSet, ...]
323 Fetch all (remaining) rows from the query result, returning a
324 sequence of PgResultSets. An empty sequence is returned when
325 no more rows are available.
326
327 cursor.description -> [(column info), ... ]
328 Returns a sequence of 8-item tuples. Each tuple describes one
329 column of the result: (name, type code, display size, internal
330 size, precision, scale, null_ok, isArray).
331
332 NOTE: null_ok is not implemented.
333 isArray is a PostgreSQL specific extension.
334
335 cursor.rowcount
336 The number of rows the last execute produced (for DQL statements)
337 or affected (for DML statement).
338
339 cursor.oidValue
340 The object ID of the inserted record, if the last SQL command
341 was an INSERT, otherwise it returns 0 (aka. InvalidOid)
342
343 NOTE: oidValue is a PostgreSQL specific extension.
344
345 cursor.close()
346 Closes the cursor now (instead of when __del__ is called). The
347 cursor will be unusable from this point forward.
348
349 cursor.rewind()
350 Moves the cursor back to the beginning of the query result.
351 This is a PgSQL extension to the PyDB 2.0 API.
352
353 PgResultSet.description() -> [(column info), ... ]
354 Returns a sequence of 8-item tuples. Each tuple describes one
355 column of the result: (name, type code, display size, internal
356 size, precision, scale, null_ok. isArray).
357
358 NOTE: null_ok is not implemented.
359 isArray is a PostgreSQL specific extension.
360
361 PgResultSet.<column name> -> value
362 Column names are attributes to the PgResultSet.
363
364 Note: Setting the variable, PgSQL.fetchReturnsList = 1 will cause
365 the fetch*() methods to return a list instead of a PgResultSet.
366
367 PgSQL.version
368 This string object contains the version number of PgSQL.
369 """
370
371 from types import *
372 from sys import getrefcount, getdefaultencoding
373 import sys
374 import copy
375 import string
376 import re
377 import new
378
379 try:
380 import weakref
381 noWeakRef = 0
382 except:
383 noWeakRef = 1
384
385 try:
386 from mx import DateTime
387 except ImportError:
388 raise ImportError, \
389 """You need to install mxDateTime
390 (http://www.egenix.com/files/python/eGenix-mx-Extensions.html)"""
391 from pyPgSQL.libpq import *
392
393 version = '$Revision: 1.2 $'
394
395 apilevel = '2.0'
396 threadsafety = 1
397 paramstyle = 'pyformat'
398
399 # Setting this variable to 1 will cause the fetch*() methods to return
400 # a list instead of a PgResultSet
401
402 fetchReturnsList = 0
403
404 # Setting this variable to 1 will prevent the use of a PostgreSQL Cursor
405 # (via the "DECLARE ... CURSOR FOR ..." syntax). A cursor will be simulated
406 # by reading all of the query result into memory and doling out the results
407 # as needed.
408
409 noPostgresCursor = 0
410
411 re_DQL = re.compile('^[\s]*SELECT[\s]', re.I)
412 re_DRT = re.compile('[\s]*DROP[\s]+TABLE[\s]', re.I)
413 re_DRI = re.compile('[\s]*DROP[\s]+INDEX[\s]', re.I)
414 re_4UP = re.compile('[\s]FOR[\s]+UPDATE', re.I)
415
416 replace = string.replace
417
418 #-----------------------------------------------------------------------+
419 # Make the required Date/Time constructor visable in the PgSQL module. |
420 #-----------------------------------------------------------------------+
421
422 Date = DateTime.Date
423 Time = DateTime.Time
424 Timestamp = DateTime.Timestamp
425 DateFromTicks = DateTime.DateFromTicks
426 TimeFromTicks = DateTime.TimeFromTicks
427 TimestampFromTicks = DateTime.TimestampFromTicks
428
429 #-----------------------------------------------+
430 # The DateTimeDelta type for PgInterval support |
431 #-----------------------------------------------+
432
433 DateTimeDelta = DateTime.DateTimeDelta
434
435 #-------------------------------+
436 # Also the DateTime types |
437 #-------------------------------+
438
439 DateTimeType = DateTime.DateTimeType
440 DateTimeDeltaType = DateTime.DateTimeDeltaType
441 DateTimeDelta = DateTime.DateTimeDelta
442
443 #-----------------------------------------------------------------------+
444 # Name: DBAPITypeObject |
445 # |
446 # Description: The DBAPITypeObject class allows implementing the re- |
447 # quired DP-API 2.0 type objects even if their are multi- |
448 # ple database types for the DP-API 2.0 types. |
449 # |
450 # Note: This object is taken from the Python DP-API 2.0 imple- |
451 # mentation hints. |
452 #-----------------------------------------------------------------------+
453
454 class DBAPITypeObject:
455 def __init__(self, name, *values):
456 self.name = name
457 self.values = values
458
459 def __repr__(self):
460 return self.name
461
462 def __cmp__(self, other):
463 if other in self.values:
464 return 0
465 elif other < self.values:
466 return 1
467 return -1
468
469 # Define the object types required by the DB-API 2.0 specification.
470
471 BINARY = DBAPITypeObject('BINARY', PG_OID, PG_BLOB, PG_BYTEA)
472
473 DATETIME = DBAPITypeObject('DATETIME', PG_DATE, PG_TIME, PG_TIMETZ,
474 PG_TIMESTAMP, PG_TIMESTAMPTZ,
475 PG_ABSTIME, PG_RELTIME,
476 PG_INTERVAL, PG_TINTERVAL)
477
478 NUMBER = DBAPITypeObject('NUMBER', PG_INT8, PG_INT2, PG_INT4, PG_FLOAT4,
479 PG_FLOAT8, PG_MONEY, PG_NUMERIC)
480
481 ROWID = DBAPITypeObject('ROWID', PG_OID, PG_ROWID, PG_CID, PG_TID, PG_XID)
482
483 STRING = DBAPITypeObject('STRING', PG_CHAR, PG_BPCHAR, PG_TEXT, PG_VARCHAR,
484 PG_NAME)
485
486 # BOOLEAN is the PostgreSQL boolean type.
487
488 BOOLEAN = DBAPITypeObject('BOOLEAN', PG_BOOL)
489
490 # OTHER is for PostgreSQL types that don't fit in the standard Objects.
491
492 OTHER = DBAPITypeObject('OTHER', PG_POINT, PG_LSEG, PG_PATH, PG_BOX,
493 PG_POLYGON, PG_LINE, PG_CIDR, PG_CIRCLE,
494 PG_INET, PG_MACADDR, PG_ACLITEM,
495 PG_REFCURSOR)
496
497 #-----------------------------------------------------------------------+
498 # Name: PgTypes |
499 # |
500 # Description: PgTypes is an object wrapper for the type OID's used by |
501 # PostgreSQL. It is used to display a meaningful text |
502 # description of the type while still allowing it to be |
503 # compared as a numeric value. |
504 #-----------------------------------------------------------------------+
505
506 class PgTypes:
507 def __init__(self, value):
508 self.value = value
509
510 def __coerce__(self, other):
511 if type(other) in [IntType, LongType, FloatType]:
512 return (self.value, int(other))
513 return None
514
515 def __cmp__(self, other):
516 return cmp(self.value, other)
517
518 def __repr__(self):
519 return repr(self.value)
520
521 def __str__(self):
522 return PQftypeName(self.value)
523
524 def __int__(self):
525 return int(self.value)
526
527 def __long__(self):
528 return long(self.value)
529
530 def __float__(self):
531 return float(self.value)
532
533 def __complex__(self):
534 return complex(self.value)
535
536 #-----------------------------------------------------------------------+
537 # Name: TypeCache |
538 # |
539 # Description: TypeCache is an object that defines methods to: |
540 # 1. Cast PostgreSQL result strings into the appropiate |
541 # Python type or object [typecast()]. |
542 # 2. Retrieve addition information about a type from the |
543 # PostgreSQL system catalogs [getTypeInfo()]. This |
544 # type information is maintained in a local cache so |
545 # that subsequent request for the same information do |
546 # not require a database query to fulfill it. |
547 #-----------------------------------------------------------------------+
548
549 class TypeCache:
550 """Type cache -- used to cache postgreSQL data type information."""
551
552 def __init__(self, conn):
553 if noWeakRef:
554 self.__conn = conn
555 else:
556 self.__conn = weakref.proxy(conn, self.__callback)
557 self.__type_cache = {}
558 self.__lo_cache = {}
559
560 def __callback(self, o):
561 self.__conn = None
562
563 def interval2DateTimeDelta(self, s):
564 """Parses PostgreSQL INTERVALs.
565 The expected format is [[[-]YY years] [-]DD days] [-]HH:MM:SS.ss"""
566 parser = DateTime.Parser.DateTimeDeltaFromString
567
568 ydh = s.split()
569 ago = 1
570
571 result = DateTimeDelta(0)
572
573 # Convert any years using 365.2425 days per year, which is PostgreSQL's
574 # assumption about the number of days in a year.
575 if len(ydh) > 1:
576 if ydh[1].lower().startswith('year'):
577 result += parser('%s days' % ((int(ydh[0]) * 365.2425),))
578 ydh = ydh[2:]
579
580 # Converts any days and adds it to the years (as an interval)
581 if len(ydh) > 1:
582 if ydh[1].lower().startswith('day'):
583 result += parser('%s days' % (ydh[0],))
584 ydh = ydh[2:]
585
586 # Adds in the hours, minutes, seconds (as an interval)
587 if len(ydh) > 0:
588 result += parser(ydh[0])
589
590 return result
591
592 def parseArray(self, s):
593 """Parse a PostgreSQL array strings representation.
594 This parses a PostgreSQL array and return a list of the array
595 elements as strings.
596 """
597 class LeaveLoopException(Exception): pass
598
599 # Get rid of the escaping in the array string
600 def convertEscapes(s):
601 # If we're called with a list in a multi-dimensional
602 # array, simply return the list. We only convert the
603 # elements of the multi-dimensional array.
604 if type(s) is ListType:
605 return s
606
607 schars = []
608 escape = 0
609 octdigits = []
610
611 for char in s:
612 if char == '\\':
613 escape += 1
614 if escape == 2:
615 schars.append(char)
616 escape = 0
617 else:
618 if escape:
619 if char in string.digits:
620 octdigits.append(char)
621 else:
622 if octdigits != []:
623 curchar = chr(int(octdigits[0]) * 64) + \
624 chr(int(octdigits[1]) * 8) + \
625 chr(int(octdigits[2]))
626 schars.append(curchar)
627 octdigits = []
628 schars.append(char)
629 else:
630 schars.append(char)
631 escape = 0
632 return "".join(schars)
633
634 lst = PgArray()
635 s = s[1:-1] # drop '{' and '}' at start/end
636
637 # If the array is empty, return immediately
638 if len(s) == 0:
639 return lst
640
641 pos = 0
642 try:
643 while 1:
644 if s[pos] == '"':
645 # A quoted element, find the end-quote, which is the next
646 # quote char that is not escaped.
647 end_quote_pos = pos + 1
648 escape = 0
649 while 1:
650 if s[end_quote_pos] == '\\':
651 escape = not escape
652 elif s[end_quote_pos] == '"':
653 if not escape:
654 break
655 escape = 0
656 else:
657 escape = 0
658 end_quote_pos += 1
659 lst.append(convertEscapes(s[pos + 1:end_quote_pos]))
660
661 # Skip quote char and next comma
662 pos = end_quote_pos + 2
663
664 # If end-of-string. leave loop.
665 if pos >= len(s):
666 break
667 else:
668 # This array element is not quoted, so it ends either at
669 # the next comma that isn't escaped, or at the end of the
670 # string, or, if it contains a subarray, at the position
671 # of the corresponding curly brace.
672 if s[pos] != '{':
673 next_comma_pos = pos + 1
674 escape = 0
675 while 1:
676 if next_comma_pos >= len(s):
677 # This is the last array element.
678 lst.append(convertEscapes(s[pos:]))
679 raise LeaveLoopException
680
681 if s[next_comma_pos] == '\\':
682 escape = not escape
683 elif s[next_comma_pos] == ',':
684 if not escape:
685 break
686 escape = 0
687 else:
688 escape = 0
689 next_comma_pos += 1
690
691 curelem = s[pos:next_comma_pos]
692 if curelem.startswith("{"):
693 lst.append(self.parseArray(curelem[1:-1]))
694 else:
695 lst.append(convertEscapes(curelem))
696 pos = next_comma_pos + 1
697 if s[pos] == ',':
698 pos += 1
699 else:
700 # The current character is '{', which means we've
701 # found a sub-array:
702 # We find the end of the sub-array, then feed this
703 # string into parseArray again.
704 escape = 0
705 open_braces = 1
706 closing_brace_pos = pos + 1
707 in_quotes = 0
708 while 1:
709 if s[closing_brace_pos] == '\\':
710 escape = not escape
711 elif s[closing_brace_pos] == '{':
712 if (not escape) and (not in_quotes):
713 open_braces += 1
714 escape = 0
715 elif s[closing_brace_pos] == '}':
716 if (not escape) and (not in_quotes):
717 open_braces -= 1
718 if open_braces == 0:
719 break
720 escape = 0
721 elif s[closing_brace_pos] == '"':
722 if not escape:
723 in_quotes = not in_quotes
724 escape = 0
725 else:
726 escape = 0
727 closing_brace_pos += 1
728
729 curelem = s[pos:closing_brace_pos + 1]
730 lst.append(self.parseArray(curelem))
731 pos = closing_brace_pos + 1
732 if pos >= len(s):
733 break
734 if s[pos] == ',':
735 pos += 1
736
737 except LeaveLoopException:
738 pass
739
740 #lst = map(convertEscapes, lst)
741 return lst
742
743 def typecast(self, colinfo, value):
744 """
745 typecast(rowinfo, value)
746 Convert certain postgreSQL data types into the appropiate python
747 object."""
748
749 if value is None:
750 return value
751
752 _fn, _ft, _ds, _is, _p, _s, _nu, _ia = colinfo
753
754 _ftv = _ft.value
755
756 if _ia:
757 # Convert string representation of the array into PgArray object.
758 _list = self.parseArray(value)
759 return self.handleArray(colinfo, _list)
760
761 if _ftv == PG_INT2:
762 if type(value) is PgInt2Type:
763 return value
764 else:
765 return int(value)
766 elif _ftv == PG_INT4:
767 if type(value) is IntType:
768 return value
769 else:
770 return int(value)
771 elif _ftv == PG_INT8 or _ftv == ROWID:
772 if type(PgInt8) is ClassType:
773 if isinstance(value, PgInt8):
774 return value
775 else:
776 if type(value) is PgInt8Type:
777 return value
778 return long(value)
779 elif _ftv == PG_NUMERIC:
780 if isinstance(value, PgNumeric):
781 return value
782 else:
783 try:
784 return PgNumeric(value, _p, _s)
785 except OverflowError:
786 # If we reached this point, then the precision and scale
787 # of the current field does not match the precision and
788 # scale of the first record in the result set (there are
789 # a few reasons why this can happen). Let PgNumeric
790 # figure out a precision and scale from the value.
791 return PgNumeric(value)
792 elif _ftv == PG_MONEY:
793 if isinstance(value, PgMoney):
794 return value
795 else:
796 return PgMoney(value).value
797 elif _ftv == DATETIME:
798 if type(value) in [DateTimeType, DateTimeDeltaType]:
799 return value
800 else:
801 if _ftv == PG_INTERVAL:
802 return value #self.interval2DateTimeDelta(value)
803 else:
804 return value #DateTime.ISO.ParseAny(value)
805 elif _ftv == BINARY:
806 if isinstance(value, PgBytea) or type(value) is PgLargeObjectType:
807 return value
808 elif type(value) is IntType:
809 return PgLargeObject(self.conn, value)
810 else:
811 return PgBytea(value)
812 elif _ftv == PG_REFCURSOR:
813 return self.__conn.cursor(value, isRefCursor=PG_True)
814 elif _ftv == OTHER:
815 if isinstance(value, PgOther):
816 return value
817 else:
818 return value
819 elif self.__conn.unicode_results \
820 and _ftv in (PG_CHAR, PG_BPCHAR, PG_TEXT, PG_VARCHAR, PG_NAME):
821 return unicode(value, *self.__conn.client_encoding)
822 # Other typecasting is not needed. It will be once support for
823 # the other built-in types (ie. box, line, inet, cidr, etc) are added.
824
825 return value
826
827 def handleArray(self, colinfo, lst):
828 # If the list is empty, just return the empty list.
829
830 if len(lst) == 0:
831 return lst
832
833 _fn, _ft, _ds, _is, _p, _s, _nu, _ia = colinfo
834
835 _ftv = _ft.value
836
837 for _i in range(len(lst)):
838 if isinstance(lst[_i], PgArray):
839 lst[_i] = self.handleArray(colinfo, lst[_i])
840 elif _ftv == PG_INT4 or _ftv == ROWID:
841 lst[_i] = int(lst[_i])
842 elif _ftv == PG_INT8:
843 lst[_i] = PgInt8(lst[_i])
844 elif _ftv == PG_NUMERIC:
845 try:
846 lst[_i] = PgNumeric(lst[_i], _p, _s)
847 except OverflowError:
848 # If we reached this point, then the precision and scale
849 # of the current field does not match the precision and
850 # scale of the first record in the result set (there are
851 # a few reasons why this can happen). Let PgNumeric
852 # figure out a precision and scale from the value.
853 lst[_i] = PgNumeric(lst[_i])
854 elif _ftv == PG_INT2:
855 lst[_i] = PgInt2(lst[_i])
856 elif _ftv == DATETIME:
857 lst[_i] = DateTime.ISO.ParseAny(lst[_i])
858 elif _ftv == PG_MONEY:
859 if lst[_i][0] == '(':
860 lst[_i] = PgMoney(-float(replace(lst[_i][2:-1], ',', '')))
861 elif lst[_i][0] == '-':
862 lst[_i] = PgMoney(-float(replace(lst[_i][2:], ',', '')))
863 else:
864 lst[_i] = PgMoney(float(replace(lst[_i][1:], ',', '')))
865 elif _ftv == BINARY:
866 if _ftv == PG_BYTEA:
867 # There is no need to un-escape lst[_i], it's already been
868 # done when the PostgreSQL array was converted to a list
869 # via parseArray().
870 lst[_i] = PgBytea(lst[_i])
871 else:
872 lst[_i] = PgLargeObject(self.conn, int(lst[_i]))
873
874 return lst
875
876 def getTypeInfo(self, pgtype):
877 try:
878 return self.__type_cache[pgtype.value]
879 except KeyError:
880 _nl = len(self.__conn.notices)
881 _res = self.__conn.conn.query("SELECT typname, -1 , typelem "
882 "FROM pg_type "
883 "WHERE oid = %s" % pgtype.value)
884
885 if len(self.__conn.notices) != _nl:
886 raise Warning, self.__conn.notices.pop()
887
888 _n = _res.getvalue(0, 0)
889 _p = _res.getvalue(0, 1)
890 _b = _res.getvalue(0, 2)
891 if _n[0] == '_':
892 _n = _n[1:]
893 _i = 1
894 else:
895 _i = 0
896
897 self.__type_cache[pgtype.value] = (_n, _p, _i, PgTypes(_b))
898
899 return self.__type_cache[pgtype.value]
900
901 #-----------------------------------------------------------------------+
902 # Name: PgOther |
903 # |
904 # Description: A Python wrapper class for the PostgreSQL types that do |
905 # not (yet) have an implementation in python. The number |
906 # of types in this category will shrink as more wrappers |
907 # are implemented. |
908 # |
909 # Note: A Python String is used to store the PostgreSQL type in |
910 # class. |
911 #-----------------------------------------------------------------------+
912
913 class PgOther:
914 def __init__(self, value):
915 if type(value) is not StringType:
916 raise TypeError, "argument must be a string."
917
918 self.value = value
919
920 if hasattr(value, '__methods__'):
921 for i in self.value.__methods__:
922 exec 'self.%s = self.value.%s' % (i, i)
923
924 # This definition of __coerce__ will cause Python to always call the
925 # (existing) arithmatic operators for this class. We can the perform the
926 # appropiate operation on the base type, letting it decide what to do.
927 def __coerce__(self, other):
928 return (self, other)
929
930 def __getitem__(self, index):
931 if type(index) is SliceType:
932 if index.step is None:
933 return PgOther(self.value[index.start:index.stop])
934 else:
935 return PgOther(self.value[index.start:index.stop:index.step])
936
937 return self.value[index];
938
939 def __setitem__(self, index, item):
940 raise TypeError, "object doesn't support slice assignment"
941
942 def __delitem__(self, index):
943 raise TypeError, "object doesn't support slice deletion"
944
945 def __contains__(self, item):
946 return (item in self.value)
947
948 if sys.version_info < (2, 0):
949 # They won't be defined if version is at least 2.0 final
950 def __getslice__(self, i, j):
951 return PgOther(self.value[max(0, i):max(0, j)])
952
953 def __setslice__(self, i, j, seq):
954 raise TypeError, "object doesn't support slice assignment"
955
956 def __delslice__(self, i, j):
957 raise TypeError, "object doesn't support slice deletion"
958
959 # NOTE: A string is being concatenated to a PgOther, so the result type
960 # is a PgOther
961 def __add__(self, other):
962 return PgOther((self.value + other))
963
964 # NOTE: A PgOther is being concatenated to a string, so the result type
965 # is a string.
966 def __radd__(self, other):
967 return (other + self.value)
968
969 def __mul__(self, other):
970 return PgOther((self.value * other))
971
972 def __rmul__(self, other):
973 return PgOther((self.value * other))
974
975 def __repr__(self):
976 return repr(self.value)
977
978 def __str__(self):
979 return str(self.value)
980
981 def __hash__(self):
982 return hash(self.value)
983
984 def __cmp__(self, other):
985 return cmp(self.value, other)
986
987 def __rcmp__(self, other):
988 return cmp(other, self.value)
989
990 def __lt__(self, other):
991 return self.value < other
992
993 def __le__(self, other):
994 return self.value <= other
995
996 def __eq__(self, other):
997 return self.value == other
998
999 def __ne__(self, other):
1000 return self.value != other
1001
1002 def __gt__(self, other):
1003 return self.value > other
1004
1005 def __ge__(self, other):
1006 return self.value >= other
1007
1008 # NOTE: A PgOther object will use the PgQuoteString() function in libpq.
1009 def _quote(self, forArray=0):
1010 if self.value:
1011 return PgQuoteString(self.value, forArray)
1012 return 'NULL'
1013
1014 #-----------------------------------------------------------------------+
1015 # Name: PgArray |
1016 # |
1017 # Description: A Python wrapper class for PostgreSQL arrays. |
1018 # It is used so that the list type can be used as an arg- |
1019 # ument to Connection.execute() without being treated as |
1020 # a PostgreSQL array. |
1021 #-----------------------------------------------------------------------+
1022
1023 class PgArray:
1024 def __init__(self, value=None):
1025 if value is None:
1026 self.value = []
1027 return
1028
1029 if type(value) is not ListType and not isinstance(value, PgArray):
1030 raise TypeError, "argument must be a list or a PgArray."
1031
1032 # We have to insure that nested mutable sequences (list and PgArray)
1033 # get copied.
1034 for i in range(len(value)):
1035 if type(value[i]) is ListType or isinstance(value[i], PgArray):
1036 value[i] = PgArray(value[i][:])
1037
1038 self.value = value
1039
1040 # Define the methods used
1041 def append(self, item):
1042 if type(item) is ListType:
1043 item = PgArray(item)
1044 self.value.append(item)
1045
1046 def count(self, item):
1047 return self.value.count(item)
1048
1049 def extend(self, item):
1050 if type(item) is ListType:
1051 item = PgArray(item)
1052 self.value.extend(item)
1053
1054 def index(self, item):
1055 return self.value.index(item)
1056
1057 def insert(self, key, item):
1058 if type(item) is ListType:
1059 item = PgArray(item)
1060 self.value.insert(key, item)
1061
1062 def pop(self, key=-1):
1063 return self.value.pop(key)
1064
1065 def remove(self, item):
1066 self.value.remove(item)
1067
1068 def reverse(self):
1069 self.value.reverse()
1070
1071 def sort(self, compfunc=None):
1072 if compfunc is None:
1073 self.value.sort()
1074 else:
1075 self.value.sort(compfunc)
1076
1077 def __cmp__(self, other):
1078 if not isinstance(other, PgArray):
1079 return cmp(id(self), id(other))
1080
1081 # PgArray objects are considered equal if:
1082 # 1. The lengh of the PgArray objects are equal and
1083 # 2. Each item[k] in the PgArray objects are equal.
1084
1085 res = cmp(len(self), len(other))
1086 if res != 0:
1087 return res
1088
1089 for i in range(len(self.value)):
1090 res = cmp(self.value[i], other.value[i])
1091 if res != 0:
1092 return res
1093
1094 return 0
1095
1096 def __len__(self):
1097 return len(self.value)
1098
1099 def __getitem__(self, index):
1100 if type(index) is SliceType:
1101 if index.step is None:
1102 return PgArray(self.value[index.start:index.stop])
1103 else:
1104 return PgArray(self.value[index.start:index.stop:index.step])
1105
1106 return self.value[index];
1107
1108 def __setitem__(self, index, item):
1109 if type(item) is ListType:
1110 item = PgArray(item)
1111
1112 if type(index) is SliceType:
1113 if index.step is None:
1114 self.value[index.start:index.stop] = item
1115 else:
1116 self.value[index.start:index.stop:index.step] = item
1117 else:
1118 self.value[index] = item
1119
1120 def __delitem__(self, index):
1121 if type(index) is SliceType:
1122 if index.step is None:
1123 del self.value[index.start:index.stop]
1124 else:
1125 del self.value[index.start:index.stop:index.step]
1126 else:
1127 del self.value[index];
1128
1129 def __contains__(self, item):
1130 return (item in self.value)
1131
1132 if sys.version_info < (2, 0):
1133 # They won't be defined if version is at least 2.0 final
1134 def __getslice__(self, i, j):
1135 return PgArray(self.value[max(0, i):max(0, j)])
1136
1137 def __setslice__(self, i, j, seq):
1138 if type(seq) is ListType:
1139 seq = PgArray(seq)
1140 self.value[max(0, i):max(0, j)] = seq.value
1141
1142 def __delslice__(self, i, j):
1143 del self.value[max(0, i):max(0, j)]
1144
1145 def __add__(self, other):
1146 return PgArray((self.value + other))
1147
1148 def __radd__(self, other):
1149 return PgArray(other + self.value)
1150
1151 def __iadd__(self, other):
1152 value = value + other
1153 return self
1154
1155 def __mul__(self, other):
1156 return PgArray((self.value * other))
1157
1158 def __rmul__(self, other):
1159 return PgArray((self.value * other))
1160
1161 def __imul__(self, other):
1162 value = value * other
1163 return self
1164
1165 def __repr__(self):
1166 return repr(self.value)
1167
1168 def __str__(self):
1169 return str(self.value)
1170
1171 # NOTE: A PgArray object will use the _handleArray() function to quote
1172 # itself.
1173 def _quote(self, forArray=0):
1174 if self.value:
1175 return _handleArray(self.value)
1176 return 'NULL'
1177
1178 #-----------------------------------------------------------------------+
1179 # Name: PgBytea |
1180 # |
1181 # Description: A Python wrapper class for the PostgreSQL BYTEA type. |
1182 # |
1183 # Note: A Python String is used to store the PostgreSQL type in |
1184 # class. |
1185 #-----------------------------------------------------------------------+
1186
1187 class PgBytea:
1188 def __init__(self, value):
1189 if type(value) is not StringType:
1190 raise TypeError, "argument must be a string."
1191
1192 self.value = value
1193
1194 if hasattr(value, '__methods__'):
1195 for i in self.value.__methods__:
1196 exec 'self.%s = self.value.%s' % (i, i)
1197
1198 # This definition of __coerce__ will cause Python to always call the
1199 # (existing) arithmatic operators for this class. We can the perform the
1200 # appropiate operation on the base type, letting it decide what to do.
1201 def __coerce__(self, other):
1202 return (self, other)
1203
1204 def __getitem__(self, index):
1205 if type(index) is SliceType:
1206 if index.step is None:
1207 return PgBytea(self.value[index.start:index.stop])
1208 else:
1209 return PgBytea(self.value[index.start:index.stop:index.step])
1210
1211 return self.value[index];
1212
1213 def __setitem__(self, index, item):
1214 raise TypeError, "object doesn't support slice assignment"
1215
1216 def __delitem__(self, index):
1217 raise TypeError, "object doesn't support slice deletion"
1218
1219 def __contains__(self, item):
1220 return (item in self.value)
1221
1222 if sys.version_info < (2, 0):
1223 # They won't be defined if version is at least 2.0 final
1224 def __getslice__(self, i, j):
1225 return PgBytea(self.value[max(0, i):max(0, j)])
1226
1227 def __setslice__(self, i, j, seq):
1228 raise TypeError, "object doesn't support slice assignment"
1229
1230 def __delslice__(self, i, j):
1231 raise TypeError, "object doesn't support slice deletion"
1232
1233 def __add__(self, other):
1234 return PgBytea((self.value + other))
1235
1236 def __radd__(self, other):
1237 return PgBytea(other + self.value)
1238
1239 def __mul__(self, other):
1240 return PgBytea((self.value * other))
1241
1242 def __rmul__(self, other):
1243 return PgBytea((self.value * other))
1244
1245 def __repr__(self):
1246 return repr(self.value)
1247
1248 def __str__(self):
1249 return str(self.value)
1250
1251 def __hash__(self):
1252 return hash(self.value)
1253
1254 def __cmp__(self, other):
1255 return cmp(self.value, other)
1256
1257 def __rcmp__(self, other):
1258 return cmp(other, self.value)
1259
1260 def __lt__(self, other):
1261 return self.value < other
1262
1263 def __le__(self, other):
1264 return self.value <= other
1265
1266 def __eq__(self, other):
1267 return self.value == other
1268
1269 def __ne__(self, other):
1270 return self.value != other
1271
1272 def __gt__(self, other):
1273 return self.value > other
1274
1275 def __ge__(self, other):
1276 return self.value >= other
1277
1278 # NOTE: A PgBytea object will use the PgQuoteBytea() function in libpq
1279 def _quote(self, forArray=0):
1280 if self.value:
1281 return PgQuoteBytea(self.value, forArray)
1282 return 'NULL'
1283
1284 #-----------------------------------------------------------------------+
1285 # Name: PgNumeric |
1286 # |
1287 # Description: A Python wrapper class for the PostgreSQL numeric type. |
1288 # It implements addition, subtraction, mulitplcation, and |
1289 # division of scaled, fixed precision numbers. |
1290 # |
1291 # Note: The PgNumeric class uses a Python Long type to store |
1292 # the PostgreSQL numeric type. |
1293 #-----------------------------------------------------------------------+
1294
1295 class PgNumeric:
1296 def __init__(self, value, prec=None, scale=None):
1297 if type(value) in [IntType, LongType] or value is None:
1298 if prec is None or scale is None:
1299 raise TypeError, \
1300 "you must supply precision and scale when value is a " \
1301 "integer, long, or None"
1302 if value is None:
1303 self.__v = value
1304 else:
1305 self.__v = long(value)
1306 # Check to see if the value is too large for the given
1307 # precision/scale
1308 _v = str(abs(value))
1309 if _v[-1:] == 'L':
1310 _v = _v[:-1]
1311 if len(_v) > prec:
1312 raise OverflowError, "value too large for PgNumeric"
1313
1314 self.__p = prec
1315 self.__s = scale
1316 elif type(value) in (FloatType, StringType):
1317 # Get the value to convert as a string.
1318 # The expected input is in the form of [+|-][d]*[.[d]*][e[d]+]
1319
1320 # First get the value as a (trimmed) string
1321 if type(value) is FloatType:
1322 _v = str(value)
1323 else:
1324 _v = value.split()
1325 if len(_v) == 0 or len(_v) > 1:
1326 raise ValueError, \
1327 "invalid literal for PgNumeric: %s" % value
1328 _v = _v[0]
1329
1330 # Save the sign character (if any) and remove from the string
1331 _sign = '+'
1332 if _v[0] in ('-', '+'):
1333 _sign = _v[0]
1334 _v = _v[1:]
1335
1336 # Split the remaining string into int part, frac part and exponet
1337 _d = _v.rfind('.')
1338 _e = _v.rfind('e')
1339
1340 # Ensure that _e and _v contains a sane value
1341 if _e < 0:
1342 _e = _v.rfind('E')
1343 if _e < 0:
1344 _e = len(_v)
1345
1346 if _d < 0:
1347 _d = _e
1348
1349 _ip = _v[:_d]
1350 _fp = _v[_d+1:_e]
1351 _xp = _v[_e+1:]
1352
1353 # Check the validity of the input
1354 if len(_ip) == 0 and len(_fp) == 0:
1355 raise ValueError, \
1356 "invalid literal for PgNumeric: %s" % value
1357
1358 if len(_xp) > 0:
1359 try:
1360 _exp = int(_xp)
1361 except:
1362 raise ValueError, \
1363 "invalid literal for PgNumeric: %s" % value
1364 else:
1365 _exp = 0
1366
1367 if _exp > 999:
1368 raise OverflowError, "value too large for PgNumeric"
1369
1370 if _exp < -999:
1371 raise OverflowError, "value too small for PgNumeric"
1372
1373 # Create the string that will become the base (long) object
1374 _v = _ip + _fp
1375
1376 _sc = len(_fp)
1377 if _exp > 0:
1378 if _exp > _sc:
1379 _v = _v + ("0" * (_exp - _sc))
1380 _sc = 0
1381 else:
1382 _sc = _sc - _exp
1383 else:
1384 _sc = _sc - _exp
1385
1386 try:
1387 self.__v = long(_sign + _v)
1388 except:
1389 raise ValueError, \
1390 "invalid literal for PgNumeric: %s" % value
1391
1392 self.__p = len(_v)
1393 if self.__p < _sc:
1394 self.__p = _sc
1395 self.__s = _sc
1396
1397 # Now adjust for the inputted scale (if any)
1398 if scale is None:
1399 pass
1400 else:
1401 _adj = scale - self.__s
1402 if _adj > 0:
1403 self.__v = self.__v * (10L ** (scale - self.__s))
1404 elif _adj < 0:
1405 self.__v = self._round(self.__v, -_adj)
1406
1407 self.__p = self.__p + _adj
1408 self.__s = scale
1409
1410 # Apply the inputted precision (if any)
1411 if prec is None:
1412 pass
1413 else:
1414 if prec > 1000:
1415 raise ValueError, "precision too large for PgNumeric"
1416 elif self.__p > prec:
1417 raise OverflowError, "value too large for PgNumeric"
1418 else:
1419 self.__p = prec
1420 elif isinstance(value, PgNumeric):
1421 # This is used to "cast" a PgNumeric to the specified precision
1422 # and scale. It can also make a copy of a PgNumeric.
1423 self.__v = value.__v
1424 if scale:
1425 self.__s = scale
1426 _ds = scale - value.__s
1427 else:
1428 self.__s = value.__s
1429 _ds = 0
1430 if prec:
1431 self.__p = prec
1432 else:
1433 self.__p = value.__p
1434 # Now we adjust the value to reflect the new scaling factor.
1435 if _ds > 0:
1436 if _ds == 1:
1437 self.__v = self.__v * 10
1438 else:
1439 self.__v = self.__v * (10L ** _ds)
1440 elif _ds < 0:
1441 self.__v = self._round(self.__v, -_ds)
1442 if self.__v > (10L ** self.__p):
1443 raise OverflowError, "result exceeds precision of %d" % self.__p
1444 else:
1445 raise TypeError, "value can not be converted to a PgNumeric."
1446
1447 if self.__s > self.__p:
1448 raise ValueError, \
1449 "scale of %d exceeds precision of %d" % (self.__s, self.__p)
1450
1451 # The value (10L ** self.__s) is used a lot. Save it as a constant
1452 # to save a (small) bit of time.
1453
1454 self.__sf = 10L ** self.__s
1455
1456 def __fmtNumeric(self, value=None):
1457 if value is None:
1458 _v = self.__v
1459 else:
1460 _v = value
1461
1462 # Check for a negative value and adjust if necessary
1463 if _v < 0:
1464 _sign = '-'
1465 _v = -_v
1466 else:
1467 _sign = ''
1468 _v = str(_v)
1469
1470 # Check to see if the string representation of the python long has
1471 # a trailing 'L', if so, remove it. Python 1.5 has the trailing 'L',
1472 # Python 1.6 does not.
1473 if _v[-1:] == 'L':
1474 _v = _v[:-1]
1475
1476 # Check to see if the numeric is less than one and fix string if so.
1477 if len(_v) <= self.__s:
1478 _v = ("0" * (self.__s - len(_v) + 1)) + _v
1479
1480 if self.__s:
1481 _s = "%s%s.%s" % (_sign, _v[:-(self.__s)], _v[-(self.__s):])
1482 else:
1483 _s = "%s%s" % (_sign, _v)
1484 return _s
1485
1486 def __repr__(self):
1487 return "<PgNumeric instance - precision: %d scale: %d value: %s>" % \
1488 (self.__p, self.__s, self.__fmtNumeric())
1489
1490 def __str__(self):
1491 return self.__fmtNumeric()
1492
1493 def getScale(self):
1494 return self.__s
1495
1496 def getPrecision(self):
1497 return self.__p
1498
1499 def __coerce__(self, other):
1500 if isinstance(other, PgNumeric):
1501 return self, other
1502 elif type(other) in [IntType, LongType]:
1503 _s = str(other)
1504 if _s[-1:] == 'L':
1505 _s = _s[:-1] # Work around v1.5/1.6 differences
1506 return (self, PgNumeric(_s))
1507 elif type(other) == FloatType:
1508 return (self, PgNumeric(other))
1509 return None
1510
1511 def _round(self, value, drop):
1512 if drop == 1:
1513 return ((value + 5L) / 10L)
1514 elif drop > 1:
1515 return (((value / (10L ** (drop - 1))) + 5L) / 10L)
1516
1517 return value
1518
1519 def __add__(self, other):
1520 _c = self.__coerce__(other)
1521 if _c is None:
1522 return None
1523 self, other = _c
1524 if self.__s < other.__s:
1525 _s = self.__v * (other.__sf / self.__sf)
1526 _o = other.__v
1527 elif self.__s > other.__s:
1528 _s = self.__v
1529 _o = other.__v * (self.__sf / other.__sf)
1530 else:
1531 _s = self.__v
1532 _o = other.__v
1533
1534 mp = max(self.__p - self.__s, other.__p - other.__s)
1535 ms = max(self.__s, other.__s)
1536 v = _s + _o
1537 # Check to see if the addition caused an increase in the precision
1538 # due to a carry. If so, compensate for it.
1539 if (v / (10L ** (mp + ms))) > 0:
1540 mp = mp + 1
1541
1542 return PgNumeric((_s + _o), (mp + ms), ms)
1543
1544 def __radd__(self, other):
1545 return self.__add__(other)
1546
1547 def __iadd__(self, other):
1548 _r = self.__add__(other)
1549 if _r is None:
1550 return None
1551 self.__v = _r.__v
1552 self.__p = _r.__p
1553 self.__s = _r.__s
1554 self.__sf = _r.__sf
1555 return self
1556
1557 def __sub__(self, other):
1558 _c = self.__coerce__(other)
1559 if _c is None:
1560 return None
1561 self, other = _c
1562 if self.__s < other.__s:
1563 _s = self.__v * (other.__sf / self.__sf)
1564 _o = other.__v
1565 elif self.__s > other.__s:
1566 _s = self.__v
1567 _o = other.__v * (self.__sf / other.__sf)
1568 else:
1569 _s = self.__v
1570 _o = other.__v
1571
1572 mp = max(self.__p - self.__s, other.__p - other.__s)
1573 ms = max(self.__s, other.__s)
1574 return PgNumeric((_s - _o), (mp + ms), ms)
1575
1576 def __rsub__(self, other):
1577 return other.__sub__(self)
1578
1579 def __isub__(self, other):
1580 _r = self.__sub__(other)
1581 if _r is None:
1582 return None
1583 self.__v = _r.__v
1584 self.__p = _r.__p
1585 self.__s = _r.__s
1586 self.__sf = _r.__sf
1587 return self
1588
1589 def __mul__(self, other):
1590 _c = self.__coerce__(other)
1591 if _c is None:
1592 return None
1593 self, other = _c
1594 _p = self.__v * other.__v
1595 return PgNumeric(_p, self.__p + other.__p, self.__s + other.__s)
1596
1597 def __rmul__(self, other):
1598 return self.__mul__(self, other)
1599
1600 def __imul__(self, other):
1601 _r = self.__mul__(other)
1602 if _r is None:
1603 return None
1604 self.__v = _r.__v
1605 self.__p = _r.__p
1606 self.__s = _r.__s
1607 self.__sf = _r.__sf
1608 return self
1609
1610 def __div__(self, other):
1611 _c = self.__coerce__(other)
1612 if _c is None:
1613 return None
1614 self, other = _c
1615 _n = self.__v * other.__sf * self.__sf
1616 _d = other.__v
1617 _q = self._round((_n / _d), self.__s)
1618 return PgNumeric(_q, self.__p, self.__s)
1619
1620 def __rdiv__(self, other):
1621 return other.__div__(self)
1622
1623 def __idiv__(self, other):
1624 _r = self.__div__(other)
1625 if _r is None:
1626 return None
1627 self.__v = _r.__v
1628 self.__p = _r.__p
1629 self.__s = _r.__s
1630 self.__sf = _r.__sf
1631 return self
1632
1633 def __cmp__(self, other):
1634 if other is None:
1635 return 1
1636 _c = self.__coerce__(other)
1637 if _c is None:
1638 return None
1639 self, other = _c
1640 if self.__s < other.__s:
1641 _s = self.__v * (other.__sf / self.__sf)
1642 _o = other.__v
1643 elif self.__s > other.__s:
1644 _s = self.__v
1645 _o = other.__v * (self.__sf / other.__sf)
1646 else:
1647 _s = self.__v
1648 _o = other.__v
1649 return cmp(_s, _o)
1650
1651 def __neg__(self):
1652 return PgNumeric(-self.__v, self.__p, self.__s)
1653
1654 def __nonzero__(self):
1655 return self.__v not in (None, 0)
1656
1657 def __pos__(self):
1658 return PgNumeric(self.__v, self.__p, self.__s)
1659
1660 def __abs__(self):
1661 if self.__v >= 0:
1662 return PgNumeric(self.__v, self.__p, self.__s)
1663 else:
1664 return PgNumeric(-self.__v, self.__p, self.__s)
1665
1666 def _quote(self, forArray=0):
1667 if self.__v:
1668 if forArray:
1669 return '"%s"' % self.__fmtNumeric()
1670 else:
1671 return "'%s'" % self.__fmtNumeric()
1672 return 'NULL'
1673
1674 def __int__(self):
1675 return int(self.__v / self.__sf)
1676
1677 def __long__(self):
1678 return self.__v / self.__sf
1679
1680 def __float__(self):
1681 v = self.__v
1682 s = self.__sf
1683 return (float(v / s) + (float(v % s) / float(s)))
1684
1685 def __complex__(self):
1686 return complex(self.__float__())
1687
1688 def __hash__(self):
1689 if self.__s == 0:
1690 return hash(self.__v)
1691 v = self.__v / self.__sf
1692 if (v * self.__sf) == self.__v:
1693 return hash(v)
1694 return hash(float(self))
1695
1696 #-----------------------------------------------------------------------+
1697 # Name: PgMoney |
1698 # |
1699 # Description: A Python wrapper class for the PostgreSQL Money type. |
1700 # It's primary purpose it to check for overflow during |
1701 # calulations and to provide formatted output. |
1702 # |
1703 # Note: The PgMoney class uses a Python Floating point number |
1704 # represent a PostgreSQL money type. |
1705 #-----------------------------------------------------------------------+
1706
1707 class PgMoney:
1708 def __init__(self, value):
1709 if value is None:
1710 self.value = value
1711 return
1712
1713 if type(value) is StringType:
1714 if value[0] == '(':
1715 self.value = PgMoney(-float(replace(value[2:-1], ',', '')))
1716 elif value[0] == '-':
1717 self.value = PgMoney(-float(replace(value[2:], ',', '')))
1718 else:
1719 self.value = PgMoney(float(replace(value[1:], ',', '')))
1720 else:
1721 self.value = float(value)
1722
1723 if self.value < -21474836.48 or self.value > 21474836.47:
1724 raise OverflowError, 'money initialization'
1725
1726
1727 def __checkresult(self, value, op):
1728 if value < -21474836.48 or value > 21474836.47:
1729 raise OverflowError, 'money %s' % op
1730 return PgMoney(value)
1731
1732 def __coerce__(self, other):
1733 if other is None:
1734 return None
1735 res = coerce(self.value, other)
1736 if res is None:
1737 return None
1738 _s, _o = res
1739 return (self, _o)
1740
1741 def __hash__(self):
1742 return hash(self.value)
1743
1744 def __cmp__(self, other):
1745 return cmp(self.value, other)
1746
1747 def __add__(self, other):
1748 return self.__checkresult(self.value + other, "addition")
1749
1750 def __sub__(self, other):
1751 return self.__checkresult(self.value - other, "subtraction")
1752
1753 def __mul__(self, other):
1754 return self.__checkresult(self.value * other, "mulitplication")
1755
1756 def __div__(self, other):
1757 return self.__checkresult(self.value / other, "division")
1758
1759 def __divmod__(self, other):
1760 _a, _b = divmod(self.value, other)
1761 return (self.__checkresult(_a, "divmod"), _b)
1762
1763 def __pow__(self, other, modulus=None):
1764 return self.__checkresult(pow(self.value, other, modulus), "pow")
1765
1766 def __radd__(self, other):
1767 return self.__checkresult(other + self.value, "addition")
1768
1769 def __rsub__(self, other):
1770 return self.__checkresult(other - self.value, "subtraction")
1771
1772 def __rmul__(self, other):
1773 return self.__checkresult(other * self.value, "multiplication")
1774
1775 def __rdiv__(self, other):
1776 return self.__checkresult(other / self.value, "division")
1777
1778 def __rdivmod__(self, other):
1779 _a, _b = divmod(other, self.value)
1780 return (self.__checkresult(_a, "divmod"), _b)
1781
1782 def __rpow__(self, other, modulus=None):
1783 return self.__checkresult(pow(other, self.value, modulus), "pow")
1784
1785 def __neg__(self):
1786 return self.__checkresult(self.value * -1, "negation")
1787
1788 def __pos__(self):
1789 return self.value
1790
1791 def __abs__(self):
1792 return self.__checkresult(abs(self.value), "abs")
1793
1794 def __complex__(self):
1795 return complex(self.value)
1796
1797 def __int__(self):
1798 return int(self.value)
1799
1800 def __long__(self):
1801 return long(self.value)
1802
1803 def __float__(self):
1804 return self.value # PgMoney is already a float :-)
1805
1806 def __repr__(self):
1807 return '%.2f' % self.value
1808
1809 def __str__(self):
1810 _s = '%.2f' % abs(self.value)
1811 _i = string.rfind(_s, '.')
1812 _c = (_i - 1) / 3
1813 for _j in range(_c):
1814 _i = _i - 3
1815 _s = '%s,%s' % (_s[:_i], _s[_i:])
1816 if self.value < 0.0:
1817 return '($%s)' % _s
1818 else:
1819 return '$%s' % _s
1820
1821 def _quote(self, forArray=0):
1822 if self.value:
1823 if forArray:
1824 return '"%s"' % str(self.value)
1825 else:
1826 return "'%s'" % str(self.value)
1827 return 'NULL'
1828
1829 #-----------------------------------------------------------------------+
1830 # Name: PgInt8 |
1831 # |
1832 # Description: A Python wrapper class for the PostgreSQL int8 type. |
1833 # It's primary purpose it to check for overflow during |
1834 # calulations. |
1835 # |
1836 # Note: The PgInt8 class uses a Python Long Integer to hold |
1837 # the PostgreSQL int8 type. |
1838 # |
1839 # Note: This class will only be defined if the C implementation |
1840 # of the PgInt8 object was not imported with/from libpq. |
1841 #-----------------------------------------------------------------------+
1842
1843 if dir().count('PgInt8') == 0: # Only define this class is PgInt8 wasn't
1844 # brought in via libpq.
1845 class PgInt8:
1846 def __init__(self, value):
1847 if value is None:
1848 self.value = value
1849 return
1850
1851 self.value = long(value)
1852 if self.value < -9223372036854775808L or \
1853 self.value > 9223372936854775807L:
1854 raise OverflowError, 'int8 initialization'
1855
1856 def __checkresult(self, value, op):
1857 if value < -9223372036854775808L or value > 9223372936854775807L:
1858 raise OverflowError, 'int8 %s' % op
1859 return PgInt8(value)
1860
1861 def __coerce__(self, other):
1862 if other is None:
1863 return None
1864 res = coerce(self.value, other)
1865 if res is None:
1866 return None
1867 _s, _o = res
1868 return (self, _o)
1869
1870 def __hash__(self):
1871 return hash(self.value)
1872
1873 def __cmp__(self, other):
1874 return cmp(self.value, other)
1875
1876 def __nonzero__(self):
1877 return self.value != 0
1878
1879 def __add__(self, other):
1880 return self.__checkresult(self.value + other, "addition")
1881
1882 def __sub__(self, other):
1883 return self.__checkresult(self.value - other, "subtraction")
1884
1885 def __mul__(self, other):
1886 return self.__checkresult(self.value * other, "mulitplication")
1887
1888 def __div__(self, other):
1889 return self.__checkresult(self.value / other, "division")
1890
1891 def __divmod__(self, other):
1892 _a, _b = divmod(self.value, other)
1893 return (self.__checkresult(_a, "divmod"), _b)
1894
1895 def __pow__(self, other, modulus=None):
1896 return self.__checkresult(pow(self.value, other, modulus), "pow")
1897
1898 def __lshift__(self, other):
1899 return self.__checkresult(self.value << other, 'lshift')
1900
1901 def __rshift__(self, other):
1902 return self.__checkresult(self.value >> other, 'rshift')
1903
1904 def __and__(self, other):
1905 return self.__checkresult(self.value & other, 'and')
1906
1907 def __xor__(self, other):
1908 return self.__checkresult(self.value ^ other, 'xor')
1909
1910 def __or__(self, other):
1911 return self.__checkresult(self.value | other, 'or')
1912
1913 def __radd__(self, other):
1914 return self.__checkresult(other + self.value, "addition")
1915
1916 def __rsub__(self, other):
1917 return self.__checkresult(other - self.value, "subtraction")
1918
1919 def __rmul__(self, other):
1920 return self.__checkresult(other * self.value, "mulitplication")
1921
1922 def __rdiv__(self, other):
1923 return self.__checkresult(other / self.value, "division")
1924
1925 def __rdivmod__(self, other):
1926 _a, _b = divmod(other, self.value)
1927 return (self.__checkresult(_a, "divmod"),
1928 self.__checkresult(_b, "divmod"))
1929
1930 def __rpow__(self, other, modulus=None):
1931 return self.__checkresult(pow(other, self.value, modulus), "pow")
1932
1933 def __rlshift__(self, other):
1934 return self.__checkresult(other << self.value, 'lshift')
1935
1936 def __rrshift__(self, other):
1937 return self.__checkresult(other >> self.value, 'rshift')
1938
1939 def __rand__(self, other):
1940 return self.__checkresult(other & self.value, 'and')
1941
1942 def __rxor__(self, other):
1943 return self.__checkresult(other ^ self.value, 'xor')
1944
1945 def __ror__(self, other):
1946 return self.__checkresult(other | self.value, 'or')
1947
1948 def __neg__(self):
1949 return self.__checkresult(neg(self.value), 'neg')
1950
1951 def __pos__(self):
1952 return self.__checkresult(pos(self.value), 'pos')
1953
1954 def __abs__(self):
1955 return self.__checkresult(abs(self.value), 'abs')
1956
1957 def __complex__(self):
1958 return complex(self)
1959
1960 def __int__(self):
1961 return int(self.value)
1962
1963 def __long__(self):
1964 return self.value # PgInt8 is already a Long.
1965
1966 def __float__(self):
1967 return float(self.value)
1968
1969 def __complex__(self):
1970 return complex(self.value)
1971
1972 def __hex__(self):
1973 return hex(self.value)
1974
1975 def __oct__(self):
1976 return oct(self.value)
1977
1978 def __repr__(self):
1979 return repr(self.value)
1980
1981 def __str__(self):
1982 return str(self.value)
1983
1984 def _quote(self, forArray=0):
1985 if self.value is not None:
1986 s = str(self.value)
1987 if s[-1:] == "L":
1988 s = s[:-1]
1989 return "%s" % s
1990 return 'NULL'
1991
1992 PgInt8Type = PgInt8
1993
1994
1995 #-----------------------------------------------------------------------+
1996 # Name: PgResultSet |
1997 # |
1998 # Description: This class defines the DB-API query result set for a |
1999 # single row. It emulates a sequence with the added |
2000 # feature of being able to reference an attribute by |
2001 # column name in addition to a zero-based numeric index. |
2002 # |
2003 # This class isn't used directly, instead it's used as a |
2004 # base class for the actual result set class created with |
2005 # make_PgResultSetClass. |
2006 # |
2007 #-----------------------------------------------------------------------+
2008
2009 class PgResultSet:
2010
2011 # It may not be obvious what self.__class__ does:
2012 # Apart from the __init__ method, all methods are called on instances of a
2013 # class dynamically created make_PgResultSetClass, which means that when
2014 # you call a method, self.__class__ is *not* PgResultSet, but a subclass of
2015 # it created with make_PgResultSetClass (using the new module). The
2016 # subclass will have a class attribute called _xlatkey, which is a mapping
2017 # of column names to column positions.
2018
2019 def __init__(self, value):
2020 self.__dict__['baseObj'] = value
2021
2022 def __getattr__(self, key):
2023 # When retrieving column data by name as an attribute, we must be
2024 # aware that a column name can be defiend with mixed-case within the
2025 # database. Because of this we must first check for an exact match
2026 # with the given key. If that fails, then we match with the key that
2027 # has been changed to lower case. Note: we are relying on the fact
2028 # that PostgreSQL sends column names that are not defined with mixed-
2029 # case to the client as lower-case names.
2030 keyl = key.lower()
2031 if self._xlatkey.has_key(key):
2032 return self.baseObj[self._xlatkey[key]]
2033 if self._xlatkey.has_key(keyl):
2034 return self.baseObj[self._xlatkey[keyl]]
2035 raise AttributeError, key
2036
2037 # We define a __setattr__ routine that will only allow the attributes that
2038 # are the column names to be updated. All other attributes are read-only.
2039 def __setattr__(self, key, value):
2040 if key in ('baseObj', '_xlatkey', '_desc_'):
2041 raise AttributeError, "%s is read-only." % key
2042
2043 # Try an exact match first, then the case-insensitive match.
2044 # See comment in __getattr__ for details.
2045 keyl = key.lower()
2046 if self._xlatkey.has_key(key):
2047 self.__dict__['baseObj'][self._xlatkey[key]] = value
2048 elif self._xlatkey.has_key(keyl):
2049 self.__dict__['baseObj'][self._xlatkey[keyl]] = value
2050 else:
2051 raise AttributeError, key
2052
2053 def __len__(self):
2054 return len(self.baseObj)
2055
2056 def __getitem__(self, key):
2057 if isinstance(key, StringType):
2058 # Try an exact match first, then the case-insensitive match.
2059 # See comment in __getattr__ for details.
2060 try:
2061 key = self._xlatkey[key]
2062 except:
2063 key = self._xlatkey[key.lower()]
2064 return self.baseObj[key]
2065
2066 def __setitem__(self, key, value):
2067 if isinstance(key, StringType):
2068 # Try an exact match first, then the case-insensitive match.
2069 # See comment in __getattr__ for details.
2070 try:
2071 key = self._xlatkey[key]
2072 except:
2073 key = self._xlatkey[key.lower()]
2074 self.baseObj[key] = value
2075
2076 def __contains__(self, key):
2077 return self.has_key(key)
2078
2079 def __getslice__(self, i, j):
2080 klass = make_PgResultSetClass(self._desc_[i:j])
2081 obj = klass(self.baseObj[i:j])
2082 return obj
2083
2084 def __repr__(self):
2085 return repr(self.baseObj)
2086
2087 def __str__(self):
2088 return str(self.baseObj)
2089
2090 def __cmp__(self, other):
2091 return cmp(self.baseObj, other)
2092
2093 def description(self):
2094 return self._desc_
2095
2096 def keys(self):
2097 _k = []
2098 for _i in self._desc_:
2099 _k.append(_i[0])
2100 return _k
2101
2102 def values(self):
2103 return self.baseObj[:]
2104
2105 def items(self):
2106 _items = []
2107 for i in range(len(self.baseObj)):
2108 _items.append((self._desc_[i][0], self.baseObj[i]))
2109 return _items
2110
2111 def has_key(self, key):
2112 # Try an exact match first, then the case-insensitive match.
2113 # See comment in __getattr__ for details.
2114 if not self._xlatkey.has_key(key):
2115 key = key.lower()
2116 return self._xlatkey.has_key(key)
2117
2118 def get(self, key, defaultval=None):
2119 try:
2120 if isinstance(key, StringType):
2121 # Try an exact match first, then the case-insensitive match.
2122 # See comment in __getattr__ for details.
2123 try:
2124 key = self._xlatkey[key]
2125 except:
2126 key = self._xlatkey[key.lower()]
2127 return self[key]
2128 except:
2129 return defaultval
2130
2131 def make_PgResultSetClass(description, mapname=None):
2132 """Dynamically create a new subclass of PgResultSet."""
2133 klass = new.classobj("PgResultSetConcreteClass", (PgResultSet,), {})
2134 klass.__dict__['_desc_'] = description
2135
2136 klass.__dict__['_xlatkey'] = {}
2137 if mapname is None:
2138 for _i in range(len(description)):
2139 klass.__dict__['_xlatkey'][description[_i][0]] = _i
2140 else:
2141 for k, v in mapname.items():
2142 klass.__dict__['_xlatkey'][k] = v
2143 return klass
2144
2145
2146 #-----------------------------------------------------------------------+
2147 # Define the PgSQL function calls: |
2148 # |
2149 # connect() -- connect to a PostgreSQL database. |
2150 # _handleArray() -- Transform a PgArray class into a string rep- |
2151 # resenting a PostgreSQL array. |
2152 # _quote() -- Transform a Python object representing a |
2153 # PostgreSQL type into a appropiately quoted |
2154 # string that can be sent to the database in a |
2155 # UPDATE/INSERT statement. _quote() calls the |
2156 # _handleArray() function to quote arrays. |
2157 # _quoteall() -- transforms all elements of a list or diction- |
2158 # ary using _quote. |
2159 # dateTimeDelta2Interval() -- converts a DateTimeDelta type into |
2160 # a string PostgreSQL accepts as a interval. |
2161 #-----------------------------------------------------------------------+
2162
2163 def connect(dsn=None, user=None, password=None, host=None, database=None,
2164 port=None, options=None, tty=None, client_encoding=None,
2165 unicode_results=None):
2166 """
2167 connection = PgSQL.connect(dsn[, user, password, host, database, port,
2168 options, tty] [, client_encoding]
2169 [, unicode_results])
2170 Opens a connection to a PostgreSQL database."""
2171
2172 _d = {}
2173
2174 # Try getting values from the DSN first.
2175 if dsn is not None:
2176 try:
2177 params = string.split(dsn, ":")
2178 if params[0] != '': _d["host"] = params[0]
2179 if params[1] != '': _d["port"] = params[1]
2180 if params[2] != '': _d["dbname"] = params[2]
2181 if params[3] != '': _d["user"] = params[3]
2182 if params[4] != '': _d["password"] = params[4]
2183 if params[5] != '': _d["options"] = params[5]
2184 if params[6] != '': _d["tty"] = params[6]
2185 except:
2186 pass
2187
2188 # Override from the keyword arguments, if needed.
2189 if (user is not None): _d["user"] = user
2190 if (password is not None): _d["password"] = password
2191 if (host is not None):
2192 _d["host"] = host
2193 try:
2194 params = string.split(host, ":")
2195 _d["host"] = params[0]
2196 _d["port"] = params[1]
2197 except:
2198 pass
2199 if (database is not None): _d["dbname"] = database
2200 if (port is not None): _d["port"] = port
2201 if (options is not None): _d["options"] = options
2202 if (tty is not None): _d["tty"] = tty
2203
2204 # Build up the connection info string passed to PQconnectdb
2205 # via the constructor to Connection.
2206
2207 connInfo = ""
2208 for i in _d.keys():
2209 connInfo = "%s%s=%s " % (connInfo, i, _d[i])
2210
2211 return Connection(connInfo, client_encoding, unicode_results)
2212
2213 def _handleArray(value):
2214 """
2215 _handleArray(list) -> string
2216 This function handle the transformation of a Python list into a string that
2217 can be used to update a PostgreSQL array attribute."""
2218
2219 #Check for, and handle an empty list.
2220 if len(value) == 0:
2221 return '{}'
2222
2223 _j = "'{"
2224 for _i in value:
2225 if _i is None:
2226 _j += ","
2227 elif isinstance(_i, PgArray):
2228 _j = _j + _handleArray(_i)[1:-1] + ','
2229 elif hasattr(_i, '_quote'):
2230 _j = '%s%s,' % (_j, _i._quote(1))
2231 elif type(_i) is DateTimeType:
2232 _j = '%s"%s",' % (_j, _i)
2233 elif type(_i) is DateTime.DateTimeDeltaType:
2234 _j = '%s"%s",' % (_j, dateTimeDelta2Interval(_i))
2235 elif type(_i) is PgInt2Type or isinstance(_i, PgInt8Type):
2236 _j = '%s%s,' % (_j, str(_i))
2237 else:
2238 _j = '%s%s,' % (_j, PgQuoteString(str(_i), 1))
2239
2240 return _j[:-1] + "}'"
2241
2242 def _quote(value):
2243 """
2244 _quote(value) -> string
2245 This function transforms the Python value into a string suitable to send
2246 to the PostgreSQL database in a insert or update statement. This function
2247 is automatically applied to all parameter sent vis an execute() call.
2248 Because of this an update/insert statement string in an execute() call
2249 should only use '%s' [or '%(name)s'] for variable subsitution without any
2250 quoting."""
2251
2252 if value is None:
2253 return 'NULL'
2254 elif hasattr(value, '_quote'):
2255 return value._quote()
2256 elif type(value) is DateTimeType:
2257 return "'%s'" % value
2258 elif type(value) is DateTimeDeltaType:
2259 return "'%s'" % dateTimeDelta2Interval(value)
2260 elif isinstance(value, StringType):
2261 return PgQuoteString(value)
2262 elif isinstance(value, LongType):
2263 return str(value)
2264 else:
2265 return repr(value)
2266
2267 def _quoteall(vdict):
2268 """
2269 _quoteall(vdict)->dict
2270 Quotes all elements in a list or dictionary to make them suitable for
2271 insertion."""
2272
2273 if type(vdict) is DictType or isinstance(vdict, PgResultSet):
2274 t = {}
2275 for k, v in vdict.items():
2276 t[k]=_quote(v)
2277 elif type(vdict) in (StringType, UnicodeType):
2278 # Note: a string is a SequenceType, but is treated as a single
2279 # entity, not a sequence of characters.
2280 t = (_quote(vdict), )
2281 elif type(vdict) in [ListType, TupleType]:
2282 t = tuple(map(_quote, vdict))
2283 else:
2284 raise TypeError, \
2285 "argument to _quoteall must be a sequence or dictionary!"
2286
2287 return t
2288
2289 def dateTimeDelta2Interval(interval):
2290 """
2291 DateTimeDelta2Interval - Converts a DateTimeDelta to an interval string\n
2292 The input format is [+-]DD:HH:MM:SS.ss\n
2293 The output format is DD days HH:MM:SS.ss [ago]\n
2294 """
2295
2296 if type(interval) is DateTimeDeltaType:
2297 s = str(interval)
2298 ago = ''
2299 if s[0] == '-':
2300 ago = ' ago'
2301 s = s[1:]
2302 else:
2303 ago = ''
2304 s = s.split(':')
2305 if len(s) < 4:
2306 return '%s:%s:%s %s' % (s[0], s[1], s[2], ago)
2307
2308 return '%s days %s:%s:%s %s' % (s[0], s[1], s[2], s[3], ago)
2309 else:
2310 raise TypeException, "DateTimeDelta2Interval requires a DataTimeDelta."
2311
2312 #-----------------------------------------------------------------------+
2313 # Name: Connection |
2314 # |
2315 # Description: Connection defines the Python DB-API 2.0 connection |
2316 # object. See the DB-API 2.0 specifiaction for details. |
2317 #-----------------------------------------------------------------------+
2318
2319 class Connection:
2320 """Python DB-API 2.0 Connection Object."""
2321
2322 def __init__(self, connInfo, client_encoding=None, unicode_results=None):
2323 try:
2324 self.__dict__["conn"] = PQconnectdb(connInfo)
2325 except Exception, m:
2326 # The connection to the datadata failed.
2327 # Clean up the Connection object that was created.
2328 # Note: _isOpen must be defined for __del__ to work.
2329 self.__dict__["_isOpen"] = None
2330 del(self)
2331 raise DatabaseError, m
2332
2333 self.__dict__["autocommit"] = 0
2334 self.__dict__["TransactionLevel"] = ""
2335 self.__dict__["notices"] = self.conn.notices
2336 self.__dict__["inTransaction"] = 0
2337 self.__dict__["version"] = self.conn.version
2338 self.__dict__["_isOpen"] = 1
2339 self.__dict__["_cache"] = TypeCache(self)
2340 if noWeakRef:
2341 self.__dict__["cursors"] = []
2342 else:
2343 self.__dict__["cursors"] = weakref.WeakValueDictionary()
2344
2345 self.unicode_results = unicode_results
2346 if type(client_encoding) in (TupleType, ListType):
2347 self.client_encoding = client_encoding
2348 else:
2349 self.client_encoding = (client_encoding or getdefaultencoding(),)
2350
2351 def __del__(self):
2352 if self._isOpen:
2353 self.close() # Ensure that the connection is closed.
2354
2355 def __setattr__(self, name, value):
2356 if name == "autocommit":
2357 if value is None:
2358 raise InterfaceError, \
2359 "Can't delete the autocommit attribute."
2360 # Don't allow autocommit to change if there are any opened cursor
2361 # associated with this connection.
2362 if self.__anyLeft():
2363 if noWeakRef:
2364 # If the are cursors left, but weak references are not
2365 # available, garbage collect any cursors that are only
2366 # referenced in self.cursors.
2367
2368 self.__gcCursors()
2369
2370 if len(self.cursors) > 0:
2371 raise AttributeError, \
2372 "Can't change autocommit when a cursor is active."
2373 else:
2374 raise AttributeError, \
2375 "Can't change autocommit when a cursor is active."
2376
2377 # It's possible that the connection can still have an open
2378 # transaction, even though there are no active cursors.
2379
2380 if self.inTransaction:
2381 self.rollback()
2382
2383 if value:
2384 self.__dict__[name] = 1
2385 else:
2386 self.__dict__[name] = 0
2387 elif name == "TransactionLevel":
2388 if value is None:
2389 raise InterfaceError, \
2390 "Can't delete the TransactinLevel attribute."
2391 # Don't allow TransactionLevel to change if there are any opened
2392 # cursors associated with this connection.
2393 if self.__anyLeft():
2394 if noWeakRef:
2395 # If the are cursors left, but weak references are not
2396 # available, garbage collect any cursors that are only
2397 # referenced in self.cursors.
2398
2399 self.__gcCursors()
2400
2401 if len(self.cursors) > 0:
2402 raise AttributeError, \
2403 "Can't change TransactionLevel when a cursor is active."
2404 else:
2405 raise AttributeError, \
2406 "Can't change TransactionLevel when a cursor is active."
2407
2408 # It's possible that the connection can still have an open
2409 # transaction, even though there are no active cursors.
2410
2411 if self.inTransaction:
2412 self.rollback()
2413
2414 if type(value) is not StringType:
2415 raise ValueError, "TransactionLevel must be a string."
2416
2417 if value.upper() in [ "", "READ COMMITTED", "SERIALIZABLE" ]:
2418 self.__dict__[name] = value.upper()
2419 else:
2420 raise ValueError, \
2421 'TransactionLevel must be: "", "READ COMMITTED", or "SERIALIZABLE"'
2422 elif name in ('unicode_results', 'client_encoding'):
2423 self.__dict__[name] = value
2424 elif self.__dict__.has_key(name):
2425 raise AttributeError, "%s is read-only." % name
2426 else:
2427 raise AttributeError, name
2428
2429 def __closeCursors(self, flag=0):
2430 """
2431 __closeCursors() - closes all cursors associated with this connection"""
2432 if self.__anyLeft():
2433 if noWeakRef:
2434 curs = self.cursors[:]
2435 else:
2436 curs = map(lambda x: x(), self.cursors.data.values())
2437
2438 for i in curs:
2439 if flag:
2440 i.close()
2441 else:
2442 i._Cursor__reset()
2443
2444 return self.inTransaction
2445
2446 def __anyLeft(self):
2447 if noWeakRef:
2448 return len(self.cursors) > 0
2449
2450 return len(self.cursors.data.keys()) > 0
2451
2452 def __gcCursors(self):
2453 # This routine, which will be called only if weak references are not
2454 # available, will check the reference counts of the cursors in the
2455 # connection.cursors list and close any that are only referenced
2456 # from that list. This will clean up deleted cursors.
2457
2458 for i in self.cursors[:]:
2459 # Check the reference count. It will be 4 if it only exists in
2460 # self.cursors. The magic number for is derived from the fact
2461 # that there will be 1 reference count for each of the follwoing:
2462 # self.cursors, self.cursors[:], i, and as the argument to
2463 # getrefcount(),
2464
2465 if getrefcount(i) < 5:
2466 i.close()
2467
2468 def __setupTransaction(self):
2469 """
2470 __setupTransaction()
2471 Internal routine that will set up a transaction for this connection.\n"""
2472 self.conn.query("BEGIN WORK")
2473 if self.TransactionLevel != "":
2474 _nl = len(self.notices)
2475 self.conn.query('SET TRANSACTION ISOLATION LEVEL %s' %
2476 self.TransactionLevel)
2477 if len(self.notices) != _nl:
2478 raise Warning, self.notices.pop()
2479 self.__dict__["inTransaction"] = 1
2480
2481
2482 def close(self):
2483 """
2484 close()
2485 Close the connection now (rather than whenever __del__ is called).
2486 Any active cursors for this connection will be closed and the connection
2487 will be unusable from this point forward.\n"""
2488
2489 if not self._isOpen:
2490 raise InterfaceError, "Connection is already closed."
2491
2492 if self.__closeCursors(1):
2493 try:
2494 _nl = len(self.conn.notices)
2495 self.conn.query("ROLLBACK WORK")
2496 if len(self.notices) != _nl:
2497 raise Warning, self.notices.pop()
2498 except:
2499 pass
2500
2501 self.__dict__["_cache"] = None
2502 self.__dict__["_isOpen"] = 0
2503 self.__dict__["autocommit"] = None
2504 self.__dict__["conn"] = None
2505 self.__dict__["cursors"] = None
2506 self.__dict__["inTransaction"] = 0
2507 self.__dict__["TransactionLevel"] = None
2508 self.__dict__["version"] = None
2509 self.__dict__["notices"] = None
2510
2511 def commit(self):
2512 """
2513 commit()
2514 Commit any pending transactions to the database.\n"""
2515
2516 if not self._isOpen:
2517 raise InterfaceError, "Commit failed - Connection is not open."
2518
2519 if self.autocommit:
2520 raise InterfaceError, "Commit failed - autocommit is on."
2521
2522 if self.__closeCursors():
2523 self.__dict__["inTransaction"] = 0
2524 _nl = len(self.conn.notices)
2525 res = self.conn.query("COMMIT WORK")
2526 if len(self.notices) != _nl:
2527 raise Warning, self.notices.pop()
2528 if res.resultStatus != COMMAND_OK:
2529 raise InternalError, "Commit failed - reason unknown."
2530
2531 def rollback(self):
2532 """
2533 rollback()
2534 Rollback to the start of any pending transactions.\n"""
2535
2536 if not self._isOpen:
2537 raise InterfaceError, "Rollback failed - Connection is not open."
2538
2539 if self.autocommit:
2540 raise InterfaceError, "Rollback failed - autocommit is on."
2541
2542 if self.__closeCursors():
2543 self.__dict__["inTransaction"] = 0
2544 _nl = len(self.conn.notices)
2545 res = self.conn.query("ROLLBACK WORK")
2546 if len(self.notices) != _nl:
2547 raise Warning, self.notices.pop()
2548 if res.resultStatus != COMMAND_OK:
2549 raise InternalError, \
2550 "Rollback failed - %s" % res.resultErrorMessage
2551
2552 def cursor(self, name=None, isRefCursor=PG_False):
2553 """
2554 cursor([name])
2555 Returns a new 'Cursor Object' (optionally named 'name')."""
2556
2557 if not self._isOpen:
2558 raise InterfaceError, \
2559 "Create cursor failed - Connection is not open."
2560
2561 return Cursor(self, name, isRefCursor)
2562
2563 def binary(self, string=None):
2564 """
2565 binary([string])
2566 Returns a new 'Large Object'. If sting is present, it is used to
2567 initialize the large object."""
2568
2569 if not self._isOpen:
2570 raise InterfaceError, \
2571 "Creation of large object failed - Connection is not open."
2572
2573 if self.autocommit:
2574 raise InterfaceError, \
2575 "Creation of large object failed - autocommit is on."
2576
2577 _nl = len(self.notices)
2578 _ct = 0
2579 # Ensure that we are in a transaction for working with large objects
2580 if not self.inTransaction:
2581 self.__setupTransaction()
2582 _ct = 1
2583
2584 _lo = self.conn.lo_creat(INV_READ | INV_WRITE)
2585
2586 if len(self.notices) != _nl:
2587 raise Warning, self.notices.pop()
2588
2589 if string:
2590 _lo.open("w")
2591 _lo.write(string)
2592 _lo.close()
2593
2594 if len(self.notices) != _nl:
2595 if self.inTransaction:
2596 self.conn.query("ROLLBACK WORK")
2597 raise Warning, self.conn.notices.pop()
2598
2599 return _lo
2600
2601 def unlink(self, lobj):
2602 """
2603 unlink(OID|PgLargeObject)
2604 Remove a large object from the database inversion file syste."""
2605
2606 if not self._isOpen:
2607 raise InterfaceError, \
2608 "Unlink of large object failed - Connection is not open."
2609
2610 if not self.version.post70 and self.inTransaction:
2611 raise NotSupportedError, \
2612 "unlink of a PostgreSQL Large Object in a transaction"
2613
2614 if type(lobj) is IntType:
2615 oid = lobj
2616 elif type(lobj) is PgLargeObjectType:
2617 oid = lobj.oid
2618
2619 _nl = len(self.conn.notices)
2620 res = self.conn.lo_unlink(oid)
2621 if len(self.notices) != _nl:
2622 raise Warning, self.notices.pop()
2623
2624 return res
2625
2626 #-----------------------------------------------------------------------+
2627 # Name: Cursor |
2628 # |
2629 # Description: Cursor defines the Python DB-API 2.0 cursor object. |
2630 # See the DB-API 2.0 specification for details. |
2631 #-----------------------------------------------------------------------+
2632
2633 class Cursor:
2634 """Python DB-API 2.0 Cursor Object."""
2635
2636 def __init__(self, conn, name, isRefCursor=PG_False):
2637 if not isinstance(conn, Connection):
2638 raise TypeError, "Cursor requires a connection."
2639
2640 # Generate a unique name for the cursor is one is not given.
2641 if name is None:
2642 if isRefCursor:
2643 raise TypeError, "Reference cursor requires a name."
2644 name = "PgSQL_%08X" % id(self)
2645 elif type(name) is not StringType:
2646 raise TypeError, "Cursor name must be a string."
2647
2648 # Define the public variables for this cursor.
2649 self.__dict__["arraysize"] = 1
2650
2651 # Define the private variables for this cursor.
2652 if noWeakRef:
2653 self.__dict__["conn"] = conn
2654 else:
2655 self.__dict__["conn"] = weakref.proxy(conn)
2656 self.__dict__["name"] = name
2657
2658 # This needs to be defined here sot that the initial call to __reset()
2659 # will work.
2660 self.__dict__["closed"] = None
2661 self.__reset()
2662
2663 # _varhdrsz is the length (in bytes) of the header for variable
2664 # sized postgreSQL data types.
2665
2666 self.__dict__["_varhdrsz"] = 4
2667
2668 # Add ourselves to the list of cursors for our owning connection.
2669 if noWeakRef:
2670 self.conn.cursors.append(self)
2671 if len(self.conn.cursors) > 1:
2672 # We have additional cursors, garbage collect them.
2673 self.conn._Connection__gcCursors()
2674 else:
2675 self.conn.cursors[id(self)] = self
2676
2677 if not self.conn.autocommit:
2678 # Only the first created cursor begins the transaction.
2679 if not self.conn.inTransaction:
2680 self.conn._Connection__setupTransaction()
2681 self.__dict__["PgResultSetClass"] = None
2682
2683 if isRefCursor:
2684 # Ok -- we've created a cursor, we will pre-fetch the first row in
2685 # order to make the description array. Note: the first call to
2686 # fetchXXX will return the pre-fetched record.
2687 self.__dict__["closed"] = 0
2688 self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
2689 self._rows_ = self.res.ntuples
2690 self._idx_ = 0
2691 self.__makedesc__()
2692
2693 def __del__(self):
2694 # Ensure that the cursor is closed when it is deleted. This takes
2695 # care of some actions that needs to be completed when a cursor is
2696 # deleted, such as disassociating the cursor from the connection
2697 # and closing an open transaction if this is the last cursor for
2698 # the connection.
2699 if not self.closed:
2700 self.close()
2701
2702 def __reset(self):
2703 try:
2704 if (self.closed == 0) and self.conn.inTransaction:
2705 try:
2706 self.conn.conn.query('CLOSE "%s"' % self.name)
2707 except:
2708 pass
2709 except:
2710 pass
2711
2712 self.__dict__["res"] = None
2713 # closed is a trinary variable:
2714 # == None => Cursor has not been opened.
2715 # == 0 => Cursor is open.
2716 # == 1 => Curosr is closed.
2717 self.__dict__["closed"] = None
2718 self.__dict__["description"] = None
2719 self.__dict__["oidValue"] = None
2720 self.__dict__["_mapname"] = None
2721 self.__dict__["_rows_"] = 0
2722 self.__dict__["_idx_"] = 1
2723 self.__dict__["rowcount"] = -1
2724
2725 def __setattr__(self, name, value):
2726 if self.closed:
2727 raise InterfaceError, "Operation failed - the cursor is closed."
2728
2729 if name in ["rowcount", "oidValue", "description"]:
2730 raise AttributeError, "%s is read-only." % name
2731 elif self.__dict__.has_key(name):
2732 self.__dict__[name] = value
2733 else:
2734 raise AttributeError, name
2735
2736 def __unicodeConvert(self, obj):
2737 if type(obj) is StringType:
2738 return obj
2739 elif type(obj) is UnicodeType:
2740 return obj.encode(*self.conn.client_encoding)
2741 elif type(obj) in (ListType, TupleType):
2742 converted_obj = []
2743 for item in obj:
2744 if type(item) is UnicodeType:
2745 converted_obj.append(item.encode(*self.conn.client_encoding))
2746 else:
2747 converted_obj.append(item)
2748 return converted_obj
2749 elif type(obj) is DictType:
2750 converted_obj = {}
2751 for k, v in obj.items():
2752 if type(v) is UnicodeType:
2753 converted_obj[k] = v.encode(*self.conn.client_encoding)
2754 else:
2755 converted_obj[k] = v
2756 return converted_obj
2757 elif isinstance(obj, PgResultSet):
2758 obj = copy.copy(obj)
2759 for k, v in obj.items():
2760 if type(v) is UnicodeType:
2761 obj[k] = v.encode(*self.conn.client_encoding)
2762 return obj
2763 else:
2764 return obj
2765
2766 def __fetchOneRow(self):
2767 if self._idx_ >= self._rows_:
2768 self.__dict__['rowcount'] = 0
2769 return None
2770
2771 _j = []
2772 _r = self.res
2773 _c = self.conn._cache
2774 for _i in range(self.res.nfields):
2775 _j.append(_c.typecast(self.description[_i],
2776 _r.getvalue(self._idx_, _i)))
2777
2778 self._idx_ = self._idx_ + 1
2779
2780 self.__dict__['rowcount'] = 1
2781
2782 if fetchReturnsList:
2783 # Return a list (This is the minimum required by DB-API 2.0
2784 # compliance).
2785 return _j
2786 else:
2787 return self.PgResultSetClass(_j)
2788
2789 def __fetchManyRows(self, count, iList=[]):
2790 _many = iList
2791 if count < 0:
2792 while 1:
2793 _j = self.__fetchOneRow()
2794 if _j is not None:
2795 _many.append(_j)
2796 else:
2797 break
2798 elif count > 0:
2799 for _i in range(count):
2800 _j = self.__fetchOneRow()
2801 if _j is not None:
2802 _many.append(_j)
2803 else:
2804 break
2805
2806 self.__dict__['rowcount'] = len(_many)
2807
2808 return _many
2809
2810 def __makedesc__(self):
2811 # Since __makedesc__ will only be called after a successful query or
2812 # fetch, self.res will contain the information needed to build the
2813 # description attribute even if no rows were returned. So, we always
2814 # build up the description.
2815 self.__dict__['description'] = []
2816 self._mapname = {}
2817 _res = self.res
2818 _cache = self.conn._cache
2819 for _i in range(_res.nfields):
2820 _j = []
2821
2822 _j.append(_res.fname(_i))
2823
2824 _typ = PgTypes(_res.ftype(_i))
2825 _mod = _res.fmod(_i)
2826 _tn, _pl, _ia, _bt = _cache.getTypeInfo(_typ)
2827 if _ia:
2828 _s, _pl, _s, _s = _cache.getTypeInfo(_bt)
2829 if _bt == PG_OID:
2830 _bt = PgTypes(PG_BLOB)
2831 _typ = _bt
2832 elif _typ.value == PG_OID:
2833 try:
2834 _p = _res.getvalue(0, _i)
2835 except (ValueError, TypeError), m:
2836 # We can only guess here ...
2837 _typ = PgTypes(PG_BLOB)
2838 else:
2839 if type(_p) in [PgLargeObjectType, NoneType]:
2840 _typ = PgTypes(PG_BLOB)
2841 else:
2842 _typ = PgTypes(PG_ROWID)
2843
2844 _j.append(_typ)
2845
2846 # Calculate Display size, Internal size, Precision and Scale.
2847 # Note: Precision and Scale only have meaning for PG_NUMERIC
2848 # columns.
2849 if _typ.value == PG_NUMERIC:
2850 if _mod == -1:
2851 # We have a numeric with no scale/precision.
2852 # Get them from by converting the string to a PgNumeric
2853 # and pulling them form the PgNumeric object. If that
2854 # fails default to a precision of 30 with a scale of 6.
2855 try:
2856 nv = PgNumeric(_res.getvalue(0, _i))
2857 _p = nv.getPrecision()
2858 _s = nv.getScale()
2859 except (ValueError, TypeError), m:
2860 _p = 30
2861 _s = 6
2862 else:
2863 # We hava a valid scale/precision value. Use them.
2864 _s = _mod - self._varhdrsz
2865 _p = (_s >> 16) & 0xffff
2866 _s = _s & 0xffff
2867 _j.append(None) # Display size (always None since PG7.3)
2868 _j.append(_p) # Internal (storage) size
2869 _j.append(_p) # Precision
2870 _j.append(_s) # Scale
2871 else:
2872 if _pl == -1:
2873 _pl = _res.fsize(_i)
2874 if _pl == -1:
2875 _pl = _mod - self._varhdrsz
2876 _j.append(None) # Display size (always None since PG7.3)
2877 _s = _res.fsize(_i)
2878 if _s == -1:
2879 _s = _mod
2880 _j.append(_s) # Internal (storage) size
2881 if _typ.value == PG_MONEY:
2882 _j.append(9) # Presicion and Scale (from
2883 _j.append(2) # the PostgreSQL doco.)
2884 else:
2885 _j.append(None) # Preision
2886 _j.append(None) # Scale
2887
2888 _j.append(None) # nullOK is not implemented (yet)
2889 _j.append(_ia) # Array indicator (PostgreSQL specific)
2890
2891 self.__dict__["description"].append(_j)
2892
2893 # Add the fieldname:fieldindex to the _mapname dictionary
2894 self._mapname[_j[0]] = _i
2895
2896 # Create a subclass of PgResultSet. Note that we pass a copy of the
2897 # description to this class.
2898 self.PgResultSetClass = make_PgResultSetClass(self.description[:], self._mapname)
2899
2900 def callproc(self, proc, *args):
2901 if self.closed:
2902 raise InterfaceError, "callproc failed - the cursor is closed."
2903
2904 if self.conn is None:
2905 raise Error, "connection is closed."
2906
2907 if self.closed == 0:
2908 raise InterfaceError, "callproc() failed - cursor is active."
2909
2910 if self.conn.autocommit:
2911 pass
2912 else:
2913 if not self.conn.inTransaction:
2914 self.conn._Connection__setupTransaction()
2915
2916 proc = self.__unicodeConvert(proc)
2917 args = self.__unicodeConvert(args)
2918
2919 _qstr = "select %s(" % proc
2920 for _i in range(len(args)):
2921 _qstr = '%s%s, ' % (_qstr, _quote(args[_i]))
2922 if len(args) == 0:
2923 _qstr = '%s)' % _qstr
2924 else:
2925 _qstr = '%s)' % _qstr[:-2]
2926
2927 _nl = len(self.conn.notices)
2928
2929 try:
2930 self.res = self.conn.conn.query(_qstr)
2931 self._rows_ = self.res.ntuples
2932 self._idx_ = 0
2933 if type(self.res) is not PgResultType:
2934 self.__dict__['rowcount'] = -1
2935 else:
2936 self.__dict__['oidValue'] = self.res.oidValue
2937 if self.res.resultType == RESULT_DQL:
2938 pass
2939 elif self.res.resultType == RESULT_DML:
2940 self.__dict__['rowcount'] = self.res.cmdTuples
2941 else:
2942 self.__dict__['rowcount'] = -1
2943 except OperationalError, msg:
2944 # Uh-oh. A fatal error occurred. This means the current trans-
2945 # action has been aborted. Try to recover to a sane state.
2946 if self.conn.inTransaction:
2947 self.conn.conn.query('END WORK')
2948 self.conn.__dict__["inTransaction"] = 0
2949 self.conn._Connection__closeCursors()
2950 raise OperationalError, msg
2951 except InternalError, msg:
2952 # An internal error occured. Try to get to a sane state.
2953 self.conn.__dict__["inTransaction"] = 0
2954 self.conn._Connection__closeCursors_()
2955 self.conn.close()
2956 raise InternalError, msg
2957
2958 if len(self.conn.notices) != _nl:
2959 _drop = self.conn.notices[-1]
2960 if _drop.find('transaction is aborted') > 0:
2961 raise Warning, self.conn.notices.pop()
2962
2963 self._rows_ = self.res.ntuples
2964 self._idx_ = 0
2965 self.__dict__['rowcount'] = -1 # New query - no fetch occured yet.
2966 self.__makedesc__()
2967
2968 return None
2969
2970 def close(self):
2971 if self.closed:
2972 raise InterfaceError, "The cursor is already closed."
2973
2974 # Dis-associate ourselves from our cursor.
2975 self.__reset()
2976 try:
2977 _cc = self.conn.cursors
2978 if noWeakRef:
2979 _cc.remove(self)
2980 if (len(_cc) > 0):
2981 # We have additional cursors, garbage collect them.
2982 _cc._Connection__gcCursors()
2983 else:
2984 del _cc.data[id(self)]
2985 except:
2986 pass
2987 self.conn = None
2988 self.closed = 1
2989
2990 def execute(self, query, *parms):
2991 if self.closed:
2992 raise InterfaceError, "execute failed - the cursor is closed."
2993
2994 if self.conn is None:
2995 raise Error, "connection is closed."
2996
2997 if self.closed == 0:
2998 if re_DQL.search(query):
2999 # A SELECT has already been executed with this cursor object,
3000 # which means a PostgreSQL portal (may) have been opened.
3001 # Trying to open another portal will cause an error to occur,
3002 # so we asusme that the developer is done with the previous
3003 # SELECT and reset the cursor object to it's inital state.
3004 self.__reset()
3005
3006 _qstr = query
3007 if self.conn.autocommit:
3008 pass
3009 else:
3010 _badQuery = (self.conn.version < 70100) and \
3011 (re_DRT.search(query) or re_DRI.search(query))
3012 if not self.conn.inTransaction:
3013 if _badQuery:
3014 pass # PostgreSQL version < 7.1 and not in transaction,
3015 # so DROP TABLE/INDEX is ok.
3016 else:
3017 self.conn._Connection__setupTransaction()
3018
3019 if re_DQL.search(query) and \
3020 not (noPostgresCursor or re_4UP.search(query)):
3021 _qstr = 'DECLARE "%s" CURSOR FOR %s' % (self.name, query)
3022 self.closed = 0
3023 elif _badQuery and self.conn.inTransaction:
3024 raise NotSupportedError, \
3025 "DROP [TABLE|INDEX] within a transaction"
3026 if not self.conn.inTransaction:
3027 if _badQuery:
3028 pass # not in transaction so DROP TABLE/INDEX is ok.
3029 else:
3030 self.conn._Connection__setupTransaction()
3031
3032 _nl = len(self.conn.notices)
3033
3034 try:
3035 _qstr = self.__unicodeConvert(_qstr)
3036 if len(parms) == 0:
3037 # If there are no paramters, just execute the query.
3038 self.res = self.conn.conn.query(_qstr)
3039 else:
3040 if len(parms) == 1 and \
3041 (type(parms[0]) in [DictType, ListType, TupleType] or \
3042 isinstance(parms[0], PgResultSet)):
3043 parms = (self.__unicodeConvert(parms[0]),)
3044 parms = _quoteall(parms[0])
3045 else:
3046 parms = self.__unicodeConvert(parms)
3047 parms = tuple(map(_quote, parms));
3048 self.res = self.conn.conn.query(_qstr % parms)
3049 self._rows_ = self.res.ntuples
3050 self._idx_ = 0
3051 self.__dict__['rowcount'] = -1 # New query - no fetch occured yet.
3052 if type(self.res) is not PgResultType:
3053 self.__dict__['rowcount'] = -1
3054 else:
3055 self.__dict__['oidValue'] = self.res.oidValue
3056 if self.res.resultType == RESULT_DQL:
3057 pass
3058 elif self.res.resultType == RESULT_DML:
3059 self.__dict__['rowcount'] = self.res.cmdTuples
3060 else:
3061 self.__dict__['rowcount'] = -1
3062 except OperationalError, msg:
3063 # Uh-oh. A fatal error occurred. This means the current trans-
3064 # action has been aborted. Try to recover to a sane state.
3065 if self.conn.inTransaction:
3066 _n = len(self.conn.notices)
3067 self.conn.conn.query('ROLLBACK WORK')
3068 if len(self.conn.notices) != _n:
3069 raise Warning, self.conn.notices.pop()
3070 self.conn.__dict__["inTransaction"] = 0
3071 self.conn._Connection__closeCursors()
3072 raise OperationalError, msg
3073 except InternalError, msg:
3074 # An internal error occured. Try to get to a sane state.
3075 self.conn.__dict__["inTransaction"] = 0
3076 self.conn._Connection__closeCursors_()
3077 self.conn.close()
3078 raise InternalError, msg
3079
3080 if len(self.conn.notices) != _nl:
3081 _drop = self.conn.notices[-1]
3082 if _drop.find('transaction is aborted') > 0:
3083 raise Warning, self.conn.notices.pop()
3084
3085 if self.res.resultType == RESULT_DQL:
3086 self.__makedesc__()
3087 elif _qstr[:8] == 'DECLARE ':
3088 # Ok -- we've created a cursor, we will pre-fetch the first row in
3089 # order to make the description array. Note: the first call to
3090 # fetchXXX will return the pre-fetched record.
3091 self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
3092 self._rows_ = self.res.ntuples
3093 self._idx_ = 0
3094 self.__makedesc__()
3095
3096 if len(self.conn.notices) != _nl:
3097 _drop = self.conn.notices[-1]
3098 if _drop.find('transaction is aborted') > 0:
3099 raise Warning, self.conn.notices.pop()
3100
3101 def executemany(self, query, parm_sequence):
3102 if self.closed:
3103 raise InterfaceError, "executemany failed - the cursor is closed."
3104
3105 if self.conn is None:
3106 raise Error, "connection is closed."
3107
3108 for _i in parm_sequence:
3109 self.execute(query, _i)
3110
3111 def fetchone(self):
3112 if self.closed:
3113 raise InterfaceError, "fetchone failed - the cursor is closed."
3114
3115 if self.conn is None:
3116 raise Error, "connection is closed."
3117
3118 if self.res is None:
3119 raise Error, \
3120 "fetchone() failed - cursor does not contain a result."
3121 elif self.res.resultType != RESULT_DQL:
3122 if self.closed is None:
3123 raise Error, \
3124 "fetchone() Failed - cursor does not contain any rows."
3125
3126 if self._idx_ < self._rows_:
3127 pass # Still data in result buffer, use it.
3128 elif self.closed == 0:
3129 _nl = len(self.conn.notices)
3130 self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
3131 self._rows_ = self.res.ntuples
3132 self._idx_ = 0
3133
3134 if len(self.conn.notices) != _nl:
3135 _drop = self.conn.notices[-1]
3136 if _drop.find('transaction is aborted') > 0:
3137 raise Warning, self.conn.notices.pop()
3138
3139 return self.__fetchOneRow()
3140
3141 def fetchmany(self, sz=None):
3142 if self.closed:
3143 raise InterfaceError, "fetchmany failed - the cursor is closed."
3144
3145 if self.conn is None:
3146 raise Error, "connection is closed."
3147
3148 if self.res is None:
3149 raise Error, \
3150 "fetchmany() failed - cursor does not contain a result."
3151 elif self.res.resultType != RESULT_DQL:
3152 if self.close is None:
3153 raise Error, \
3154 "fetchmany() Failed - cursor does not contain any rows."
3155
3156 if sz is None:
3157 sz = self.arraysize
3158 else:
3159 self.__dict__["arraysize"] = abs(sz)
3160
3161 if sz < 0:
3162 return self.fetchall()
3163
3164 _list = []
3165
3166 # While there are still results in the PgResult object, append them
3167 # to the list of results.
3168 while self._idx_ < self._rows_ and sz > 0:
3169 _list.append(self.__fetchOneRow())
3170 sz = sz - 1
3171
3172 # If still need more results to fullfill the request, fetch them from
3173 # the PostgreSQL portal.
3174 if self.closed == 0 and sz > 0:
3175 _nl = len(self.conn.notices)
3176 self.res = self.conn.conn.query('FETCH %d FROM "%s"' %
3177 (sz, self.name))
3178 self._rows_ = self.res.ntuples
3179 self._idx_ = 0
3180
3181 if len(self.conn.notices) != _nl:
3182 _drop = self.conn.notices[-1]
3183 if _drop.find('transaction is aborted') > 0:
3184 raise Warning, self.conn.notices.pop()
3185
3186 return self.__fetchManyRows(sz, _list)
3187
3188 def fetchall(self):
3189 if self.closed:
3190 raise InterfaceError, "fetchall failed - the cursor is closed."
3191
3192 if self.conn is None:
3193 raise Error, "connection is closed."
3194
3195 if self.res is None:
3196 raise Error, \
3197 "fetchall() failed - cursor does not contain a result."
3198 elif self.res.resultType != RESULT_DQL:
3199 if self.closed is None:
3200 raise Error, \
3201 "fetchall() Failed - cursor does not contain any rows."
3202
3203 _list = []
3204
3205 # While there are still results in the PgResult object, append them
3206 # to the list of results.
3207 while self._idx_ < self._rows_:
3208 _list.append(self.__fetchOneRow())
3209
3210 # Fetch the remaining results from the PostgreSQL portal.
3211 if self.closed == 0:
3212 _nl = len(self.conn.notices)
3213 self.res = self.conn.conn.query('FETCH ALL FROM "%s"' % self.name)
3214 self._rows_ = self.res.ntuples
3215 self._idx_ = 0
3216
3217 if len(self.conn.notices) != _nl:
3218 _drop = self.conn.notices[-1]
3219 if _drop.find('transaction is aborted') > 0:
3220 raise Warning, self.conn.notices.pop()
3221
3222 return self.__fetchManyRows(self._rows_, _list)
3223
3224 def rewind(self):
3225 if self.closed:
3226 raise InterfaceError, "rewind failed - the cursor is closed."
3227
3228 if self.conn is None:
3229 raise Error, "connection is closed."
3230
3231 if self.res is None:
3232 raise Error, "rewind() failed - cursor does not contain a result."
3233 elif self.res.resultType != RESULT_DQL:
3234 if self.closed is None:
3235 raise Error, \
3236 "rewind() Failed - cursor does not contain any rows."
3237
3238 if self.closed == 0:
3239 _nl = len(self.conn.notices)
3240 self.res = self.conn.conn.query('MOVE BACKWARD ALL IN "%s"' %
3241 self.name)
3242 self._rows_ = 0
3243 if len(self.conn.notices) != _nl:
3244 _drop = self.conn.notices[-1]
3245 if _drop.find('transaction is aborted') > 0:
3246 raise Warning, self.conn.notices.pop()
3247
3248 self.__dict__["rowcount"] = -1
3249 self._idx_ = 0
3250
3251 def setinputsizes(self, sizes):
3252 if self.closed:
3253 raise InterfaceError, "setinputsize failed - the cursor is closed."
3254
3255 def setoutputsize(self, size, column=None):
3256 if self.closed:
3257 raise InterfaceError, "setoutputsize failed - the cursor is closed."