python处理百万行数据文件 python百万级数据_大猩猩的技术博客_

一、百万数据入库测试结果

1、读取数据

追求速度问题，使用csv缓存，从csv中获取数据，速度最快

pymssql模块 ：使用execute, 然后fetchall()，速度在 7min 左右

pandas read_sql ：速度差不多，数据量大，速度有优势

pyodbc模块 、pyodbc模块待测试，速度应该会没差别

pandas模块 read_csv ：直接从csv文件取相同数据，read_csv()，只需几秒。

2、百万DataFrame 入库：

pymssql模块 ：10分钟

execute和executemany(遍历execute，坑爹)是一条条的插入，超级慢
拼接sql，value后面最多接1000条数据。速度大概在10分钟作用

pyodbc模块 ：3分钟

设置fast_executemany=True,只需3分钟就可以完成，设置1W参数,然后遍历executemany，速度最快（还需要测试）

pandas模块 ：3分钟

原始状态就是一条条插入，速度太慢，10几个小时吧
2、修改conn，设置为fast_executemany=True，即底层调用pyodbc模块，速度3分钟左右

调用java模块 ：2分钟

速度在几十秒，但是写成csv需要1分钟多，然后java从csv读数据写入sql server只需几十秒

bulk insert方式 ：1分钟

几秒完成，百万数据写入csv需要1分钟，但是遇到code3(路径)和code5的错误(权限),还需要解决。

pyodbc vs turbodbc

当 to_sql 用于将pandas DataFrame上传到SQL Server时，turbodbc肯定会比pyodbc更快 fast_executemany=False ，但是， fast_executemany=True 的pyodbc，两种方法都会产生基本相同的性能。1W行100列的DataFrame，平均30秒左右。

二、FAQ

环境：win10，sql server 2016

1、 conn = 'DRIVER={SQL Server Native Client 11.0};SERVER=xxxx;DATABASE=xxx;UID=xxxx;PWD=xxxx'

这里driver必须选择11.0版本

2、to_sql一直显示create附近语法错误，还有一个问题是，to_sql执行成功，无任何错误，但数据库没有数

ERROR： sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'BIGINT'.

原因是 bak.[51card_speed_up_test] 带了括号，而to_sql不会自己转义，去掉括号就能解决。

正确格式：df.to_sql(name=‘51card_speed_up_test’, con=engine,schema=‘bak’, index=False, if_exists=‘append’)
错误：name=‘bak.51card_speed_up_test’，能执行成功，但数据库没数。
错误：name=‘bak.[51card_speed_up_test]’，create附近语法报错
引用： https://stackoverflow.com/questions/48307008/pandas-to-sql-doesnt-insert-any-data-in-my-table

3、密码错误，接口参数不对 sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('28000', "[28000]

4、这里para为60W的时候不能组装。当数据为100左右的时候，能自己组装。

ERROR： sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('The SQL contains -66 parameter markers, but 65470 parameters were supplied', 'HY000')。cursor.execute(statement, parameters)

版本问题，从23.0升级到24.2最新版本解决问题，至少需要23.1版本。老版本23.0未实现fast_executemany方法，只能接受2100个参数。可以使用chunksize参数缓慢执行。

tsql_chunksize = 2097 // len(df.columns)
# cap at 1000 (limit for number of rows inserted by table-value constructor)
tsql_chunksize = 1000 if tsql_chunksize > 1000 else tsql_chunksize

5、使用上下文模式

with engine.begin() as conn:
    df.to_sql(name='my_balance', con=conn, if_exists='append',
              index=False, index_label='id')
    # 故意出现错误的代码，测试事物回滚
    df1.to_sql(name='my_balance', con=conn, if_exists='append')

后续：

多线程入库，没有尝试成功，多个connect 或者单个con和多个cursor 也没有解决。查询资料是说没有pyodbc 模块速度快。