美文网首页
python读excel/csv/txt数据,批量/逐条写入My

python读excel/csv/txt数据,批量/逐条写入My

作者: 沉思的雨季 | 来源:发表于2022-03-31 13:56 被阅读0次

    0、背景

    在数据分析的时候,需要将excel/csv/txt保存的格式化数据,通过python程序写入mysql中,以便于进行数据查询分析。处理过程是,读取excel/csv/txt -> pandas数据清洗 -> 导入Mysql库,一般是做一个表append 或者是 if exist -> replace的操作。

    1、批量方式写入

    用pandas.to_sql()方法, 以块的方式批量导入数据,优点是效率高,当数据表已存在, 可在表中追加数据,若表不存在, 导入时可直接创建表,缺点是如果表存在, 且设置了主键约束, 写入会报错。
    代码实例:

    import pandas as pd
    from sqlalchemy import create_engine
    
    
    #设置文件目录
    filePath = "d:/pythonP/人员信息.xls"
    
    #读取excel文件"明细"页签数据
    table = pd.read_excel(filePath, sheet_name="明细")
    
    #创建MySql数据库连接串, //后的参数为: 用户名, 密码, 主机, 数据库名
    engine = create_engine("mysql+pymysql://root:123456@192.168.1.86:3306/companyINF")
    
    #MySQL连接测试,验证能否连通
    try:
        pd.read_sql("show tables;", con=engine); print("connect successfully!")
    except Exception as error:
        print("connect fail! because of :", error)
    
    #用to_sql()方法插入数据,if_exists参数值:"replace"表示如果表存在, 则删掉重建该表, 重新创建;"append"表示如果表存在, 则会追加数据。
    try:
        table.to_sql("person", con=engine, index=False, if_exists="replace");
        print("insert successfully!")
    except Exception as error: 
        print("insert fail! because of:", error)
    print("data write complete!")
    

    2、逐条方式写入

    用Python的pymysql驱动,将每条数据拼接SQL语句insert into执行,适用于有主键约束的的表,直接将新数据逐条插入, 如遇到ID重复,则更新对应记录。
    代码实例:

    import pandas as pd
    import pymysql
    
    #数据库连接测试,执行sql, 提交, fetchall查询
    def connectTEST(cursor):
        try:
            cursor.execute("show tables;")
            con.commit(
            print(cursor.fetchall())
            cursor.close()
            con.close()
            print("连接测试成功!")
        except Exception as e:
            print("连接测试失败!", e)
    
    #数据处理,sheet=None返回所有sheet数据,可指定序号sheet=[0,1,2],默认第一个为0
    def dataDEAL(path, sheet=None):
        table = pd.read_excel(path, sheet)
        data = table.where(pd.notnull(table), None)     # 将缺失值用 None 填充
        return table
    
    #数据导入,获取字段个数,指定数据插入方法replace,遇到主键重复直接替换
    def dataIMP(cursor, table, to_table, method="replace"):
        len_cols = table.columns.size  #获取table字段个数
        insert_sql = "%s into %s values (%s)" % (method,to_table, "%s,"*(len_cols-1) + "%s")
        #将每行数据, 组成一条记录
        args =  (tuple(row) for _, row in table.iterrows())
        try:
            _ = cursor.executemany(insert_sql, args)
            con.commit()
        except Exception as e:
            print("fail",e)
        finally:
            cursor.close()
            con.close()
            print("数据导入完成!")
    
    
    if __name__ == '__main__':
        #MySQL服务器配置
        host = '192.168.1.86'
        user = 'root'
        passwd = '123456'
        dataBase = 'companyINF'
        to_table = 'person'
        path = 'd:/pythonP/人员信息.xls'
    
        #创建连接对象con, 游标对象cursor
        con = pymysql.connect(host, user, passwd, dataBase)
        cursor = con.cursor()
        connectTEST(cursor)
        dataDEAL(path, sheet=None)
        dataIMP(cursor, table, to_table, method="replace")
    

    相关文章

      网友评论

          本文标题:python读excel/csv/txt数据,批量/逐条写入My

          本文链接:https://www.haomeiwen.com/subject/gkwgjrtx.html