python_从sqlite导出数据至Excel

cursor.fetchall()的方式

import sqlite3
from openpyxl import Workbook

# 数据库文件名
db_file = 'D:\\desktop\\ni.db'

# XLSX 文件名
xlsx_file = 'D:\\desktop\\output2.xlsx'

# 连接到数据库
conn = sqlite3.connect(db_file)
cursor = conn.cursor()

# 执行查询
cursor.execute("SELECT * FROM 日常测试2")

# 获取所有行
rows = cursor.fetchall()

# 获取列名
column_names = [description[0] for description in cursor.description]

# 创建一个新的工作簿
workbook = Workbook()
worksheet = workbook.active

# 写入列名
worksheet.append(column_names)

# 写入数据行
for row in rows:
    worksheet.append(row)

# 保存工作簿
workbook.save(xlsx_file)

# 关闭游标和连接
cursor.close()
conn.close()

print(f"Data has been exported to {xlsx_file}")

pd.read_sql()的方式

import pandas as pd
import sqlite3

output_file_path='D:\\desktop\\test.xlsx'

# 连接到 SQLite 数据库
conn = sqlite3.connect('D:\\desktop\\ni.db')

# 查询所有订单
query = "SELECT * FROM 日常测试2"
df = pd.read_sql(query, conn)

# 关闭连接
conn.close()

# 将 DataFrame 写入 Excel 文件
df.to_excel(output_file_path, index=False)

# 输出确认信息
print(f"DataFrame has been written to {output_file_path}")