如何在Python中使用mysqldump和mysql复制数据库? [英] How to copy a database with mysqldump and mysql in Python?
问题描述
我正在编写一个简单的Python脚本来复制MySQL数据库.我正在尝试根据以下SO问题及其答案来复制数据库:"复制/复制数据库而不使用mysqldump "," python子进程和mysqldump "和"Python子进程,mysqldump和管道".但是,由于某些原因,我的脚本无法正常工作,因为表和数据没有出现在新数据库中.
I am writing a simple Python script to copy a MySQL database. I am attempting to copy the database based on the following SO questions and their answers: "Copy/duplicate database without using mysqldump", "python subprocess and mysqldump" and "Python subprocess, mysqldump and pipes". However, my script does not work for some reason I cannot see as the tables and the data do not appear in my new database.
我从输出中看到mysqldump正常工作(我在输出中看到在...上完成转储"),所以我认为管道出问题了.
I can see from my output that the mysqldump works correctly (I see a "Dump completed on..." in my output), so I think that something is wrong with my pipeline.
这是我的剧本:
#!/usr/bin/env python
import pymysql
from subprocess import Popen, PIPE, STDOUT
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='mydb')
cur = conn.cursor()
print("Attempting to create new database...")
try:
cur.execute("CREATE DATABASE mydb2")
print("Creating new database")
except Exception:
print("Database already exists")
print()
# close connection just to be sure
cur.close()
conn.close()
print("Trying to copy old database to new database...")
args1 = ["mysqldump", "-h", "localhost", "-P", "3306", "-u", "root", "-p", "mydb"]
args2 = ["mysql", "-h", "localhost", "-P", "3306", "-u", "root", "-p", "mydb2"]
p1 = Popen(args1, stdout=PIPE, stderr=STDOUT)
p2 = Popen(args1, stdin=p1.stdout, stdout=PIPE, stderr=STDOUT)
output = p2.communicate()
print("output:")
print(output)
print()
您可以看到,我从此答案中获取了副本数据库管道.最初我遇到了错误mysqldump: Couldn't find table: "|"
,就像那个其他问题一样.因此,现在我按照建议使用两个subprocess.Popen
调用,从而解决了该错误消息.
As you can see I took the copy database pipeline from this answer. And at first I had the error mysqldump: Couldn't find table: "|"
just as in that other question. So now I use two subprocess.Popen
calls as suggested, which solved that error message.
输出变量显示已执行mysqldump,但我看不到有关mysql命令的任何内容.
The output variable shows that a mysqldump is performed, but I see nothing being mentioned about the mysql command.
我已尝试使用p2.wait()
和p1.wait()
代替p2.communicate()
在一个答案中建议,但这只会使我的Python脚本无响应.
I have tried to use p2.wait()
and p1.wait()
instead of p2.communicate()
as suggested in one answer, but that just makes my Python script become unresponsive.
我也尝试了以下方法:
output1 = p1.communicate()
output2 = p2.communicate()
但是然后output1和output2都显示相同的mysqldump输出.所以我想那只是一个愚蠢的事情.
But then both output1 and output2 show the same mysqldump output. So that was just a silly thing to do I guess..
我还尝试使用subprocess.call
而不是subprocess.Popen
,但这也使我的脚本无响应.
I have also tried to use subprocess.call
instead of subprocess.Popen
, but that also makes my script become unresponsive.
在Popen
或call
中还包含shell=True
也会导致脚本无响应.
Also including shell=True
in either Popen
or call
also results in the script being just unresponsive.
但是,确实可以在命令提示符(我使用Windows 8.1)中键入命令,如下所示:
However, it does work to type in the command in the command prompt (I use Windows 8.1) as follows:
mysqldump -h localhost -P 3306 -u root -p mydb | mysql -h localhost -P 3306 -u root -p mydb2
它在不到三秒钟的时间内复制了我的小型测试数据库.
It copies my small test database in less than three seconds.
我希望我也能在Python中使用它.
I wish I could also get it to work in Python.
推荐答案
我不知道要用于复制的纯Python 的程度,但是您可以仅委派整个管道对外壳进行操作.
I don't know the degree of pure Python you want to use for the copy, but you can just delegate the entire pipe operation to the shell.
subprocess.Popen('mysqldump -h localhost -P 3306 -u -root mydb | mysql -h localhost -P 3306 -u root mydb2', shell=True)
这应该与在shell上运行时的工作方式相同.
This should work the same way it works when you run it on the shell.
这篇关于如何在Python中使用mysqldump和mysql复制数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!