Python实现数据库与Excel文件之间的数据自动化导入与导出

风迪

引言

数据库和Excel文件是两种常见且重要的数据存储方式。数据库通常用于大规模数据的高效存储、管理和查询，而Excel则以其直观的界面和简单的操作方式广泛应用于数据分析、报告生成和可视化等领域。在实际工作中，可能需要在这两者之间进行数据的导入与导出。例如，从数据库中提取数据到Excel进行深入分析和图表绘制，或者将Excel文件中的数据清洗整理后导入数据库进行集中管理和进一步处理。而Python因其便捷性和丰富的生态，能够帮助用户通过简单的代码处理数据库与Excel文件之间数据转换。
本文将介绍如何使用Python有效地实现数据库与Excel文件之间的数据自动化导入与导出，以SQLite数据库为例。
本文所使用的方法需要用到sqlite3（Python标准库中的组件）和Spire.XLS for Python（PyPI:

1. pip install Spire.XLS

复制代码

）。

用Python将数据库数据导出到Excel表格

我们可以使用

1. sqlite3

复制代码

模块从数据库读取数据，并利用

1. Spire.XLS

复制代码

模块创建 Excel 文件并将数据写入其中，从而实现数据库数据的导出。详细步骤如下：

• 导入必要的模块。
  
• 连接数据库：使用
  1. sqlite3.connect()

  复制代码
  连接 SQLite 数据库，并创建游标
  1. cursor

  复制代码
  执行 SQL 命令。
  
• 获取表名：执行 SQL 查询获取数据库中所有表的名称，并存储在
  1. tableNames

  复制代码
  列表中。
  
• 创建 Excel 工作簿：初始化一个
  1. Workbook

  复制代码
  对象，并使用
  1. Workbook.Worksheets.Clear()

  复制代码
  方法清除默认工作表。
  
• 遍历数据库表：对于
  1. tableNames

  复制代码
  中的每一个表名：
  
  
  
  • 查询表的列信息，提取列名并添加到
    1. columnNames

    复制代码
    列表中。
    
  • 获取表中的所有数据行到
    1. rows

    复制代码
    中。
    
  • 使用
    1. Workbook.Worksheets.Add(sheetname)

    复制代码
    方法在 Excel 中添加一个以表名命名的新工作表。
    
  • 使用
    1. Worksheet.Range[row, col].Value

    复制代码
    属性将
    1. columnNames

    复制代码
    作为标题写入工作表。
    
  • 遍历数据行，并使用相同的属性将数据写入对应的单元格。
    
  • 格式化工作表。
    
  
  
• 使用
  1. Workbook.SaveToFile()

  复制代码
  方法将工作簿保存到文件。
  
• 释放
  1. workbook

  复制代码
  的资源并关闭数据库连接。
  

代码示例

1. from spire.xls import *
   
2. from spire.xls.common import *
   
3. import sqlite3
   
4. 
   
5. # 连接到数据库
   
6. conn = sqlite3.connect("output/CompanyInfo.db")
   
7. cursor = conn.cursor()
   
8. 
   
9. # 获取数据库中所有的表名
   
10. cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    
11. tableNames = [name[0] for name in cursor.fetchall()]
    
12. 
    
13. # 创建 Excel 文件
    
14. workbook = Workbook()
    
15. workbook.Worksheets.Clear()
    
16. 
    
17. # 遍历数据库中的每个表
    
18. for tableName in tableNames:
    
19.     # 获取表的列名
    
20.     cursor.execute(f"PRAGMA table_info('{tableName}')")
    
21.     columnsInfo = cursor.fetchall()
    
22.     columnNames = [columnInfo[1] for columnInfo in columnsInfo]
    
23. 
    
24.     # 获取表的数据
    
25.     cursor.execute(f"SELECT * FROM {tableName}")
    
26.     rows = cursor.fetchall()
    
27.    
    
28.     # 创建工作表
    
29.     sheet = workbook.Worksheets.Add(tableName)
    
30.    
    
31.     # 将标题行写入工作表
    
32.     for i in range(len(columnNames)):
    
33.         sheet.Range[1, i + 1].Value = columnNames[i]
    
34.    
    
35.     # 将数据写入工作表
    
36.     for j in range(1, len(rows)):
    
37.         column = rows[j]
    
38.         for k in range(len(column)):
    
39.             sheet.Range[j + 1, k + 1].Value = column[k]
    
40.    
    
41.     # 设置工作表格式
    
42.     sheet.AllocatedRange.Style.Font.FontName = "Times New Roman"
    
43.     sheet.AllocatedRange.Style.Font.Size = 12.0
    
44.     sheet.AllocatedRange.AutoFitRows()
    
45.     sheet.AllocatedRange.AutoFitColumns()
    
46. 
    
47. # 保存 Excel 文件
    
48. workbook.SaveToFile("output/DatabaseToExcel.xlsx", FileFormat.Version2016)
    
49. workbook.Dispose()
    
50. conn.close()

复制代码

结果

用Python将Excel表格数据导入到数据库

我们也可以使用

1. Spire.XLS

复制代码

从 Excel 文件读取各种数据类型，然后使用

1. sqlite3

复制代码

将数据写入数据库。详细步骤如下：

• 导入必要的模块。
  
• 创建
  1. Workbook

  复制代码
  实例：初始化一个
  1. Workbook

  复制代码
  对象以操作 Excel 工作簿。
  
• 加载 Excel 文件：使用
  1. LoadFromFile

  复制代码
  方法从指定路径加载 Excel 文件。
  
• 连接数据库：使用
  1. sqlite3.connect()

  复制代码
  连接 SQLite 数据库，并创建游标
  1. cursor

  复制代码
  执行 SQL 命令。
  
• 遍历工作表：对于工作簿中的每个工作表：
  
  
  
  • 使用
    1. Worksheet.Name

    复制代码
    属性获取工作表对象和名称，并去除名称中的空格。
    
  • 提取标题：使用
    1. Worksheet.Range[row, col].Value

    复制代码
    属性收集第一行数据作为数据库表的列名，并去除空格。
    
  • 创建数据库表：基于提取的标题动态生成 SQL 语句以创建表（如果不存在）。
    
  • 插入数据：遍历工作表的每一行，使用
    1. Worksheet.Range[row, col].Value

    复制代码
    属性收集数据，然后构造 SQL 插入语句将数据插入相应的数据库表。
    
  
  
• 提交并关闭数据库连接：将所有更改提交到数据库并关闭数据库连接。
  
• 释放
  1. Workbook

  复制代码
  资源：清理
  1. Workbook

  复制代码
  对象所使用的资源。
  

代码示例

1. from spire.xls import *
   
2. from spire.xls.common import *
   
3. import sqlite3
   
4. 
   
5. # 创建 Workbook 实例
   
6. workbook = Workbook()
   
7. 
   
8. # 加载 Excel 文件
   
9. workbook.LoadFromFile("Sample.xlsx")
   
10. 
    
11. # 连接到数据库
    
12. conn = sqlite3.connect("output/ExcelToDatabase.db")
    
13. cursor = conn.cursor()
    
14. 
    
15. for s in range(workbook.Worksheets.Count):
    
16.     # 获取一个工作表
    
17.     sheet = workbook.Worksheets.get_Item(s)
    
18. 
    
19.     # 获取工作表名称
    
20.     sheetName = sheet.Name
    
21.     sheetName = sheetName.replace(" ", "")
    
22. 
    
23.     # 获取标题行中的数据
    
24.     header = []
    
25.     for i in range(sheet.AllocatedRange.ColumnCount):
    
26.         headerValue = sheet.Range[1, i + 1].Value
    
27.         headerValue = headerValue.replace(" ", "")
    
28.         header.append(headerValue)
    
29. 
    
30.     # 创建数据库表
    
31.     createTableSql = f"CREATE TABLE IF NOT EXISTS {sheetName} ({', '.join([f'{header[i]} TEXT' for i in range(len(header))])})"
    
32.     cursor.execute(createTableSql)
    
33. 
    
34.     # 插入数据到数据库表中
    
35.     for row in range(1, sheet.AllocatedRange.RowCount):
    
36.         data = []
    
37.         for col in range(sheet.AllocatedRange.ColumnCount):
    
38.             # 获取单元格值
    
39.             value = sheet.Range[row + 1, col + 1].Value
    
40.             data.append(value)
    
41.         # 插入单元格值到数据库表中
    
42.         insertSql = f"INSERT INTO {sheetName} ({', '.join(header)}) VALUES ({', '.join(['?' for _ in data])})"
    
43.         cursor.execute(insertSql, data)
    
44. 
    
45. # 提交更改并关闭连接
    
46. conn.commit()
    
47. conn.close()
    
48. 
    
49. workbook.Dispose()

复制代码

结果

本文展示了如何使用 Python 代码在 Excel 工作簿和数据库之间进行数据的导入与导出。
以上就是Python实现数据库与Excel文件之间的数据自动化导入与导出的详细内容，更多关于Python数据库与Excel导入与导出的资料请关注脚本之家其它相关文章！

来源:https://www.jb51.net/python/323425kra.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！