Usage error in pandas.read_sql with sqlalchemy

Question

Consider you have the following code:

import pandas as pd
pd.read_sql("SELECT * FROM foo_schema.run_info WHERE id=:param order by run_id desc", s.bind, **{'param':5})

Whereas s.bind is sqlchemy's engine object.

I get the following error:

{TypeError}read_sql() got an unexpected argument 'param'

what is wrong with my function call?

Just simple example to parameter passing to the query, we have placeholder there `WHERE id=:param`, so this need to be parsed to `WHERE id=5` in this case — JavaSa, Dec 15 '18 at 14:08
Related: https://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters — Ilja Everilä, Dec 15 '18 at 14:53
@IljaEverilä: I alraedy saw this. Any idea, why I still get error even after trying `coldspeed`'s suggested answer? — JavaSa, Dec 15 '18 at 16:56

cs95 · Accepted Answer · 2018-12-15T18:07:57.147

2

From the docs,

pd.read_sql?
...
params : list, tuple or dict, optional, default: None

What you're doing is unpacking the dictionary, so it is passed as a keyword argument. See Understanding kwargs in Python for more information.

The correct way to pass parameters is:

pd.read_sql(... params={'param':5})

As @Ilja Everilä expertly mentioned, you should wrap your query inside a call to text in order to have the parameter syntax correctly parsed.

from sqlalchemy import text
pd.read_sql(text(query), s.bind, params={'param':5})

edited Dec 15 '18 at 18:07

answered Dec 15 '18 at 14:11

cs95

I did it but got: `sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version `for the right syntax to use near ':param order by run_id desc' at line 1") ` – JavaSa Dec 15 '18 at 15:40
1

The colon-style named parameter is something that not many DB-API drivers support (though it is [part of the spec](https://www.python.org/dev/peps/pep-0249/#paramstyle)), but if you've had a look at SQLA docs you might've picked it up. It's used by the SQLA `text()` construct to provide DB-API agnostic named parameter handling. So the fix is to wrap `query` in a call to `text()` before passing to `read_sql()`. – Ilja Everilä Dec 15 '18 at 18:04
@IljaEverilä Want to take this one? I'll be happy to delete. – cs95 Dec 15 '18 at 18:04
1

Nah, just amend with `text(query)` and that's that :) – Ilja Everilä Dec 15 '18 at 18:05

1 Answers1