Escaping single quote in questDB using Python3

I'm trying to insert a row with a string containing a single quote symbol into a QuestDB table. I tried using the following code below as well as the following SQL query using the console. Both gave me the same error: "')' expected".

INSERT INTO 'cpe_list' (mystring) VALUES ('o''brian');

import psycopg2

connection = psycopg2.connect(user='someuser', password='somepw', host='127.0.0.1', port=8812, database='mydb')
cursor = connection.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS mytable (mystring STRING)")
sql_query = "INSERT INTO 'mytable' (mystring) VALUES (%s);"

cursor.execute(sql_query, ("o'brian",))



Traceback (most recent call last):
  File "testSql.py", line 9, in <module>
    cursor.execute(sql_query, ("o'brian",))
psycopg2.DatabaseError: ')' expected
LINE 1: INSERT INTO 'mytable' (mystring) VALUES ('o''brian');

The above code is based on psycopg2 2.9.3 documentation on query parameters: https://www.psycopg.org/docs/usage.html?#the-problem-with-the-query-parameters

Solution

I verified it and this seems to be a bug. I created an issue on Github to track it

In the meantime, you could always use the ILP protocol (Python example available at the QuestDB docs)

There is also an upcoming QuestDB official Python client that should make ILP ingestion much more convenient, but still to be released.