背景:由于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")
最后执行成功,数据库表中注释就有了