[Tutor] pandas read sql query advice

[Albert-Jan Roskam]

On Apr 28, 2018 06:54, Glenn Schultz <glennmschultz at me.com> wrote:
>
> All,
>
> I have the following set-up (below) which will be used to call data from multiple sectors.  There is a sql query (transact sql) and connection.  This works fine.  However, I would like to parametrize the query so I can enter different sectors.  I have checked through pyodbc and several SO posts.  However, I am stuck and cannot get the parametrized version to work.  Most examples I have found are either sqlite or postgres.
>
> sql ="""select foo from mutable where model sector = 'mysector'"""
> conn = myconn
>
> def modeldata(modelquery, conn)
> modeldata = pandas.read_sql_query(sql, conn)
> return modeldata
>
> Here is what I have tried (various combinations) - what am I doing wrong?
>
> sql ="""select foo from mutable where model sector = ?""", [params]
> conn = myconn
>
> def modeldata(modelquery, conn, params)
> modeldata = pandas.read_sql_query(sql, conn, params)
> return modeldata

pandas.read_sql_query(sql, conn, params=params)

The third positional arg is actually index_col, so you need to use a keyword arg. https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html#pandas.read_sql_query

BTW, I think you can use a context manager to automatically close the connection:
with pyodbc.connect(your_conn_string) as conn:
    df = pd.read_sql_query(sql, conn, params=params)