mysql 如何从python SQL查询中插入多对多关系中的两个值？

c9qzyr3d 于 2023-08-02 发布在 Mysql

关注(0)|答案(2)|浏览(134)

我尝试在authors表中插入authors，以便通过flask网站中author_ids和source_ids的关联表来匹配源。我正在使用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));

字符串
pull变量进入python的漫长过程如下所示。

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

型

mysql

来源：https://stackoverflow.com/questions/76686920/how-to-insert-two-values-in-a-many-to-many-relationship-from-a-python-sql-query

2条答案

按热度按时间

u5rb5r591#

这是一个方法，但是你需要在一个循环中为每个作者做一个查询
mysql支持用户定义的变量在会话中有效。
必须这样做，因为MySQL不支持RETURNING

CREATE tABLE author (id int auto_increment primary key, forename varchar(10),surname varchar(10))

CREATE tABLE source (id int auto_increment primary key, title varchar(30))

CREATE tABLE associate_author (author_id int,source_id int)

BEGIN;
INSERT INTO source (title) VALUES ("Short Stories");
SET @title = (select LAST_INSERT_ID() from source);

INSERT INTO author (forename,surname) VALUES ("Bob","Smith");
INSERT INTO associate_author (author_id,source_id)  
SELECT 
  (select LAST_INSERT_ID() id from author LIMIT 1) , @title title;

INSERT INTO author (forename,surname) VALUES ("Jackie", "Brown");
INSERT INTO associate_author (author_id,source_id)  
SELECT 
  (select LAST_INSERT_ID() id from author LIMIT 1) , @title title;
COMMIT;

Records: 1  Duplicates: 0  Warnings: 0

Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM associate_author

| 源ID| source_id |
| --| ------------ |
| 一个| 1 |
| 一个| 1 |
fiddle
一个更好的解决方案是使用触发器，但您仍然需要首先捕获源ID。
请注意，dbfiddle在创建触发器时不需要DELIMITER，因此根据gui需要添加它

CREATE tABLE author (id int auto_increment primary key, forename varchar(10),surname varchar(10))

CREATE tABLE source (id int auto_increment primary key, title varchar(30))

CREATE tABLE associate_author (author_id int,source_id int)

CREATE TRIGGER after_author_insert
AFTER INSERT
ON author FOR EACH ROW
BEGIN
INSERT INTO associate_author (author_id,source_id)  
SELECT 
  (NEW.id) , @title ;
END

BEGIN;
INSERT INTO source (title) VALUES ("Short Stories");
SET @title = (select LAST_INSERT_ID() from source);

INSERT INTO author (forename,surname) VALUES ("Bob","Smith"), ("Jackie", "Brown");

COMMIT;

Records: 2  Duplicates: 0  Warnings: 0

SELECT * FROM associate_author

| 源ID| source_id |
| --| ------------ |
| 一个| 1 |
| 一个| 1 |
fiddle的

赞(0）回复(0）举报 2023-08-02

vjhs03f72#

仅限MariaDB：

保持简单，避免额外的选择，触发器和其他开销。特别是SELECT max(id)可能会给予您一个无效的结果，因为另一个连接可能已经在SELECT max(id)之间插入并提交了另一行。
使用RETURNING：

INSERT INTO author (forename,surname) VALUES ("Bob","Smith") RETURNING @a_id:=author_id;
INSERT INTO source (title) VALUES ("Short Stories") RETURNING @s_id:=source_id;
INSERT INTO associate_author (author_id, source_id) VALUES (@a_id, @s_id);

字符串

赞(0）回复(0）举报 2023-08-02

我来回答

mysql 如何从python SQL查询中插入多对多关系中的两个值？

2条答案

相关问题

热门标签

最新问答