Using arbitrary sqlalchemy select results (e.g., from a CTE) to create ORM instances

Question

When I create an instance of an object (in my example below, a Company) I want to automagically create default, related objects. One way is to use a per-row, after-insert trigger, but I'm trying to avoid that route and use CTEs which are easier to read and maintain. I have this SQL working (underlying db is PostgreSQL and the only thing you need to know about table company is its primary key is: id SERIAL PRIMARY KEY and it has one other required column, name VARCHAR NOT NULL):

with new_company as (
     -- insert my company row, returning the whole row
     insert into company (name)
          values ('Acme, Inc.')
       returning *
),

other_related as (
   -- herein I join to `new_company` and create default related rows
   -- in other tables.  Here we use, effectively, a no-op - what it
   -- actually does is not germane to the issue.
   select id from new_company
)

-- Having created the related rows, we return the row we inserted into
-- table `company`.
select * from new_company;

The above works like a charm and with the recently added Select.add_cte() (in sqlalchemy 1.4.21) I can write the above with the following python:

import sqlalchemy as sa

from myapp.models import Company

new_company = (
   sa.insert(Company)
   .values(name='Acme, Inc.')
   .returning(Company)
   .cte(name='new_company')
)
other_related = (
   sa.select(sa.text('new_company.id'))
   .select_from(new_company)
   .cte('other_related')
)
fetch_company = (
   sa.select(sa.text('* from new_company'))
   .add_cte(other_related)
)
print(fetch_company)

And the output is:

WITH new_company AS
(INSERT INTO company (name) VALUES (:param_1) RETURNING company.id, company.name),
other_related AS
(SELECT new_company.id FROM new_company) 
SELECT * from new_company

Perfect! But when I execute the above query I get back a Row:

>>> result = session.execute(fetch_company).fetchone()
>>> print(result)
(26, 'Acme, Inc.')

I can create an instance with:

>>> result = session.execute(fetch_company).fetchone()
>>> company = Company(**result)

But this instance, if added to the session, is in the wrong state, pending, and if I flush and/or commit, I get a duplicate key error because the company is already in the database.

If I try using Company in the select list, I get a bad query because sqlalchemy automagically sets the from-clause and I cannot figure out how to clear or explicitly set the from-clause to use my CTE.

I'm looking for one of several possible solutions:

annotate an arbitrary query in some way to say, "build an instance of MyModel, but use this table/alias", e.g., query = sa.select(Company).select_from(new_company.alias('company'), reset=True).
tell a session that an instance is persistent regardless of what the session thinks about the instance, e.g., company = Company(**result); session.add(company, force_state='persistent')

Obviously I could do another round-trip to the db with a call to session.merge() (as discussed in early comments of this question) so the instance ends up in the correct state, but that seems terribly inefficient especially if/when used to return lists of instances.

Using arbitrary sqlalchemy select results (e.g., from a CTE) to create ORM instances

0 Answers