1

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()
ColossalBoar
  • 101
  • 1
  • 2
  • 13

2 Answers2

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%',})
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
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.

marcinowski
  • 349
  • 2
  • 4
  • 3
    Don't do this with untrusted input. That is how SQL injection happens. Instead use parameter substitution: http://stackoverflow.com/a/24418294/3901060 – FamousJameous Apr 13 '17 at 21:54