研发组需要同步表数据,但维护表结构注释是记录在Excel中,同步10几张表,字段注释几百行,手动加太麻烦,同时也防止后续再加,所以写了如下脚本。
脚本功能是读excel内容,第一列是表名/字段名 ,第二列是描述,第三列是demo (可以忽略)将字段注释使用alter table的方式加上。
1.先引用包 我们用的openpyxl ,调试中用到了pdb 连接数据库用的是pymysql
import openpyxl
import os
import pymysql
import pdb
hostname='172.17.4.4'
uname='user'
pword='password****'
2.以追加的形式打印出建表sql和需要加注释的sql
def writeFile(content:str):
with open('adddesc.sql','a') as f :
f.write('{} \n'.format(content))
3.连接数据库,获取建表sql。
def getTableSql(tablename):
conn = pymysql.connect(host=hostname,port=3306,user=uname,password=pword,database='dbname')
res = None
try:
cursor = conn.cursor()
cursor.execute('show create table {}'.format(tablename))
_,res = cursor.fetchone()
except pymysql.Error as err:
print(err)
finally:
cursor.close()
conn.close()
return res
4.替换sql部分 匹配到相关行 后面加上注释。因为用的逗号分隔 考虑到decimal(m,n)这种情况,所以第一段匹配后,要加上后面的再加注释,所以用flag来标识。
def
def replaceSql(createsql,fieldvalue,fielddesc,tablename,isfirst):
if createsql is None or createsql == '' or 'exist' in createsql or str(fieldvalue).lower() == 'id':
return
#pdb.set_trace()
lsql = createsql.split(',')
#print(lsql)
col = '`{}`'.format(fieldvalue.lower())
desc = "comment '{}'".format(fielddesc)
altertab = ''
decimalproc = ''
decimalflag = False
if isfirst == 1:
altertab = ' alter table {} '.format(tablename)
else:
altertab = ''
altersql = ''
for isql in lsql:
if decimalflag:
altersql = '{} ,{} {},'.format(altersql,isql,desc)
decimalflag = False
break
if col in isql:
#if col == 'discount_amount':
# pdb.set_trace()
if 'decimal' in isql:
#decimal process two line
#pdb.set_trace()
decimalflag =True
altersql = '{} modify {} '.format(altertab,isql)
else:
altersql = '{} modify {} {},'.format(altertab,isql,desc)
break
writeFile(altersql)
5.接下来是读excel 区分是读表名 或 列名
def readExcel(fpath):
''' read sheet1 file contents:
row1 col1 'tablename' col2 tablename col3 describtion
row2 fields fieldsdescribtion demo
real content....
space
row1 col1 'tablename' col2 tablename col3 describtion
....
can igore fiedl3
'''
readwb=openpyxl.load_workbook(fpath)
#read sheet1 default
ws = readwb.active
col = 1 #column number in Excel
isfirst = 1 #table first column
createsql = '' #create sql statement
tablename = '' #tablename
for row in range(1,ws.max_row+1):
#print(row)
#pdb.set_trace()
col = 1
evaluecoo = ws.cell(row = row,column = col).value
if str(evaluecoo).strip() is None or str(evaluecoo).strip() == '':
createsql = ''
tablename = ''
isfirst = 1
elif str(evaluecoo).strip() == '字段':
continue
elif str(evaluecoo).strip() == '表名':
col += 1
evaluecot = ws.cell(row = row,column = col).value
# connect db get create table sql
tablename = evaluecot
createsql = getTableSql(evaluecot)
# write create sql to file
if createsql is None:
writeFile('\n table is not found {}'.format(tablename))
else:
writeFile(createsql)
else:
fieldvalue = ws.cell(row = row,column = col).value
fielddesc = ws.cell(row = row,column = col + 1).value
#add alter table statement
#skip fielddesc None
if fielddesc is None or str(fielddesc).strip() == '':
continue
#add alter table statement
replaceSql(createsql,fieldvalue,fielddesc,tablename,isfirst)
#skip id
if str(fieldvalue).lower() =='id':
isfirst = 1
else:
isfirst = 0
6.程序开始执行
if __name__ == '__main__':
readExcel('./des.xlsx')
Excel模板
| 表名 | table_name | 描述 |
|---|---|---|
| 字段 | 字段描述 | 示例 |
| col1 | col1desc | 主键 |
| col2 | col2desc | 唯一 |
| 表名 | table_name | 描述 |
| … | … | … |
347



被折叠的 条评论
为什么被折叠?



