CODEWITHSUNDEEP
pythonsqlsqlite
Marcatectura
Marcatectura

Reputation: 1695

SQLite Multiple Insert with Query Syntax Error

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

UniversityA:

UniversityB:

CourseMap:

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.

Upvotes: 0

Views: 575

Answers (2)

CL.
CL.

Reputation: 180010

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.

Upvotes: 1

Parfait
Parfait

Reputation: 107567

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.

Upvotes: 1

Related Questions