This is the sixth day of my participation in genwen Challenge. More details: “This article has participated in the weekend learning plan, click to view the details”

To rename a table in a database, use the following native SQL:

RENAME TABLE old_table TO new_table;  
Copy the code

Dilemma: But MySQL does not directly support database renaming

How do you quickly rename an existing database using Python?

For example, at the beginning of the project, the name of the database (db_ridingRoad) was not planned. Then a large number of tables were created and a large amount of data was written. Now the name of the database needs to be renamed to (db_news_website).

Conventional thinking

The following method is more complicated


-- Database backupMysqldump -u [UserName] -p [Password] -r [DB_Name]> [DB_Name].sql
Create a new database
create database [New_DB_Name];
Import backup data into the new databaseMysql -u [UserName] -p [Password] [New_DB_Name]< [DB_Name].sql
Delete old database
drop database [DB_Name];
Copy the code

A faster way

You only need to run the following command

python rename_database.py old_db_name new_db_name
Copy the code

You can rename a table to a new database using the table rename method. The basic logic is as follows:

  1. Creating a new database
  2. Gets all the table names under the old database
  3. Rename the table to the new database name
  4. Delete old database

This is done using Python code, the main code (see end for full code) :

def rename_db(old_name, new_name) :
    """ Database rename :param old_name: old database name: param new_name: new database name: return: returns 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 a new database name
        cursor.execute('create database if not exists {}'.format(new_name))

        cursor.execute(sql, (old_name, ))
        results = cursor.fetchall()
        The loop is placed under the new database name
        for r in results:
            tb = r['TABLE_NAME']
            rename_sql = """RENAME TABLE {}.{} to {}.{}""".format(old_name, tb, new_name, tb)
            cursor.execute(rename_sql)
        Delete the old database
        cursor.execute('drop database {}'.format(old_name))
    except Exception as ex:
        conn.rollback()
        print("rename_db Exception: {},{}".format(sql, ex))
        return False
    else:
        If no exception occurs, the transaction is committed
        conn.commit()
    finally:
        conn.close()
    return True
Copy the code

Method of use

  1. Install PyMySQL
pip install PyMySQL
Copy the code
  1. Modify the database account configuration information in the script
MYSQL_HOST = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_USER = 'ridingroad'
MYSQL_PASSWORD = 'xxxxyyyy'
MYSQL_DATABASE = 'db_ridingroad'
Copy the code
  1. Go to the directory where the script resides and run the following command:
python rename_database.py old_db_name new_db_name
Copy the code

The complete code is as follows:

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) :
    Param IS_DICt_CURSOR: Whether to return data with dictionary structure :param DATABASE: default connected database: return: Returns a connection and a buoy ""
    try:
        config = {
            'host': MYSQL_HOST,
            'port': MYSQL_PORT,
            'user': MYSQL_USER,
            'password': MYSQL_PASSWORD,
            'database': database,
            'charset': MYSQL_CHARSET,
        }

        conn = pymysql.connect(**config)
        if is_dict_cursor:
            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        else:
            cursor = conn.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) :
    """ Database rename :param old_name: old database name: param new_name: new database name: return: returns 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:
        conn.begin()
        Create a new database name
        cursor.execute('create database if not exists {}'.format(new_name))

        cursor.execute(sql, (old_name, ))
        results = cursor.fetchall()
        The loop is placed under the new database name
        for r in results:
            tb = r['TABLE_NAME']
            rename_sql = """RENAME TABLE {}.{} to {}.{}""".format(old_name, tb, new_name, tb)
            cursor.execute(rename_sql)
        Delete the old database
        cursor.execute('drop database {}'.format(old_name))
    except Exception as ex:
        conn.rollback()
        print("rename_db Exception: {},{}".format(sql, ex))
        return False
    else:
        If no exception occurs, the transaction is committed
        conn.commit()
    finally:
        conn.close()
    return True


if __name__ == '__main__':
    old_db = sys.argv[1]
    new_db = sys.argv[2]
    rename_db(old_name=old_db, new_name=new_db)
Copy the code