python - Pandas to_sql() CREATE TABLE 权限被拒绝

Question

我正在尝试使用以下代码将 df 写入带有 pandas.to_sql 的现有表:

import sqlalchemy 

#CREATE CONNECTION
constring = "mssql+pyodbc://UID:PASSWORD@SERVER/DATABASE?driver=SQL Server"
dbEngine = sqlalchemy.create_engine(constring, fast_executemany=True, connect_args={'connect_timeout':10}, echo=False)

#WRITE INTO TABLE
df.to_sql(con=dbEngine, schema="dbo", name="target_table", if_exists="replace", index=False, chunksize=1000)

但它给出了以下错误:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE permission denied in database 'database'. (262) (SQLExecDirectW)")
[SQL:
CREATE TABLE [target_table] (
[chLocalIndustryCode] VARCHAR(max) NULL, 
[vcLocalIndustryDesc] VARCHAR(max) NULL, 
[chLocalSectorCode] VARCHAR(max) NULL, 
[vcLocalSectorDesc] VARCHAR(max) NULL, 
[chLocalClusterCode] VARCHAR(max) NULL, 
[vcLocalClusterDesc] VARCHAR(max) NULL, 
[chLocalMegaClusterCode] VARCHAR(max) NULL, 
[vcLocalMegaClusterDesc] VARCHAR(max) NULL)
]

(Background on this error at: https://sqlalche.me/e/14/f405)

我已经检查了连接并在读取表时正常工作，所以我认为问题出在 pandas to_sql() 函数上。我也试过用光标写入表格，但需要很长时间。

我正在使用 Pandas 1.3.4

是否有任何方法可以修复此错误或可以使用 pd.to_sql() 函数的任何替代方法来提高写入速度？

Answer 1

您可能需要为 SQL Server 用户添加创建权限。您可以按照 link 中的以下步骤操作:

To add a Windows user that has the login “domainname \username” to thesysadmin fixed server role

a. Log on to the computer using the credentials for thedomainname\username account.

b. Click the Start button, point to All Programs, click Microsoft SQLServer, right-click SQL Server Management Studio, and then click Runas administrator. ps: "Run As Administrator" option elevates the userpermissions In the User Access Control dialog box, click Continue.

c. In SQL Server Management Studio, connect to an instance of SQLServer.

d. Click Security, right-click Logins, and then click New Login.

e. In the Login name box, enter the user name.

f. In the Select a page pane, click Server Roles, select the sysadmincheck box, and then click OK.