python教程分享用python生成mysql数据库结构文档

pip install pymysql  pip install document  

# -*- coding: utf-8 -*-  import pymysql  from docx import document  from docx.shared import pt  from docx.oxml.ns import qn    db = pymysql.connect(host='127.0.0.1', #数据库服务器ip                           port=3306,                           user='root',                           passwd='123456',                           db='test_db') #数据库名称)  #根据表名查询对应的字段相关信息  def query(tablename):      #打开数据库连接      cur = db.cursor()      sql = "select b.column_name,b.column_type,b.column_comment from (select * from information_schema.`tables`  where table_schema='test_db') a right join(select * from information_schema.`columns` where table_schema='test_db_test') b on a.table_name = b.table_name where a.table_name='" + tablename+"'"      cur.execute(sql)      data = cur.fetchall()      cur.close      return data  #查询当前库下面所有的表名，表名：tablename；表名+注释(用于填充至word文档)：concat(table_name,'(',table_comment,')')  def querytablename():      cur = db.cursor()      sql = "select table_name,concat(table_name,'(',table_comment,')') from information_schema.`tables`  where table_schema='test_db_test'"      cur.execute(sql)      data = cur.fetchall()      return data  #将每个表生成word结构，输出到word文档  def generateword(singletabledata,document,tablename):      p=document.add_paragraph()      p.paragraph_format.line_spacing=1.5 #设置该段落 行间距为 1.5倍      p.paragraph_format.space_after=pt(0) #设置段落 段后 0 磅      #document.add_paragraph(tablename,)      r=p.add_run('n'+tablename)      r.font.name=u'宋体'      r.font.size=pt(12)      table = document.add_table(rows=len(singletabledata)+1, cols=3,)      table.style.font.size=pt(11)      table.style.font.name=u'calibri'      #设置表头样式      #这里只生成了三个表头，可通过实际需求进行修改      for i in ((0,'name'),(1,'type'),(2,'comment')):          run = table.cell(0,i[0]).paragraphs[0].add_run(i[1])          run.font.name = 'calibri'          run.font.size = pt(11)          r = run._element          r.rpr.rfonts.set(qn('w:eastasia'), '宋体')            for i in range(len(singletabledata)):          #设置表格内数据的样式          for j in range(len(singletabledata[i])):              run = table.cell(i+1,j).paragraphs[0].add_run(singletabledata[i][j])              run.font.name = 'calibri'              run.font.size = pt(11)              r = run._element              r.rpr.rfonts.set(qn('w:eastasia'), '宋体')          #table.cell(i+1, 0).text=singletabledata[i][1]          #table.cell(i+1, 1).text=singletabledata[i][2]          #table.cell(i+1, 2).text=singletabledata[i][3]          if __name__ == '__main__':      #定义一个document      document = document()      #设置字体默认样式      document.styles['normal'].font.name = u'宋体'      document.styles['normal']._element.rpr.rfonts.set(qn('w:eastasia'), u'宋体')      #获取当前库下所有的表名信息和表注释信息      tablelist = querytablename()      #循环查询数据库，获取表字段详细信息，并调用generateword，生成word数据      #由于时间匆忙，我这边选择的是直接查询数据库，执行了100多次查询，可以进行优化，查询出所有的表结构，在代码里面将每个表结构进行拆分      for singletablename in tablelist:          data = query(singletablename[0])          generateword(data,document,singletablename[1])      #保存至文档      document.save('数据库设计.docx')