sqlserver 数据库迁移到 mysql 后,自增主键丢失(AUTO_INCREMENT)，写了个 python 脚本批量生成

1，python 批量设置主键自增属性脚本；

import pymysql

def q_primarykey_type(columnname,tablename,cursor):
    sql = "SELECT DATA_TYPE FROM information_schema.`COLUMNS` WHERE COLUMN_NAME = '{0}' and TABLE_NAME = '{1}' AND TABLE_SCHEMA = 'lis_setup0'".format(columnname,tablename)
    cursor.execute(sql)
    return cursor.fetchone()


def q_primarykey_name(tablename,cursor):
    sql = "SELECT cu.Column_Name,cu.ORDINAL_POSITION FROM  INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` cu WHERE CONSTRAINT_NAME = 'PRIMARY' AND cu.Table_Name = '{0}' AND CONSTRAINT_SCHEMA='lis_setup0'".format(tablename)
    cursor.execute(sql)
    return cursor.fetchone()


db = pymysql.connect(host='127.0.0.1', user='root', password='dbword', database='dbname')
cursor = db.cursor()


cursor.execute("SHOW TABLES")
data = cursor.fetchall()
tables_list = (i[0] for i in data)
e = []
for i in tables_list:
    try:    
        q_name_info = q_primarykey_name(i,cursor)
        if q_name_info[1] == 1:
            q_type = q_primarykey_type(q_name_info[0],i,cursor)
            #排除字符主键；
            if 'int' in q_type:
                auto_inc_sql = "alter table {0} change {1} {1} {2} AUTO_INCREMENT;".format(i,q_name_info[0],q_type[0])
                #print(auto_inc_sql)
                print("设置主键自增，SQL{0}".format(auto_inc_sql))
                cursor.execute(auto_inc_sql)
    except:
        e.append(i)        
        
print('==========设置完成==========')
print('==========设置错误表名列表：==========')
print(e)


db.close()

2，脚本调用方法；

>>  python bath_mysql.py