python应用-excel和数据库的读取及写入操作

近日完成一个交办任务，从excel表读取数据，根据ID在数据库表匹配相应的记录，并回填至excel表里。我使用的工具是python。下面记录下相应的模块。

一、从excel表读取数据

import pandas as pd
import numpy as npdef read_excel():path = "导出模板.xlsx"sheetname = "Sheet1"df = pd.read_excel(path, sheet_name=sheetname,index_col=None, na_values=['NA']))columns = df.columns    #读取标题rows = df.values        #读取数据集 列表listrows = np.array(rows)   #需要切片，转换np.array# print(rows[:, 0])     #切片列return rows

二、据ID在数据库表匹配相应的记录

# coding:utf-8
import pandas as pd
import pymysqlclass order:def __init__(self):self.cn = pymysql.connect(host='XX.XX.XX.XX',user='root',password='123456',database='database')#使用read_sql函数来读取数据，也可以有其他写法，只是这种更简洁def findById(self, sql):df = pd.read_sql(sql, con=self.cn)return df.values.tolist()#这种写法也可以，只是不够简洁def update_sql(self, rows):sql = "update table set c_iserr=%s where c_id=%s"cur = self.cn.cursor()cur.executemany(sql, rows)self.cn.commit()self.cn.close()# df是DataFrame格式 ，table_name是写入的目标表名'''name 指定的是目标数据库表con 与数据库链接的方式，推荐使用sqlalchemy的engine类型if_exists 当数据库中已经存在数据表时对数据表的操作，有replace替换、append追加，fail则当表存在时提示ValueErrorindex 对DataFrame的index索引的处理，=True时索引也将作为数据写入数据表chunsize 整数，一次写入数据时的数据行数量'''def write_sql(self, df, table_name):df.to_sql(name=table_name, con=self.cn,if_exists='append', index=False, chunksize=1000)

其中由于调用接口，还需要使用request模块

import requests
import datatime
import jsondef getInterface(url):riqi = datetime.datetime.now().strftime("%Y/%m")text = requests.get(url).text#若返回的是字符串，还需json.loads转为dict格式。return json.loads(text)# 数据demo如下
# {"message": "OK", "success": true, "data": [{}]}

三、回填至excel表里

以上新建class类，就可以在接下来的函数调用它。

if __name__ == '__main__':sql = "select * from table where id='%s'"db = order()#此处rows是第一点返回的列表listfor row in rows:result = db.findById(sql % (row[0]))#需要切片，转换np.arrayresult = np.array(result)#todo 逻辑写入......#再一次将rows转换为dataframedf = pd.DataFrame(rows, columns=columns)# print(df.head())pd.DataFrame(df).to_excel(path, sheet_name=sheetname, index=False)

总结，一个小任务，涉及到np.array的切片，panda操作sql和excel，dataframe和list之间转换，datatime和字符串之间转换，json和字符串之间的转换等知识。

感慨python的基础知识需要扎实才行。