note also pysqlite allows Python functions to embedded in SQL directly, which can do the percentile_cont() part but not the WITHIN GROUP part.
https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_aggregate On Mon, Aug 5, 2019, at 6:30 PM, Jonathan Vanasco wrote: > > > On Monday, August 5, 2019 at 3:21:40 PM UTC-4, Michael wrote: >> Thanks a lot! Indeed SQLite seems to be the problem and the code works fine >> with PostgreSQL. >> >> Unfortunately, a full fledged database server is not an option. Therefore I >> probably have to work around the problem in Python. >> >> Is there an easy way to obtain a list of objects generated by multiple >> group_by conditions? Then I could calculate the percentiles e.g. in numpy. > > If you can generate a function in raw sql that works with sqlite, you may be > able to use the @compiles to build a function in Python > > as a very simple example, this is how i deal with sqlite wanting `least` and > postgres wanting `min`: > > > > class min_date(expression.FunctionElement): > type = sqlalchemy.types.DateTime() > name = 'min_date' > > > @compiles(min_date) > def min_date__default(element, compiler, **kw): > # return compiler.visit_function(element) > """ > # just return the first date > """ > args = list(element.clauses) > return compiler.process(args[0]) > > > @compiles(min_date, 'postgresql') > def min_date__postgresql(element, compiler, **kw): > """ > # select least(col_a, col_b); > """ > args = list(element.clauses) > return "LEAST(%s, %s)" % ( > compiler.process(args[0]), > compiler.process(args[1]), > ) > > > @compiles(min_date, 'sqlite') > def min_date__sqlite(element, compiler, **kw): > """ > # select min(col_a, col_b); > """ > args = list(element.clauses) > return "min(%s, %s)" % ( > compiler.process(args[0]), > compiler.process(args[1]), > ) > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/cadba638-2a44-4e45-9152-4d8aa0294deb%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/cadba638-2a44-4e45-9152-4d8aa0294deb%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dcb10636-8a20-4b0a-b7fa-613215988c47%40www.fastmail.com.
