Help with a custom "seconds_interval()" construct

[Kent]

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

Much thanks in advance!

Kent

[Mike Bayer]

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

> *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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Kent Bower]

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

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.