Quickly Rename MySQL Databases with Python

Renaming a table in the database can be done using the following native sql:

RENAME TABLE old_table TO new_table;

Dilemma: But MySQL doesn't directly support renaming databases

So how can you use Python to quickly rename an existing database?

For example, at the beginning of the project, the naming of the database (db_ridingroad) was not planned.
Then after creating a lot of tables and writing a lot of data below, the database now needs to be renamed to (db_news_website)

Conventional thinking

The following method has more tedious steps

-- Database Backup
mysqldump –u [UserName] –p[Password] –R [DB_Name] > [DB_Name].sql
-- Creating a new database
create database [New_DB_Name];
-- Importing backed up data into a new database
mysql –u [UserName] –p[Password] [New_DB_Name] < [DB_Name].sql
-- Deleting old databases
drop database [DB_Name];

A quicker way

Simply execute the following command

python rename_database.py old_db_name new_db_name

We can use the table renaming method to rename the table under the new database. The basic logic is as follows:

Creating a new database
Get all table names under the old database
Rename the table to the new database name
Deleting old databases

Here's how to do it using Python code, the main code (full code at the end of the article):

def rename_db(old_name, new_name):
    """
    Renaming the database
    :param old_name: original database name
    :param new_name: new database name
    :return: True on success, False on failure
    """
    # Get all table names
    sql = """SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=%s"""
    conn, cursor = context()
    try:
        # Create new database name
        ('create database if not exists {}'.format(new_name))

        (sql, (old_name, ))
        results = ()
        # Get indication, loop through to new database name
        for r in results:
            tb = r['TABLE_NAME']
            rename_sql = """RENAME TABLE {}.{} to {}.{}""".format(old_name, tb, new_name, tb)
            (rename_sql)
        # Delete the old database
        ('drop database {}'.format(old_name))
    except Exception as ex:
        ()
        print("rename_db Exception: {},{}".format(sql, ex))
        return False
    else:
        # If no exception occurs, the transaction is committed
        ()
    finally:
        ()
    return True

Usage

1. Install PyMySQL

pip install PyMySQL

2. Modify the part of the script about the database account configuration information.

MYSQL_HOST = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_USER = 'ridingroad'
MYSQL_PASSWORD = 'xxxxyyyy'
MYSQL_DATABASE = 'db_ridingroad'

3. Switch to the directory where the script, the implementation of the following commands can be (data priceless, please first mysqldump backup)

python rename_database.py old_db_name new_db_name

The full code is below:

import sys
import pymysql


MYSQL_HOST = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_USER = 'ridingroad'
MYSQL_PASSWORD = 'xxxxyyyy'
MYSQL_DATABASE = 'db_ridingroad'
MYSQL_CHARSET = 'utf8'


def context(is_dict_cursor=True, database=MYSQL_DATABASE):
    """
    Creates a database connection and returns the data in a dictionary structure
    :param is_dict_cursor: If or not the data will be returned as a dictionary.
    :param database: the default database to connect to
    :return: Returns a connection and a float.
    """
    try:
        config = {
            'host': MYSQL_HOST,
            'port': MYSQL_PORT,
            'user': MYSQL_USER,
            'password': MYSQL_PASSWORD,
            'database': database,
            'charset': MYSQL_CHARSET,
        }

        conn = (**config)
        if is_dict_cursor:
            cursor = (cursor=)
        else:
            cursor = ()
        return conn, cursor
    except Exception as ex:
        print("connect database failed, {},{}".format(400, ex))
        raise Exception({'code': 400, 'msg': ex})


def rename_db(old_name, new_name):
    """
    Renaming the database
    :param old_name: original database name
    :param new_name: new database name
    :return: True on success, False on failure
    """
    # Get all table names
    sql = """SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=%s"""
    conn, cursor = context()
    try:
        ()
        # Create new database name
        ('create database if not exists {}'.format(new_name))

        (sql, (old_name, ))
        results = ()
        # Get indication, loop through to new database name
        for r in results:
            tb = r['TABLE_NAME']
            rename_sql = """RENAME TABLE {}.{} to {}.{}""".format(old_name, tb, new_name, tb)
            (rename_sql)
        # Delete the old database
        ('drop database {}'.format(old_name))
    except Exception as ex:
        ()
        print("rename_db Exception: {},{}".format(sql, ex))
        return False
    else:
        # If no exception occurs, the transaction is committed
        ()
    finally:
        ()
    return True


if __name__ == '__main__':
    old_db = [1]
    new_db = [2]
    rename_db(old_name=old_db, new_name=new_db)

Above is the use of Python quickly rename MySQL database details, more information about python rename MySQL database please pay attention to my other related articles!