Hey Mike and company,
I've run into what I assume is a bug but hope that someone could correct
this
assumption.
A where clause of whether a tstzrange (resolved to a DateTimeTZRange)
contains
"now" raises an exception because an incorrect "contains" function is
associated
with it during the translation to SQL. Rather than resolving to an
appropriate "@>" containment operator, sqlalchemy / psycopg2 are resolving
to something that seems to be appropriate for the interval type within the
tstzrange
expression.
The following valid SQL lets you create a tstzrange from two timestamps.
Based on
the traceback, it appears that SQLAlchemy is failing to recognize the
parameters as
timestamps, recognizing them as intervals instead:
```
select tstzrange((current_date + '8 hours'::interval), (current_date +
'12 hours'::interval), '[]');
```
The line that triggers the exception using sqlalchemy is:
where(sched_sel.c.timeslot.contains(func.now())))
For the sake of keeping the code "alive" indefinitely, I'm pasting all of
it here.
If you want to run the example, update db_url with your postgresql
connection string.
The traceback is at the end.
I'm using Postgresql v10, SQLAlchemy v1.1.14, psycopg2 v2.7.3.2, and Python
3.6.1.
Thanks for considering!
Regards
Darin
github profile: (https://github.com/Dowwie)
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
"""
The following example illustrates a recurring meeting/appointment schedule
data
model using Postgresql's date, time, and range features. A query confirms
whether there is a scheduled event currently ongoing -- now.
This example currently does not work, but the issue may likely be due to a
bug
rather than the implementation presented.
"""
from datetime import datetime, timedelta
import pytz
from psycopg2.extras import DateTimeTZRange
from sqlalchemy.dialects.postgresql import INTERVAL, ARRAY
from sqlalchemy.dialects.postgresql.ranges import TSTZRANGE
from sqlalchemy import (Integer, Table, Column, create_engine, MetaData,
select, func, insert, literal, ForeignKey, join,
and_)
db_url = "postgres://username:password@db:5432/yourdb"
engine = create_engine(db_url, echo=True)
metadata = MetaData(engine)
t_schedule = Table('tmp_schedule', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('time_period', TSTZRANGE, nullable=False))
t_timeslot = Table('tmp_timeslot', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('schedule_id', ForeignKey('tmp_schedule.id')),
Column('weekdays', ARRAY(Integer)),
Column('start_time', INTERVAL),
Column('end_time', INTERVAL))
metadata.drop_all()
metadata.create_all()
now = datetime.now(pytz.utc)
later = now + timedelta(days=60)
time_period = DateTimeTZRange(now, later, bounds='[]')
conn = engine.connect()
with conn.begin() as trans:
schedule_id = conn.scalar(insert(t_schedule).
values({'time_period': time_period}).
returning(t_schedule.c.id))
conn.execute(insert(t_timeslot).
values({'schedule_id': schedule_id,
'weekdays': [6],
'start_time': timedelta(hours=9),
'end_time': timedelta(hours=12)}))
conn.execute(insert(t_timeslot).
values({'schedule_id': schedule_id,
'weekdays': [1, 2, 3, 4, 5],
'start_time': timedelta(hours=17),
'end_time': timedelta(hours=20)}))
now_weekday = datetime.now().weekday()
thejoin = join(t_schedule, t_timeslot,
t_schedule.c.id == t_timeslot.c.schedule_id)
timeslot = func.tstzrange(func.current_date() + t_timeslot.c.start_time,
func.current_date() + t_timeslot.c.end_time,
'[]')
sched_sel = (select([timeslot.label('timeslot')]).
select_from(thejoin).
where(and_(t_timeslot.c.weekdays.contains([now_weekday]),
t_schedule.c.time_period.contains(func.now())))).alias('z')
ongoing_sel = (select([literal(True)]).
where(sched_sel.c.timeslot.contains(func.now())))
print('\n\n', ongoing_sel.compile(engine))
result = conn.scalar(ongoing_sel)
print('A scheduled timeslot is ongoing: {}'.format(result))
trans.rollback()
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for
type interval: "%"
LINE 5: WHERE (z.timeslot LIKE '%' + now() || '%')
^
[SQL: "SELECT %(param_1)s AS anon_1 \nFROM (SELECT tstzrange(CURRENT_DATE
+ tmp_timeslot.start_time, CURRENT_DATE + tmp_timeslot.end_time,
%(tstzrange_1)s) AS timeslot \nFROM tmp_schedule JOIN tmp_timeslot ON
tmp_schedule.id = tmp_timeslot.schedule_id \nWHERE tmp_timeslot.weekdays @>
%(weekdays_1)s AND (tmp_schedule.time_period @> now())) AS z \nWHERE
(z.timeslot LIKE '%%' + now() || '%%')"] [parameters: {'param_1': True,
'tstzrange_1': '[]', 'weekdays_1': [5]}]
--
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.
