OperationalError: near "in": syntax error

Question

Getting Error

"OperationalError: near "in": syntax error" in line 7: conn.execute('INSERT INTO Good_Raw_Data VALUES ({values})'.format(values=(list_)))

conn.execute('INSERT INTO Good_Raw_Data VALUES ({values})'.format(values=(list_)))

with open("adult.csv", "r") as f:
    next(f)
    reader = csv.reader(f, delimiter="\n")
    for line in enumerate(reader):
        for list_ in (line[1]):
            try:
                conn.execute('INSERT INTO Good_Raw_Data VALUES ({values})'.format(values=(list_)))
                                
                conn.commit()
            except Exception as e:
                raise(e)

What to do?

Why do you use an underscore suffix for `list_` ? That's a weird variable name! — Marcello Romani, Sep 30 '21 at 19:01
Could you post a sample line from `adult.csv` ? I suspect you need to format `{values}` in SQL-compatible way — Marcello Romani, Sep 30 '21 at 19:02
First of all, I hope that data is anonymised :-D Also, there you go: that string gets passed unaltered into the SQL query (very dangerous practice!) without any quoting around strings. I suggest you split `conn.execute('INSERT ...'.format(...))` in two steps: 1) build the string (and then print it out) 2) execute the string — Marcello Romani, Sep 30 '21 at 19:09
See https://stackoverflow.com/a/589416/5320906 (the parameter substitution character is `%s` for most database connectors, `?` for sqlite; check the connector's `paramstyle` attribute). Using parameter substitution will protect you from quoting errors and SQL injection. Using string formatting will not. — snakecharmerb, Sep 30 '21 at 19:35
I would guess that the problem is the particular value of `values`, which you did not show us... — John Gordon, Dec 20 '22 at 01:14

score 0 · Answer 1 · answered Dec 20 '22 at 01:04

The problem is most probably due to single quotes in your data that you want to insert. So you should remove or replace single quotes for example by double quotes or escape them by backslash "".

Link to similar question - How do I convert a string into safe SQL String?

OperationalError: near "in": syntax error

1 Answers1