python - 尝试在 Python 中使用 bcp 将 CSV 上传到数据库时出现问题

Question

我在尝试通过 bcp 将 CSV 文件上传到 Azure MS SQL 数据库时遇到问题。

我正在使用Bcpy工具来实现这一点。

这是我正在运行的脚本:

sql_config = {
            'server': 'sql_server_hostname',
            'database': 'database_name',
            'username': 'user',
            'password': 'password'
        }
        sql_table_name = 'test_data1'
        csv_file_path = 'data1.csv'            #File in the script directory
        flat_file = bcpy.FlatFile(qualifier='', path=csv_file_path)
        sql_table = bcpy.SqlTable(sql_config, table=sql_table_name)
        flat_file.to_sql(sql_table)

运行脚本后，我收到以下错误:

<ipython-input-11-97d18f6b2041> in function()
    263         flat_file = bcpy.FlatFile(qualifier='', path=csv_file_path)
    264         sql_table = bcpy.SqlTable(sql_config, table=sql_table_name)
--> 265         flat_file.to_sql(sql_table)
    266 
    267 

c:\users\user\appdata\local\programs\python\python37\lib\site-packages\bcpy\data_objects.py in to_sql(self, sql_table, use_existing_sql_table, batch_size)
    157                 ),
    158                 username=sql_table.username,
--> 159                 password=sql_table.password)
    160         bcp(sql_table=sql_table, flat_file=self, batch_size=batch_size)
    161 

c:\users\user\appdata\local\programs\python\python37\lib\site-packages\bcpy\binary_callers.py in sqlcmd(server, database, command, username, password)
     81                      ['-s,', '-W', '-Q', command]
     82     result = subprocess.run(sqlcmd_command, stdout=subprocess.PIPE,
---> 83                             stderr=subprocess.PIPE)
     84     if result.returncode:
     85         result_dump = str(result).replace(password, sha512(password))

c:\users\user\appdata\local\programs\python\python37\lib\subprocess.py in run(input, capture_output, timeout, check, *popenargs, **kwargs)
    470         kwargs['stderr'] = PIPE
    471 
--> 472     with Popen(*popenargs, **kwargs) as process:
    473         try:
    474             stdout, stderr = process.communicate(input, timeout=timeout)

c:\users\user\appdata\local\programs\python\python37\lib\subprocess.py in __init__(self, args, bufsize, executable, stdin, stdout, stderr, preexec_fn, close_fds, shell, cwd, env, universal_newlines, startupinfo, creationflags, restore_signals, start_new_session, pass_fds, encoding, errors, text)
    773                                 c2pread, c2pwrite,
    774                                 errread, errwrite,
--> 775                                 restore_signals, start_new_session)
    776         except:
    777             # Cleanup if the child failed starting.

c:\users\user\appdata\local\programs\python\python37\lib\subprocess.py in _execute_child(self, args, executable, preexec_fn, close_fds, pass_fds, cwd, env, startupinfo, creationflags, shell, p2cread, p2cwrite, c2pread, c2pwrite, errread, errwrite, unused_restore_signals, unused_start_new_session)
   1176                                          env,
   1177                                          os.fspath(cwd) if cwd is not None else None,
-> 1178                                          startupinfo)
   1179             finally:
   1180                 # Child is launched. Close the parent's copy of those pipe

FileNotFoundError: [WinError 2] The system cannot find the specified file

在填写“sql_table_name”参数时，我尝试使用“test_data1”和“dbo.test_data1”。

由于它是 Azure MS SQL 数据库，因此服务器参数的写法如下:“servername.database.windows.net”

在使用这个工具之前，我也尝试过通过os.system()使用bcp。它没有打印任何错误，但也没有将 CSV 中的任何行上传到数据库。这是脚本:

 command = 'bcp "dbo.test_data1" in "data1.csv" -S"servername.database.windows.net" -d"database_name" -F2 -c -t"," - U"user" -P"password" -e error.txt'
os.system(command)

你知道是什么原因造成的吗？您是否知道将 CSV 文件上传到我的数据库的任何其他选项？

谢谢!

Answer 1

错误是:

FileNotFoundError: [WinError 2] The system cannot find the specified file

您确定以下行

csv_file_path = 'data1.csv'            #File in the script directory

是文件的完整路径吗？