as this involves a MySQL-specific syntax, the easiest way to just make this
happen without any hassle is to use literal_column(), such as:
q1 = s.query(
func.group_concat(
literal_column(
"DISTINCT lower(`role`.`name`) ORDER BY lower(`role`.`name`)
SEPARATOR '|'"
)
)
).select_from(Role)
print(q1)
beyond that, the SQL in there is unusual enough that I would otherwise build a
custom @compiles to handle it, and of course this is an operator that can be
made available in the MySQL dialect if we can get quality contributions.
I've written out an example of the custom compiles version in a test script
below which you can experiment with.
from sqlalchemy import BigInteger
from sqlalchemy import Boolean
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import func
from sqlalchemy import literal_column
from sqlalchemy import String
from sqlalchemy import text
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.sql.expression import ColumnElement
Base = declarative_base()
class Role(Base):
__tablename__ = "role"
id = Column(BigInteger, primary_key=True)
_created = Column(
DateTime, nullable=False, server_default=text("CURRENT_TIMESTAMP")
)
_updated = Column(DateTime)
_deleted = Column(Boolean, nullable=False, server_default=text("'0'"))
name = Column(String(200), nullable=False)
description = Column(String(1000))
"""
SELECT group_concat(DISTINCT lower(`role`.`name`) ORDER BY lower(`role`.`name`)
SEPARATOR '|')
FROM ...
GROUP BY ...
"""
s = Session()
# approach #1, just use literal_column()
q1 = s.query(
func.group_concat(
literal_column(
"DISTINCT lower(`role`.`name`) ORDER BY lower(`role`.`name`)
SEPARATOR '|'"
)
)
).select_from(Role)
print(q1)
# approach #2, build a ColumnElement.
class GroupConcat(ColumnElement):
def __init__(self, expr, distinct=False, order_by=None, separator="|"):
self.expr = expr
self.distinct = distinct
self.order_by = order_by
self.separator = separator
# this slightly private attribute automatically exports the FROM elements,
# you can also use select_from() in your query
@property
def _from_objects(self):
return self.expr._from_objects
@compiles(GroupConcat)
def _compile_group_concat(element, compiler, **kw):
return "GROUP_CONCAT(%s%s%s SEPARATOR '%s')" % (
"DISTINCT " if element.distinct else "",
compiler.process(element.expr, **kw),
" ORDER BY %s" % compiler.process(element.order_by, **kw)
if element.order_by is not None
else "",
element.separator,
)
q2 = s.query(
GroupConcat(
func.lower(Role.name), distinct=True, order_by=func.lower(Role.name)
)
)
print(q2)
On Tue, Dec 15, 2020, at 8:35 PM, [email protected] wrote:
> I want to execute the following MySQL query:
>
> SELECT group_concat(DISTINCT lower(`role`.`name`) ORDER BY
> lower(`role`.`name`) SEPARATOR '|')
> FROM ...
> GROUP BY ...
>
> Using the latest stable release of SQLAlchemy (1.3.20), I've written the
> following code:
>
> class Role(Base):
> __tablename__ = *'*role*'
**
*** id = Column(BigInteger, primary_key=True)
> _created = Column(DateTime, nullable=False,
> server_default=text(*'*CURRENT_TIMESTAMP*'*))
> _updated = Column(DateTime)
> _deleted = Column(Boolean, nullable=False, server_default=text(*"'*0*'"*))
> name = Column(String(200), nullable=False)
> description = Column(String(1000))
>
> result = Session.query()\
> .add_columns(func.group_concat(func.lower(Role.name).distinct()
> .op(*'*ORDER BY*'*)(func.lower(Role.name).asc())
> .op(*'*SEPARATOR*'*)(literal_column(*"'|'"*)))) \
> ...
>
> When executed, this emits an extra pair of parentheses in the statement which
> results in a SQL error:
>
> SELECT group_concat((DISTINCT lower(`role`.name) ORDER BY lower(`role`.name)
> ASC) SEPARATOR '|') AS group_concat_1
>
> How can I construct my SQLAlchemy query so it does not include the extra
> parantheses?
>
> Thanks in advance!
>
> --
> 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/f92b7a04-d5d6-46e6-8fad-0420f6f840b4n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/f92b7a04-d5d6-46e6-8fad-0420f6f840b4n%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/c1386a2a-3817-41d1-8b56-9c3a59584139%40www.fastmail.com.
