Error Passing Variable to SQL Query Python

Question

Error message 'Execution failed on sql ... expecting string, unicode or buffer object' is returned when trying to pass a Python string variable to a SQL query using cx_Oracle. Any help on this issue is greatly appreciated, thanks!

import pandas as pd
import cx_Oracle as ora

var = 'string'

conn = ora.connect('connection_string')
df = pd.read_sql(("SELECT * FROM table WHERE field LIKE '%s'", (var)), conn)
df.head()

mechanical_meat · Accepted Answer · 2017-04-13 22:56:00Z

3

To avoid the chance of SQL-injection attack you should pass the variable in the params keyword argument:

df = pdsql.read_sql("""SELECT * 
                       FROM table 
                       WHERE field LIKE %(var)s""", conn, params={'var':'string%',})

answered Apr 13, 2017 at 22:56

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

Comments

marcinowski · Accepted Answer · 2017-04-13 21:40:17Z

1

pd.read_sql("SELECT * FROM table WHERE field LIKE '{}'".format(var), conn)

This should do it. You were trying to pass a tuple to a function instead of string/unicode object.

answered Apr 13, 2017 at 21:40

marcinowski

3593 silver badges4 bronze badges

1 Comment

FamousJameous Over a year ago

Don't do this with untrusted input. That is how SQL injection happens. Instead use parameter substitution: stackoverflow.com/a/24418294/3901060

Collectives™ on Stack Overflow

Error Passing Variable to SQL Query Python

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related