On Wed, Mar 31, 2021, at 12:37 PM, Scott Colby wrote:
> 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?
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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/fb1efc22-ab63-482a-bb45-e4291c624bf8n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/fb1efc22-ab63-482a-bb45-e4291c624bf8n%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/abf2b80a-a73f-4996-83b6-a892bc8477b6%40www.fastmail.com.
