python to sql_使用sqlalchemy的python pandas to_sql：如何加快导...

DataFrame.to_sql方法为ODBC连接器生成插入语句,然后ODBC连接器将其视为常规插入.

当这很慢时,它不是大熊猫的错.

将DataFrame.to_sql方法的输出保存到文件,然后通过ODBC连接器重播该文件将花费相同的时间.

将数据批量导入数据库的正确方法是生成csv文件,然后使用load命令,该命令在SQL数据库的MS风格中称为BULK INSERT

例如：

BULK INSERT mydatabase.myschema.mytable

FROM 'mydatadump.csv';

语法参考如下：

BULK INSERT

[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]

FROM 'data_file'

[ WITH

(

[ [ , ] BATCHSIZE = batch_size ]

[ [ , ] CHECK_CONSTRAINTS ]

[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]

[ [ , ] DATAFILETYPE =

{ 'char' | 'native'| 'widechar' | 'widenative' } ]

[ [ , ] FIELDTERMINATOR = 'field_terminator' ]

[ [ , ] FIRSTROW = first_row ]

[ [ , ] FIRE_TRIGGERS ]

[ [ , ] FORMATFILE = 'format_file_path' ]

[ [ , ] KEEPIDENTITY ]

[ [ , ] KEEPNULLS ]

[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]

[ [ , ] LASTROW = last_row ]

[ [ , ] MAXERRORS = max_errors ]

[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]

[ [ , ] ROWS_PER_BATCH = rows_per_batch ]

[ [ , ] ROWTERMINATOR = 'row_terminator' ]

[ [ , ] TABLOCK ]

[ [ , ] ERRORFILE = 'file_name' ]

)]