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, jjbrei...@gmail.com 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 sqlalchemy+unsubscr...@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c1386a2a-3817-41d1-8b56-9c3a59584139%40www.fastmail.com.