python - 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)

Answer 1

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)