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?)
