16523

How do I write a group_concat function in sqlalchemy?

Question:

I am trying to re-write this mysql query in SQLAlchemy:

Schema:

CREATE TABLE `posts` ( `post_id` INT UNSIGNED PRIMARY AUTO_INCREMENT, `post_name` VARCHAR(255) ) Engine=InnoDB; CREATE TABLE `post_tags` ( `tag_id` INT UNSIGNED PRIMARY AUTO_INCREMENT, `tag_name` VARCHAR(255) ) Engine=InnoDB; CREATE TABLE `post_tags_map` ( `map_id` INT PRIMARY AUTO_INCREMENT, `post_id` INT NOT NULL, `tags_id` INT NOT NULL, FOREIGN KEY `post_id` REFERENCES `posts` (`post_id`), FOREIGN KEY `post_id` REFERENCES `post_tags` (`tag_id`) ) Engine=InnoDB;

Query:

SELECT posts.*, GROUP_CONCAT( post_tags.tag_name order by post_tags.tag_name ) AS tags FROM posts LEFT JOIN posts_tags_map ON posts_tags_map.post_id = posts.post_id LEFT JOIN post_tags ON posts_tags_map.tags_id = posts_tags.tag_id WHERE posts.post_id = 1 GROUP BY post_id

Here's what I have, but I keep getting:

1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR /)) rows = DBSession.query(Posts,func.group_concat(Post_Tags.tag_name.op('SEPARATOR')(literal_column('/')))).outerjoin(PostsTagsMap,Posts.post_id==PostsTagsMap.post_id).outerjoin(Post_Tags,PostsTagsMap.tags_id==Post_Tags.tag_id).group_by(Posts.post_id)

Answer1:

Defining a custom function element seems the easiest way to take care of the group_concat method.

from sqlalchemy.sql import expression import sqlalchemy from sqlalchemy.ext import compiler class group_concat(expression.FunctionElement): name = "group_concat" @compiler.compiles(group_concat, 'mysql') def _group_concat_mysql(element, compiler, **kw): if len(element.clauses) == 2: separator = compiler.process(element.clauses.clauses[1]) else: separator = ',' return 'GROUP_CONCAT(%s SEPARATOR %s)'.format( compiler.process(element.clauses.clauses[0]), separator, )

And use it something like this:

query = select([ table.c.some_column, expression.label( 'grouped column', group_concat( table.c.some_oter_column, ' separator ', ), ), ]).group_by(table.c.some_column)

Answer2:

Actually, you did the group_concat bit right:

DBSession.query( Posts, func.group_concat(Post_Tags.tag_name.op('SEPARATOR')(literal_column('/'))))<...>

I am not sure what exactly is going on, as it seems your schema is not valid, your query has misspelled table names, and judging by the error, you tried to feed python code into mysql.

For those who, like me, found this page while googling, the code bit above should be helpful, also there is the source, apparently: <a href="https://groups.google.com/forum/#!msg/sqlalchemy/eNbOoRJ425s/bScfQVat15EJ" rel="nofollow">https://groups.google.com/forum/#!msg/sqlalchemy/eNbOoRJ425s/bScfQVat15EJ</a>

Answer3:

Wasted a lot of time testing the above examples and other random sources to find out how to change the standard delimiting character "," in the func.group_concat() ... only to then follow intuition and find out that the syntax works exactly as you would anticipate: pass your delimiting character as an argument. Such as:

from sqlalchemy import func #write the query: db.session.query(... ...func.group_concat(Table.column_name, "; ")

So, modifying a previous answer's example:

DBSession.query( Posts, func.group_concat(Post_Tags.tag_name, "/"))<...>

Note: this is with Python 3.5, SQLite 3, Flask-SQLAlchemy 2.3.2 (SQLAlchemy 1.0.13 installed)

Answer4:

I finally get this working.

>>> str(func.group_concat(Keyword.text.op('separator')(text('","')))) 'group_concat(keywords.text separator ",")'

Recommend

  • SQLalchemy/wtforms update issue - 400 bad request
  • Session Management in Castle Active Record
  • How to deal with m2m relationship(posts and tags) by doctrine(in symfony)?
  • wp_get_recent_posts stopped working
  • How to pass a PHP variable from one function to another?
  • mysql how to find the total number of child rows with respect to a parent
  • MySQL stored procedure doesn't work, error says “Truncated incorrect DOUBLE value”, error occur
  • Select only columns with null/empty values from the table
  • Return rows where column matches all values in a set
  • MySQL - SELECTING COLUMNS ONLY IF THEY HAVE VALUES
  • How to determine the length of a string (without using strlen())
  • How can I use regex to put quotes around a statement
  • compose Try nicer
  • Unable to use Environment.GetResourceString static method
  • Sprox form with Turbogears, using Mako, only displays plain text
  • Is there a way of avoiding so many list(chain(*list_of_list))?
  • Creating a grid of images with jQuery
  • How can Haskell read S-expressions from files?
  • BASH - Split file into several files based on conditions
  • MS Access 2010 SQL Top N query by group performance issue (continued)
  • Implicit conversion confusion between signed and unsigned when reading K&R book
  • Displaying search term (Tag/Archive) in WordPress
  • Gnuplot histogram 3d
  • Splitting string into groups of specific length
  • Plot ROC curve and calculate AUC in R at specific cutoff info
  • constexpr vs const vs constexpr const
  • Prevent Emacs from modifying the OS X clipboard?
  • CFNetwork SSLHandshake failed (-9806) & (-9800) & (-9830)
  • Convert Func to Func
  • Dispose not working as expected in DirectorySearcher
  • Fluid video height
  • Weird LEFT OUTER JOIN on Includes eager loading of rails 3
  • How to sort a same column both in asc order and desc order
  • Custom Nav Title offset ios 11
  • mave 3.2 not able to access local nexus instance return 502 code
  • Find group of records that match multiple values
  • Webgrid not refreshing after delete MVC
  • Functions in global context
  • Update CALayer sublayers immediately
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass