Hi,
I think I've encountered my first time caring about psycopg2 mangling
intervals and this looks super helpful. I was wondering if you have any
updated suggestions since this post is a few years old?
Thanks!
David
On Tuesday, December 24, 2013 at 6:27:06 PM UTC-5, David Bolen wrote:
>
> Sibylle Koczian <nulla....@web.de <javascript:>> 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)
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/9657cb0a-83f0-4253-95dd-6d07186d0aa0o%40googlegroups.com.
