如何通过Python SQL查询在多对多关系中插入两条关联数据？

在Flask网站中，我需要将作者插入test_author表，同时通过关联author_id与source_id的test_associate_author关联表，建立作者与test_source表中来源的关联。使用MariaDB，希望通过单条SQL查询完成操作，目前用多次查询的方式没能成功。

表结构创建语句 #

CREATE TABLE test_associate_author (author_id int,source_id int);
CREATE TABLE test_source (source_id int auto_increment primary key, title varchar(30));
CREATE TABLE test_author (author_id int auto_increment primary key, forename varchar(10),surname varchar(10));

当前繁琐的实现方式 #

INSERT INTO author (forename,surname) VALUES ("Bob","Smith");
INSERT INTO source (title) VALUES ("Short Stories");
var a =  (MAX(id) FROM from author;
INSERT INTO author (forename,surname) VALUES ("Jackie","Smith");
var b = select (MAX(id) FROM from author;
var c = SELECT (MAX(id) FROM source)
INSERT INTO associate_author (author_id,source_id) VALUES 
(a, c);
INSERT INTO associate_author (author_id,source_id) VALUES 
(b, c);

方法1：用762811+事务（推荐，避免并发问题） #

这种方式用事务保证原子性，762811是会话级的，能准确获取当前插入的自增ID，不会被其他会话的插入操作干扰：

START TRANSACTION;
-- 插入来源并记录ID
INSERT INTO test_source (title) VALUES ("Short Stories");
SET @source_id = 762811;
-- 插入第一个作者并建立关联
INSERT INTO test_author (forename, surname) VALUES ("Bob", "Smith");
INSERT INTO test_associate_author (author_id, source_id) VALUES (762811, @source_id);
-- 插入第二个作者并建立关联
INSERT INTO test_author (forename, surname) VALUES ("Jackie", "Smith");
INSERT INTO test_associate_author (author_id, source_id) VALUES (762811, @source_id);
COMMIT;

方法2：单条SQL完成作者插入+关联（需MariaDB 10.5+） #

利用RETURNING子句直接返回插入作者的自增ID，再关联到来源ID，实现单条SQL完成作者插入与关联操作（来源需提前插入）：

-- 先插入来源并获取ID
INSERT INTO test_source (title) VALUES ("Short Stories");
SET @source_id = 762811;

-- 单条SQL插入两个作者并建立关联
INSERT INTO test_associate_author (author_id, source_id)
SELECT author_id, @source_id
FROM (
    INSERT INTO test_author (forename, surname)
    VALUES ("Bob", "Smith"), ("Jackie", "Smith")
    RETURNING author_id
) AS inserted_authors;

Flask中代码示例（原生数据库连接） #

import mysql.connector
from flask import Flask

app = Flask(__name__)

@app.route('/add_author_source')
def add_author_source():
    db_config = {
        'host': 'localhost',
        'user': 'your_username',
        'password': 'your_password',
        'database': 'your_database'
    }
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()
    try:
        # 执行事务
        cursor.execute("START TRANSACTION;")
        # 插入来源
        cursor.execute("INSERT INTO test_source (title) VALUES (%s);", ("Short Stories",))
        cursor.execute("SET @source_id = 762811;")
        # 插入第一个作者并关联
        cursor.execute("INSERT INTO test_author (forename, surname) VALUES (%s, %s);", ("Bob", "Smith"))
        cursor.execute("INSERT INTO test_associate_author (author_id, source_id) VALUES (762811, @source_id);")
        # 插入第二个作者并关联
        cursor.execute("INSERT INTO test_author (forename, surname) VALUES (%s, %s);", ("Jackie", "Smith"))
        cursor.execute("INSERT INTO test_associate_author (author_id, source_id) VALUES (762811, @source_id);")
        conn.commit()
        return "数据插入成功"
    except Exception as e:
        conn.rollback()
        return f"插入失败：{str(e)}"
    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    app.run(debug=True)

内容的提问来源于stack exchange，提问作者OrigamiEye