Re: [sqlalchemy] how not to add tablename as column prefix
вторник, 6 июня 2017 г., 19:46:43 UTC+3 пользователь Mike Bayer написал:
>
>
>
> On 06/06/2017 11:14 AM, Антонио Антуан wrote:
> > 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:
> >
> > |
> > fromsqlalchemy importcoalesce,case,func, Column
> > from clickhouse_sqlalchemy import types, engines
>
>
> what is "clickhouse_sqlalchemy" ? Are you writing a custom dialect for
> a database called "clickhouse"?
> if so, then you'd want to customize how the column name is generated in
> the visit_column() method in your compiler. Make sure the
> "include_table" keyword argument is False:
>
>
No, that is not my library, but now I see what was wrong :)
Thanks for your help.
> class MyClickhouseCompiler(SQLCompiler):
> def visit_column(self, *arg, **kw):
> kw['include_table'] = False
> super(MyClickhouseCompiler, self).visit_column(*arg, **kw)
>
>
>
>
> >
> > 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
> > 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
> > 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
> >
>
Re: [sqlalchemy] how not to add tablename as column prefix
On 06/06/2017 11:14 AM, Антонио Антуан wrote:
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:
|
fromsqlalchemy importcoalesce,case,func, Column
from clickhouse_sqlalchemy import types, engines
what is "clickhouse_sqlalchemy" ? Are you writing a custom dialect for
a database called "clickhouse"?
if so, then you'd want to customize how the column name is generated in
the visit_column() method in your compiler. Make sure the
"include_table" keyword argument is False:
class MyClickhouseCompiler(SQLCompiler):
def visit_column(self, *arg, **kw):
kw['include_table'] = False
super(MyClickhouseCompiler, self).visit_column(*arg, **kw)
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
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
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)
[sqlalchemy] how not to add tablename as column prefix
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
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
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.
---
