On Mon, Aug 5, 2019, at 3:21 PM, 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.
group_by() accepts any number of expressions and you can use it multiple times.
>
> Am Montag, 5. August 2019 18:16:38 UTC+2 schrieb Mike Bayer:
>> does SQLite support WITHIN GROUP ? Try it out on PostgreSQL, I think this is
>> just not syntax SQLite supports.
>>
>>
>> On Mon, Aug 5, 2019, at 10:38 AM, Michael wrote:
>>> Hi!
>>>
>>> I'm really having a great time with sqlalchemy so far!
>>>
>>> Currently I'm trying to apply a percentile function on a ORM schema with
>>> sqlite3. Average, min, max etc are working fine, but i cannot compute the
>>> median or any other percentile using 'percentile_cont'.
>>>
>>> A minimal example and the corresponding error messages can be found below.
>>>
>>> Any help would be greatly appreciated!
>>>
>>> Best
>>> Michael
>>>
>>>
>>> import sqlalchemy
>>> sqlalchemy.__version__ # '1.3.5'
>>>
>>>
>>>
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=False)
>>>
>>>
>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()
>>>
>>>
>>> from sqlalchemy import Column, Float, String, Integer
>>> class User(Base):
>>> __tablename__ = 'users'
>>>
>>>
>>> id = Column(Integer, primary_key=True)
>>> name = Column(String)
>>> role = Column(String)
>>> salary = Column(Float)
>>>
>>>
>>> def __repr__(self):
>>> return "<User(name='%s', fullname='%s', nickname='%s')>" % (
>>> self.name, self.fullname, self.nickname)
>>>
>>>
>>>
>>>
>>>
>>>
>>> Base.metadata.create_all(engine)
>>>
>>>
>>> u1 = User(name='u1', role='Manager', salary = 100)
>>> u2 = User(name='u2', role='Manager', salary = 110)
>>> u3 = User(name='u3', role='Employee', salary = 1000)
>>> u4 = User(name='u4', role='Employee', salary = 200)
>>>
>>>
>>>
>>>
>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>>
>>>
>>>
>>>
>>> session.add(u1)
>>> session.add(u2)
>>> session.add(u3)
>>> session.add(u4)
>>>
>>>
>>>
>>>
>>> from sqlalchemy.sql import func
>>> from sqlalchemy import within_group
>>>
>>> q1 =
>>> session.query(func.avg(User.salary).label('average')).group_by(User.role)
>>> print(q1.all())
>>> q2 =
>>> session.query(func.percentile_disc(0.5).within_group(User.salary)).group_by(User.role)
>>> print(q2)
>>>
>>>
>>> print(q2.all()) # ERROR
>>>
>>> ---------------------------------------------------------------------------
>>> OperationalError Traceback (most recent call last)
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in
>>> _execute_context(self, dialect, constructor, statement, parameters, *args)
>>> 1243 self.dialect.do_execute(
>>> -> 1244 cursor, statement, parameters, context
>>> 1245 )
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/default.py in
>>> do_execute(self, cursor, statement, parameters, context)
>>> 549 def do_execute(self, cursor, statement, parameters,
>>> context=None):
>>> --> 550 cursor.execute(statement, parameters)
>>> 551
>>>
>>> OperationalError: near "(": syntax error
>>>
>>> The above exception was the direct cause of the following exception:
>>>
>>> OperationalError Traceback (most recent call last)
>>> <ipython-input-13-bded0b5cee0a> in <module>
>>> ----> 1 print(q2.all()) # ERROR
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in all(self)
>>> 3166
>>> 3167 """
>>> -> 3168 return list(self)
>>> 3169
>>> 3170 @_generative(_no_clauseelement_condition)
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in __iter__(self)
>>> 3322 if self._autoflush and not self._populate_existing:
>>> 3323 self.session._autoflush()
>>> -> 3324 return self._execute_and_instances(context)
>>> 3325
>>> 3326 def __str__(self):
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in
>>> _execute_and_instances(self, querycontext)
>>> 3347 )
>>> 3348
>>> -> 3349 result = conn.execute(querycontext.statement, self._params)
>>> 3350 return loading.instances(querycontext.query, result,
>>> querycontext)
>>> 3351
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in
>>> execute(self, object_, *multiparams, **params)
>>> 986 raise exc.ObjectNotExecutableError(object_)
>>> 987 else:
>>> --> 988 return meth(self, multiparams, params)
>>> 989
>>> 990 def _execute_function(self, func, multiparams, params):
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/sql/elements.py in
>>> _execute_on_connection(self, connection, multiparams, params)
>>> 285 def _execute_on_connection(self, connection, multiparams,
>>> params):
>>> 286 if self.supports_execution:
>>> --> 287 return connection._execute_clauseelement(self,
>>> multiparams, params)
>>> 288 else:
>>> 289 raise exc.ObjectNotExecutableError(self)
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in
>>> _execute_clauseelement(self, elem, multiparams, params)
>>> 1105 distilled_params,
>>> 1106 compiled_sql,
>>> -> 1107 distilled_params,
>>> 1108 )
>>> 1109 if self._has_events or self.engine._has_events:
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in
>>> _execute_context(self, dialect, constructor, statement, parameters, *args)
>>> 1246 except BaseException as e:
>>> 1247 self._handle_dbapi_exception(
>>> -> 1248 e, statement, parameters, cursor, context
>>> 1249 )
>>> 1250
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in
>>> _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
>>> 1464 util.raise_from_cause(newraise, exc_info)
>>> 1465 elif should_wrap:
>>> -> 1466 util.raise_from_cause(sqlalchemy_exception,
>>> exc_info)
>>> 1467 else:
>>> 1468 util.reraise(*exc_info)
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/util/compat.py in
>>> raise_from_cause(exception, exc_info)
>>> 397 exc_type, exc_value, exc_tb = exc_info
>>> 398 cause = exc_value if exc_value is not exception else None
>>> --> 399 reraise(type(exception), exception, tb=exc_tb, cause=cause)
>>> 400
>>> 401
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/util/compat.py in reraise(tp,
>>> value, tb, cause)
>>> 151 value.__cause__ = cause
>>> 152 if value.__traceback__ is not tb:
>>> --> 153 raise value.with_traceback(tb)
>>> 154 raise value
>>> 155
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in
>>> _execute_context(self, dialect, constructor, statement, parameters, *args)
>>> 1242 if not evt_handled:
>>> 1243 self.dialect.do_execute(
>>> -> 1244 cursor, statement, parameters, context
>>> 1245 )
>>> 1246 except BaseException as e:
>>>
>>> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/default.py in
>>> do_execute(self, cursor, statement, parameters, context)
>>> 548
>>> 549 def do_execute(self, cursor, statement, parameters,
>>> context=None):
>>> --> 550 cursor.execute(statement, parameters)
>>> 551
>>> 552 def do_execute_no_params(self, cursor, statement, context=None):
>>>
>>> OperationalError: (sqlite3.OperationalError) near "(": syntax error
>>> [SQL: SELECT percentile_cont(?) WITHIN GROUP (ORDER BY users.salary DESC)
>>> AS anon_1
>>> FROM users GROUP BY users.role]
>>> [parameters: (0.5,)]
>>> (Background on this error at: http://sqlalche.me/e/e3q8)
>>>
>>>
>>>
>>> --
>>> 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/00f96614-56d4-4cef-9134-632458b5793c%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/00f96614-56d4-4cef-9134-632458b5793c%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/2ddea076-cd34-4e16-b786-02766a948c9a%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/2ddea076-cd34-4e16-b786-02766a948c9a%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/1b1a6107-871f-4cec-9158-097427c8d352%40www.fastmail.com.
