0

I am having trouble inserting text with single quotes into Postgres DB using psycogp2. This is the error I get whenever it encounters text with single quotes.

psycopg2.errors.UndefinedColumn: column "dept of health (department), in consul" does not exist

When I removed the single quotes in the text.. the record was inserted without any issue.

Here's my query:

query = "INSERT INTO %s VALUES %s ON CONFLICT (id) DO UPDATE SET %s;" % (name, tuple(row), ', '
                                                                                            ''.join(update_string))

I have tried the solutions mentioned in this link like replacing a single quote with a double-quote and escaping the single quote but still have the issue.

agastya teja
  • 629
  • 2
  • 9
  • 18
  • Can you give examples of `name`, `row` and `update_string` ? Please have a look at [Passing parameters to SQL queries](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries), which should solve all your issues. – Maurice Meyer Jun 28 '21 at 13:44
  • yes, name = "table_name", row = ['val1','val2'...'val50'], update_string basically added excludes statement for each row item. – agastya teja Jun 28 '21 at 14:02
  • One of the values in the row is basically the text which has single quotes.. – agastya teja Jun 28 '21 at 14:04
  • You are mixing identifiers - table & column names - and values. Identifiers have to be inserted using string formatting, but values should be inserted using parameter substitution. I wrote [this answer](https://stackoverflow.com/a/68153087/5320906) yesterday which goes into a little more detail. It's about sqlite rather than postgres, but the principles are the same. – snakecharmerb Jun 28 '21 at 15:06
  • Take a look at [Dynamic SQL](https://www.psycopg.org/docs/sql.html). In any case for text value with enclosed single quotes use double quotes to enclose the string e.g. "This text has 'single quotes'" – Adrian Klaver Jun 28 '21 at 17:48
  • @AdrianKlaver I finally used the Dynamic SQL approach and was able to make it work. – agastya teja Jun 29 '21 at 12:40

0 Answers0