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
– JavaSa, Commented Dec 15, 2018 at 14:08
@IljaEverilä: I alraedy saw this. Any idea, why I still get error even after trying coldspeed's suggested answer? — JavaSa
– JavaSa, Commented Dec 15, 2018 at 16:56

cs95 · Accepted Answer · 2018-12-15 18:07:57Z

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, 2018 at 18:07

answered Dec 15, 2018 at 14:11

cs95

406k106 gold badges744 silver badges795 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JavaSa Over a year ago

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") `

Ilja Everilä Over a year ago

The colon-style named parameter is something that not many DB-API drivers support (though it is part of the spec), 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().

cs95 Over a year ago

@IljaEverilä Want to take this one? I'll be happy to delete.

Ilja Everilä Over a year ago

Nah, just amend with text(query) and that's that :)

Collectives™ on Stack Overflow

Usage error in pandas.read_sql with sqlalchemy

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related