A-ha! :) a return type! Thanks, Mike! Once a return type was added, the correct sql generated.
On Monday, November 6, 2017 at 12:40:49 PM UTC-5, Mike Bayer wrote: > > On Sun, Nov 5, 2017 at 5:58 AM, Darin Gordon <[email protected] > <javascript:>> 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] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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.
