运用Python快速的对MySQL数据库进行重命名

RENAME TABLE old_table TO new_table;  

-- 数据库备份
mysqldump –u [UserName] –p[Password] –R [DB_Name] > [DB_Name].sql
-- 创建新数据库
create database [New_DB_Name];
-- 把备份的数据导入到新数据库
mysql –u [UserName] –p[Password] [New_DB_Name] < [DB_Name].sql
-- 删除旧数据库
drop database [DB_Name];

python rename_database.py old_db_name new_db_name

def rename_db(old_name, new_name):
 """
 数据库重命名
 :param old_name: 原来的数据库名
 :param new_name: 新数据库名
 :return: 成功返回True， 失败返回False
 """
 # 获取所有的表名
 sql = """SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=%s"""
 conn, cursor = context()
 try:
  # 创建新数据库名
  cursor.execute('create database if not exists {}'.format(new_name))
  cursor.execute(sql, (old_name, ))
  results = cursor.fetchall()
  # 获取表明，循环处理放到新的数据库名下
  for r in results:
tb = r['TABLE_NAME']
rename_sql = """RENAME TABLE {}.{} to {}.{}""".format(old_name, tb, new_name, tb)
cursor.execute(rename_sql)
  # 把旧数据库删掉
  cursor.execute('drop database {}'.format(old_name))
 except Exception as ex:
  conn.rollback()
  print("rename_db Exception: {},{}".format(sql, ex))
  return False
 else:
  # 如果没有发生异常，则提交事务
  conn.commit()
 finally:
  conn.close()
 return True

pip install PyMySQL

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

python rename_database.py old_db_name new_db_name

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: 是否返回字典结构的数据
 :param database: 默认连接的数据库
 :return: 返回一个连接和一个浮标
 """
 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):
 """
 数据库重命名
 :param old_name: 原来的数据库名
 :param new_name: 新数据库名
 :return: 成功返回True， 失败返回False
 """
 # 获取所有的表名
 sql = """SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=%s"""
 conn, cursor = context()
 try:
  conn.begin()
  # 创建新数据库名
  cursor.execute('create database if not exists {}'.format(new_name))
  cursor.execute(sql, (old_name, ))
  results = cursor.fetchall()
  # 获取表明，循环处理放到新的数据库名下
  for r in results:
tb = r['TABLE_NAME']
rename_sql = """RENAME TABLE {}.{} to {}.{}""".format(old_name, tb, new_name, tb)
cursor.execute(rename_sql)
  # 把旧数据库删掉
  cursor.execute('drop database {}'.format(old_name))
 except Exception as ex:
  conn.rollback()
  print("rename_db Exception: {},{}".format(sql, ex))
  return False
 else:
  # 如果没有发生异常，则提交事务
  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)