On Sep 4, 2012, at 7:02 AM, David McKeone wrote:
> I've been using hybrid properties recently and ran into a solution that would
> only work on PostgreSQL. That's fine for my usage, but it's so ugly I was
> curious if there was a better, more portable way to do this.
>
> Here is the scenario: x minutes before a given date things can happen in my
> program. This is used for things like notifications and things where storing
> the actual date is more onerous than storing the delta itself. Here is some
> code to give an idea (note that this is simplified example and
> self.minutes_before could actually come from a different table, so saving the
> calculated date would be impractical)
>
> @hybrid_property
> def notify_date(self)
> return self.date - timedelta(minutes=self.minutes_before)
>
> @notify_date.expression
> def notify_date(cls)
> return cls.date - cast(cast(cls.minutes_before, Unicode) + ' minutes',
> Interval)
>
> So that last part is attempting to mimic this in PostgreSQL:
>
> SELECT date - (minutes_before::varchar || ' minutes')::interval FROM my_table
>
> So that's it. Anyone aware of a better way to accomplish this? Is there
> something in SQLAlchemy that would do something similar to timedelta?
> Something like sqlalchemy.interval(minutes=cls.minutes_before) that can use
> attributes as well as literals and can translate between databases?
I've had to deal with exactly this issue, and the solution is to build up
custom @compiles constructs against the target databases you need. Ideally it
would be nice if SQLAlchemy had a suite of portable date arithmetic included,
but this is a hard problem with a lot of tradeoffs on different backends.
Here's code I've been using to get the interval between two dates that works on
Postgresql and MSSQL. You'd need to adapt this to the date arithmetic and
backends you're looking for:
from sqlalchemy.sql import expression
from sqlalchemy import Integer
from sqlalchemy.ext.compiler import compiles
class date_diff(expression.FunctionElement):
type = Integer()
name = 'age'
@compiles(date_diff, 'postgresql')
def _pg_date_diff(element, compiler, **kw):
# this is using the same method as SQL Server
return "(%s::date - %s::date)" % (
compiler.process(element.clauses.clauses[1]),
compiler.process(element.clauses.clauses[0]),
)
@compiles(date_diff, 'mssql')
def _ms_date_diff(element, compiler, **kw):
return "DATEDIFF(day, %s, %s)" % (
compiler.process(element.clauses.clauses[0], **kw),
compiler.process(element.clauses.clauses[1], **kw),
)
from sqlalchemy.dialects import mssql, postgresql
import datetime
expr = date_diff(datetime.date(2012, 4, 18), datetime.date(2012, 5, 20))
print expr.compile(dialect=mssql.dialect())
print expr.compile(dialect=postgresql.dialect())
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/myAFyaY-m4EJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
