带有查询语法错误的SQLite多次插入

Question

我试图用python将多个行插入到SQLite DB中。我有三张桌子：

UniversityA：

• id
• course_name
• course_code
• course_prefix

UniversityB：

• id
• course_name
• course_code
• course_prefix

CourseMap：

• universityA_id (来自universityA的引用id )
• universityB_id (来自universityB的引用id )
• is_flagged (布尔值0/1)

使用以下语法，我可以轻松地向CourseMap表添加一条记录：

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)')

但是，当我试图将这个语句合并到一个列表中以使用executemany()执行多个插入时，我会得到一个语法错误：

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)

我得到的错误是“语法错误:无效语法”，该插入符位于第一个插入的布尔is_flagged值之前。

我是不是在executemany()的多重插入语法中遗漏了什么？我的SQLite 3版本是3.10.0。

Answer 1

不能将DDL/DML语句(即SELECT语句)作为参数传递。实际上，这就是非常适合参数化的！回想一下鲍比桌注入问题。参数设计为每次执行调用都接收单个绑定值。

根据您的需要，考虑一个执行的联合交叉连接语句，您可以通过逗号分隔的SELECT语句或显式CROSS JOIN来执行该语句。下面假设每个查询SELECT只返回一个id值)，否则您将追加笛卡尔交叉产品。

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)

此外-将此模式重新考虑为重复的结构化表，如两个University偏离了最佳实践，因为两个表都可以附加到一个带有指示符字段('A‘或'B')中，以便进行有效的存储、查询和缩放。

Answer 2

在SQL中，可以在VALUES子句中使用多个元组；这需要多次写入子查询：

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

Python的executemany()使用不同的参数值多次执行相同的语句。因此，您唯一可以更改的是子查询中使用的实际值：

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)

如果这不是您想要更改的内容，则必须分别执行子查询，并将其结果提供给executemany。