On Sun, Nov 5, 2017 at 5:58 AM, Darin Gordon <[email protected]> wrote:
> Hey Mike and company,
>
> If you were to run the following code against a postgresql 9.5+ database,
> you'll see an exception because the "contains" function sqlalchemy
> associates with the func.tstzrange expression below is intended for an
> Interval type when instead it ought to use the contains for a Timestamp.
> After the code example is the exception that is raised, followed by a query
> you can run to confirm that a Timestamp type is created.
The func.tstzrange() function doesn't have a preset return type in
SQLAlchemy core, you have to specify it:
from sqlalchemy.dialects.postgresql import INTERVAL, TSTZRANGE
from sqlalchemy import (create_engine, select, func, literal, cast)
db_url = "postgres://scott:tiger@localhost/test"
engine = create_engine(db_url, echo=True)
with engine.connect() as conn:
timeslot = func.tstzrange(
(
func.current_date() + cast('8 hours', INTERVAL())
),
(
func.current_date() + cast('12 hours', INTERVAL())
),
'[]',
type_=TSTZRANGE
)
is_ongoing = conn.scalar(
select([literal(True)]).where(timeslot.contains(func.now())))
then the output is:
SELECT %(param_1)s AS anon_1
WHERE tstzrange(CURRENT_DATE + CAST(%(param_2)s AS INTERVAL),
CURRENT_DATE + CAST(%(param_3)s AS INTERVAL), %(tstzrange_1)s) @>
now()
which I think is what you're looking for.
>
> Thanks for considering!
>
> Darin
>
>
> -------------------------------------------------------------------------------------------------------------------------
>
> from sqlalchemy.dialects.postgresql import INTERVAL
> from sqlalchemy import (create_engine, select, func, literal, cast)
>
> db_url = "postgres://enter your db info here"
>
> engine = create_engine(db_url, echo=True)
>
> with engine.connect() as conn:
>
> timeslot = func.tstzrange((func.current_date() + cast('8 hours',
> INTERVAL())),
> (func.current_date() + cast('12
> hours', INTERVAL())),
> '[]')
>
> is_ongoing = conn.scalar(select([literal(True)]).
>
> where(timeslot.contains(func.now())))
>
> print('\n\n', is_ongoing.compile(engine))
>
> -------------------------------------------------------------------------------------------------------------------------
> -------------------------------------------------------------------------------------------------------------------------
>
> sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for type
> interval: "%"
> LINE 2: ...T_DATE + CAST('12 hours' AS INTERVAL), '[]') LIKE '%' + now(...
> ^
> [SQL: "SELECT %(param_1)s AS anon_1 \nWHERE (tstzrange(CURRENT_DATE +
> CAST(%(param_2)s AS INTERVAL), CURRENT_DATE + CAST(%(param_3)s AS INTERVAL),
> %(tstzrange_1)s) LIKE '%%' + now() || '%%')"] [parameters: {'param_1': True,
> 'param_2': '8 hours', 'param_3': '12 hours', 'tstzrange_1': '[]'}]
>
> -------------------------------------------------------------------------------------------------------------------------
> -------------------------------------------------------------------------------------------------------------------------
>
> To confirm Timestamp type, run: SELECT pg_typeof(CURRENT_DATE + '8
> hours'::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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
