How to take backup of MySQL database using Python?
Last Updated :
21 Feb, 2022
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:
Python3
import mysql.connector as m
db = 'geeksforgeeks'
connection = m.connect(host = 'localhost' , user = 'root' ,
password = '123' , database = db)
cursor = connection.cursor()
cursor.execute( 'SHOW TABLES;' )
table_names = []
for record in cursor.fetchall():
table_names.append(record[ 0 ])
|
tables in the db
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:
Python3
backup_dbname = db + '_backup'
try :
cursor.execute(f 'CREATE DATABASE {backup_dbname}' )
except :
pass
|
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.
Python3
cursor.execute(f 'USE {backup_dbname}' )
|
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:
Python3
for table_name in table_names:
cursor.execute(
f 'CREATE TABLE {table_name} SELECT * FROM {db}.{table_name}' )
|
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:
Python3
import mysql.connector as m
db = 'geeksforgeeks'
connection = m.connect(host = 'localhost' , user = 'root' ,
password = '123' , database = db)
cursor = connection.cursor()
cursor.execute( 'SHOW TABLES;' )
table_names = []
for record in cursor.fetchall():
table_names.append(record[ 0 ])
backup_dbname = db + '_backup'
try :
cursor.execute(f 'CREATE DATABASE {backup_dbname}' )
except :
pass
cursor.execute(f 'USE {backup_dbname}' )
for table_name in table_names:
cursor.execute(
f 'CREATE TABLE {table_name} SELECT * FROM {db}.{table_name}' )
|
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:
Share your thoughts in the comments
Please Login to comment...