How to take backup of MySQL database using Python?

In this article, we are going to learn how to back up a MySQL database using python.

Database used:

Python modules required:

In this post, we are going to use the mysql-connector module of python to back up our data. You can install mysql-connector with the following command:

pip install mysql-connector

Backing Up Database:

So, it's a fairly simple task to backup our database.

What we will do is, first of all, create a connection to the database (which we want to backup) using the mysql-connector module. Then we will create an instance of the cursor object obtained through that connection. Then we will first fetch all the table names using the "SHOW TABLES " command of SQL and execute the method of the cursor object. 

Example:

Note: The cursor.fetchall() method returns a list of tuples with table names as its elements. And we are running a for loop on that and storing the first element of that tuple (which is our table name) in our list so as to get a plain list that consists of all the table names.

Then we will create our backup database using the "CREATE" command of SQL and cursor object. We will do this in a try block so that if the created database already exists then it won't return any error. The code for the above looks like this:

This will create our backup database. Now we will simply run the "USE" command with the cursor object to use the backup_dbname database instead of our current database.

This will change our current database to backup_dbname. So, in SQL when we run the following command we can create a copy of the table1 into a newly made table2.

CREATE TABLE table2 SELECT * FROM table1;

So, what we will do in our python code is that we will run a for loop on all our table names and execute a "CREATE" command for that table name (and make sure we are now using the backup_dbname as our current database and it does not have any tables as of now) and we will create a query, similar to the above and copy the table into the newly made table.

The code for that is:

Notice that we are referencing the original table through {db}.{table_name} as it exists in another database as our current database is backup_dbname.

So, the full picture of our code looks like this:

And running this code we create our backup database with all the tables and data in them. The geeksforgeeks_backup database (which we created in the process) looks like this: