On Tue, Jan 16, 2018 at 7:40 PM, Avi Blackmore <[email protected]> wrote: > Hello, > > I am trying to use SQLAlchemy to generate queries against introspected > tables, based on filter expressions that I parse from another > domain-specific language. I have it mostly working, except that I want to > be able to use aggregate-type functions in the DSL, and have them work > properly in SQLAlchemy's generated SQL. > > The code construct I use in my "running functions" code to do this is this > (in essence): > > > fn = getattr(sqlalchemy.func, fname)(*fargs) > > return fn > > > ...where "fname" is the function name and "fargs" the argument list. The > returned fn is then applied to a query object using query.filter(). > > This all works fine for normal functions, but aggregates of course trigger > error messages like "misuse of aggregate function" from SQLite, or > "aggregates not allowed in WHERE clause" from PostgreSQL, or the like. > > I have found that I can "whitelist" a few commonly-known aggregates (min, > max, avg, count, sum), and issue a call to fn.select() to wrap that function > call in a subquery before returning it. This works, but whitelisting > specific aggregate functions is not only tedious, but error-prone, and not > very extensible. > > Is there a property or method I can use in the SQLAlchemy Function objects > to determine if a Function object represents an aggregate?
whitelisting is the only way. You're asking that SQLAlchemy does this, and if we were to do so it would also use...whitelisting. While SQLAlchemy has a very small handful of aggregate functions explicitly set up in sql/functions.py for the purposes of defining the return type for common functions like min(), max(), count(), there's no plan to maintain an exhaustive list of all possible aggregate functions. Such a system would need to extend into each dialect as each database has its own non-standard aggregate function as well. Your DSL OTOH likely only refers to a finite set of aggregate functions so that would be the source of your whitelist. > > Thanks very much, > > Avi Blackmore > > -- > 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.
