Python实现迁表

发表于 | 分类于 | 阅读次数：

python实现mysql的迁表

注意事项:

​ 1.表结构问题

​ 2.权限问题(无删除权限时,不能truncate表重置索引)

​ 3.思路问题(读表结构,修改表结构数据,读表数据,在新服务器库里建表,插入数据)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
import pymysql
import pandas as pd
from multiprocessing import Pool
from sqlalchemy import create_engine
import re
#获取pre_formal_2中所有以expect开头的表名称
db1 = pymysql.connect(host='192.168.0.222',port=3306,user='user_r_2',database='pre_formal_2',password='4f5g6h7j',charset='utf8mb4')
cursor1 = db1.cursor()
db2 = pymysql.connect(host='localhost', port=3306, user='root', database='ceshi',
                             password='123456',
                             charset='UTF8mb4')
cursor2 = db2.cursor()
sql = "SELECT distinct TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'expect%'"
cursor1.execute(sql)
data = cursor1.fetchall()
data = pd.DataFrame(list(data))
data = data[0].tolist()
def func(table_name_list):
    for table_name in table_name_list:
        # 查看建表语句
        sql = f"show create table {table_name}"
        cursor1.execute(sql)
        data = cursor1.fetchone()
        # 重置自增值
        try:
            patten = re.compile('AUTO_INCREMENT=(.*) DEFAULT')
            result = patten.findall(data[1])
            data1 = data[1].replace(result[0], '0')
        except:
            data1 = data[1]
        # 获取数据内容
        engine = create_engine('mysql+pymysql://user_r_2:4f5g6h7j@192.168.0.222:3306/pre_formal_2')
        sql = f'select * from {table_name}'
        df = pd.read_sql_query(sql, engine)
        print(df)
        # 将表数据存入数据库
        print(data1)
        sql_1 = f"{data1}"
        cursor2.execute(sql_1)
        engine = create_engine('mysql+pymysql://root:123456@localhost:3306/ceshi?charset=UTF8mb4')
        pd.io.sql.to_sql(df, f'{table_name}', engine, index=False, if_exists='append', chunksize=10000, )
        engine.dispose()
if __name__ == '__main__':
    pool = Pool(5)
    n = 300
    count = (len(data) // n) + 1
    for num in range(count):
        table_name_list = data[num * n:(num + 1) * n]
        print(table_name_list)
        #参数是指各个不同进程使用的参数本能当成传递使用
        pool.apply_async(func, args=(table_name_list,))
    pool.close()
    pool.join()