import pymysql
# MySQL 连接信息
host = '指定IP'
port = 指定端口
user = '用户名'
password = '密码'
database = '数据库名'
# 创建连接
conn = pymysql.connect(
host=host,
port=port,
user=user,
password=password,
database=database
)
try:
# 创建游标对象
cursor = conn.cursor()
# 创建新的 schema
new_schema = 'new_schema'
cursor.execute(f'CREATE SCHEMA {new_schema}')
# 获取源 schema 中的表名
old_schema = 'old_schema'
cursor.execute(f'SELECT table_name FROM information_schema.tables WHERE table_schema = "{old_schema}" AND table_type = "BASE TABLE"')
tables = cursor.fetchall()
# 复制表结构和数据
for table in tables:
table_name = table[0]
new_table = f'{new_schema}.{table_name}'
# 复制表结构
cursor.execute(f'CREATE TABLE {new_table} LIKE {old_schema}.{table_name}')
# 复制表数据
cursor.execute(f'INSERT INTO {new_table} SELECT * FROM {old_schema}.{table_name}')
# 提交事务
conn.commit()
finally:
# 关闭游标和连接
cursor.close()
conn.close()
几行Python代码搞定MySQL复制schema,无需导出SQL脚本
最新推荐文章于 2023-12-18 13:27:07 发布