使用 Pyodbc 通过 Active Directory Interactive/2MFA 将 Pandas Dataframe 上传到 Azure SQL Server 数据库时出错

Question

精彩的 StackOverflow 社区大家好！

我需要你神圣的帮助，因为我无法在其他地方找到答案。

使用Python（版本3.10.2 64位）时，我尝试将Pandas Dataframe“hr_personaldata”上传到SQL Server“test-sql-azure-01.database.windows”上的Azure SQL Server数据库“hr-data” 。网'。

输入密码并在身份验证器应用上批准连接后，我可以使用 pyodbc 包和 ActiveDirectoryInteractive 身份验证方法来创建连接。使此连接继续进行的代码如下所示，并用于建立连接：

# Create a connection to the database
import pyodbc
server = 'test-sql-azure-01.database.windows.net'
database = 'hr-data'
username ='[email protected]'
Authentication='ActiveDirectoryInteractive'
driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect('DRIVER='+driver+
                      ';SERVER='+server+
                      ';PORT=1433;DATABASE='+database+
                      ';UID='+username+
                      ';AUTHENTICATION='+Authentication
                      )

当尝试从数据库读取数据时，这可以正常工作。但是，当向数据库发送数据时，出现错误。我使用以下代码将数据发送到数据库，其中“hr_personaldata”的类型为“pandas.core.frame.DataFrame”并填充了个人 HR 数据：

hr_personaldata.to_sql('HR_PersonalData', conn, if_exists='replace')

此语句返回的完整错误如下：

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:2017, in SQLiteDatabase.execute(self, *args, **kwargs)
   2016 try:
-> 2017     cur.execute(*args, **kwargs)
   2018     return cur

OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.\r\n (10054) (SQLExecDirectW); [08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (10054)')

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:2021, in SQLiteDatabase.execute(self, *args, **kwargs)
   2020 try:
-> 2021     self.con.rollback()
   2022 except Exception as inner_exc:  # pragma: no cover

OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (-2147467259) (SQLEndTran)')

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
c:\Users\r.vdaa\Documents\Scripts HR-Data Processing\main.py in <module>
----> 108 hr_personaldata.to_sql('HR_PersonalData', conn, if_exists='replace')

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\generic.py:2963, in NDFrame.to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2806 """
   2807 Write records stored in a DataFrame to a SQL database.
   2808 
   (...)
   2959 [(1,), (None,), (2,)]
   2960 """  # noqa:E501
   2961 from pandas.io import sql
-> 2963 return sql.to_sql(
   2964     self,
   2965     name,
   2966     con,
   2967     schema=schema,
   2968     if_exists=if_exists,
   2969     index=index,
   2970     index_label=index_label,
   2971     chunksize=chunksize,
   2972     dtype=dtype,
   2973     method=method,
   2974 )

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:697, in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
    692 elif not isinstance(frame, DataFrame):
    693     raise NotImplementedError(
    694         "'frame' argument should be either a Series or a DataFrame"
    695     )
--> 697 return pandas_sql.to_sql(
    698     frame,
    699     name,
    700     if_exists=if_exists,
    701     index=index,
    702     index_label=index_label,
    703     schema=schema,
    704     chunksize=chunksize,
    705     dtype=dtype,
    706     method=method,
    707     engine=engine,
    708     **engine_kwargs,
    709 )

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:2186, in SQLiteDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, **kwargs)
   2175             raise ValueError(f"{col} ({my_type}) not a string")
   2177 table = SQLiteTable(
   2178     name,
   2179     self,
   (...)
   2184     dtype=dtype,
   2185 )
-> 2186 table.create()
   2187 return table.insert(chunksize, method)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:828, in SQLTable.create(self)
    827 def create(self):
--> 828     if self.exists():
    829         if self.if_exists == "fail":
    830             raise ValueError(f"Table '{self.name}' already exists.")

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:812, in SQLTable.exists(self)
    811 def exists(self):
--> 812     return self.pd_sql.has_table(self.name, self.schema)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:2194, in SQLiteDatabase.has_table(self, name, schema)
   2191 wld = "?"
   2192 query = f"SELECT name FROM sqlite_master WHERE type='table' AND name={wld};"
-> 2194 return len(self.execute(query, [name]).fetchall()) > 0

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:2026, in SQLiteDatabase.execute(self, *args, **kwargs)
   2022 except Exception as inner_exc:  # pragma: no cover
   2023     ex = DatabaseError(
   2024         f"Execution failed on sql: {args[0]}\n{exc}\nunable to rollback"
   2025     )
-> 2026     raise ex from inner_exc
   2028 ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
   2029 raise ex from exc

DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name=?;
('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.\r\n (10054) (SQLExecDirectW); [08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (10054)')
unable to rollback

还给出了以下警告：

C:\Users\r.vdaa\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:758: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

任何人都可以在这里帮助我，因为我被困在客户的这个项目中，并且似乎找不到答案。

非常感谢，非常感谢 StackOverflow 社区，并继续努力！

你好，

里卡多