[Zope-CVS] CVS: Products3/PsycopgDA - Adapter.py:1.2
Viktorija Zaksiene
ryzaja@codeworks.lt
Mon, 2 Dec 2002 06:12:38 -0500
Update of /cvs-repository/Products3/PsycopgDA
In directory cvs.zope.org:/tmp/cvs-serv4376
Modified Files:
Adapter.py
Log Message:
Type conversion for datetime fields
=== Products3/PsycopgDA/Adapter.py 1.1 => 1.2 ===
--- Products3/PsycopgDA/Adapter.py:1.1 Wed Nov 13 05:13:22 2002
+++ Products3/PsycopgDA/Adapter.py Mon Dec 2 06:12:37 2002
@@ -16,19 +16,277 @@
$Id$
"""
-import psycopg
-
from Persistence import Persistent
from Zope.App.RDB.ZopeDatabaseAdapter import ZopeDatabaseAdapter, parseDSN
from Zope.App.RDB.ZopeConnection import ZopeConnection
-dsn2option_mapping = {'dbname': 'dbname',
+from datetime import date, time, timetz, datetime, datetimetz, timedelta
+import psycopg
+
+# These OIDs are taken from pg_types.h from PostgreSQL headers.
+# Unfortunatelly psycopg does not export them as constants, and
+# we cannot use psycopg.FOO.values because they overlap.
+DATE_OID = 1082
+TIME_OID = 1083
+TIMETZ_OID = 1266
+TIMESTAMP_OID = 1114
+TIMESTAMPTZ_OID = 1184
+INTERVAL_OID = 1186
+# The following ones are obsolete and we don't handle them
+#ABSTIME_OID = 702
+#RELTIME_OID = 703
+#TINTERVAL_OID = 704
+
+# Date/time parsing functions
+def parse_date(s):
+ """Parses ISO-8601 compliant dates and returns a tuple (year, month,
+ day).
+
+ The following formats are accepted:
+ YYYY-MM-DD (extended format)
+ YYYYMMDD (basic format)
+ """
+ if len(s) == 8:
+ return (int(s[:4]), int(s[4:6]), int(s[6:])) # YYYYMMDD
+ elif len(s) == 10 and s[4] == s[7] == '-':
+ return (int(s[:4]), int(s[5:7]), int(s[8:])) # YYYY-MM-DD
+ else:
+ raise ValueError, 'invalid date string: %s' % s
+
+def parse_time(s):
+ """Parses ISO-8601 compliant times and returns a tuple (hour, minute,
+ second).
+
+ The following formats are accepted:
+ HH:MM:SS.ssss or HHMMSS.ssss
+ HH:MM:SS,ssss or HHMMSS,ssss
+ HH:MM:SS or HHMMSS
+ HH:MM or HHMM
+ HH
+ """
+ if len(s) == 2:
+ return (int(s[:2]), 0, 0) # HH
+ elif len(s) == 4:
+ return (int(s[:2]), int(s[2:]), 0) # HHMM
+ elif len(s) == 5 and s[2] == ':':
+ return (int(s[:2]), int(s[3:]), 0) # HH:MM
+ elif len(s) == 6:
+ return (int(s[:2]), int(s[2:4]), int(s[4:])) # HHMMSS
+ elif len(s) == 8 and s[2] == s[5] == ':':
+ return (int(s[:2]), int(s[3:5]), int(s[6:])) # HH:MM:SS
+ elif len(s) > 9 and s[2] == s[5] == ':' and s[8] == '.':
+ return (int(s[:2]), int(s[3:5]), float(s[6:])) # HH:MM:SS.ssss
+ elif len(s) > 9 and s[2] == s[5] == ':' and s[8] == ',':
+ return (int(s[:2]), int(s[3:5]), float(s[6:8] + "." + s[9:]))
+ # HH:MM:SS,ssss
+ elif len(s) > 7 and s[6] == '.':
+ return (int(s[:2]), int(s[2:4]), float(s[4:])) # HHMMSS.ssss
+ elif len(s) > 7 and s[6] == ',':
+ return (int(s[:2]), int(s[2:4]), float(s[4:6] + "." + s[7:]))
+ # HHMMSS,ssss
+ else:
+ raise ValueError, 'invalid time string: %s' % s
+
+def parse_tz(s):
+ """Parses ISO-8601 timezones and returns the offset east of UTC in
+ minutes.
+
+ The following formats are accepted:
+ +/-HH:MM
+ +/-HHMM
+ +/-HH
+ Z (equivalent to +0000)
+ """
+ if s == "Z":
+ return 0
+ if len(s) in (3, 5, 6) and s[0] in ('+', '-'):
+ hoff = int(s[1:3])
+ if len(s) > 3:
+ moff = int(s[-2:])
+ if len(s) == 6 and s[3] != ':':
+ raise ValueError, 'invalid time zone: %s' % s
+ else:
+ moff = 0
+ if s[0] == '-':
+ return - hoff * 60 - moff
+ else:
+ return hoff * 60 + moff
+ else:
+ raise ValueError, 'invalid time zone: %s' % s
+
+def parse_timetz(s):
+ """Parses ISO-8601 compliant times that may include timezone information
+ and returns a tuple (hour, minute, second, tzoffset).
+
+ tzoffset is the offset east of UTC in minutes. It will be None if s does
+ not include time zone information.
+
+ Formats accepted are those listed in the descriptions of parse_time() and
+ parse_tz(). Time zone should immediatelly follow time without intervening
+ spaces.
+ """
+ idx = s.find('+')
+ if idx < 0: idx = s.find('-')
+ if idx < 0: idx = s.find('Z')
+ if idx < 0:
+ return parse_time(s) + (None,)
+ else:
+ return parse_time(s[:idx]) + (parse_tz(s[idx:]),)
+
+def parse_datetime(s):
+ """Parses ISO-8601 compliant timestamp and returns a tuple (year, month,
+ day, hour, minute, second).
+
+ Formats accepted are those listed in the descriptions of parse_date() and
+ parse_time() with ' ' or 'T' used to separate date and time parts.
+ """
+ idx = s.find('T')
+ if idx < 0: idx = s.find(' ')
+ if idx < 0:
+ raise ValueError, 'time part of datetime missing: %s' % s
+ return parse_date(s[:idx]) + parse_time(s[idx + 1:])
+
+def parse_datetimetz(s):
+ """Parses ISO-8601 compliant timestamp that may include timezone
+ information and returns a tuple (year, month, day, hour, minute, second,
+ tzoffset).
+
+ tzoffset is the offset east of UTC in minutes. It will be None if s does
+ not include time zone information.
+
+ Formats accepted are those listed in the descriptions of parse_date() and
+ parse_timetz() with ' ' or 'T' used to separate date and time parts.
+ """
+ idx = s.find('T')
+ if idx < 0: idx = s.find(' ')
+ if idx < 0:
+ raise ValueError, 'time part of datetime missing: %s' % s
+ return parse_date(s[:idx]) + parse_timetz(s[idx + 1:])
+
+
+def parse_interval(s):
+ """Parses PostgreSQL interval notation and returns a tuple (years, months,
+ days, hours, minutes, seconds).
+
+ Values accepted:
+ interval ::= date
+ | time
+ | date time
+ date ::= date_comp
+ | date date_comp
+ date_comp ::= 1 'day'
+ | number 'days'
+ | 1 'month'
+ | number 'months'
+ | 1 'year'
+ | number 'years'
+ time ::= number ':' number
+ | number ':' number ':' number
+ | number ':' number ':' number '.' fraction
+ """
+ years = months = days = 0
+ hours = minutes = seconds = 0
+ elements = s.split()
+ for i in range(0, len(elements) - 1, 2):
+ count, unit = elements[i:i+2]
+ if unit == 'day' and count == '1':
+ days += 1
+ elif unit == 'days':
+ days += int(count)
+ elif unit == 'month' and count == '1':
+ months += 1
+ elif unit == 'months':
+ months += int(count)
+ elif unit == 'year' and count == '1':
+ years += 1
+ elif unit == 'years':
+ years += int(count)
+ else:
+ raise ValueError, 'unknown time interval %s %s' % (count, unit)
+ if len(elements) % 2 == 1:
+ hours, minutes, seconds = parse_time(elements[-1])
+ return (years, months, days, hours, minutes, seconds)
+
+
+# Type conversions
+def _conv_date(s):
+ if s:
+ return date(*parse_date(s))
+
+def _conv_time(s):
+ if s:
+ hr, mn, sc = parse_time(s)
+ sc, micro = divmod(sc, 1.0)
+ micro = round(micro * 1000000)
+ return time(hr, mn, sc, micro)
+
+def _conv_timetz(s):
+ if s:
+ from Zope.Misc.DateTimeParse import tzinfo
+ hr, mn, sc, tz = parse_timetz(s)
+ sc, micro = divmod(sc, 1.0)
+ micro = round(micro * 1000000)
+ if tz: tz = tzinfo(tz)
+ return timetz(hr, mn, sc, micro, tz)
+
+def _conv_timestamp(s):
+ if s:
+ y, m, d, hr, mn, sc = parse_datetime(s)
+ sc, micro = divmod(sc, 1.0)
+ micro = round(micro * 1000000)
+ return datetime(y, m, d, hr, mn, sc, micro)
+
+def _conv_timestamptz(s):
+ if s:
+ from Zope.Misc.DateTimeParse import tzinfo
+ y, m, d, hr, mn, sc, tz = parse_datetimetz(s)
+ sc, micro = divmod(sc, 1.0)
+ micro = round(micro * 1000000)
+ if tz: tz = tzinfo(tz)
+ return datetimetz(y, m, d, hr, mn, sc, micro, tz)
+
+def _conv_interval(s):
+ if s:
+ y, m, d, hr, mn, sc = parse_interval(s)
+ if (y, m) != (0, 0):
+ # XXX: Currently there's no way to represent years and months as
+ # timedeltas
+ return s
+ else:
+ return timedelta(days=d, hours=hr, minutes=mn, seconds=sc)
+
+
+# User-defined types
+DATE = psycopg.new_type((DATE_OID,), "ZDATE", _conv_date)
+TIME = psycopg.new_type((TIME_OID,), "ZTIME", _conv_time)
+TIMETZ = psycopg.new_type((TIMETZ_OID,), "ZTIMETZ", _conv_timetz)
+TIMESTAMP = psycopg.new_type((TIMESTAMP_OID,), "ZTIMESTAMP", _conv_timestamp)
+TIMESTAMPTZ = psycopg.new_type((TIMESTAMPTZ_OID,), "ZTIMESTAMPTZ",
+ _conv_timestamptz)
+INTERVAL = psycopg.new_type((INTERVAL_OID,), "ZINTERVAL", _conv_interval)
+
+
+dsn2option_mapping = {'host': 'host',
+ 'port': 'port',
+ 'dbname': 'dbname',
'username': 'user',
'password': 'password'}
class PsycopgAdapter(ZopeDatabaseAdapter):
- """A PsycoPG adapter for Zope3"""
+ """A PsycoPG adapter for Zope3.
+
+ The following type conversions are performed:
+
+ DATE -> datetime.date
+ TIME -> datetime.time
+ TIMETZ -> datetime.timetz
+ TIMESTAMP -> datetime.datetime
+ TIMESTAMPTZ -> datetime.datetimetz
+
+ XXX: INTERVAL cannot be represented exactly as datetime.timedelta since
+ it might be something like '1 month', which is a variable number of days.
+ """
__implements__ = ZopeDatabaseAdapter.__implements__
@@ -41,11 +299,15 @@
conn_list.append('%s=%s' %(dsn2option_mapping[option],
conn_info[option]))
conn_str = ' '.join(conn_list)
+ self._registerTypes()
return psycopg.connect(conn_str)
-
-
-
-
-
+ def _registerTypes(self):
+ """Register type conversions for psycopg"""
+ psycopg.register_type(DATE)
+ psycopg.register_type(TIME)
+ psycopg.register_type(TIMETZ)
+ psycopg.register_type(TIMESTAMP)
+ psycopg.register_type(TIMESTAMPTZ)
+ psycopg.register_type(INTERVAL)