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?

CodePudding user response：

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,))
                                 ^^^^^^^^^^^^^