I’m trying to read a table into a dataframe using the pandas.io.sql read_sql method. However, I’m getting errors as I need to format my query with a tuple, and some elements of my tuples contain single quotes.
Here is an example of a tuple:
tuple = ('dog', 'cat', "wendy's")
And my query:
query = """SELECT * FROM my_table WHERE my_var IN %s"""
I know how to format it to use the cursor.execute command, like this:
cursor.execute(query,(tup,))
But I didn’t manage to use the read_sql command.
For example
psql.read_sql(query,(tup,), connection)
throws an error ('tuple' object has no attribute 'cursor').
I also tried using .format(tup) but it creates an error near the tuple’s element that has a single quote
How can I use read_sql with the query I mentioned?
>Solution :
The read_sql function has a params parameter which you can use to pass the parameters to underlaying execute method . ie,
psql.read_sql(query, connection, params=(tup,))
^^^^^^^^^^^^^