我试图用python-mariadb连接器实现一些简单的SQL插入语句,但无法找出我做错了什么。
数据库如下所示:
SET FOREIGN_KEY_CHECKS = false;
CREATE OR REPLACE TABLE `forums` (
`id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE OR REPLACE TABLE `accounts` (
`id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE OR REPLACE TABLE `posts` (
`id` int(10) unsigned NOT NULL,
`forum_id` int(10) unsigned NOT NULL,
`account_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `posts_forum_fk` (`forum_id`),
KEY `posts_account_fk` (`account_id`),
CONSTRAINT `posts_account_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
CONSTRAINT `posts_forum_fk` FOREIGN KEY (`forum_id`) REFERENCES `forums` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE OR REPLACE TABLE `comments` (
`id` int(10) unsigned NOT NULL,
`post_id` int(10) unsigned NOT NULL,
`account_id` int(10) unsigned NOT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `comments_post_fk` (`post_id`),
KEY `comments_account_fk` (`account_id`),
-- KEY `comments_comments_fk` (`parent_id`),
-- CONSTRAINT `comments_comments_fk` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`),
CONSTRAINT `comments_account_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
CONSTRAINT `comments_post_fk` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = true;
插入数据的代码如下:
import mariadb
config = {
"user": "db_user_name",
"password": "db_passwd",
"host": "db_host",
"port": 3306,
"database": "db_name"
}
if __name__ == '__main__':
with mariadb.connect(**config) as conn:
cur = conn.cursor()
cur.executemany(
"INSERT INTO `forums` (`id`) VALUES (?)",
[(1,), (2,), (3,)]
)
cur.executemany(
"INSERT INTO `accounts` (`id`) VALUES (?)",
[(1,), (2,), (3,), (4,)]
)
cur.executemany(
"INSERT INTO `posts` (`id`, `forum_id`, `account_id`) VALUES (?, ?, ?)",
[(6, 3, 1)]
)
cur.executemany(
"INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (?, ?, ?, ?)",
[(1, 6, 1, None), (2, 6, 2, 1)]
) # exception happens here
当执行此操作时,我得到以下错误:
Traceback (most recent call last):
File ".../db_test.py", line 28, in <module>
cur.executemany(
mariadb.DatabaseError.DataError: Invalid parameter type at row 2, column 4
我不确定executemany是如何实现的,但我认为它应该做一些类似于以下SQL查询的事情:
INSERT INTO `forums` (`id`) VALUES (1), (2), (3);
INSERT INTO `accounts` (`id`) VALUES (1), (2), (3), (4);
INSERT INTO `posts` (`id`, `forum_id`, `account_id`) VALUES (6, 3, 1);
INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`)
VALUES (1, 6, 1, NULL), (2, 6, 2, 1);
这对我来说很好…
是bug还是我做错了什么?
花了我一段时间
cur.executemany(
"INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (?, ?, ?, ?)",
[(1, 6, 1, None), (2, 6, 2, 1)]
)
但在你的评论表中,你有这个限制
CONSTRAINT `comments_comments_fk` FOREIGN KEY (`parent_id`)
REFERENCES `comments` (`id`),
在输入(2,6,2,1(之前,元组(1,6,1,None(必须已经在数据库中提交,并且在批量插入中,提交是在所有插入都在数据库中之后进行的,但第一个元组当时不在中
因此,如果您这样做,两行都将出现在数据库中(在大容量插入后,我还提交了所有其他表(:
MariaDB
mycursor.execute(
"INSERT INTO `comments` (`id`, `post_id`, `account_id`,
`parent_id`) VALUES (?, ?, ?, ?)",
(1, 6, 1,None ))
mydb.commit()
mycursor.executemany(
"INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (?, ?, ?, ?)",
[ (2, 6, 2, 1)])
mydb.commit()
MySQL
sql = """INSERT INTO forums (id) VALUES (%s)"""
val = [("1",), ("2",), ("3",)]
mycursor.executemany(sql,val)
mydb.commit()
mycursor.executemany(
"INSERT INTO accounts (id) VALUES (%s)",
[(1,), (2,), (3,), (4,)]
)
mydb.commit()
mycursor.executemany(
"INSERT INTO posts (id, forum_id, account_id) VALUES (%s, %s, %s)",
[(6, 3, 1)]
)
mydb.commit()
mycursor.execute(
"INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (%s, %s, %s, %s)",
(1, 6, 1,None ))
mydb.commit()
mycursor.executemany(
"INSERT INTO `comments` (`id`, `post_id`, `account_id`, `parent_id`) VALUES (%s, %s, %s, %s)",
[ (2, 6, 2, 1)])
mydb.commit()