вторник, 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 <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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > 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 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.
