Sibylle Koczian <nulla.epist...@web.de> writes:
> Exactly, that's it. The stackoverflow discussion was very instructive,
> thank you! I'll try out psycopg2-dateutils.
I'm a big fan of dateutil. If you do use it, you may also choose to
bypass the use of timedelta entirely, since as you've seen it can fail
to accurately represent the database value (timedelta only has days,
minutes and seconds as components).
For example, I use the code below to map PostgreSQL interval columns
directly to dateutil's relativedelta subclass.
Any returned queries involving interval values will use the new
Interval type automatically, and will accurately round-trip. You'll
have to use the Interval class explicitly when creating values to go
the other way.
The mapping occurs purely at the psycopg2 level.
-- David
#
# --------------------------------------------------------------------------
# Interval Mapping
#
# Override normal psycopg2 mapping so Interval types get mapped into a
# relativedelta since it can more accurately represent them. We use our own
# relativedelta subclass as we need it to be a new style class for psycopg2
# adaptation to work. Other code must use our Interval class if trying to
# store such data back into the database.
#
# In this case there's nothing to do at the SQLAlchemy layer because for
# PostgreSQL, it just uses the data as provided by psycopg2.
# --------------------------------------------------------------------------
#
class Interval(relativedelta, object):
pass
import psycopg2
from psycopg2._psycopg import INTERVAL as psycopg2_INTERVAL
from psycopg2.extensions import new_type, register_type, register_adapter, AsIs
import re
interval_re = re.compile(' *'
'((?P<years>\d+) y\D*)?'
'((?P<months>\d+) m\D*)?'
'((?P<days>\d+) d\D*)?'
'((?P<hours>\d+):(?P<minutes>\d+):(?P<seconds>\d+))?'
'(\.(?P<subseconds>\d+))?$')
# Configure handling for supplying an Interval to store in the database
def adapt_interval(interval):
adapter = AsIs("'%d years %d months %d days %02d:%02d:%02d.%06d'" %
(interval.years, interval.months, interval.days,
interval.hours, interval.minutes, interval.seconds,
interval.microseconds))
return adapter
register_adapter(Interval, adapt_interval)
# Configure handling upon receiving an Interval from the database
def cast_interval(value, cur):
if value is None:
return None
m = interval_re.match(value)
if m:
vals = m.groupdict(0)
# Most everything is direct, but subseconds has a varying precision,
# so force it to be microseconds if we had a value
microseconds = vals['subseconds']
if microseconds:
microseconds = (microseconds + '000000')[:6]
return Interval(years=int(vals['years']),
months=int(vals['months']),
days=int(vals['days']),
hours=int(vals['hours']),
minutes=int(vals['minutes']),
seconds=int(vals['seconds']),
microseconds=int(microseconds))
else:
raise InterfaceError("Bad interval representation: %r" % value)
INTERVAL = new_type(psycopg2_INTERVAL.values, "INTERVAL", cast_interval)
register_type(INTERVAL)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
