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 # database which you want to backup db = 'neveropen' connection = m.connect(host='localhost', user='root', password='123', database=db) cursor = connection.cursor() # Getting all the table names 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 # database which you want to backup db = 'neveropen' connection = m.connect(host='localhost', user='root', password='123', database=db) cursor = connection.cursor() # Getting all the table names 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 neveropen_backup database (which we created in the process) looks like this:
