SQLite Multiple Insert with Query Syntax Error

Question

I'm attempting to insert multiple rows into an SQLite DB with python. I've got three tables:

UniversityA:

id
course_name
course_code
course_prefix

UniversityB:

id
course_name
course_code
course_prefix

CourseMap:

universityA_id (references id from universityA)
universityB_id (references id from universityB)
is_flagged (boolean 0/1)

I can add a single record to the CourseMap table easily with this syntax:

cur.execute('INSERT INTO CourseMap (universityA_id, universityB_id, is_flagged) VALUES ( (SELECT id from universityA WHERE course_code=301 AND course_prefix="AERO"), (SELECT id from universityB WHERE course_code=101 AND course_prefix="ARCH"), 0)')

But when I try to incorporate this statement into a list to execute multiple inserts using executemany(), I get a syntax error:

equivs = [
    ((SELECT id from universityA WHERE course_code=301 AND course_prefix="AERO"), (SELECT id from universityB WHERE course_code=101 AND course_prefix="ARCH"), 0),
    ((SELECT id from universityA WHERE course_code=301 AND course_prefix="AERO"), (SELECT id from universityB WHERE course_code=101 AND course_prefix="ARCH"), 0),
    ((SELECT id from universityA WHERE course_code=301 AND course_prefix="AERO"), (SELECT id from universityB WHERE course_code=101 AND course_prefix="ARCH"), 0)
]

# Fill the table
cur.executemany('INSERT INTO courseMap (universityA_id, universityB_id, is_flagged) VALUES (?,?,?)', equivs)

The error I'm getting is "syntax error: invalid syntax", with the caret positioned right before the boolean is_flagged value on the first insert.

Am I missing something in the multiple insert syntax on executemany()? My SQLite 3 version is 3.10.0.

Are the select statements in your equivs declaration supposed to be strings? — glibdud
– glibdud, Commented Jan 10, 2017 at 21:01
My thinking was that they shouldn't be since the INSERT statement they'll be plugged into is a string...do you mean the items in equivs should be in quotes? — Marcatectura
– Marcatectura, Commented Jan 10, 2017 at 21:08
Yes. I'm not sure how you didn't get a syntax error on that before it even got to the executemany statement. — glibdud
– glibdud, Commented Jan 10, 2017 at 21:09
To clarify, there should be quotes (single quotes, since you have double quotes in the select) before each SELECT and after each "AERO" and "ARCH". — glibdud
– glibdud, Commented Jan 10, 2017 at 21:12
Actually, my mistake. It looks like they need to be outside the parentheses. — glibdud
– glibdud, Commented Jan 10, 2017 at 21:16

Parfait · Accepted Answer · 2017-01-10 22:22:17Z

1

You cannot pass a DDL/DML SQL statement (i.e., SELECT statement) as a parameter. In fact, that is the very reason for parameterization! Recall the Bobby Tables injection issue. Parameters are designed to receive a single binded value per executed call.

For your needs, consider one executed statement of a union cross join which you can do by comma separated SELECT statements or explicit CROSS JOIN. Below assumes each query SELECT returns only one id value) else you will append cartesian cross products.

INSERT INTO CourseMap (universityA_id, universityB_id, is_flagged) 

SELECT * FROM
   (SELECT id from universityA WHERE course_code=301 AND course_prefix="AERO"), 
   (SELECT id from universityB WHERE course_code=101 AND course_prefix="ARCH"), (SELECT 0)

UNION ALL 

SELECT * FROM
    (SELECT id from universityA WHERE course_code=301 AND course_prefix="AERO"), 
    (SELECT id from universityB WHERE course_code=101 AND course_prefix="ARCH"), (SELECT 0)

UNION ALL 

SELECT * FROM
    (SELECT id from universityA WHERE course_code=301 AND course_prefix="AERO"),  
    (SELECT id from universityB WHERE course_code=101 AND course_prefix="ARCH"), (SELECT 0)

Aside - reconsider this schema as duplicate structured tables like the two University ones strays from best practices as both tables can be appended into one with an indicator field ('A' or 'B') for efficient storage, querying, and scaling.

answered Jan 10, 2017 at 22:22

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

2 Comments

Marcatectura Over a year ago

I'm confused as to why I would be able to insert a single row with a subquery inside the INSERT, but couldn't do the same with multiple rows.

Parfait Over a year ago

Your first attempt that ran successfully executed an entire SQL statement without any parameters as this solution shows. Your failed attempt tried to pass an SQL statements as a parameter. Plus, parameters are not evaluated on their own but binded to a prepared statement. Prepare your SQL statement then pass values as list of tuples as @CL. shows or all at once with union cross join like here.

CL. · Accepted Answer · 2017-01-11 09:25:19Z

In SQL, you can use multiple tuples in the VALUES clause; this requires to write the subqueries multiple times:

INSERT INTO CourseMap (universityA_id, universityB_id, is_flagged)
VALUES ( (SELECT ...), (SELECT ...), 0),
       ( (SELECT ...), (SELECT ...), 0),
       ( (SELECT ...), (SELECT ...), 0);

Python's executemany() execute the same statement multiple times with different parameter values. So the only thing you can change are the actual values used in the subqueries:

args = [ (301, "AERO", 101, "ARCH"), (...) ]
db.executemany("""INSERT INTO CourseMap (universityA_id, universityB_id, is_flagged)
                  VALUES ( (SELECT id from universityA WHERE course_code=? AND course_prefix=?),
                           (SELECT id from universityB WHERE course_code=? AND course_prefix=?),
                          0)""", args)

If that is not what you want to change, you have to execute the subqueries separately, and give their results to executemany.

Collectives™ on Stack Overflow

SQLite Multiple Insert with Query Syntax Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related