使用 pyodbc 从 SQL Server 中提取数据

Question

import subprocess
import pyodbc
c = dbconn.cursor()
server = r".SQLEXPRESS01"
database = "test"
dbconn = pyodbc.connect("driver={SQL Server Native Client 11.0};server=" + server + "; database="+ database +"; trusted_connection=yes;",
autocommit=True)

\unable to write code for table name loop \

out_path = f"D:\Projects\ReferenceModel\DataFiles\IN_Download\Format_Files\{table_name}.fmt"
bcp_command = f"bcp {table_name} format nul -c -t, -f {out_path} -S {server} -d {database} -T"
cmd_args = f"/c {bcp_command}"
subprocess.call(["cmd.exe", cmd_args])

我正在尝试使用 pyodbc 提取数据库中所有表的表名(假设 50 个表test，但我在执行此操作时遇到了麻烦。我正在尝试从数据库(test(中获取表名，这样我就不必对其进行硬编码(就像我现在所做的那样(。谁能帮我写代码？

注意：尝试使用 cursor 命令在我的数据库中的所有表名之间移动

Answer 1

您应该能够直接调用bcp.exe，而不是尝试使用cmd.exe /c。这应该是您所需要的...

import subprocess
import pyodbc
server = r".SQLEXPRESS01"
database = "test"
connection = f"driver={{SQL Server Native Client 11.0}};server={server};database={database};trusted_connection=yes;"
dbconn = pyodbc.connect(connection, autocommit=True)
cursor = dbconn.cursor()
cursor.execute("SELECT name FROM sys.tables")
for row in cursor:
table_name = row[0]
out_path = f"D:\Projects\ReferenceModel\DataFiles\IN_Download\Format_Files\{table_name}.fmt"
args = [ "bcp.exe", f"{database}.dbo.{table_name}", "format", "nul", "-c", "-t,", f"-f{out_path}", f"-S{server}", "-T"]
subprocess.call(args)