Hi.
I want to generate query without tablename as columns prefix. How can I do
this?
For example, query *Session.query(table.id).filter(table.time > func.now())*
compiles
to *SELECT table.id FROM table WHERE table.time > now(). *I want to get *SELECT
id FROM table WHERE time > now(). *
I want it, because ClickHouse has its own syntax, and in some cases it is
far from SQL standards.
Here is my model and query, they as same as my real model and real query:
from sqlalchemy import coalesce, case, func, Column
from clickhouse_sqlalchemy import types, engines
from project import Base
class Model(Base):
__table__ = 'analytics'
ts_spawn = Column(types.UInt32, primary_key=True)
entity_id = Column(types.UInt32)
condition = Column(types.UInt32)
clicks = Column(types.UInt32)
Session.query(Model.entity_id.label('group_by'), coalesce(func.sum(case([(
Model.condition > 0, Model.clicks)], else_=0)).label('clicks')).group_by(
Model.entity_id)
Logs:
2017-06-06 18:09:04,270 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-06-06 18:09:04,270 | sqlalchemy.engine.base.Engine | info:109 | INFO |
BEGIN (implicit)
2017-06-06 18:09:04,274 INFO sqlalchemy.engine.base.Engine SELECT analytics.
entity_id AS group_by, coalesce(sum(CASE WHEN (analytics.condition > 0)
THEN analytics.clicks ELSE 0 END), 0) AS clicks
FROM analytics
GROUP BY analytics.entity_id FORMAT TabSeparatedWithNamesAndTypes
2017-06-06 18:09:04,274 | sqlalchemy.engine.base.Engine | info:109 | INFO |
SELECT analytics.entity_id AS group_by, coalesce(sum(CASE WHEN
(analytics.condition > 0) THEN analytics.clicks ELSE 0 END), 0) AS clicks
FROM analytics
GROUP BY analytics.entity_id FORMAT TabSeparatedWithNamesAndTypes
2017-06-06 18:09:04,276 INFO sqlalchemy.engine.base.Engine {}
2017-06-06 18:09:04,276 | sqlalchemy.engine.base.Engine | info:109 | INFO |
{}
Traceback (most recent call last):
File "/opt/pycharm-professional/helpers/pydev/pydevd.py", line 1585, in
<module>
globals = debugger.run(setup['file'], None, None, is_module)
File "/opt/pycharm-professional/helpers/pydev/pydevd.py", line 1015, in
run
pydev_imports.execfile(file, globals, locals) # execute the script
File "/home/anton/Projects/project/core/project/core/run/stuff.py", line
10, in <module>
print Statistics(Statistics.groups.entity_id, 0, int(time.time()),
Statistics.columns.clicks).total
File "/home/anton/Projects/project/core/project/core/util/stat/stat.py",
line 612, in total
self.get_data()
File "/home/anton/Projects/project/core/project/core/util/stat/stat.py",
line 452, in get_data
self.calculate()
File "/home/anton/Projects/project/core/project/core/util/stat/stat.py",
line 512, in calculate
self._get_raw_data()
File "/home/anton/Projects/project/core/project/core/util/stat/stat.py",
line 798, in _get_raw_data
for row in query.session.execute(compiled_query):
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 1097,
in execute
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 914,
in execute
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 323,
in _execute_on_connection
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1010,
in _execute_clauseelement
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1146,
in _execute_context
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1344,
in _handle_dbapi_exception
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1139,
in _execute_context
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line
450, in do_execute
File
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/connector.py",
line 98, in execute
self._process_response(response_gen)
File
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/connector.py",
line 185, in _process_response
self._columns = next(response, None)
File
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/transport.py",
line 36, in execute
r = self._send(query, params=params, stream=True)
File
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/transport.py",
line 66, in _send
raise DatabaseException(r.text)
clickhouse_sqlalchemy.exceptions.DatabaseException: Code: 47,
e.displayText() = DB::Exception: Unknown identifier: analytics.condition,
e.what() = DB::Exception
And that query works normally:
SELECT entity_id AS group_by, coalesce(sum(CASE WHEN (condition > 0) THEN
clicks ELSE 0 END), 0) AS clicks
FROM analytics
GROUP BY entity_id
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
