How to use pd.read_sql with params

Question

I am having trouble with parameterized queries with SQL server when trying to use pd.read_sql.

If I run

query = 'SELECT * FROM positions'
pd.read_sql(query,engine)

Then I get a good result:

        position_id                  position_name
    0             0             ACCOUNTING MANAGER
    1             1                     HR MANAGER
    ...
    ...
    ...

But if I run

query = 'SELECT ?,? FROM position_names'
params = ['position_id','position_name']
pd.read_sql(query, engine, params=params)

Then I get:

0   position_id  position_name
1   position_id  position_name
2   position_id  position_name
3   position_id  position_name
...
...

What am I doing wrong? Thanks for your help!

Answer 1

I believe that instead of reading the column names in as a param, you need to read them in as a columns.

Docs: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html

Parameters:

columns : list, default: None

List of column names to select from SQL table (only used when reading a table).

Answer 2

Change the query to the table name and pass list of columns to extract:

    query = 'position_names'
    columns = ['position_id','position_name']
    pd.read_sql(query, engine, columns=columns)