Pandas中to_sql方法中参数method回调函数

各位在用Python做数据批量导入导出时,不妨试试用Pandas来进行,我从实际使用的感受总结的有以下优点:

1. 相较于openpyxl库load_workbook方法读取Excel对象速度快了3倍不止,具体的我没测试,但是这种读取速度是比肉眼可见的还要明显的快速;
2. 相较于其他操作Excel的库来说(除了私人定制化)兼容性更强,不用在乎用户上传的是.xlsx还是.xls格式的文件;
3. 第一点说的是读取Excel工作簿对象的速度(当然Pandas没有读取工作簿对象这么一说),数据的读取数据以及处理速度相较于其他操作Excel库更快,即使你使用生成器也没有Pandas快,可能是因为Pandas的底层是使用C来实现的,慢慢研究看一下;

当然Pandas这个库也有一些缺点,但是目前为止我9成的需求它都能很好的应付,例如:

1. Pandas在读取工作簿时需要一个在文件系统存在的Excel文件,也就是read_xxx方法的io参数需要传入文件绝对路径,不能像openpyxl库load_workbook方法可以使用临时文件对象(tempfile库的临时文件对象),也无法直接将处理好的文件直接保存为字节对象(openpyxl库的save_virtual_workbook方法可以直接将写好的文件保存为字节对象来直接返回前端或者进行base64编码处理);
2. Pandas在读取操作数据时对于批量读取时对于单元格格式有些许苛刻的限制,不要出现较为复杂合并单元格情况;
3. Pandas在复杂图表方面也表现不佳;

总的来说业务要求的很复杂时,你就不要选用Pandas了,用openpyxl或者(xlrd,xlwd),接下来言归正传;

 

首先我需要读取Excel文件,来获取数据DataFrame对象;

# 读取Excel,取第0,1,2,5,6列的数据,返回一个DataFrame对象,这里我选取的是默认第一个sheet工作表,其他参数用法意义请自行查看官网,源码以及百度咨询
df = pd.read_excel(excel_file_path, usecols=[0, 1, 2, 5, 6])

之后你可以用df的各种方法来处理你的数据转换个类型啊,或者做个求和等等一系列操作,数据的处理工作整备完毕,现在开始存储;

存储数据我选用Pandas的to_sql方法,如果你存入的数据没有很多表关联关系的话;

from sqlalchemy import create_engine


# 这是数据库连接的字符串,根据你的数据库组合不同的连接字符串,我这里示例的是PG数据库,其他的自行百度一下
conn_str = "postgresql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}".format(
                USERNAME="postgres",
                PASSWORD="Js747142549",
                HOST="127.0.0.1",
                DATABASE="toupiaoyi_db",
                PORT=55433)
# 存入数据库
conn = create_engine(conn_str)  # 创建数据库连接驱动,供to_sql方法调用,一定要用sqlalchemy库的create_engine方法来进行实例化,你要不信邪,你可以试试connection看看能行不,我反正试过三个数据库的Oracle,PG,Mysql的都不行

# 调用to_sql方法,参数讲解:
# name:存入数据表的表名,
# schema:模式名
# con:数据库连接驱动对象
# if_exists: 如果表已经存在，该如何表现,参数值的意义自行百度一下,就三个参数很简单
# index: 是否将DataFrame对象的索引列写入,它的配合使用的参数index_label的方法看一下官网
# chunksize:一次写入多少数据,行数
# method:sql的回调函数,特别说一下此处,该回调函数只有四个默认参数
        # table:Pandas的table对象,table.name可以获取数据表名,table.schema可以获取模式名
        # conn:数据库驱动连接对象
        # keys:要存入的字段名
        # data_iter:DataFrame对象也就是数据迭代器

df.to_sql(name="blob_toupiaoinformationmodel",
                      schema="public",
                      con=conn,
                      if_exists="append",
                      index=False, chunksize=2000, method=UploadExcelViewSet.insert_func)

接下来我贴一下回调函数的细节

    def insert_func(table, conn, keys, data_iter):
        """
        Pandas中to_sql方法的回调函数
        :param table:Pandas的table
        :param conn:数据库驱动连接对象
        :param keys:要存入的字段名
        :param data_iter:DataFrame对象也就是数据迭代器
        :return:
        """
        dbapi_conn = conn.connection
        # 创建数据库游标对象
        with dbapi_conn.cursor() as cursor:
            # 遍历拼接sql语句
            for data_tuple in data_iter:
                sql = """INSERT INTO {TABLE_NAME}(bill_name, room_number, bind_status, community_name, area_m) VALUES('{BILL_NAME}', '{ROOM_NUMBER}', {BIND_STATUS}, '{COMMUNITY_NAME}', {AREA_M}) ON conflict({UNIQUE_LIST}) DO UPDATE SET bill_name='{BILL_NAME}', bind_status={BIND_STATUS}, area_m='{AREA_M}'""".format(
                    TABLE_NAME=table.name, UNIQUE_LIST="community_name, room_number",
                    BILL_NAME=data_tuple[0], ROOM_NUMBER=data_tuple[2], COMMUNITY_NAME=data_tuple[1],
                    BIND_STATUS=data_tuple[3], AREA_M=data_tuple[4])
                cursor.execute(sql)

到此处多数的人应该都能明白了吧,一般的情况使用to_sql的默认存储方法即可不用实现回调函数,但是如果你有多表关联或者先查询是否存在,存在修改不存在插入等复杂操作时需要你自己实现回调函数来直接处理to_sql的过程