Insert Into SQLite DB from Python - "near "?": syntax error"

Question

I keep receiving this error, while trying to insert data from Pandas df to SQLite DB: near "?": syntax error. The code snippet looks like this, and in another script, similar idea works fine. My df has 4 columns (int & 3x string), same column names and types are in SQLite table called titles.

conn = sqlite3.connect('test_db_2.db')
c = conn.cursor()

for i in range(len(df)):
    try:
        c.execute("""INSERT INTO titles (?,?,?,?)""",df.iloc[i,:])
        conn.commit()

What could be the cause?

GMB · Accepted Answer · 2020-06-18 10:54:35Z

3

You are missing the VALUES() keyword, which must precede the tuples of values that you want to insert:

INSERT INTO titles VALUES (?, ?, ?, ?)
                 --^--

I would also recommend enumerating the columns that you want to insert. It is a good practice in SQL since it makes the query easier to understand to those that do not know your data structure, and can allow you to not provide values for all columns:

INSERT INTO titles(col1, col2, col3, col4) VALUES (?, ?, ?, ?)
                --^ -- changae this to your actual column names

answered Jun 18, 2020 at 10:54

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

The column names comment is definitely true, and actually, even after using your answer and adding VALUES, the OP might still have a problem if the width of the data frame does not match the number of columns.

Collectives™ on Stack Overflow

Insert Into SQLite DB from Python - "near "?": syntax error"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related