PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using psycopg2 placeholders

I have the following code:

query = "SELECT * FROM appointments WHERE service IN %s AND location LIKE '%London%'"
values = (services,) # services is a tuple of strings.

cur.execute(query, values)

When I execute this code I get the following error:

--> cur.execute(query, values)
IndexError: tuple index out of range

After researching the topic a little bit, I think that the %L in the query is being detected as a placeholder, so the psycopg2 library is trying to insert data there, but there is no more data in the values tuple.

I have tried escaping the % sign using the following query:

query = "SELECT * FROM appointments WHERE service IN %s AND location LIKE '%%London%'"

but this has also not worked.

How can I fix this issue and get the expected behaviour?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    You can safely format your query using the pscyopg2.sql library. The below code adds a placeholder value for each element within the services tuple into the query string, then you can execute as you normally would.

    from psycopg2 import sql

query = sql.SQL("SELECT * FROM appointments WHERE service IN ({}) AND location LIKE '%%London%%'")
formatted_query = query.format(sql.SQL(',').join(sql.Placeholder() * len(services)))

cur.execute(formatted_query, services)

  2. 0

    from the docs, a better and easier approach for such use cases would be to use POSIX regex over LIKE or SIMILAR TO operators,

    query = "SELECT * FROM appointments WHERE service IN %s AND location ~ '.*London.*'"

    im not sure about its compatibility with different psycopg as well as posgresql versions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search