3

I need to look up for a string that contains multiple single quotes from my psql table. My current solution is replacing the single quotes with double single quotes like this:

sql_query = f"""SELECT exists (SELECT 1 FROM {table_name} WHERE my_column = '{my_string.replace("'","''")}' LIMIT 1);"""
cursor = connection.cursor()
cursor.execute(sql_query)

Is there a nicer solution for this kind of formatting ?

foxel
  • 165
  • 10
  • I also tried $$my_string$$ but this disturbs the cases where I have a dollar sign in my string – foxel Jan 07 '22 at 12:49
  • https://www.postgresql.org/docs/9.1/functions-matching.html#:~:text=9.7.3.%20posix%20regular%20expressions – gold_cy Jan 07 '22 at 13:06
  • 1
    I think you need a [Postgres escape sequence](https://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql). – Alex W Jan 07 '22 at 13:11

1 Answers1

1

I solved this by making the query like this

sql_query = f"""SELECT exists (SELECT 1 FROM {table_name} WHERE my_column = %s LIMIT 1);"""
cursor = connection.cursor()
cursor.execute(sql_query, (mystring,))

In this case it formats mystring in such way that it is not executed as sql query and doesn't cause the problems mentioned in the question

foxel
  • 165
  • 10