Hi,
I am trying to replicate the following SQL (which appears to work fine):
with x as (
select m1.id as id, (m2.start - interval '0.1 sec') as finish
from monitor_journal as m1,
monitor_journal as m2
where m1.start < m2.start
and m1.finish >= m2.start
and m1.id != m2.id
) update monitor_journal as m
set finish = x.finish
from x
where m.id = x.id;
The table monitor_journal describes intervals with start/finish times. The
update above stops those intervals from overlapping by modifying the
"finish" of any overlapping pair.
In my Python code, this table is mapped to the class MonitorJournal.
However the following code gives a warning:
m1 = aliased(MonitorJournal)
m2 = aliased(MonitorJournal)
overlap = s.query(m1.id.label('id'), (m2.start -
dt.timedelta(seconds=0.1)).label('finish')). \
filter(m1.start < m2.start,
m1.finish >= m2.start,
m1.id != m2.id).cte()
s.query(MonitorJournal). \
filter(MonitorJournal.id == overlap.c.id). \
update({MonitorJournal.finish: overlap.c.finish})
Hopefully you can see that I am trying to stay close to the original SQL
above.
The warning (which appears twice - once for overlap.c.id and once for
overlap.c.finish is:
SAWarning: Evaluating non-mapped column expression '%(140640677354656
anon)s.id' onto ORM instances; this is a deprecated use case. Please make
use of the actual mapped columns in ORM-evaluated UPDATE / DELETE
expressions.
Unfortunately I don't understand what the 'actual mapped columns' are. I
assume this is related to the fact that I seem to have ended up outside the
ORM (having to use .c.), but this seems consistent with the example at
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.cte
I can post more code if needed - sorry if the above is missing something
critical - but I hope the above is sufficient. I'm also battling with a
weird Google interface to this group and suspect this post will be an ugly
unformatted mess. Sorry.
Thanks,
Andrew
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/80fb75f3-c890-4be6-8843-7657c2446447n%40googlegroups.com.
