Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to use python to generate mysql database structure documents, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand it.

Because of the project, we need to write database design documents, but because there are too many data tables, it takes too long to write manually, so I made a simple script to quickly generate the database structure and save it to word documents.

Install pymysql and document

Pip install pymysqlpip install document

Script

#-*-coding: utf-8-*-import pymysqlfrom docx import Documentfrom docx.shared import Ptfrom docx.oxml.ns import qndb = pymysql.connect (host='127.0.0.1', # database server IP port=3306, user='root', passwd='123456' Db='test_db') # Database name) # query the corresponding field-related information based on the table name def query (tableName): # Open the database connection cur = db.cursor () sql = "select b.ColumNames name 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.TABLENNAME = b.TABLE_NAME where a.TABLEdiagram name'"+ tableName+"cur.execute (sql) data = cur.fetchall () cur.close return data# queries all table names under the current library Table name: tableName Table name + comments (used to populate the word document): 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# generates the word structure for each table Output to word document def generateWord (singleTableData,document,tableName): p=document.add_paragraph () p.paragraph_format.line_spacing=1.5 # set the paragraph line spacing to 1.5x p.paragraph_format.space_after=Pt (0) # set paragraph 0 lb # document.add_paragraph (tableName Style='ListBullet') r=p.add_run ('\ n'+tableName) r.font.namebou 'r.font.size=Pt (12) table = document.add_table (rows=len (singleTableData) + 1, cols=3,style='Table Grid') table.style.font.size=Pt (11) table.style.font.name=u'Calibri' # set header style # here only three headers are generated You can modify for i in ((0meme name), (1meme'), (2memet')): run = table.cell (0meme I [0]). Paragraphs [0] .add _ run (I [1]) run.font.name = 'Calibri' run.font.size = Pt (11) r = run._element r.rPr.rFonts.set (' wrapper eastAsia') For i in range (len (singleTableData)): # style the data in the table for j in range (len (singleTabledata [I])): run = table.cell (item1) 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 (' wana eastAsia`), 'Verdana') # table.cell (iTunes 1,0) .text = singleTableData [I] [1] # table.cell (iTun1) 1) .text = singleTableData [I] [2] # table.cell (iTun1,2) .text = singleTableData [I] [3] if _ _ name__ = ='_ _ main__': # define a document document = Document () # set font default style document.styles ['Normal']. Font.name = u' Arial 'document.styles [' Normal']. _ element.rPr.rFonts.set (qn ('waster Asia`)) ) # get all the table name information and table annotation information under the current database tableList = queryTableName () # Loop query the database Get the table field details, and call generateWord to generate word data # because of the rush of time, I chose to directly query the database, executed more than 100 queries, can be optimized to query all the table structures Split each table structure in the code for singleTableName in tableList: data = query (singleTableName [0]) generateWord (data,document,singleTableName [1]) # Save to document document.save ('database design .docx')

Preview of the generated word document

Thank you for reading this article carefully. I hope the article "how to use python to generate mysql database structure documents" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.