hi - the ORM update() function wants to locate all occurrences of MonitorJournal that are locally present in the Session in question which match the WHERE criteria in use, and then it wants to alter those instances with the newly updated value in memory, so that in order for the effects of the UPDATE to be present in local objects, it would not need to emit a second SELECT. This is controlled using the synchronize_session parameter which defaults to "evaluate":
https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=synchronize_session#sqlalchemy.orm.query.Query.update.params.synchronize_session synchronize_session raises an error and asks you to choose a different strategy when it can't make use of what's given, however in this case it's not sure what "overlap" is and it thinks this may be a deprecated use case, but in fact it's not going to work at all so you need to use a different synchronize strategy, either False or "fetch" depending on your needs. On Sat, Jul 25, 2020, at 7:09 PM, and...@acooke.org wrote: > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/80fb75f3-c890-4be6-8843-7657c2446447n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/2e9c41bf-9c14-4277-8daf-aa56ff11cedf%40www.fastmail.com.
