Tuesday, 28 November 2023

SqlAlchemy extending an existing query with additional select columns from raw sql

I'm quite new to SQLAlchemy and Python, and have to fix some bugs in a legacy environment so please bear with me..

Environment:
Python 2.7.18
Bottle: 0.12.7
SQLAlchemy: honestly don't know but something from 2014 (might be 0.9.8?)
MySQL: 5.7

Scenario: I have this SQL Statement below that pivots a linked table and adds rows dynamically as additional columns in the original table - I constructed it in SQL Workbench and it returns the results I want: All specified columns from table t1 plus the additional columns with values from table t2 appear in the result

SET SESSION group_concat_max_len = 1000000; --this is needed to prevent the group_concat function to cut off after 1024 characters, there are quite a few columns involved that easily exceed the original limit
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT(
  'SUM(
  CASE WHEN custom_fields.FIELD_NAME = "', custom_fields.FIELD_NAME, '" THEN
custom_fields_tags_link.field_value END) 
  AS "', custom_fields.FIELD_NAME, '"')
) AS t0
INTO @sql
FROM tags t1 LEFT OUTER JOIN custom_fields_tags_link t2 ON t1.id = t2.tag_id JOIN
custom_fields ON custom_fields.id = t2.custom_field_id;

SET @sql = CONCAT('SELECT tags.id AS tags_id,
tags.tag_type AS tags_tag_type, tags.name AS
tags_name, tags.version AS tags_version, ', @sql,
' FROM tags LEFT OUTER JOIN custom_fields_tags_link ON tags.id = 
custom_fields_tags_link.tag_id JOIN custom_fields ON custom_fields.id = 
custom_fields_tags_link.custom_field_id GROUP BY tags.id');
SELECT @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Problem: I have an already existing SQLAlchemy session, that expands a query to be used for pagination. Currently this query returns all my specified columns from table t1, that are joined with tables t2 and custom_fields to get all necessary columns. The missing part is the SQLAlchemy representation for the SELECT GROUP_CONCAT part of the above statement, the rest is all taken care of - since I have control and know how the Frontend presenation of this table looks and now also the raw SQL version in SQL Workbench, I tried to work backwards to get the SQLAlchemy / Python part right by consulting https://docs.sqlalchemy.org/en/14/orm/queryguide.html#orm-queryguide-selecting-text and https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.TextClause.columns, but now I am stuck at how to get this TextClause object converted into a TextualSelect without typing the columns statically in the .column() function cause I don't know what the column names, that the users provide for these custom_fields, will be.

Goal: concat a dynamically created raw SQL statement to my existing SQLAlchemy query to select these dynamically created fields from a linked table so that I have the same result as when I execute this raw SQL statement in a SQL editor

Attempts:

#session is a app-wide shared MySQL session object that is created via sqlalchemy.orm's sessionmaker, scoped_session function
alchemy = session.query(Tag)
try:
  #mainly the next line will be changed (x)
  select_custom_fields_columns_stmt = select().from_statement(text(
  '''SET SESSION group_concat_max_len = 1000000;
    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(
      CASE WHEN custom_fields.FIELD_NAME = "', custom_fields.FIELD_NAME, '" THEN
      custom_fields_tags_link.field_value END) AS "', custom_fields.FIELD_NAME, '"')) 
    AS t0
      INTO @sql
      FROM tags t1 LEFT OUTER JOIN custom_fields_tags_link t2 ON t1.id = t2.tag_id JOIN custom_fields ON custom_fields.id = t2.custom_field_id;''')) #or here after the statement (y)
  # next line is my attempt to add the columns that have been generated by the previous function but of course unsuccessful
  alchemy = alchemy.add_columns(select_custom_fields_columns_stmt)
except:
  logException()
joined_query = alchemy.outerjoin(model.tag.CustomFieldTagLink)
.join(model.tag.CustomField)

A: This results in this error: AttributeError: 'Select' object has no attribute 'from_statement'

B: Changing the line (x) above that constructs the query for the additional rows to select_custom_fields_columns_stmt = session.select().from_statement(text(... --> results in: AttributeError: 'Session' object has no attribute 'select'

C: adding a .subquery("from_custom_fields") statement at (y) --> results in: AttributeError: 'AnnotatedTextClause' object has no attribute 'alias'

D: other attempts for (x) substituting select() with session.query() or session.query(Tags) also didn't result in additional columns

What else can I try? Would it be preferable/easier to write the whole raw SQL part in SQLAlchemy and if so, how could I do that?



from SqlAlchemy extending an existing query with additional select columns from raw sql

No comments:

Post a Comment