OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

UPDATE + SubQuery with conditions in SQLAlchemy 2.0 not being rendered

  • Thread starter Thread starter Alpha
  • Start date Start date
A

Alpha

Guest
I'm trying to update a table with info from some other rows from the same table. However, I cannot get SQLAlchemy to generate the proper SQL. It always ends up with a WHERE false clause in the subquery, which nullifies the effect.

I have tried several approaches, and this one seems the most correct but still doesn't work. Other examples I found in here are for older versions of SQLAlchemy.

Here's my code to execute it. (Please forgive the ambiguous naming -- I'm trying to obscure the code from the original source but keep the readable for troubleshooting.)

Code:
parent_id: UUID = ...
iteration: int = ...
current_generation_number: int = ...
previous_generation_number: int = current_generation - 1

previous_generation = (
    select(Run)
    .where(Run.parent_id == parent_id)
    .where(Run.grand_iteration == iteration)
    .where(Run.generation == previous_generation_number)
    .where(Run.data_partition in [DataPartition.VALIDATION, DataPartition.TEST])
    .subquery(name="previous_generation")
)

update_operation = (
    update(Run)
    .where(Run.parent_id == parent_id)
    .where(Run.grand_iteration == iteration)
    .where(Run.generation == current_generation_number)
    .where(Run.arguments == previous_generation.c.arguments)
    .where(Run.data_partition == previous_generation.c.data_partition)
    .values(
        metric1=previous_generation.c.metric1,
        metric2=previous_generation.c.metric2,
        metric3=previous_generation.c.metric3,
    )
)

self.db.execute(update_operation)
self.db.commit()

What I expect to be generated is something of the sort:

Code:
UPDATE runs
SET 
    metric1=previous_generation.metric1, 
    metric2=previous_generation.metric2, 
    metric3=previous_generation.metric3, 
FROM (
    SELECT /* ... columns ... */
    FROM runs
    WHERE
        parent_id = %(parent_id_1)s::UUID 
        AND iteration = %(iteration_1)s 
        AND generation = %(generation_1)s 
        AND data_partition IN ("TEST", "VALIDATION")
) AS previous_generation
WHERE
    runs.parent_id = %(parent_id_1)s::UUID 
    AND runs.iteration = %(iteration_1)s 
    AND runs.generation = %(generation_2)s 
    AND runs.arguments = previous_generation.arguments 
    AND runs.data_partition = previous_generation.data_partition

And here's the SQL that SQLAlchemy logs output. Interestingly, it is output twice (I'm not sure if that's part of the problem). Notes below.

Code:
UPDATE runs
SET 
    metric1=previous_generation.metric1, 
    metric2=previous_generation.metric2, 
    metric3=previous_generation.metric3, 
FROM (
    SELECT 
        runs.id AS id, 
        runs.parent_id AS parent_id, 
        runs.generation AS generation, 
        runs.iteration AS iteration, 
        runs.arguments AS arguments, 
        runs.data_partition AS data_partition, 
        runs.metric1 AS metric1, 
        runs.metric2 AS metric2, 
        runs.metric3 AS metric3
    FROM runs
    WHERE false
) AS previous_generation 
WHERE 
    runs.parent_id = %(parent_id_1)s::UUID 
    AND runs.iteration = %(iteration_1)s 
    AND runs.generation = %(generation_1)s 
    AND runs.arguments = previous_generation.arguments 
    AND runs.data_partition = previous_generation.data_partition 
RETURNING runs.id

And the parameters:

Code:
{
    'parent_id_1': UUID('1cb259e1-9f2e-40b8-884a-5706a8275312'),
    'iteration_1': 1, 
    'generation_1': 3
}

Note the differences:

  1. My different variables are not captured and rendered in the subquery
  2. As such, the subquery ends up with WHERE false, and my conditions are not even included

What am I doing wrong in here? Any guidance is appreciated.

Context: SQLAlchemy 2.0, Python 3.9, PostgreSQL 16.2
<p>I'm trying to update a table with info from some other rows from the same table. However, I cannot get SQLAlchemy to generate the proper SQL. It always ends up with a <code>WHERE false</code> clause in the subquery, which nullifies the effect.</p>
<p>I have tried several approaches, and this one seems the most correct but still doesn't work. Other examples I found in here are for older versions of SQLAlchemy.</p>
<p>Here's my code to execute it. (Please forgive the ambiguous naming -- I'm trying to obscure the code from the original source but keep the readable for troubleshooting.)</p>
<pre class="lang-py prettyprint-override"><code>parent_id: UUID = ...
iteration: int = ...
current_generation_number: int = ...
previous_generation_number: int = current_generation - 1

previous_generation = (
select(Run)
.where(Run.parent_id == parent_id)
.where(Run.grand_iteration == iteration)
.where(Run.generation == previous_generation_number)
.where(Run.data_partition in [DataPartition.VALIDATION, DataPartition.TEST])
.subquery(name="previous_generation")
)

update_operation = (
update(Run)
.where(Run.parent_id == parent_id)
.where(Run.grand_iteration == iteration)
.where(Run.generation == current_generation_number)
.where(Run.arguments == previous_generation.c.arguments)
.where(Run.data_partition == previous_generation.c.data_partition)
.values(
metric1=previous_generation.c.metric1,
metric2=previous_generation.c.metric2,
metric3=previous_generation.c.metric3,
)
)

self.db.execute(update_operation)
self.db.commit()
</code></pre>
<p>What I expect to be generated is something of the sort:</p>
<pre class="lang-sql prettyprint-override"><code>UPDATE runs
SET
metric1=previous_generation.metric1,
metric2=previous_generation.metric2,
metric3=previous_generation.metric3,
FROM (
SELECT /* ... columns ... */
FROM runs
WHERE
parent_id = %(parent_id_1)s::UUID
AND iteration = %(iteration_1)s
AND generation = %(generation_1)s
AND data_partition IN ("TEST", "VALIDATION")
) AS previous_generation
WHERE
runs.parent_id = %(parent_id_1)s::UUID
AND runs.iteration = %(iteration_1)s
AND runs.generation = %(generation_2)s
AND runs.arguments = previous_generation.arguments
AND runs.data_partition = previous_generation.data_partition
</code></pre>
<p>And here's the SQL that SQLAlchemy logs output. Interestingly, it is output twice (I'm not sure if that's part of the problem). Notes below.</p>
<pre class="lang-sql prettyprint-override"><code>UPDATE runs
SET
metric1=previous_generation.metric1,
metric2=previous_generation.metric2,
metric3=previous_generation.metric3,
FROM (
SELECT
runs.id AS id,
runs.parent_id AS parent_id,
runs.generation AS generation,
runs.iteration AS iteration,
runs.arguments AS arguments,
runs.data_partition AS data_partition,
runs.metric1 AS metric1,
runs.metric2 AS metric2,
runs.metric3 AS metric3
FROM runs
WHERE false
) AS previous_generation
WHERE
runs.parent_id = %(parent_id_1)s::UUID
AND runs.iteration = %(iteration_1)s
AND runs.generation = %(generation_1)s
AND runs.arguments = previous_generation.arguments
AND runs.data_partition = previous_generation.data_partition
RETURNING runs.id
</code></pre>
<p>And the parameters:</p>
<pre class="lang-py prettyprint-override"><code>{
'parent_id_1': UUID('1cb259e1-9f2e-40b8-884a-5706a8275312'),
'iteration_1': 1,
'generation_1': 3
}
</code></pre>
<p>Note the differences:</p>
<ol>
<li>My different variables are not captured and rendered in the subquery</li>
<li>As such, the subquery ends up with <code>WHERE false</code>, and my conditions are not even included</li>
</ol>
<p>What am I doing wrong in here? Any guidance is appreciated.</p>
<p>Context: SQLAlchemy 2.0, Python 3.9, PostgreSQL 16.2</p>
 

Latest posts

Top