Binding variables to SQLAlchemy query for Pandas.read_sql

Question

Is it possible to bind variables to a SQLAlchemy query used in a Pandas.read_sql statement?

Using %s in the WHERE clause does not work and the documentation for cx_Oracle states:

cursor.execute('SELECT * FROM employees WHERE department_id=:dept_id)

Using the cx_Oracle driver directly is deprecated in Pandas and is not a viable option.

I have a list of groups I need to iterate through the WHERE statement as SELECT * is too large in memory to handle on a single PC.

EXAMPLE:

SELECT * FROM DUAL WHERE GROUP_NAME = %s

Returns this error:

(cx_Oracle.DatabaseError) ORA-00911: invalid character ... WHERE GROUP_NAME = %s

nosklo · Accepted Answer · 2019-11-21 16:02:35Z

12

As you can see here, cx_Oracle.paramstyle is named not format. According to PEP 249 you have to use the :name syntax for named paramstyle:

import pandas as pd
sql = '''
 SELECT *
 FROM DUAL
 WHERE GROUP_NAME = :name
'''
df = pd.read_sql(sql, params={'name': the_name_you_want})

edited Nov 21, 2019 at 16:02

answered Aug 10, 2018 at 16:26

nosklo

224k58 gold badges300 silver badges299 bronze badges

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

1 Comment

BossRoyce Over a year ago

what is the syntax for passing in a list of values for the parameter, instead of just one?

Collectives™ on Stack Overflow

Binding variables to SQLAlchemy query for Pandas.read_sql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related