Parse SQL parameter marker with pandas using dynamic tables

Question

I have the following code:

query = """
    DECLARE @DATABASE VARCHAR(128) = '{}';
    DECLARE @SCHEMA VARCHAR(128) = '{}';
    DECLARE @TABLE VARCHAR(128) = '{}';
    DECLARE @sql VARCHAR(200) = 
    'SELECT * FROM ' + CONCAT(QUOTENAME(@DATABASE), '.', QUOTENAME(@SCHEMA), '.', QUOTENAME(@TABLE), ' WHERE COD = ?')
    EXEC sp_executesql @sql
    """.format(
    db_config.db.database, db_config.db.schema, db_config.db.table
)

return pd.read_sql_query(
    query, db_config.db.connection, params=[cod_sol]
)

It was working right until I added a sql param marker ' WHERE COD = ?'. It seems pandas or pyodbc can't parse that type of query, with a simple sql statement it works, but not with dynamic tables.

Here is the result of the final @sql variable:

SELECT * FROM [DB].[SCHEMA].[TABLE] WHERE COD = ?

So it seems to be right.

It's possible to do that sort of thing?

score 2 · Accepted Answer · answered Sep 05 '19 at 15:10

I have been able to solve it!

Instead of trying to execute directly the sql statement in one step, I first construct the sql query, and generate its final form:

SELECT * FROM [DB].[SCHEMA].[TABLE] WHERE COD = ?

Then, I call read_sql_query passing in the params

query = """
    DECLARE @DATABASE VARCHAR(128) = '{}';
    DECLARE @SCHEMA VARCHAR(128) = '{}';
    DECLARE @TABLE VARCHAR(128) = '{}';
    DECLARE @sql NVARCHAR(200) = 
    'SELECT * FROM ' + CONCAT(QUOTENAME(@DATABASE), '.', QUOTENAME(@SCHEMA), '.', QUOTENAME(@TABLE)) WHERE COD = ?;
    SELECT @sql
    """.format(
    db_config.db.database, db_config.db.schema, db_config.db.table
)
con = db_config.db.connection
query = con.execute(query).fetchval()

# Now this is working
pd.read_sql_query(query, con, params=[cod_xml]).reset_index(drop=True)

Parse SQL parameter marker with pandas using dynamic tables

1 Answers1