我试图通过python脚本使用sqlserver2014中的BULK INSERT函数编写一个查询,将一个大的CSV文件插入数据库的表中。我有以下示例代码:import pymssql
import sqlalchemy
engine = create_engine('connection_string_goes_here', encoding="utf-8")
table_name = 'my_table'
source_path = 'C:\path\to\csv\test.csv'
q = ("BULK INSERT " + table_name + " " +
"FROM '" + source_path + "' " +
"WITH ( DATAFILETYPE = 'char', " +
"FIELDTERMINATOR = ',', " +
"ROWTERMINATOR = '\\n')")
connection = engine.connect()
connection.execute(q)
connection.close()
我得到的错误是:
(pymssql.OperationalError) (4861, Cannot bulk load because the file "C:\\path\\to\\csv\\test.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).DB-Lib error message 20018…
我认为这是因为一个双重反斜杠问题。反斜杠实际上包含在BULK INSERT语句中,而不是转换为单个反斜杠。我在SQL Server错误日志中看不到任何内容。在
我的安装程序使用MS SQL Server Express 2014和Python 2.7.9在单独的Windows机器上运行。在
我尝试过在source_path上使用原始字符串(r'C:\path\to\csv\test.csv)和decode字符串函数,但都没有帮助。在