mysql字段数据注释的迁移-- 使用python

背景：由于python中的to_sql语句创建的数据库表没有字段注释，而字段又比较多，返回给前端的字段名是英文，考虑在不创建映射文件的情况下，为MySQL每个字段添加注释

准备：已经有包含所有字段注释的SQL表，待插入注释的表与原表大部分字段名一致，字段类型一致

尝试：因为所有的字段都可以通过information_schema.columns来查看，所以考虑使用以下sql

update information_schema.columns as cc 
LEFT JOIN 
(SELECT column_name,column_comment FROM information_schema.columns WHERE table_name = 'origin_table') as tmp 
ON cc.column_name = tmp.column_name 
set cc.column_comment = tmp.column_comment 
where cc.table_name = 'new_table'

最后运行结果会报

The target table cc of the UPDATE is not updatable

的错误

然后单独 执行

update columns SET column_comment = '1' 
where table_name = 'new_table' AND column_name = 'new_field'

会报

Access denied for user 'root'@'%' to database 'information_schema'

原以为是权限不足的问题，后来发现我是root用户，而且权限都有，最后才知道information_schema是虚拟数据库，它的存在是类似于视图一样，只能查找不能进行增删改，当原始记录发生改变时，该数据库对应的表会同步发生改变

所以这一条路就走错了

最后

了解到更改字段注释的方法是

ALTER TABLE `table` MODIFY COLUMN `field` COMMENT '注释' -- 不带类型等
ALTER TABLE `table` MODIFY COLUMN `field` int NOT NULL  comment '注释' --带类型等

或

ALTER TABLE `table` MODIFY field field double COMMENT  '注释'

本次采用第二种办法

编写python脚本

 
    class Connection(object):
        def mysqlConnect(self, mysqlIpaddr, mysqlUsername, mysqlPassword, mysqlDatabase):
            mysql_dbs = pymysql.connect(mysqlIpaddr, mysqlUsername, mysqlPassword, mysqlDatabase)  # 连接MySQL
            engine = create_engine('mysql+pymysql://' + mysqlUsername + ':' + mysqlPassword +'@' + mysqlIpaddr + '/' + mysqlDatabase + '?charset=utf8')
            return mysql_dbs, engine
      
    def update_comment(mysqldb, origin_table， tablename):
        mysqldb.ping(reconnect=True)
        cursor = mysqldb.cursor()
        alter_sql = 'select concat("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME,' \
                    '" CHANGE ",COLUMN_NAME," ",COLUMN_NAME, " ",COLUMN_TYPE ," COMMENT "," ",\"{comment}\")' \
                    ' from information_schema.COLUMNS ' \
                    ' where TABLE_NAME=\"{table}\"'.format(
                    comment = '%s',
                    table = tablename
        )
        comment_sql = 'SELECT column_name,column_comment FROM information_schema.columns WHERE table_name = \'%s\'' % (origin_table)

        cursor.execute(alter_sql)
        alter = cursor.fetchall()
        sqllist = []
        for sql in alter:
            splitList = sql[0].split(" ")
            # 字段在第四位
            field = splitList[4]
            sqllist.append((sql[0], field))
        cursor.execute(comment_sql)
        name_comment = cursor.fetchall()

        # 开始拼接
        executeSql = []
        for sql in sqllist:
            for comment in name_comment:
                if comment[0] == sql[1]:
                    execute = sql[0] % ('"' + comment[1] + '"')
                    executeSql.append(execute)
        executeSql = ";".join(executeSql)
        try:
            cursor.execute(executeSql)
        except Exception as e:
            mysqldb.rollback()
            print(str(e))

        cursor.close()
        mysqldb.commit()
    if __name__ == "__main__":
        conn = Connection()
        mysqlDB, mysqlEngine = conn.mysqlConnect('ip', 'username', 'password', 'db')
        update_comment(mysqlDB, "origin_table", "new_table")

最后执行成功,数据库表中注释就有了