使用 pyodbc 批量插入 SQL Server 表:找不到文件 [英] BULK INSERT into SQL Server table using pyodbc: cannot find file

问题描述

我知道以前有人问过这种问题，但仍然找不到我要找的答案.我正在将 csv 文件批量插入到 SQL Server 表中，但出现如下所示的错误:

I know this kind of question has been asked before but still couldn't find the answer I'm looking for. I'm doing bulk insert of the csv file into the SQL Server table but I am getting error shown below:

我的代码:

df_output.to_csv('new_file_name.csv', sep=',', encoding='utf-8')
conn = pyodbc.connect(r'DRIVER={SQL Server}; PORT=1433; SERVER=Dev02; DATABASE=db;UID='';PWD='';')
curr = conn.cursor()
print("Inserting!")
curr.execute("""BULK INSERT STG_CONTACTABILITY_SCORE
               FROM 'C:\\Users\\kdalal\\callerx_project\\caller_x\\new_file_name.csv'
               WITH
               (
                 CODEPAGE = 'ACP',
                 FIRSTROW = 2,
                 FIELDTERMINATOR = ',',
                 ROWTERMINATOR = '\n'
                 );""")
conn.commit()

错误:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL服务器驱动程序][SQL Server]无法批量加载，因为文件"C:\Users\kdalal\callerx_project\caller_x\new_file_name.csv"不能打开.操作系统错误代码 3(系统无法找到指定的路径.).(4861) (SQLExecDirectW)')

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot bulk load because the file "C:\Users\kdalal\callerx_project\caller_x\new_file_name.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.). (4861) (SQLExecDirectW)')

'new_file_name.csv' 在指定的路径中.我尝试将路径更改为new_file_name.csv"，因为它位于我运行脚本的文件夹中，但它仍然抛出一个

'new_file_name.csv' is in the specified path. I tried changing the path to just 'new_file_name.csv' since it is in the folder from where I am running the script still it throws a

文件不存在

你能告诉我我在这里做错了什么吗?非常感谢.

Can you please tell me what am I doing wrong here. Thanks a lot in advance.

推荐答案

BULK INSERT 语句在 SQL Server 机器上执行，因此文件路径必须可从该机器访问.您收到系统找不到指定的路径"，因为路径

The BULK INSERT statement is executed on the SQL Server machine, so the file path must be accessible from that machine. You are getting "The system cannot find the path specified" because the path

C:\\Users\\kdalal\\callerx_project\\caller_x\\new_file_name.csv

是您的机器上的路径，而不是 SQL Server 机器上的路径.

is a path on your machine, not the SQL Server machine.

由于您将数据帧的内容转储到 CSV 文件，您可以简单地使用 df.to_sql 将内容直接推送到 SQL Server，而无需中间 CSV 文件.为了提高性能，您可以告诉 SQLAlchemy 使用 pyodbc 的 fast_executemany 选项，如相关问题中所述

Since you are dumping the contents of a dataframe to the CSV file you could simply use df.to_sql to push the contents directly to the SQL Server without an intermediate CSV file. To improve performance you can tell SQLAlchemy to use pyodbc's fast_executemany option as described in the related question

使用 pyODBC 的 fast_executemany 加速 pandas.DataFrame.to_sql

这篇关于使用 pyodbc 批量插入 SQL Server 表:找不到文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！