利用Python批量導(dǎo)出mysql數(shù)據(jù)庫表結(jié)構(gòu)的操作實(shí)例

第利用Python批量導(dǎo)出mysql數(shù)據(jù)庫表結(jié)構(gòu)的操作實(shí)例目錄前言解決方法1.mysql數(shù)據(jù)庫表信息查詢2.連接數(shù)據(jù)庫代碼3.數(shù)據(jù)查詢處理代碼3.0配置信息3.1查詢數(shù)據(jù)庫表3.2查詢對(duì)應(yīng)表結(jié)構(gòu)3.3pandas進(jìn)行數(shù)據(jù)保存導(dǎo)出excel補(bǔ)充：python腳本快速生成mysql數(shù)據(jù)庫結(jié)構(gòu)文檔總結(jié)

前言

最近在公司售前售后同事遇到一些奇怪的需求找到我，需要提供公司一些項(xiàng)目數(shù)據(jù)庫所有表的結(jié)構(gòu)信息（字段名、類型、長度、是否主鍵、***、備注），雖然不是本職工作，但是作為python技能的擁有者看到這種需求還是覺得很容易的，但是如果不用代碼解決確實(shí)非常棘手和浪費(fèi)時(shí)間。于是寫了一個(gè)輕量小型項(xiàng)目來解決一些燃眉之急，希望能對(duì)一些人有所幫助，代碼大神、小神可以忽略此貼。

代碼直達(dá):GITEE、GitHub

解決方法

1.mysql數(shù)據(jù)庫表信息查詢

想要導(dǎo)出mysql數(shù)據(jù)庫表結(jié)構(gòu)必須了解一些相關(guān)數(shù)據(jù)庫知識(shí)，mysql數(shù)據(jù)庫支持通過SQL語句進(jìn)行表信息查詢：

查詢數(shù)據(jù)庫所有表名

SHOWTABLES

查詢對(duì)應(yīng)數(shù)據(jù)庫對(duì)應(yīng)表結(jié)構(gòu)信息

SELECTCOLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,IS_NULLABLE,COLUMN_COMMENT

FROMinformation_schema.`COLUMNS`

WHERETABLE_SCHEMA='{dbName}'ANDTABLE_NAME='{tableName}'

COLUMN_NAME：字段名COLUMN_TYPE：數(shù)據(jù)類型COLUMN_KEY：主鍵IS_NULLABLE：非空COLUMN_COMMENT：字段描述

還有一些其他字段，有需要可自行百度

2.連接數(shù)據(jù)庫代碼

以下是一個(gè)較為通用的mysql數(shù)據(jù)庫連接類，創(chuàng)建MysqlConnection類，放入對(duì)應(yīng)數(shù)據(jù)庫連接信息即可使用sql,通過query查詢、update增刪改、close關(guān)閉連接。

*注：數(shù)據(jù)量過大時(shí)不推薦直接使用query查詢。

importpymysql

classMysqlConnection():

def__init__(self,host,user,passw,port,database,charset="utf8"):

self.db=pymysql.connect(host=host,user=user,password=passw,port=port,

database=database,charset=charset)

self.cursor=self.db.cursor()

defquery(self,sql):

self.cursor.execute(sql)

results=self.cursor.fetchall()

returnresults

#增刪改

defupdate(self,sql):

try:

self.cursor.execute(sql)

mit()

return1

exceptExceptionase:

print(e)

self.db.rollback()

return0

#關(guān)閉連接

defclose(self):

self.cursor.close()

self.db.close()

3.數(shù)據(jù)查詢處理代碼

3.0配置信息

config.yml,這里使用了配置文件進(jìn)行程序參數(shù)配置，方便配置一鍵運(yùn)行

#數(shù)據(jù)庫信息配置

db_config:

host:#數(shù)據(jù)庫所在服務(wù)IP

port:3306#數(shù)據(jù)庫服務(wù)端口

username:root#~用戶名

password:12346#~密碼

charset:utf8

#需要進(jìn)行處理的數(shù)據(jù)名稱列表《《填入數(shù)據(jù)庫名

db_names:['db_a','db_b']

#導(dǎo)出配置

excel_conf:

#導(dǎo)出結(jié)構(gòu)Excel表頭，長度及順序不可調(diào)整，僅支持更換名稱

column_name:['字段名','數(shù)據(jù)類型','長度','主鍵','非空','描述']

save_dir:./data

讀取配置文件的代碼

importyaml

classConfigure():

def__init__(self):

withopen("config.yaml",'r',encoding='utf-8')asf:

self._conf=yaml.load(f.read(),Loader=yaml.FullLoader)

defget_db_config(self):

host=self._conf['db_config']['host']

port=self._conf['db_config']['port']

username=self._conf['db_config']['username']

password=self._conf['db_config']['password']

charset=self._conf['db_config']['charset']

db_names=self._conf['db_config']['db_names']

returnhost,port,username,password,charset,db_names

defget_excel_title(self):

title=self._conf['excel_conf']['column_name']

save_dir=self._conf['excel_conf']['save_dir']

returntitle,save_dir

3.1查詢數(shù)據(jù)庫表

利用上面創(chuàng)建的數(shù)據(jù)庫連接和SQL查詢獲取所有表

classExportMysqlTableStructureInfoToExcel():

def__init__(self):

conf=Configure()#獲取配置初始化類信息

self.__host,self.__port,self.__username,self.__password,self.__charset,self.db_names=conf.get_db_config()

self.__excel_title,self.__save_dir=conf.get_excel_title()

```省略```

def__connect_to_mysql(self,database):#獲取數(shù)據(jù)庫連接方法

connect=MysqlConnection(self.__host,

self.__username,

self.__password,

self.__port,database,

self.__charset)

returnconnect

def__get_all_tables(self,con):#查詢所有表

res=con.query("SHOWTABLES")

tb_list=[]

foriteminres:

tb_list.append(item[0])

returntb_list

``````

3.2查詢對(duì)應(yīng)表結(jié)構(gòu)

循環(huán)獲取每一張表的結(jié)構(gòu)數(shù)據(jù)，根據(jù)需要對(duì)中英文做了一些轉(zhuǎn)換，字段長度可以從類型中分離出來，這里使用yield返回?cái)?shù)據(jù)，可以利用生成器加速處理過程（外包導(dǎo)出保存和數(shù)據(jù)庫查詢可以并行）

classExportMysqlTableStructureInfoToExcel():

```省略```

def__struct_of_table_generator(self,con,db_name):

tb_list=self.__get_all_tables(con)

forindex,tb_nameinenumerate(tb_list):

sql="SELECTCOLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,IS_NULLABLE,COLUMN_COMMENT"\

"FROMinformation_schema.`COLUMNS`WHERETABLE_SCHEMA='{}'ANDTABLE_NAME='{}'".format(db_name,tb_name)

res=con.query(sql)

struct_list=[]

foriteminres:

column_name,column_type,column_key,is_nullable,column_comment=item

length="0"

ifstr(column_type).find('(')-1:

column_type,length=str(column_type).replace(")",'').split('(')

ifcolumn_key=='PRI':

column_key="是"

else:

column_key=''

ifis_nullable=='YES':

is_nullable='是'

else:

is_nullable='否'

struct_list.append([column_name,column_type,length,column_key,is_nullable,column_comment])

yield[struct_list,tb_name]

```省略```

3.3pandas進(jìn)行數(shù)據(jù)保存導(dǎo)出excel

classExportMysqlTableStructureInfoToExcel():

```省略```

defexport(self):

iflen(self.db_names)==0:

print("請(qǐng)配置數(shù)據(jù)庫列表")

fori,db_nameinenumerate(self.db_names):#對(duì)多個(gè)數(shù)據(jù)庫進(jìn)行處理

connect=self.__connect_to_mysql(db_name)#獲取數(shù)據(jù)庫連接

ifnotos.path.exists(self.__save_dir):#判斷數(shù)據(jù)導(dǎo)出保存路徑是否存在

os.mkdir(self.__save_dir)

file_name=os.path.join(self.__save_dir,'{}.xlsx'.format(db_name))#用數(shù)據(jù)庫名命名導(dǎo)出Excel文件

ifnotos.path.exists(file_name):#文件不存在時(shí)自動(dòng)創(chuàng)建文件excel

wrokb=openpyxl.Workbook()

wrokb.save(file_name)

wrokb.close()

wb=openpyxl.load_workbook(file_name)

writer=pd.ExcelWriter(file_name,engine='openpyxl')

writer.book=wb

struct_generator=self.__struct_of_table_generator(connect,db_name)#獲取表結(jié)構(gòu)信息的生成器

fortb_infointqdm(struct_generator,desc=db_name):#從生成器中獲取表結(jié)構(gòu)并利用pandas進(jìn)行格式化保存，寫入Excel文件

s_list,tb_name=tb_info

data=pd.DataFrame(s_list,columns=self.__excel_title)

data.to_excel(writer,sheet_name=tb_name)

writer.close()

connect.close()

```省略```

補(bǔ)充：python腳本快速生成mysql數(shù)據(jù)庫結(jié)構(gòu)文檔

由于數(shù)據(jù)表太多，手動(dòng)編寫耗費(fèi)的時(shí)間太久，所以搞了一個(gè)簡(jiǎn)單的腳本快速生成數(shù)據(jù)庫結(jié)構(gòu)，保存到word文檔中。

1.安裝pymysql和document

pipinstallpymysql

pipinstalldocument

2.腳本

#-*-coding:utf-8-*-

importpymysql

fromdocximportDocument

fromdocx.sharedimportPt

fromdocx.oxml.nsimportqn

db=pymysql.connect(host='',#數(shù)據(jù)庫服務(wù)器IP

port=3306,

user='root',

passwd='123456',

db='test_db')#數(shù)據(jù)庫名稱)

#根據(jù)表名查詢對(duì)應(yīng)的字段相關(guān)信息

defquery(tableName):

#打開數(shù)據(jù)庫連接

cur=db.cursor()

sql="selectb.COLUMN_NAME,b.COLUMN_TYPE,b.COLUMN_COMMENTfrom(select*frominformation_schema.`TABLES`whereTABLE_SCHEMA='test_db')arightjoin(select*frominformation_schema.`COLUMNS`whereTABLE_SCHEMA='test_db_test')bona.TABLE_NAME=b.TABLE_NAMEwherea.TABLE_NAME='"+tableName+"'"

cur.execute(sql)

data=cur.fetchall()

cur.close

returndata

#查詢當(dāng)前庫下面所有的表名，表名：tableName；表名+注釋(用于填充至word文檔)：concat(TABLE_NAME,'(',TABLE_COMMENT,')')

defqueryTableName():

cur=db.cursor()

sql="selectTABLE_NAME,concat(TABLE_NAME,'(',TABLE_COMMENT,')')frominformation_schema.`TABLES`whereTABLE_SCHEMA='test_db_test'"

cur.execute(sql)

data=cur.fetchall()

returndata

#將每個(gè)表生成word結(jié)構(gòu)，輸出到word文檔

defgenerateWord(singleTableData,document,tableName):

p=document.add_paragraph()

p.paragraph_format.line_spacing=1.5#設(shè)置該段落行間距為1.5倍

p.paragraph_format.space_after=Pt(0)#設(shè)置段落段后0磅

#document.add_paragraph(tableName,)

r=p.add_run('\n'+tableName)

=u'宋體'

r.font.size=Pt(12)

table=document.