python - 使用 Python 文件 I/O 或 MySQL 查询更新现有数据库并插入新数据库

Question

我拥有或正在使用的:
我有两个数据库 -
1. 在线数据库(表包含大约 37k 个条目)，
2. 我的本地数据库(表包含大约 55k 个条目)。

我在服务器和本地都使用 phpMyAdmin。
在线 phpMyAdmin 和本地 phpMyAdmin 版本 - 5.5.42-cll - MySQL Community Server (GPL)(两者相同)

---

我正在尝试做的事情:
我想从我的本地数据库更新现有数据并将新数据插入到我的在线数据库的表中。
注意:我无法删除或替换表，因为它是互连的。它给我 FOREIGN KEY constraint error 和 PRIMARY KEY constraint error。

---

我尝试做的事情:
我尝试从我的本地数据库导出，然后使用 phpMyAdmin 导入在线数据库，但我收到错误消息:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''CHEA B C13279', 'CHE', 'CHK', 'Reaction kinetics can be studied by', '0', '    ' at line 1 

我尝试导出不同类型的文件格式，如 SQL、CSV、XML 等，以及使用 自定义 SQL 文件>INSERT、UPDATE 和REPLACE 但我失败了。

---

我想做什么:
以 SQL INSERT 文件格式导出我的本地和在线数据库，然后使用 Python 文件 I/O 和字符串格式比较两个文件并创建两个新文件以 UPDATE 数据库表中的现有数据和 INSERT 新数据数据库表。

---

到目前为止我的 Python 代码:

online = "questions(online).sql"
local = "questions(local).sql"

temp1 = "temp/questions(online)"
temp2 = "temp/questions(local)"

newFileName ="questions(insert).sql"
newFileName2 ="questions(update).sql"

open(newFileName, "a") as insert
open(newFileName2, "a") as update

with open(local, "r") as localfile:
    with open(online, "r") as onlinefile:
        for line in localfile:
            for line2 in onlinefile:
                if line == line2:
                    update.write(line)
                else:
                    insert.write(line)
insert.close()
update.close()

运行上面的脚本后，我得到如下输出:
在更新文件中只写了 1 行。
在插入文件中写入在线文件的所有条目-1。

注意:
1. 我个人在本地机器上编辑了超过 15k 个条目，因此在比较两个数据库时不可能只有 1 个条目不同。
2. Insert file non duplicate entries 应该写入，但它写入了在线数据库中的所有条目，除了更新文件中的重复条目。
3. 实际上，文件或数据库的Primary Key行的唯一性都在前几个字母中。但我不知道如何只使用两个文件中每行的首字母来比较行。

---

编辑:
两个 SQL 文件中的文本类似于:

在线数据库文件示例

('CHEA B C13279', 'CHE', 'CHK', 'Reaction kinetics can be studied by', 0),
('CHEA B C13281', 'CHE', 'CHK', 'Half - life is independent', 0),
('CHEA B C15590', 'CHE', 'Elo', 'A horizontal plank has a rectangular block', 0),
('PHYPTEST3217', 'PHY', 'PT', 'Two particles of same mass and charge', 0),
('PHYMP34780', 'PHY', 'MP', 'What id the momentum of X-rays', 0),
('MATCUS10678', 'MAT', 'DC', 'If $\\int x^{-3}.{5^{x^\\frac{1}{^2}}}dx{5^{x^\\frac{1}{^2}}} $  then $ k $  is', 0),

本地数据库文件示例

('CHEA B C13279', 'CHE', 'CHK', 'Reaction kinetics can be studied by', '0'), #Unchanged content

('CHEA B C13281', 'CHE', 'CHK', 'This is edited and updated', '0'), #Updated content
('CHEA B C15590', 'CHE', 'Elo', 'This is edited and updated', '0'), #Updated content
('PHYPTEST3217', 'PHY', 'PT', 'This is edited and updated', 0), #Updated content

('PHYMP34780', 'PHY', 'MP', 'What id the momentum of X-rays', 0), #New contents
('MATCUS10678', 'MAT', 'DC', 'If $\\int x^{-3}.{5^{x^\\frac{1}{^2}}}dx{5^{x^\\frac{1}{^2}}} $  then $ k $  is', 0), #New contents
('CHEVHV14696', 'CHE', 'TCE', 'Variation of heat of reaction with temperature is given by Kirchhoff''s equation, which is :', '0'), #New contents
('MAT2D35456', 'MAT', '2D', 'The shortest distance between the parabolas $y^{2}=4x$ and $y^{2}=2x-6$ is', '0'), #New contents
('PHYHaT33353', 'PHY', 'TP', 'What is the unit $pV$ in the gas equation $pV = \\ce{RT}?$', '0', '$\\ce{Nm}$', '0'), #New contents
('PHYWEP4745', 'PHY', 'WEP', 'A rubber ball of mass m and radius r is submerged', '0'), #New contents

所有都可以被认为是元组。所以我有两个列表(文件)，本地有 55,263 个元组，在线有 37548 个元组。每个元组有相同数量的元素，即 21 个元素，它们是字符串、一些特殊字符(如 $、^ 等)和整数。

---

请帮助我解决这个问题，无论是使用 Python 还是使用 SQL 查询。我是语言初学者。

提前致谢!

Answer 1

您的解决方案是错误的。而且你的逻辑也不对。说明:

本地数据库文件:

Line 1: aaa
Line 2: bbb
Line 3: ccc

在线数据库文件:

Line 1: aaa
Line 2: bbb
Line 3: ccc

现在只需在循环内逐行运行您的代码:

迭代 1:

line = aaa
line2 = aaa

**update**.write(line ) => **line == aaa**

迭代 2:

line = aaa
line2 = bbb

**insert**.write(line ) => **line == aaa**

迭代 3:

line = aaa
line2 = ccc

**insert**.write(line )  => **line == aaa**

发生了什么事？你只是写了三遍，同一行。一次更新，两次插入。

到底发生了什么，您正在进行 37k*55k 比较。每次您都在向某个文件写入行。

这是错误的做法。

可能的解决方案:

1. 尝试用Mysql的REPLACE代替INSERT和UPDATE
2. 尝试使用 ON DUPLICATE
3. 禁用/删除约束、更新表、恢复约束
4. 使用PL/SQL编写复杂且冗长的数据库更新脚本<​​/li>
5. 将所有本地数据作为行放入 Python 的集合数据结构中。然后仅在在线文件上运行，检查集合中是否存在行，并写入更新/插入文件。但它会变慢并且需要太多内存。

但是，为什么不通过Mysql的一行命令在几秒钟内导出新编辑的数据库呢？将本地 .sql 文件上传到服务器。您甚至可以使用压缩文件进行快速上传。在服务器上提取它。然后在 MySQL 的命令行中运行此命令:

mysql -u mysql_username -p new_db_name < /path/to/your/edited/database_local.sql

然后检查一切是否正常，并重命名数据库，或者强制您的应用程序使用新的数据库...