« back to all changes in this revision
Viewing changes to psycopg2da/adapter.py
- browse files at revision 232
- compare with another revision
- download diff
- download tarball
- view history from revision 232
- Committer: Fabio Tranchitella
- Date: 2006-09-19 03:50:35 UTC
- Revision ID: kobold-07fbb593353955e9f6a5cfd34723c48813102a7c
Added psycopg2da, the zope3 database adapter for psycopg2.
added
removed
psycopg2da/adapter.py
1
# psycopg2da
2
# Copyright (C) 2006 Fabio Tranchitella <fabio@tranchitella.it>
3
#
4
# Based on psycopgda:
5
#
6
# Copyright (c) 2002-2006 Zope Corporation and Contributors.
7
# All Rights Reserved.
8
#
9
# This software is subject to the provisions of the Zope Public License,
10
# Version 2.1 (ZPL). A copy of the ZPL should accompany this distribution.
11
# THIS SOFTWARE IS PROVIDED "AS IS" AND ANY AND ALL EXPRESS OR IMPLIED
12
# WARRANTIES ARE DISCLAIMED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
13
# WARRANTIES OF TITLE, MERCHANTABILITY, AGAINST INFRINGEMENT, AND FITNESS
14
# FOR A PARTICULAR PURPOSE.
15
#
16
# This program is free software; you can redistribute it and/or modify
17
# it under the terms of the GNU General Public License as published by
18
# the Free Software Foundation; either version 2 of the License, or
19
# (at your option) any later version.
20
#
21
# This program is distributed in the hope that it will be useful,
22
# but WITHOUT ANY WARRANTY; without even the implied warranty of
23
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24
# GNU General Public License for more details.
25
#
26
# You should have received a copy of the GNU General Public License
27
# along with this program; if not, write to the Free Software
28
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
29
#
30
# If you prefer you can use this package using the ZPL license as
31
# published on the Zope web site, http://www.zope.org/Resources/ZPL.
32
#
33
"""PostgreSQL Database Adapter for Zope 3"""
34
35
from zope.interface import implements
36
from zope.rdb import ZopeDatabaseAdapter, parseDSN, ZopeConnection, ZopeCursor
37
from zope.rdb.interfaces import DatabaseException, IZopeConnection
38
from zope.publisher.interfaces import Retry
39
40
from datetime import date, time, datetime, timedelta
41
42
import psycopg2
43
import psycopg2.extensions
44
import re
45
import sys
46
47
# OIDs from psycopg/pgtypes.h
48
DATE_OID = 1082
49
TIME_OID = 1083
50
TIMETZ_OID = 1266
51
TIMESTAMP_OID = 1114
52
TIMESTAMPTZ_OID = 1184
53
INTERVAL_OID = 1186
54
CHAR_OID = 18
55
TEXT_OID = 25
56
BPCHAR_OID = 1042
57
VARCHAR_OID = 1043
58
59
# date/time parsing functions
60
_dateFmt = re.compile(r"^(\d\d\d\d)-?([01]\d)-?([0-3]\d)$")
61
62
def parse_date(s):
63
"""Parses ISO-8601 compliant dates and returns a tuple (year, month,
64
day).
65
66
The following formats are accepted:
67
YYYY-MM-DD (extended format)
68
YYYYMMDD (basic format)
69
"""
70
m = _dateFmt.match(s)
71
if m is None:
72
raise ValueError, 'invalid date string: %s' % s
73
year, month, day = m.groups()
74
return int(year), int(month), int(day)
75
76
77
_timeFmt = re.compile(
78
r"^([0-2]\d)(?::?([0-5]\d)(?::?([0-5]\d)(?:[.,](\d+))?)?)?$")
79
80
def parse_time(s):
81
"""Parses ISO-8601 compliant times and returns a tuple (hour, minute,
82
second).
83
84
The following formats are accepted:
85
HH:MM:SS.ssss or HHMMSS.ssss
86
HH:MM:SS,ssss or HHMMSS,ssss
87
HH:MM:SS or HHMMSS
88
HH:MM or HHMM
89
HH
90
"""
91
m = _timeFmt.match(s)
92
if m is None:
93
raise ValueError, 'invalid time string: %s' % s
94
hr, mn, sc, msc = m.groups(0)
95
if msc != 0:
96
sc = float("%s.%s" % (sc, msc))
97
else:
98
sc = int(sc)
99
return int(hr), int(mn), sc
100
101
102
_tzFmt = re.compile(r"^([+-])([0-2]\d)(?::?([0-5]\d))?$")
103
104
def parse_tz(s):
105
"""Parses ISO-8601 timezones and returns the offset east of UTC in
106
minutes.
107
108
The following formats are accepted:
109
+/-HH:MM
110
+/-HHMM
111
+/-HH
112
Z (equivalent to +0000)
113
"""
114
if s == 'Z':
115
return 0
116
m = _tzFmt.match(s)
117
if m is None:
118
raise ValueError, 'invalid time zone: %s' % s
119
d, hoff, moff = m.groups(0)
120
if d == "-":
121
return - int(hoff) * 60 - int(moff)
122
return int(hoff) * 60 + int(moff)
123
124
125
_tzPos = re.compile(r"[Z+-]")
126
127
def parse_timetz(s):
128
"""Parses ISO-8601 compliant times that may include timezone information
129
and returns a tuple (hour, minute, second, tzoffset).
130
131
tzoffset is the offset east of UTC in minutes. It will be None if s does
132
not include time zone information.
133
134
Formats accepted are those listed in the descriptions of parse_time() and
135
parse_tz(). Time zone should immediatelly follow time without intervening
136
spaces.
137
"""
138
m = _tzPos.search(s)
139
if m is None:
140
return parse_time(s) + (None,)
141
pos = m.start()
142
return parse_time(s[:pos]) + (parse_tz(s[pos:]),)
143
144
145
_datetimeFmt = re.compile(r"[T ]")
146
147
def _split_datetime(s):
148
"""Split date and time parts of ISO-8601 compliant timestamp and
149
return a tuple (date, time).
150
151
' ' or 'T' used to separate date and time parts.
152
"""
153
m = _datetimeFmt.search(s)
154
if m is None:
155
raise ValueError, 'time part of datetime missing: %s' % s
156
pos = m.start()
157
return s[:pos], s[pos + 1:]
158
159
160
def parse_datetime(s):
161
"""Parses ISO-8601 compliant timestamp and returns a tuple (year, month,
162
day, hour, minute, second).
163
164
Formats accepted are those listed in the descriptions of parse_date() and
165
parse_time() with ' ' or 'T' used to separate date and time parts.
166
"""
167
dt, tm = _split_datetime(s)
168
return parse_date(dt) + parse_time(tm)
169
170
171
def parse_datetimetz(s):
172
"""Parses ISO-8601 compliant timestamp that may include timezone
173
information and returns a tuple (year, month, day, hour, minute, second,
174
tzoffset).
175
176
tzoffset is the offset east of UTC in minutes. It will be None if s does
177
not include time zone information.
178
179
Formats accepted are those listed in the descriptions of parse_date() and
180
parse_timetz() with ' ' or 'T' used to separate date and time parts.
181
"""
182
dt, tm = _split_datetime(s)
183
return parse_date(dt) + parse_timetz(tm)
184
185
186
def parse_interval(s):
187
"""Parses PostgreSQL interval notation and returns a tuple (years, months,
188
days, hours, minutes, seconds).
189
190
Values accepted:
191
interval ::= date
192
| time
193
| date time
194
date ::= date_comp
195
| date date_comp
196
date_comp ::= 1 'day'
197
| number 'days'
198
| 1 'month'
199
| 1 'mon'
200
| number 'months'
201
| number 'mons'
202
| 1 'year'
203
| number 'years'
204
time ::= number ':' number
205
| number ':' number ':' number
206
| number ':' number ':' number '.' fraction
207
"""
208
years = months = days = 0
209
hours = minutes = seconds = 0
210
elements = s.split()
211
# Tests with 7.4.6 on Ubuntu 5.4 interval output returns 'mon' and 'mons'
212
# and not 'month' or 'months' as expected. I've fixed this and left
213
# the original matches there too in case this is dependant on
214
# OS or PostgreSQL release.
215
for i in range(0, len(elements) - 1, 2):
216
count, unit = elements[i:i+2]
217
if unit == 'day' and count == '1':
218
days += 1
219
elif unit == 'days':
220
days += int(count)
221
elif unit == 'month' and count == '1':
222
months += 1
223
elif unit == 'mon' and count == '1':
224
months += 1
225
elif unit == 'months':
226
months += int(count)
227
elif unit == 'mons':
228
months += int(count)
229
elif unit == 'year' and count == '1':
230
years += 1
231
elif unit == 'years':
232
years += int(count)
233
else:
234
raise ValueError, 'unknown time interval %s %s' % (count, unit)
235
if len(elements) % 2 == 1:
236
hours, minutes, seconds = parse_time(elements[-1])
237
return (years, months, days, hours, minutes, seconds)
238
239
240
# Type conversions
241
def _conv_date(s, cursor):
242
if s:
243
return date(*parse_date(s))
244
245
def _conv_time(s, cursor):
246
if s:
247
hr, mn, sc = parse_time(s)
248
sc, micro = divmod(sc, 1.0)
249
micro = round(micro * 1000000)
250
return time(hr, mn, int(sc), int(micro))
251
252
def _conv_timetz(s, cursor):
253
if s:
254
from zope.datetime import tzinfo
255
hr, mn, sc, tz = parse_timetz(s)
256
sc, micro = divmod(sc, 1.0)
257
micro = round(micro * 1000000)
258
if tz: tz = tzinfo(tz)
259
return time(hr, mn, int(sc), int(micro), tz)
260
261
def _conv_timestamp(s, cursor):
262
if s:
263
y, m, d, hr, mn, sc = parse_datetime(s)
264
sc, micro = divmod(sc, 1.0)
265
micro = round(micro * 1000000)
266
return datetime(y, m, d, hr, mn, int(sc), int(micro))
267
268
def _conv_timestamptz(s, cursor):
269
if s:
270
from zope.datetime import tzinfo
271
y, m, d, hr, mn, sc, tz = parse_datetimetz(s)
272
sc, micro = divmod(sc, 1.0)
273
micro = round(micro * 1000000)
274
if tz: tz = tzinfo(tz)
275
return datetime(y, m, d, hr, mn, int(sc), int(micro), tz)
276
277
def _conv_interval(s, cursor):
278
if s:
279
y, m, d, hr, mn, sc = parse_interval(s)
280
if (y, m) != (0, 0):
281
# XXX: Currently there's no way to represent years and months as
282
# timedeltas
283
return s
284
else:
285
return timedelta(days=d, hours=hr, minutes=mn, seconds=sc)
286
287
def _get_string_conv(encoding):
288
def _conv_string(s, cursor):
289
if s is not None:
290
s = s.decode(encoding)
291
return s
292
return _conv_string
293
294
# User-defined types
295
DATE = psycopg2.extensions.new_type((DATE_OID,), "ZDATE", _conv_date)
296
TIME = psycopg2.extensions.new_type((TIME_OID,), "ZTIME", _conv_time)
297
TIMETZ = psycopg2.extensions.new_type((TIMETZ_OID,), "ZTIMETZ", _conv_timetz)
298
TIMESTAMP = psycopg2.extensions.new_type((TIMESTAMP_OID,), "ZTIMESTAMP", _conv_timestamp)
299
TIMESTAMPTZ = psycopg2.extensions.new_type((TIMESTAMPTZ_OID,), "ZTIMESTAMPTZ", _conv_timestamptz)
300
INTERVAL = psycopg2.extensions.new_type((INTERVAL_OID,), "ZINTERVAL", _conv_interval)
301
302
def registerTypes(encoding):
303
"""Register type conversions for psycopg"""
304
psycopg2.extensions.register_type(DATE)
305
psycopg2.extensions.register_type(TIME)
306
psycopg2.extensions.register_type(TIMETZ)
307
psycopg2.extensions.register_type(TIMESTAMP)
308
psycopg2.extensions.register_type(TIMESTAMPTZ)
309
psycopg2.extensions.register_type(INTERVAL)
310
STRING = psycopg2.extensions.new_type((CHAR_OID, TEXT_OID, BPCHAR_OID, VARCHAR_OID), "ZSTRING", _get_string_conv(encoding))
311
psycopg2.extensions.register_type(STRING)
312
313
314
dsn2option_mapping = {'host': 'host',
315
'port': 'port',
316
'dbname': 'dbname',
317
'username': 'user',
318
'password': 'password'}
319
320
class Psycopg2Adapter(ZopeDatabaseAdapter):
321
"""A psycopg2 adapter for Zope3.
322
323
The following type conversions are performed:
324
325
DATE -> datetime.date
326
TIME -> datetime.time
327
TIMETZ -> datetime.time
328
TIMESTAMP -> datetime.datetime
329
TIMESTAMPTZ -> datetime.datetime
330
331
XXX: INTERVAL cannot be represented exactly as datetime.timedelta since
332
it might be something like '1 month', which is a variable number of days.
333
"""
334
335
def connect(self):
336
if not self.isConnected():
337
try:
338
self._v_connection = Psycopg2Connection(
339
self._connection_factory(), self
340
)
341
except psycopg2.Error, error:
342
raise DatabaseException, str(error)
343
344
def registerTypes(self):
345
registerTypes(self.getEncoding())
346
347
def _connection_factory(self):
348
"""Create a psycopg2 DBI connection based on the DSN"""
349
self.registerTypes()
350
conn_info = parseDSN(self.dsn)
351
conn_list = []
352
for dsnname, optname in dsn2option_mapping.iteritems():
353
if conn_info[dsnname]:
354
conn_list.append('%s=%s' % (optname, conn_info[dsnname]))
355
conn_str = ' '.join(conn_list)
356
connection = psycopg2.connect(conn_str)
357
connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)
358
return connection
359
360
def disconnect(self):
361
if self.isConnected():
362
try:
363
self._v_connection.close()
364
except psycopg2.InterfaceError:
365
pass
366
self._v_connection = None
367
368
369
def _handle_psycopg_exception(error):
370
"""Called from a exception handler for psycopg2.Error.
371
372
If we have a serialization exception or a deadlock, we should retry the
373
transaction by raising a Retry exception. Otherwise, we reraise.
374
"""
375
if not error.args:
376
raise
377
msg = error.args[0]
378
# These messages are from PostgreSQL 8.0. They may change between
379
# PostgreSQL releases - if so, the different messages should be added
380
# rather than the existing ones changed so this logic works with
381
# different versions.
382
if msg.startswith(
383
'ERROR: could not serialize access due to concurrent update'
384
):
385
raise Retry(sys.exc_info())
386
if msg.startswith('ERROR: deadlock detected'):
387
raise Retry(sys.exc_info())
388
raise
389
390
391
class IPsycopg2ZopeConnection(IZopeConnection):
392
"""A marker interface stating that this connection uses PostgreSQL."""
393
394
395
class Psycopg2Connection(ZopeConnection):
396
397
implements(IPsycopg2ZopeConnection)
398
399
def cursor(self):
400
"""See IZopeConnection"""
401
return Psycopg2Cursor(self.conn.cursor(), self)
402
403
def commit(self):
404
try:
405
ZopeConnection.commit(self)
406
except psycopg2.Error, error:
407
_handle_psycopg_exception(error)
408
409
410
class Psycopg2Cursor(ZopeCursor):
411
412
def execute(self, operation, parameters=None):
413
"""See IZopeCursor"""
414
try:
415
return ZopeCursor.execute(self, operation, parameters)
416
except psycopg2.Error, error:
417
_handle_psycopg_exception(error)
418
419
def executemany(operation, seq_of_parameters=None):
420
"""See IZopeCursor"""
421
raise RuntimeError, 'Oos'
422
try:
423
return ZopeCursor.execute(self, operation, seq_of_parameters)
424
except psycopg2.Error, error:
425
_handle_psycopg_exception(error)
Loggerhead is a web-based interface for Breezy
Version: 2.0.1