[SOLVED] How do I Dump MySql Tables One At a Time Using Python

How do I Dump MySql Tables One At a Time Using Python

I am using MySQL 5.6.21 on a Windows server and need to migrate from one machine to another. Been trying to use the MySQL Workbench Export Tables and I keep getting an error 22 on write at some point during the export process.

I am using Jupyter for simplicity.

I want to try to dump each table individually and see which one is causing the issue.

So first thing I did was to write a test using one table, as follows:

import subprocess
tablename="templedger"
gettablestring="mysqldump -u user1 -pB57$52 db_main %s > G:\Databasebackup\dbmain\%s.sql" % (tablename,tablename)
subprocess.Popen(gettablestring, shell=True)
print("Done")

The word "Done" immediately came back, but no dump

I then tried the following

!{gettablestring}

And got "Access Denied"

How do I code this us so that I can execute the dump command from within a Jupyter cell?

Thanks

Solution

Can you try this please? Tell me if it works. I used communicate method instead of relying on shell=True here:

import subprocess

tablename = "templedger"
username = "user1"
password = "B57$52"
database = "db_main"
backup_path = "G:/Databasebackup/dbmain/"

escaped_password = password.replace("$", "\\$")
command = ["mysqldump", "-u", username, f"-p{escaped_password}", database, tablename]

with open(f"{backup_path}{tablename}.sql", "w") as output_file:
    process = subprocess.Popen(command, stdout=output_file, stderr=subprocess.PIPE)
    stdout, stderr = process.communicate()

    if process.returncode != 0:
        print(f"An error occurred: {stderr}")
    else:
        print("backup completed successfully.")