I have a SQLite database with a single table called min that has a single column (TEXT) called name and I want to use the following SQLite command to insert values.
INSERT OR IGNORE INTO min VALUES ('{}');
The problem is that when the name has a single quote, I get the error in the question's title.
Here's a minimal example using pandas to visualize the table.
import sqlite3
import pandas as pd
db = "question.db"
#helper functions
def run_query(q):
with sqlite3.connect(db) as conn:
return pd.read_sql(q, conn)
def run_command(c):
with sqlite3.connect(db) as conn:
conn.isolation_level = None
conn.execute(c)
create_table =\
"""
CREATE TABLE IF NOT EXISTS min(
name TEXT
);
"""
run_command(create_table)
display(run_query("SELECT * FROM min;"))
insert_command = """\
INSERT OR IGNORE INTO min
VALUES ('{}');
"""
run_command(insert_command.format("Jules"))
display(run_query("SELECT * FROM min;"))
print(insert_command.format("O'Brien")) #visualize the command string
Prints
INSERT OR IGNORE INTO min
VALUES ('O'Brien');
And running the command itself
run_command(insert_command.format("O'Brien"))
Yields:
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
<ipython-input-8-e374715cfbec> in <module>()
1 print(insert_command.format("O'Brien"))
----> 2 run_command(insert_command.format("O'Brien"))
<ipython-input-3-0c8eb910c0ac> in run_command(c)
7 with sqlite3.connect(db) as conn:
8 conn.isolation_level = None
----> 9 conn.execute(c)
OperationalError: near "Brien": syntax error
Escaping the single quote does not help:
print(insert_command.format("O\\'Brien"))
Prints
INSERT OR IGNORE INTO min
VALUES ('O\'Brien');
And the same error happens with
run_command(insert_command.format("O\\'Brien"))
How can I escape this quote or otherwise solve this problem? I do not wish to replace the single quotes with doubles quotes as that would only shift the problem.
