数据库和Excel文件是两种常见且重要的数据存储方式。数据库通常用于大规模数据的高效存储、管理和查询，而Excel则以其直观的界面和简单的操作方式广泛应用于数据分析、报告生成和可视化等领域。在实际工作中，可能需要在这两者之间进行数据的导入与导出。例如，从数据库中提取数据到Excel进行深入分析和图表绘制，或者将Excel文件中的数据清洗整理后导入数据库进行集中管理和进一步处理。而Python因其便捷性和丰富的生态，能够帮助用户通过简单的代码处理数据库与Excel文件之间数据转换。
本文将介绍如何使用Python有效地实现数据库与Excel文件之间的数据自动化导入与导出，以SQLite数据库为例。
文章目录
- 用Python将数据库数据导出到Excel表格
- 用Python将Excel表格数据导入到数据库
本文所使用的方法需要用到sqlite3（Python标准库中的组件）和Spire.XLS for Python（PyPI: pip install Spire.XLS）。
用Python将数据库数据导出到Excel表格
我们可以使用 sqlite3 模块从数据库读取数据，并利用 Spire.XLS 模块创建 Excel 文件并将数据写入其中，从而实现数据库数据的导出。详细步骤如下：
- 导入必要的模块。
- 连接数据库：使用
sqlite3.connect()连接 SQLite 数据库，并创建游标cursor执行 SQL 命令。 - 获取表名：执行 SQL 查询获取数据库中所有表的名称，并存储在
tableNames列表中。 - 创建 Excel 工作簿：初始化一个
Workbook对象，并使用Workbook.Worksheets.Clear()方法清除默认工作表。 - 遍历数据库表：对于
tableNames中的每一个表名：- 查询表的列信息，提取列名并添加到
columnNames列表中。 - 获取表中的所有数据行到
rows中。 - 使用
Workbook.Worksheets.Add(sheetname)方法在 Excel 中添加一个以表名命名的新工作表。 - 使用
Worksheet.Range[row, col].Value属性将columnNames作为标题写入工作表。 - 遍历数据行，并使用相同的属性将数据写入对应的单元格。
- 格式化工作表。
- 查询表的列信息，提取列名并添加到
- 使用
Workbook.SaveToFile()方法将工作簿保存到文件。 - 释放
workbook的资源并关闭数据库连接。
代码示例
from spire.xls import *
from spire.xls.common import *
import sqlite3
# 连接到数据库
conn = sqlite3.connect("output/CompanyInfo.db")
cursor = conn.cursor()
# 获取数据库中所有的表名
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tableNames = [name[0] for name in cursor.fetchall()]
# 创建 Excel 文件
workbook = Workbook()
workbook.Worksheets.Clear()
# 遍历数据库中的每个表
for tableName in tableNames:
# 获取表的列名
cursor.execute(f"PRAGMA table_info('{tableName}')")
columnsInfo = cursor.fetchall()
columnNames = [columnInfo[1] for columnInfo in columnsInfo]
# 获取表的数据
cursor.execute(f"SELECT * FROM {tableName}")
rows = cursor.fetchall()
# 创建工作表
sheet = workbook.Worksheets.Add(tableName)
# 将标题行写入工作表
for i in range(len(columnNames)):
sheet.Range[1, i + 1].Value = columnNames[i]
# 将数据写入工作表
for j in range(1, len(rows)):
column = rows[j]
for k in range(len(column)):
sheet.Range[j + 1, k + 1].Value = column[k]
# 设置工作表格式
sheet.AllocatedRange.Style.Font.FontName = "Times New Roman"
sheet.AllocatedRange.Style.Font.Size = 12.0
sheet.AllocatedRange.AutoFitRows()
sheet.AllocatedRange.AutoFitColumns()
# 保存 Excel 文件
workbook.SaveToFile("output/DatabaseToExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()
conn.close()
结果
用Python将Excel表格数据导入到数据库
我们也可以使用 Spire.XLS 从 Excel 文件读取各种数据类型，然后使用 sqlite3 将数据写入数据库。详细步骤如下：
- 导入必要的模块。
- 创建
Workbook实例：初始化一个Workbook对象以操作 Excel 工作簿。 - 加载 Excel 文件：使用
LoadFromFile方法从指定路径加载 Excel 文件。 - 连接数据库：使用
sqlite3.connect()连接 SQLite 数据库，并创建游标cursor执行 SQL 命令。 - 遍历工作表：对于工作簿中的每个工作表：
- 使用
Worksheet.Name属性获取工作表对象和名称，并去除名称中的空格。 - 提取标题：使用
Worksheet.Range[row, col].Value属性收集第一行数据作为数据库表的列名，并去除空格。 - 创建数据库表：基于提取的标题动态生成 SQL 语句以创建表（如果不存在）。
- 插入数据：遍历工作表的每一行，使用
Worksheet.Range[row, col].Value属性收集数据，然后构造 SQL 插入语句将数据插入相应的数据库表。
- 使用
- 提交并关闭数据库连接：将所有更改提交到数据库并关闭数据库连接。
- 释放
Workbook资源：清理Workbook对象所使用的资源。
代码示例
from spire.xls import *
from spire.xls.common import *
import sqlite3
# 创建 Workbook 实例
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("Sample.xlsx")
# 连接到数据库
conn = sqlite3.connect("output/ExcelToDatabase.db")
cursor = conn.cursor()
for s in range(workbook.Worksheets.Count):
# 获取一个工作表
sheet = workbook.Worksheets.get_Item(s)
# 获取工作表名称
sheetName = sheet.Name
sheetName = sheetName.replace(" ", "")
# 获取标题行中的数据
header = []
for i in range(sheet.AllocatedRange.ColumnCount):
headerValue = sheet.Range[1, i + 1].Value
headerValue = headerValue.replace(" ", "")
header.append(headerValue)
# 创建数据库表
createTableSql = f"CREATE TABLE IF NOT EXISTS {sheetName} ({', '.join([f'{header[i]} TEXT' for i in range(len(header))])})"
cursor.execute(createTableSql)
# 插入数据到数据库表中
for row in range(1, sheet.AllocatedRange.RowCount):
data = []
for col in range(sheet.AllocatedRange.ColumnCount):
# 获取单元格值
value = sheet.Range[row + 1, col + 1].Value
data.append(value)
# 插入单元格值到数据库表中
insertSql = f"INSERT INTO {sheetName} ({', '.join(header)}) VALUES ({', '.join(['?' for _ in data])})"
cursor.execute(insertSql, data)
# 提交更改并关闭连接
conn.commit()
conn.close()
workbook.Dispose()
结果
本文展示了如何使用 Python 代码在 Excel 工作簿和数据库之间进行数据的导入与导出。
有关更多 Excel 文件处理技巧，请访问 Spire.XLS for Python教程。
