Python解决 excel导入数据库 & Python读取数据库并保存 excel文件（多种方法&详细讲解）

我们现有一个excel文件，并想将其导入数据库，并且想从数据库里（或代码组织的数据）将数据导出成excel

导入：

1. 读取excel文件(这里可以写死路径，也可以用相对引用路径)

首先环境中需要有 pandas xlrd xlwt 包，如果没有，使用以下脚本在conda环境中安装

我们主要利用 pandas 的 dataframe 进行数据表操作，xlrd 用于 excel 文件的 读，xlwt 用于 excel 文件的 写

tips: xlrd 这个包最新的 2.0版本不支持读取 xlsx 文件

pip install pandas
pip install xlrd==1.2.0
pip install xlwt==1.3.0

在路径字符串前面 + r 是为了屏蔽转义符导致出现问题 (可以去掉 r 对比试试即可)

import pandas as pd

# 以下两种方式均可
df = pd.read_excel('xls/test.xlsx')
# 这种是路径写死的方式
df = pd.read_excel(r'D:\Code_Offline\DBExcel\xls\test.xlsx')
# 查看 df
print(df)

df 包含了 excel 表中的数据

这时候我们可以按行遍历数据，为下一步做准备

首先我们打印查看一下 dataframe 的长度，输出：

print("excel总行数为: ", len(df), "\n")

for i in range(len(df)):
    print(i)

2. 创建与SqlServer的连接 (其他数据库也一样，这里用的SqlServer)

首先需要安装 pymssql 包（SqlServer是 pymssql，mysql 是 pymysql，Oracle... 自己查阅相关资料，只是名字不同，作用一致）

pip install pymssql

同时记得引入 pymssql 包

import pymssql

连接数据库的方法

def conn():
    # 后续如果出现乱码 请调整此处的 charset 换成utf-8
    connect = pymssql.connect('.', 'sa', '123', 'DBCenter', charset='GBK')
    if connect:
        print("连接成功")
    return connect

3. 接下来就通过读取的excel向数据库中插入

我们提供两种方式

一种是通过列名寻值

另一种是通过 iloc 找固定行列（推荐）

# 第一种方式
for i in range(len(df)):
    id = df['ID'].values[i]
    name = df['NAME'].values[i]
    height = df['HEIGHT'].values[i]
    weight = df['WEIGHT'].values[i]

    strsql = f"INSERT INTO testinfo SELECT {id},'{name}',{height},{weight}"
    print(strsql)

print('1 ↑\n\n2 ↓')

# 第二种方式
# 通过 iloc 寻找固定行列值
for i in range(len(df)):
    id = df.iloc[i][0]
    name = df.iloc[i][1]
    height = df.iloc[i][2]
    weight = df.iloc[i][3]
    
    strsql = f"INSERT INTO testinfo SELECT {id},'{name}',{height},{weight}"
    print(strsql)

打印测试输出完全一致

插入数据库

当然，你表要先建好，不然没法执行 Insert 不多解释，以下为 testinfo 表的 SqlServer 建表语句

CREATE TABLE testinfo(
	id INT NOT NULL,
	name VARCHAR(64) NULL,
	height INT NULL,
	weight INT NULL
)

这里两种方法都可以导入数据库，我们使用第二种方式（有需要自己替换即可）

conn = conn()
for i in range(len(df)):
    id = df.iloc[i][0]
    name = df.iloc[i][1]
    height = df.iloc[i][2]
    weight = df.iloc[i][3]

    strsql = f"INSERT INTO testinfo SELECT {id},'{name}',{height},{weight}"
    print(strsql)

    cursor = conn.cursor()
    cursor.execute(strsql)  # 执行sql语句
    conn.commit()  # 提交
    cursor.close()

结果显示如下

数据库中已有数据，问题解决

导出：

这里提供两种导出方式：

1. 通过表名直接从数据库导出

2. 利用pandas的dataframe导出

1. 通过表名导出excel：

import xlwt

def export_excel(table_name):
    # 连接数据库，查询数据
    host, user, passwd, db = '127.0.0.1', 'root', '123', 'bms'

    cur = conn.cursor()
    sql = 'select * from %s' % table_name
    cur.execute(sql)  # 返回受影响的行数

    fields = [field[0] for field in cur.description]  # 获取所有字段名
    all_data = cur.fetchall()  # 所有数据

    # 写入excel
    book = xlwt.Workbook()
    sheet = book.add_sheet('sheet1')

    for col, field in enumerate(fields):
        sheet.write(0, col, field)

    row = 1
    for data in all_data:
        for col, field in enumerate(data):
            sheet.write(row, col, field)
        row += 1
    book.save("%s.xlsx" % table_name)

export_excel("testinfo")

执行后，产生如下文件：

2. 利用pandas的dataframe导出

提前在目录下新建好 outp 文件夹，用于接收输出文件

 

我们先通过查询数据库中的表，然后利用 pandas dataframe 的 to_excel 方法直接导出

strsql = 'select * from testinfo'

conn = conn()
cursor = conn.cursor()
cursor.execute(strsql)  # 执行sql语句
sqldata = cursor.fetchall() # 获取执行查询语句获得的结果数据
df = pd.DataFrame(data=sqldata) # 数据送入 dataframe
cursor.close()
print(df) # 查看 dataframe

# to_excel 导出
df.to_excel('outp/testinfo.xls', sheet_name='sheet1')

执行后在 outp 目录下已输出 excel 文件 

 

希望可以解决你的问题