批量插入错误代码3：系统找不到指定的路径 [英] BULK INSERT error code 3: The system cannot find the path specified

问题描述

我正在尝试使用pyodbc将本地文件批量插入到远程MS_SQL数据库中。我能够连接到数据库，并且能够像以前一样对 INSERT INTO 表进行操作。我遇到的问题是大容量插入。

I am trying to bulk insert a local file into a remote MS_SQL database using pyodbc. I am able to connect to the DB and I am able to INSERT INTO tables, as I have done it before. Where I have been having issues is to BULK INSERT.

我正在使用大容量插入作为加快我的 INSERT 过程的一种方法。

I am using BULK INSERT as a way to speed up my INSERT process.

代码如下：

statement = """ BULK INSERT BulkTable FROM 'C:\\Users\\userName\\Desktop\\Folder\\Book1.csv' WITH (
       FIRSTROW=2,
       FIELDTERMINATOR=',',
       ROWTERMINATOR = '\\n'
);
"""
cursor.execute(statement)
cnxn.commit()

此代码会产生此错误：

Traceback (most recent call last):
   File "tester.py", line 41, in <module> cursor.execute(statement)
   pyodbc.ProgrammingError: 
    ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]  Cannot bulk load because the file    "C:\\Users\\userName\\Desktop
\\Folder\\Book1.csv" could not be opened.
    Operating system error code 3(The system cannot find the path specified.). (4861) (SQLExecDirectW)')`

我真的不知道了解为什么无法打开文件，因为路径绝对正确。

I really don't understand why it can't open the file as the path is definitely correct.

让我知道是否需要更多信息。

Let me know if you need any more information.

推荐答案

我正在尝试将本地文件批量插入到远程MS_SQL数据库中

您的方法无效，因为文件规范'C：\\Users\\userName\\Desktop\\Folder\\Book1.csv'只是工作站上的有效路径正在运行您的Python代码，但是批量插入文档解释了

Your approach is not working because the file specification 'C:\\Users\\userName\\Desktop\\Folder\\Book1.csv' is only a valid path on the workstation that is running your Python code, but the BULK INSERT documentation explains that

数据文件必须指定运行SQL Server的服务器的有效路径 。如果 data_file 是远程文件，请指定通用命名约定（UNC）名称。

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name.

（强调我的）。也就是说，BULK INSERT语句在服务器上运行 ，因此就服务器而言，其他计算机（例如工作站）上的文件规范实际上是远程文件。换句话说，SQL Server会在服务器本身上查找名为 C：\Users\userName\Desktop\Folder\Book1 的文件，如果失败，则会引发找不到路径错误。

(emphasis mine). That is, the BULK INSERT statement is running on the server, so a file specification on some other machine (like your workstation) is actually a "remote file" as far as the server is concerned. In other words, SQL Server goes looking for a file named C:\Users\userName\Desktop\Folder\Book1 on the server itself and when that fails it raises the "cannot find the path" error.

要使用批量插入，您将需要使用

In order to use BULK INSERT you would need to either

1. 将文件放在SQL Server可以查看的网络共享上，然后提供该文件的UNC路径，或者

1. put the file on a network share that the SQL Server can "see", and then supply the UNC path to that file, or

将文件上传到SQL Server上的本地文件夹，然后提供文件的本地（服务器）路径。

upload the file to a local folder on the SQL Server and then supply the local (server) path to the file.

如果这两种选择都不可行，那么从Python中选择的另一种选择是使用子进程模块来调用SQL Server的 bcp实用工具可将数据从本地文件上传到SQL Server数据库。

If neither of those alternatives is feasible then your other option from Python is to use the subprocess module to invoke SQL Server's bcp utility to upload the data from your local file into the SQL Server database.

这篇关于批量插入错误代码3：系统找不到指定的路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！