Python sqlite3 executescript() error with multiple inserts

Question

I'm in the process of transferring my brain from PHP + MySQL to Python and at the moment I'm using sqlite as an intermediary to something else later because it's built in.

When I try to set up an initial database to work with, it throws an error on the INSERT statement that INSERTs multiple rows:

import sqlite3

query = """CREATE TABLE IF NOT EXISTS "users" (
             "userID" INTEGER PRIMARY KEY autoincrement,
             "email" varchar(255) NOT NULL,
             "forename" varchar(50) NOT NULL,
             "surname" varchar(50) NOT NULL
           );
           INSERT INTO "users" ("userID", "email", "forename", "surname") 
           VALUES
             (1, 'user1@example.com', 'Test', 'User'),
             (2, 'user2@example.com', 'Test', 'User');"""

 db_conn = sqlite3.connect('test.db')
 try:
     db_conn.cursor().executescript(query)
     db_conn.commit()
 except:
     print "error with database"
     raise
 finally:
     db_conn.close()

When I run it, I get:

error with database
Traceback (most recent call last):
  File "dbTest.py", line 20, in <module>
    db_conn.cursor().executescript(query)
sqlite2.OperationalError: near ",": syntax error

It took me quite a while to find exactly which "," it didn't like (I mean, really? SQL errors suck!). It is indeed the "," separating INSERT rows.

What stupid thing did I do?

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

For your insert I would use .executemany() instead of execute script, if you can. Otherwise if you can't, your INSERT statement will need to be seperate for each VALUES set you wish to pass it.

query = """INSERT INTO "users" ("userID", "email", "forename", "surname") 
           VALUES
           (?, ?, ?, ?)
        """
user_list = ((1, 'user1@example.com', 'Test', 'User'), (2, 'user2@example.com', 'Test', 'User'))
cursor.executemany("INSERT INTO myTable(data) values (?) ", user_list)

EDIT: According to this post, you can in fact have multiple value sets in a single INSERT statement, but the syntax is far from pretty.

edited May 23 '17 at 12:27

Community

1
1

answered May 17 '12 at 10:29

Christian Witts

11,375
1
33
46

ok, that's a bit of a pain - obviously not a problem once everything is set up, but I wanted to transport the database from a mysql dump to get it up and running – MalphasWats May 17 '12 at 10:57
I found a post similar to yours, which is linked in the `EDIT` section of my post. It does appear to be possible to have a multi-row insert, but the syntax doesn't look pretty. – Christian Witts May 17 '12 at 11:05
Ah, so it's a feature that's not actually supported by sqlite - according to that post it was added in version 3.7.11, which is why this page http://www.sqlite.org/lang_insert.html says you can have them, which is why I used it! I must have an older version - I'm just using whatever I got in my Python install! I'd rather type the data in by hand than use `UNION SELECT` like that! Thank you – MalphasWats May 17 '12 at 12:46

Python sqlite3 executescript() error with multiple inserts

1 Answers1