How to use pandas read_sql with string formating

Question

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?

user459872 · Accepted Answer · 2022-10-17 09:22:32Z

1

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

edited Oct 17, 2022 at 9:22

answered Oct 17, 2022 at 9:17

user459872

26k4 gold badges51 silver badges74 bronze badges

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

1 Comment

victorfink Over a year ago

Thank you, can't believe I missed it! I'll accept the answer once I can

Collectives™ on Stack Overflow

How to use pandas read_sql with string formating

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related