On Mon, Jan 15, 2018 at 6:09 PM, Mike Bayer <[email protected]> wrote:
> On Mon, Jan 15, 2018 at 5:32 PM, Mike Bayer <[email protected]> wrote:
>
> that's not quite right, because I'm not selecting from Test. Adding
> the JOIN back in, I've tried aliasing Test everywhere and it still
> produces the error. MySQL is really bad at subqueries. give me a
> minute to find a query that works here.
OK try this:
latest_items = session.query(
Test.value, func.max(Test.timestamp).label('latest_timestamp')
).group_by(Test.value).subquery(name='subquery1')
deleted_rows = (
session.query(Test)
.filter((Test.timestamp < 5))
.filter(
~exists().
where(Test.value == latest_items.c.value).
where(Test.timestamp == latest_items.c.latest_timestamp)
).delete(synchronize_session=False)
)
output:
DELETE FROM test WHERE test.timestamp < %s AND NOT (EXISTS (SELECT *
FROM (SELECT test.value AS value, max(test.timestamp) AS latest_timestamp
FROM test GROUP BY test.value) AS subquery1
WHERE test.value = subquery1.value AND test.timestamp =
subquery1.latest_timestamp))
>
>
>
>
>
>>
>>
>>
>>
>>>
>>> … I guess that's a bug? At least I would assume that this should not result
>>> in an OperationalError, especially since it works with sqlite?
>>>
>>> Thanks for any help,
>>>
>>> Lukas
>>>
>>> [0] https://tinloaf.de/~tinloaf/sqlalchemy/mariadb.html
>>> [1] https://tinloaf.de/~tinloaf/sqlalchemy/sqlite.html
>>> [2]
>>> https://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table
>>>
>>> --
>>> 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.
