Iterating over 2 lists in SQL execution using SQL Alchemy-CodePudding

I currently have this query:

        for table_name in bmds_stage_tables:
            get_updated_tables = conn.execute(
                f"""
                    DECLARE @csId INT = ?
                    SELECT * FROM bmds_stage.{table_name}
                    WHERE ChangeSetId = @csId
                """,
                change_set_ids
            ).fetchall()

That I changed to use bindparams:

query_updated_tables = text(
    """
    SELECT * FROM bmds_stage.:table_name
    WHERE ChangeSetId in :csId
    """
).bindparams(
    bindparam("table_name"),
    bindparam("csId", expanding=True),
)

So before, I was able to make the query per table in the bmds_stage_table list and change_set_ids was just 1 int

But now change_set_ids is a list of ints.

So now, for each change_set_id I want to iterate through all the tables in the bmds_stage_table list

With the change I had to adjust how I execute the query:

        for table_name in bmds_stage_tables:
            get_updated_tables = conn.execute(query_updated_tables, {"table_name":table_name, "csId":change_set_id}).fetchall()

But I get this error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)")
[SQL: 
    SELECT * FROM bmds_stage.?
    WHERE ChangeSetId in (?)
    ]
[parameters: ('PM_Category', 1045)]

I would greatly appreciate any help!

CodePudding user response：

Consider formatting the table name to raw SQL since identifiers cannot be parameterized and bind_params for literal values:

for table_name in bmds_stage_tables:
    query_updated_tables = text( 
        f"""SELECT * FROM bmds_stage.{table_name} 
           WHERE ChangeSetId in :csId 
        """ 
    ).bindparams( 
        bindparam("csId", expanding=True)
    )

    params = {"csId": change_set_id}
    get_updated_tables = (
        conn.execute(
            query_updated_tables, params
        ).fetchall() 
    )

Possibly, too, consider the functional form of sqlalchemy:

from sqlalchemy.schema import Table
from sqlachemy import select, column, bindparam

for table_name in bmds_stage_tables:
   query_updated_tables = (
        select(
            Table(
                table_name,
                schema = "bmds_stage"
            )
        ).where(
            column("ChangeSetId").in_(
                bindparam("csId", expand=True)
            )
        )
    )

    params = {"csId": change_set_id}
    get_updated_tables = (
        conn.execute(
            query_updated_tables, params
        ).fetchall() 
    )