Pandas 输出表结构到Excel

博主： zmx
发布时间：2021 年 07 月 20 日
811 次浏览
暂无评论
8987字数
分类： MySQL Python

# 引言

开发一时爽，一直开发一直爽，但是到了写文档的时候，那就真的火葬场了，几百张表怎么写说明？不管是项目交接还是交付客户，有一张数据库表的说明是很重要的。因此自动化生成这个Excel是有必要的。

# 准备

## 安装特定环境

1. python
2. pandas
3. pymysql
4. openpyxl

按道理来说应该还有excel相关的依赖，缺啥补啥吧。

# 初版

```Python
import pymysql
import pandas as pd
import os

path = r'z:/excelFile/result.xlsx'
dbip = "127.0.0.1"
uname = "root"
psw = "root"
db = "your_database_name"

def listAllFiles(rootDir):
    files = []
    list = os.listdir(rootDir)
    for i in range(0, len(list)):
        path = os.path.join(rootDir, list[i])
        if os.path.isdir(path):
            files.extend(listAllFiles(path))
        if os.path.isfile(path):
            files.append(path)
    return files

conn = pymysql.connect(dbip, uname, psw, db)
cursor = conn.cursor()
cursor.execute("show tables;")
resultSet = cursor.fetchall()
now = 0  # 计数器
with pd.ExcelWriter(path) as xlsx:
    for i in resultSet:  # 遍历数据表
        now += 1
        sql = """SELECT
        ORDINAL_POSITION AS 序号,
        COLUMN_NAME 列名,
        COLUMN_COMMENT 列说明,
        DATA_TYPE 数据类型,
        CHARACTER_MAXIMUM_LENGTH AS 长度,
        NUMERIC_SCALE AS 小数位数,
        (
            CASE
            WHEN column_key = 'PRI' THEN
                '√'
            ELSE
                ''
            END
        ) AS 主键,
        (
            CASE
            WHEN is_nullable = 'NO' THEN
                ''
            ELSE
                '√'
            END
        ) AS 允许空,
        COLUMN_DEFAULT 默认值
    FROM
        INFORMATION_SCHEMA. COLUMNS
    WHERE
        table_schema = '{0}'
    AND table_name = '{1}'""".format(db, i[0])

print("task:index table\t" + "current:" + str(now) + "\t" + "total:" + str(
            len(resultSet)) + "\t" + "%:" + str((now / len(resultSet)) * 100)[0:6] + "%")
        data = pd.read_sql_query(sql=sql, con=conn, index_col=None)
        data = data.sort_values(by="序号", axis=0)  # 保证顺序和sql输出的顺序一致
        data.to_excel(xlsx, sheet_name=i[0], index=None, startrow=0)

print("task:successfully")
```

## 效果图

![image.png](https://www.zunmx.top/usr/uploads/2021/07/986692585.png)

# 进阶

因为pandas不是很熟，所以采用了openpyxl来添加表头，并且间隔行不同颜色，加上样式，提高了用户体验。

```python
import pymysql
import pandas as pd
import os
import xlwt
import openpyxl
from openpyxl.styles import Alignment
from openpyxl import Workbook
from openpyxl.styles import PatternFill

path = r'z:/excelFile/result.xlsx'
dbip = "127.0.0.1"
uname = "root"
psw = "root"
db = "your_database_name"
sheetList = []

conn = pymysql.connect(dbip, uname, psw, db)
cursor = conn.cursor()
cursor.execute("show tables;")
resultSet = cursor.fetchall()
now = 0  # 计数器

with pd.ExcelWriter(path) as xlsx:
    for i in resultSet:  # 遍历数据表
        now += 1
        sql = """SELECT
        ORDINAL_POSITION AS 序号,
        COLUMN_NAME 列名,
        COLUMN_COMMENT 列说明,
        DATA_TYPE 数据类型,
        CHARACTER_MAXIMUM_LENGTH AS 长度,
        NUMERIC_SCALE AS 小数位数,
        (
            CASE
            WHEN column_key = 'PRI' THEN
                '√'
            ELSE
                ''
            END
        ) AS 主键,
        (
            CASE
            WHEN is_nullable = 'NO' THEN
                ''
            ELSE
                '√'
            END
        ) AS 允许空,
        COLUMN_DEFAULT 默认值
    FROM
        INFORMATION_SCHEMA. COLUMNS
    WHERE
        table_schema = '{0}'
    AND table_name = '{1}'""".format(db, i[0])
        # print(sql)
        print("task:index table:" + i[0] + "\t" + "current:" + str(now) + "\t" + "total:" + str(
            len(resultSet)) + "\t" + "%:" + str((now / len(resultSet)) * 100)[0:6] + "%")
        if i[0].find("_tmp") > 0 or i[0].find("_copy1") > 0 or i[0].find("_122") > 0:
            print("pass:" + i[0])
            continue
        sheetList.append(i[0])
        data = pd.read_sql_query(sql=sql, con=conn, index_col=None)
        data = data.sort_values(by="序号", axis=0)  # 保证顺序和sql输出的顺序一致
        data.to_excel(xlsx, sheet_name=i[0], index=None, startrow=1)

print("task:prepare insert header data")
workbook = openpyxl.load_workbook(path)
names = workbook.sheetnames
now = 0
for i in names:
    now += 1
    print("task:change sheet style:" + i + "\t" + "current:" + str(now) + "\t" + "total:" + str(
        len(names)) + "\t" + "%:" + str((now / len(names)) * 100)[0:6] + "%")
    sheet = workbook[i]
    cell = sheet.cell(1, 1, "表名：" + i)
    sheet.merge_cells("A1:I1")
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.fill = PatternFill(fill_type="solid", fgColor="AACF91")
    row = sheet.max_row
    for j in range(1, 10):
        cell = sheet.cell(2, j)
        sheet.column_dimensions[chr(ord('A') + j)].width = 20 - j
        cell.fill = PatternFill(fill_type="solid", fgColor="fca700")
    for j in range(3, row + 1):
        for k in range(1, 10):
            cell = sheet.cell(j, k)
            if j % 2 == 0:
                cell.fill = PatternFill(fill_type="solid", fgColor="f5f5f5")

workbook.save(path)
print("task:successfully")
```

## 效果图

![image.png](https://www.zunmx.top/usr/uploads/2021/07/420236978.png)

# 终版

金融行业需要显示数字类型长度，因此可以使用下面的代码。

```python
import pymysql
import pandas as pd
import os
import xlwt
import openpyxl
from openpyxl.styles import Alignment
from openpyxl import Workbook
from openpyxl.styles import PatternFill

path = r'z:/excelFile/result.xlsx'
dbip = "127.0.0.1"
uname = "root"
psw = "root"
db = "your_databases_name"
sheetList = []

conn = pymysql.connect(dbip, uname, psw, db)
cursor = conn.cursor()
cursor.execute("show tables;")
resultSet = cursor.fetchall()
now = 0  # 计数器

with pd.ExcelWriter(path) as xlsx:
    for i in resultSet:  # 遍历数据表
        now += 1
        sql = """SELECT
        ORDINAL_POSITION AS 序号,
        COLUMN_NAME 列名,
        COLUMN_COMMENT 列说明,
        DATA_TYPE 数据类型,
        CHARACTER_MAXIMUM_LENGTH AS 字符长度,
        NUMERIC_PRECISION AS 数字长度,
        NUMERIC_SCALE AS 小数位数,
        (
            CASE
            WHEN column_key = 'PRI' THEN
                '√'
            ELSE
                ''
            END
        ) AS 主键,
        (
            CASE
            WHEN is_nullable = 'NO' THEN
                ''
            ELSE
                '√'
            END
        ) AS 允许空,
        COLUMN_DEFAULT 默认值
    FROM
        INFORMATION_SCHEMA. COLUMNS
    WHERE
        table_schema = '{0}'
    AND table_name = '{1}'""".format(db, i[0])
        # print(sql)
        print("task:index table:" + i[0] + "\t" + "current:" + str(now) + "\t" + "total:" + str(
            len(resultSet)) + "\t" + "%:" + str((now / len(resultSet)) * 100)[0:6] + "%")
        if i[0].find("_tmp") > 0 or i[0].find("_copy1") > 0 or i[0].find("_122") > 0:
            print("pass:" + i[0])
            continue
        sheetList.append(i[0])
        data = pd.read_sql_query(sql=sql, con=conn, index_col=None)
        data = data.sort_values(by="序号", axis=0)  # 保证顺序和sql输出的顺序一致
        data.to_excel(xlsx, sheet_name=i[0], index=None, startrow=1)

print("task:prepare insert header data")
workbook = openpyxl.load_workbook(path)
names = workbook.sheetnames
now = 0
for i in names:
    now += 1
    print("task:change sheet style:" + i + "\t" + "current:" + str(now) + "\t" + "total:" + str(
        len(names)) + "\t" + "%:" + str((now / len(names)) * 100)[0:6] + "%")
    sheet = workbook[i]
    cell = sheet.cell(1, 1, "表名：" + i)
    sheet.merge_cells("A1:J1")
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.fill = PatternFill(fill_type="solid", fgColor="AACF91")
    row = sheet.max_row
    for j in range(1, 11):
        cell = sheet.cell(2, j)
        sheet.column_dimensions[chr(ord('A') + j)].width = 20 - j
        cell.fill = PatternFill(fill_type="solid", fgColor="fca700")
    for j in range(3, row + 1):
        for k in range(1, 11):
            cell = sheet.cell(j, k)
            if j % 2 == 0:
                cell.fill = PatternFill(fill_type="solid", fgColor="f5f5f5")

workbook.save(path)
print("task:successfully")
```

## 效果图

![image.png](https://www.zunmx.top/usr/uploads/2021/07/3163799083.png)

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

zunmx.fans
我 www.hao123.com实名申请友链OωO
zunmx.fans
作者小小骗人，我登陆了也看不到( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
zunmx.fans
please many update 算法 (´இ皿இ｀)boke
zunmx.fans
df1/df2 df1+df2 转置，累加----*。。。...
zunmx.fans
为何无人评论？OωO

Pandas 输出表结构到Excel

zmx • 2021 年 07 月 20 日

# 引言

# 准备

## 安装特定环境

1. python
2. pandas
3. pymysql
4. openpyxl

按道理来说应该还有excel相关的依赖，缺啥补啥吧。

# 初版

```Python
import pymysql
import pandas as pd
import os

path = r'z:/excelFile/result.xlsx'
dbip = "127.0.0.1"
uname = "root"
psw = "root"
db = "your_database_name"

print("task:successfully")
```

## 效果图

![image.png](https://www.zunmx.top/usr/uploads/2021/07/986692585.png)

# 进阶

因为pandas不是很熟，所以采用了openpyxl来添加表头，并且间隔行不同颜色，加上样式，提高了用户体验。

```python
import pymysql
import pandas as pd
import os
import xlwt
import openpyxl
from openpyxl.styles import Alignment
from openpyxl import Workbook
from openpyxl.styles import PatternFill

path = r'z:/excelFile/result.xlsx'
dbip = "127.0.0.1"
uname = "root"
psw = "root"
db = "your_database_name"
sheetList = []

conn = pymysql.connect(dbip, uname, psw, db)
cursor = conn.cursor()
cursor.execute("show tables;")
resultSet = cursor.fetchall()
now = 0  # 计数器

workbook.save(path)
print("task:successfully")
```

## 效果图

![image.png](https://www.zunmx.top/usr/uploads/2021/07/420236978.png)

# 终版

金融行业需要显示数字类型长度，因此可以使用下面的代码。

```python
import pymysql
import pandas as pd
import os
import xlwt
import openpyxl
from openpyxl.styles import Alignment
from openpyxl import Workbook
from openpyxl.styles import PatternFill

path = r'z:/excelFile/result.xlsx'
dbip = "127.0.0.1"
uname = "root"
psw = "root"
db = "your_databases_name"
sheetList = []

conn = pymysql.connect(dbip, uname, psw, db)
cursor = conn.cursor()
cursor.execute("show tables;")
resultSet = cursor.fetchall()
now = 0  # 计数器

with pd.ExcelWriter(path) as xlsx:
    for i in resultSet:  # 遍历数据表
        now += 1
        sql = """SELECT
        ORDINAL_POSITION AS 序号,
        COLUMN_NAME 列名,
        COLUMN_COMMENT 列说明,
        DATA_TYPE 数据类型,
        CHARACTER_MAXIMUM_LENGTH AS 字符长度,
        NUMERIC_PRECISION AS 数字长度,
        NUMERIC_SCALE AS 小数位数,
        (
            CASE
            WHEN column_key = 'PRI' THEN
                '√'
            ELSE
                ''
            END
        ) AS 主键,
        (
            CASE
            WHEN is_nullable = 'NO' THEN
                ''
            ELSE
                '√'
            END
        ) AS 允许空,
        COLUMN_DEFAULT 默认值
    FROM
        INFORMATION_SCHEMA. COLUMNS
    WHERE
        table_schema = '{0}'
    AND table_name = '{1}'""".format(db, i[0])
        # print(sql)
        print("task:index table:" + i[0] + "\t" + "current:" + str(now) + "\t" + "total:" + str(
            len(resultSet)) + "\t" + "%:" + str((now / len(resultSet)) * 100)[0:6] + "%")
        if i[0].find("_tmp") > 0 or i[0].find("_copy1") > 0 or i[0].find("_122") > 0:
            print("pass:" + i[0])
            continue
        sheetList.append(i[0])
        data = pd.read_sql_query(sql=sql, con=conn, index_col=None)
        data = data.sort_values(by="序号", axis=0)  # 保证顺序和sql输出的顺序一致
        data.to_excel(xlsx, sheet_name=i[0], index=None, startrow=1)

print("task:prepare insert header data")
workbook = openpyxl.load_workbook(path)
names = workbook.sheetnames
now = 0
for i in names:
    now += 1
    print("task:change sheet style:" + i + "\t" + "current:" + str(now) + "\t" + "total:" + str(
        len(names)) + "\t" + "%:" + str((now / len(names)) * 100)[0:6] + "%")
    sheet = workbook[i]
    cell = sheet.cell(1, 1, "表名：" + i)
    sheet.merge_cells("A1:J1")
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.fill = PatternFill(fill_type="solid", fgColor="AACF91")
    row = sheet.max_row
    for j in range(1, 11):
        cell = sheet.cell(2, j)
        sheet.column_dimensions[chr(ord('A') + j)].width = 20 - j
        cell.fill = PatternFill(fill_type="solid", fgColor="fca700")
    for j in range(3, row + 1):
        for k in range(1, 11):
            cell = sheet.cell(j, k)
            if j % 2 == 0:
                cell.fill = PatternFill(fill_type="solid", fgColor="f5f5f5")

workbook.save(path)
print("task:successfully")
```

## 效果图

![image.png](https://www.zunmx.top/usr/uploads/2021/07/3163799083.png)

Pandas 输出表结构到Excel

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。

Typecho 插件开发 ZSecurity

个人空间使用规则[拟定] 23/3/9修订

企业微信-摸鱼必备，避免离开状态

基于Lively wallpaper的壁纸

中公教育CTS视频下载

MySQL id重新自增录入

Java 入门 (七)

重温经典-半条命-第一章

安装原生的FastDFS

MySQL update select结果

Pandas 输出表结构到Excel

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。

Pandas 输出表结构到Excel

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。