将pandas数据帧快速上载到Microsoft SQL Server
pd-to-mssql的Python项目详细描述
pd_to_mssql:将pandas数据帧快速上载到Microsoft SQL Server
价值主张
pandas提供的令人难以置信的功能可以使自动化ETL tasks快速而轻松,如果该任务不涉及将数据上载到Microsoft SQL Server,就像标准的to_sql
功能是painfully slow。这个包只使用pandas和pyodbc来获得与SSIS packages或SQLAlchemy+pyodbc的fast_executemany=True
选项相当的上传速度,同时还保持现有表结构的完整性(即删除目标表并用新的列数据类型创建一个表),并且只需要一行代码即可完成此操作。在
pd_到_mssql
在编写这个包之前,我尝试了许多其他的sqlserver上传解决方案。以下是这些选项的简短列表以及我的主要问题:
Solution | Issue |
---|---|
Writing dataframes to csv and using the bcp utility | bcp cannot handle the delimiting character appearing within a field value, even if it is properly quoted. Also, too much tedious work outside of python is required for each upload. |
Writing dataframes to excel and using SSIS packages launched by ^{ | Works well, but requries a sql server instance to be installed in the deployment environment. Again, way too much tedious developement outside of python. |
pyodbc + SQLAchemy's ^{ | Definitely the best of these options. Should work for most people. Not a fan of the behavior of the ^{ |
如何将pd安装到\u mssql
pip install pd_to_mssql
依赖性
如何使用pd_to_mssql
frompd_to_mssqlimportto_sqlto_sql(df_in,table_name,cnxn_string,schema='dbo',index=True,replace=False,chunk_size=1000,thread_count=5,ignore_truncation=False,ignore_missing=False)^{tb2}$
pd-to-mssql如何工作
首先,将dataframe中包含的所有数据进行字符串化,以适应insert语句的创建。然后,根据thread_count
参数生成多个线程(来自线程模块)。然后,每个线程都会接收一个单独的pyodbc连接。在每个连接中创建一个临时表,并并发地插入到每个临时表中。一旦temp表的所有线程都被卸载到一个表中。只有当所有临时表插入成功完成时,最后一步才完成。在
列映射
对于目标表中的每个非标识列,所提供的dataframe中必须存在一个同名的列,但dataframe中这些列的数据类型是无关的,因为它们将根据列规范转换为正确的数据类型。如果提供的数据帧中不存在该列,则将引发MissingColumnsException,除非ignore_missing=True
,在这种情况下,如果列可以为null,则将向该列插入空值。在
调试
有些错误通过了初始验证检查,只有在生成插入语句后才会被捕获。因此,生成的sqlexceptions通常很少包含识别问题的有用信息。为了帮助调试,每个线程中第一个失败的insert语句将被写入磁盘,该目录名为__pd_to_mssql_exception
,该目录位于上载时的当前工作目录中。从那里,您将能够在ssm中运行这些脚本,以轻松地识别它们在哪里失败以及为什么失败。根据我的经验,进入这个阶段最常见的问题是无效的数据转换。E、 G.无法识别的数据格式的字符串表示(转换为日期时间。日期(time)或字符串列,其中包含数字数据和一些非数字字符实例。在
其他注释
- 任何字符串中包含的所有换行符都将在上载之前转换为
\\n
。要在再次下载时恢复换行符,只需将\\n
值替换为\n
。一、 E.df['string_column'] = df['string_column'].apply(lambda x: str(x).replace('\\n', '\n')
- 只有在没有错误的情况下,插入才会提交给数据库。如果抛出了SQLException,则不会向目标数据库中插入任何行。在
- 如果
replace=True
则只有在临时表插入过程中没有错误时才会发生表截断。在
- 项目
标签: