Thanks Mike.
I'm not sure FunctionElement is most appropriate or if Interval() is the
best "type," but for future readers, as a recipe, this seems to work:
class seconds_interval(FunctionElement):
type = Interval()
name = 'seconds'
def __init__(self, *args, **kwargs):
FunctionElement.__init__(self, *args, **kwargs)
self.seconds = args[0]
@compiles(seconds_interval)
def compile_seconds_interval(element, compiler, **kw):
if compiler.dialect.name == 'oracle' and \
compiler.dialect.server_version_info < (9,):
return "%s/86400" % compiler.process(element.clauses)
else:
# use timedelta as bind param
td = timedelta(seconds=element.seconds)
return compiler.process(bindparam('seconds', value=td, unique=True))
On Sun, May 29, 2016 at 8:25 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
>
>
> On 05/28/2016 09:44 AM, Kent wrote:
>
>> I'm interested in being able to use second time intervals on PostgreSQL,
>> Oracle 8 /and /modern Oracle versions, agnostically.
>>
>> The native python timedelta works great for the postgres and cx_Oracle
>> drivers. However /cx_Oracle connected to Oracle 8 won't support this/
>> so I'm building my own construct to help.
>>
>> We want support for something like this:
>>
>> /current_timestamp() + timedelta(seconds=15)/
>>
>> (Assume "current_timestamp()" works on all these databases -- I've
>> already built that.)
>>
>> *PostgreSQL*, works great with python's timedelta:
>>
>>> DBSession.execute(select([current_timestamp() +
>>>>>
>>>> timedelta(seconds=15)])).scalar()
>> MainThread: 09:15:20,871 INFO [sqlalchemy.engine.base.Engine] BEGIN
>> (implicit)
>> MainThread: 09:15:20,872 INFO [sqlalchemy.engine.base.Engine] SELECT
>> localtimestamp + %(param_1)s AS anon_1
>> MainThread: 09:15:20,872 INFO [sqlalchemy.engine.base.Engine]
>> {'param_1': datetime.timedelta(0, 15)}
>> datetime.datetime(2016, 5, 28, 9, 15, 35, 872999)
>>
>> *Modern Oracle*, works great with python's timedelta:
>>
>>> DBSession.execute(select([current_timestamp() +
>>>>>
>>>> timedelta(seconds=15)])).scalar()
>> MainThread: 09:28:15,009 INFO [sqlalchemy.engine.base.Engine] BEGIN
>> (implicit)
>> MainThread: 09:28:15,010 INFO [sqlalchemy.engine.base.Engine] SELECT
>> systimestamp + :param_1 AS anon_1 FROM DUAL
>> MainThread: 09:28:15,010 INFO [sqlalchemy.engine.base.Engine]
>> {'param_1': datetime.timedelta(0, 15)}
>> datetime.datetime(2016, 5, 28, 9, 28, 30, 11530)
>>
>> *Oracle 8: *no
>>
>> DBSession.execute(select([current_timestamp() +
>>>>>
>>>> timedelta(seconds=15)])).scalar()
>> MainThread: 08:29:37,659 INFO [sqlalchemy.engine.base.Engine] SELECT
>> sysdate + :param_1 AS anon_1 FROM DUAL
>> MainThread: 08:29:37,659 INFO [sqlalchemy.engine.base.Engine]
>> {'param_1': datetime.datetime(1970, 1, 1, 0, 0, 15)}
>> Traceback (most recent call last):
>> File "<console>", line 1, in <module>
>> File
>>
>> "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/scoping.py",
>> line 114, in do
>> return getattr(self.registry(), name)(*args, **kwargs)
>> File
>>
>> "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py",
>> line 804, in execute
>> clause, params or {})
>> File
>>
>> "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
>> line 1450, in execute
>> params)
>> File
>>
>> "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
>> line 1583, in _execute_clauseelement
>> compiled_sql, distilled_params
>> File
>>
>> "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
>> line 1697, in _execute_context
>> context)
>> File
>>
>> "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
>> line 1690, in _execute_context
>> context)
>> File
>>
>> "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py",
>> line 335, in do_execute
>> cursor.execute(statement, parameters)
>> DatabaseError: (DatabaseError) ORA-00975: date + date not allowed
>> 'SELECT sysdate + :param_1 AS anon_1 FROM DUAL' {'param_1':
>> datetime.datetime(1970, 1, 1, 0, 0, 15)}
>>
>>
>> I've already constructed a class called "seconds_interval" that works
>> for Oracle 8, but I don't know how to use the default behavior when not
>> Oracle 8:
>>
>> *Oracle 8 usage:*
>>
>>> DBSession.execute(select([current_timestamp() +
>>>>>
>>>> *seconds_interval(15)*])).scalar()
>> MainThread: 08:37:06,539 INFO [sqlalchemy.engine.base.Engine] SELECT
>> sysdate +*:seconds_1/86400 *AS anon_1 FROM DUAL
>> MainThread: 08:37:06,539 INFO [sqlalchemy.engine.base.Engine]
>> {'seconds_1': 15}
>> datetime.datetime(2016, 5, 28, 8, 37, 25)
>>
>>
>> class seconds_interval(FunctionElement):
>> type = Interval()
>> name = 'seconds'
>>
>> def __init__(self, *args, **kwargs):
>> FunctionElement.__init__(self, *args, **kwargs)
>> self.seconds = args[0]
>>
>> @compiles(seconds_interval)
>> def compile_seconds_interval(element, compiler, **kw):
>> if compiler.dialect.name == 'oracle' and \
>> compiler.dialect.server_version_info < (9,):
>> return "%s/86400" % compiler.process(element.clauses)
>> else:
>> # use timedelta as bind param
>> from datetime import timedelta
>> td = timedelta(seconds=element.seconds)
>> *return ...???*
>>
>> Can anyone help me with the else: above to use the native python
>> timedelta as a bind param? (Or trash it completely if there is a better
>> strategy?)
>>
>
>
> return compiler.process(bindparam(element.clauses[0]), **kw
>
> ?
>
> if you want a bindparam() just put it there....
>
>
>
>> Much thanks in advance!
>> Kent
>>
>>
>>
>> --
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/PkdQdYyEzrg/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
