SQLite syntax error, but SQL string works manually (python)

Question

My programme raises an sqlite3.OperationalError exception with the following message:

: near ",": syntax error

I have a try-except block that prints me the SQL string.:

try:
    self._db_cur.execute(sql)
except Exception, e:
    print sql
    raise e

This is the printed SQL... the most interesting part is... that if I insert that query in my SQLite manager... it DOES work. I'm clueless here.

INSERT INTO connections_to_jjos(
    connections_id,
    jjo_error_id,
    binder_task_id
)
VALUES

(
    55,
    (select id from jjo_errors where name = "--Unknown--"),
    (select id from tasks where name = "DCDD")
)
, 

(
    55,
    (select id from jjo_errors where name = "--Unknown--"),
    (select id from tasks where name = "ANDD")
)

For some reasons, I am not using parameterized statements, if it helps...

Uhm, if I don't read it wrong: VALUES -> ( -> expr -> ) -> , -> ( -> exp -> ) -> , ... BUT I just googled multiples values in SQLite, and apparently it is not a valid statement. There are workarounds, though. — bgusach
– bgusach, Commented Jul 6, 2013 at 14:16
If you want to insert multiple data sets, use prepared statements (sql parameters), a sequence of input values (sequence of tuples or dictionaries, depending on the style of sql params used) and use .executemany() instead. — Martijn Pieters
– Martijn Pieters, Commented Jul 6, 2013 at 14:20

mata · Accepted Answer · 2013-07-06 16:06:33Z

3

This is probably a bug* in sqlite3.

While this test code works fine with python3.3 and sqlite 3.7.15.2, it fails with sqlite 3.7.3.

That means it should be fixed in newer versions, so you'd need to update your sqlite version (or python) to make it work, or work around this by not using multiple values sets.

*edit: actually not a bug, just a feature that was only introduced in version 3.7.11

edited Jul 6, 2013 at 16:06

answered Jul 6, 2013 at 14:47

mata

69.3k10 gold badges168 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

SQLite syntax error, but SQL string works manually (python)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related