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 int
s.
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!
Advertisement
Answer
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() )