Baked Query with Bound Entity/FromClause

[Scott Colby]

Hello,

I have a bunch of tables with created_at columns and I would like to bake queries to retrieve counts of rows from them.

def _entities_created(model: Model, before: datetime) -> int:

    baked_query = BAKERY(lambda session: session.query(model))

    baked_query += lambda q: q.with_entities(func.count())

    baked_query += lambda q: q.filter(model.created_at < bindparam("before"))

    return baked_query(session()).params(before=before).scalar()

foos_created = partial(_entities_created, Foo)

bars_created = partial(_entities_created, Bar)

This doesn't work, and upon a minute of reflection, it's clear why: the passed-in value of model is cached in the baked query. If you call foos_created(...) first and then call bars_created(...), you'll get the count of the Foos, and vice versa. I've tried a few things to fix this:

• baked_query = BAKERY(lambda session: session.query(bindparam("model")))
  • This "works" in that it runs, but shows the same problem as the original version.
• baked_query = BAKERY(lambda session: session.query()); baked_query += lambda q: q.select_from(bindparam("entity"))
  • This raises an "ArgumentError: argument is not a mapped class, mapper, aliased(), or FromClause instance." at query compilation time (in _as_query())

Is what I'm trying to do here possible? Is there a way to get an aliased() or FromClause from a bindparam? Is there an alternative approach that would allow me to continue to use baked queries while not having to implement a separate *_entities_created() for every model I'm interested in?

The next problem would then be how to refer to model.created_at within the filter clause. Can that be handled with a bindparam too?

Thanks,

Scott

[Mike Bayer]

so this is not what bound parameters are used for in SQL; bound parameters are a specific construct in the DBAPI driver that only applies to literal parameters in a statement, that is, strings, numbers and other values inside of comparisons, values to be passed.  they never correspond to database objects like table or column names nor do they refer to parts of a SQL statement.

If you are experimenting with baked query, I would strongly advise bypassing them entirely and upgrading to SQLAlchemy 1.4, where queries are now cached automatically and the awkwardness of baked queries is no longer needed.

With baked queries, to include your incoming "model" as part of the cache key, you can add it up front as one of the arguments to cache on:

    baked_query = BAKERY(lambda session: session.query(model), args=(model, ))

that will include the class mentioned by "model" as part of the cache key.  I would ensure that "model" is a long lived object , e.g. a mapped class.  If it's an aliased() object, I'd make sure to use the same aliased() object each time.

The next problem would then be how to refer to model.created_at within the filter clause. Can that be handled with a bindparam too?

Thanks,

Scott

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fb1efc22-ab63-482a-bb45-e4291c624bf8n%40googlegroups.com.

[Scott Colby]

Hi Mike,

On Wednesday, March 31, 2021 at 1:36:33 PM UTC-4 Mike Bayer wrote:

so this is not what bound parameters are used for in SQL; bound parameters are a specific construct in the DBAPI driver that only applies to literal parameters in a statement, that is, strings, numbers and other values inside of comparisons, values to be passed.  they never correspond to database objects like table or column names nor do they refer to parts of a SQL statement.

Thanks for the quick response, and thanks for helping me to improve my understanding of bindparams. In my mind, they were a general way of "templating" the query from SQLAlchemy's point of view, but it makes more sense that they map directly to the bound parameter concept from the DBAPI's perspective.

 

If you are experimenting with baked query, I would strongly advise bypassing them entirely and upgrading to SQLAlchemy 1.4, where queries are now cached automatically and the awkwardness of baked queries is no longer needed.

As you intuited, I'm still on SQLAlchemy 1.3 at the moment; we have existing baked queries that I was doing some maintenance on and I noticed a repeating query in multiple functions and wanted to factor it out. Once it was not working, I had to figure out how to make it work, even though it's not strictly necessary, just out of my own curiosity.

 

With baked queries, to include your incoming "model" as part of the cache key, you can add it up front as one of the arguments to cache on:

    baked_query = BAKERY(lambda session: session.query(model), args=(model, ))

that will include the class mentioned by "model" as part of the cache key.  I would ensure that "model" is a long lived object , e.g. a mapped class.  If it's an aliased() object, I'd make sure to use the same aliased() object each time.

Adding an additional positional argument to augment the cache key worked perfectly for my use case. I am indeed using a subclass of my ORM's declarative base, so it is long-lived.

 

Thanks!

Scott