我们现有一个excel文件，并想将其导入数据库，并且想从数据库里（或代码组织的数据）将数据导出成excel
导入：
1. 读取excel文件(这里可以写死路径，也可以用相对引用路径)
首先环境中需要有 pandas xlrd xlwt包，如果没有，使用以下脚本在conda环境中安装
我们主要利用 pandas 的 dataframe 进行数据表操作，xlrd 用于 excel 文件的 读，xlwt 用于 excel 文件的 写
tips:xlrd 这个包最新的 2.0版本不支持读取 xlsx 文件
pip install pandaspip install xlrd==1.2.0pip 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')# 查看 dfprint(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-8connect = 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语句mit() # 提交cursor.close()
结果显示如下
数据库中已有数据，问题解决
导出：
这里提供两种导出方式：
1. 通过表名直接从数据库导出
2. 利用pandas的dataframe导出
1. 通过表名导出excel：
import xlwtdef export_excel(table_name):# 连接数据库，查询数据host, user, passwd, db = '127.0.0.1', 'root', '123', 'bms'cur = conn.cursor()sql = 'select * from %s' % table_namecur.execute(sql) # 返回受影响的行数fields = [field[0] for field in cur.description] # 获取所有字段名all_data = cur.fetchall() # 所有数据# 写入excelbook = xlwt.Workbook()sheet = book.add_sheet('sheet1')for col, field in enumerate(fields):sheet.write(0, col, field)row = 1for data in all_data:for col, field in enumerate(data):sheet.write(row, col, field)row += 1book.save("%s.xlsx" % table_name)export_excel("testinfo")
执行后，产生如下文件：
2.利用pandas的dataframe导出
提前在目录下新建好 outp 文件夹，用于接收输出文件
我们先通过查询数据库中的表，然后利用 pandasdataframe 的 to_excel 方法直接导出
strsql = 'select * from testinfo'conn = conn()cursor = conn.cursor()cursor.execute(strsql) # 执行sql语句sqldata = cursor.fetchall() # 获取执行查询语句获得的结果数据df = pd.DataFrame(data=sqldata) # 数据送入 dataframecursor.close()print(df) # 查看 dataframe# to_excel 导出df.to_excel('outp/testinfo.xls', sheet_name='sheet1')
执行后在 outp 目录下已输出 excel 文件
希望可以解决你的问题
