服务器未能恢复事务。Desc:9800000002。(3971) (SQLEndTran)')

Question

我要做的是使用pd.to_sql方法在server中推送数据框架。langyuage- python 3.6，熊猫版pandas(0.25.1)

发动机变量

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s"%PARAM,pool_size = 20,max_overflow =10,pool_recycle=60,pool_timeout=30)

这是调用staging_standardisation()方法的主要脚本。

import pyodbc
from sqlalchemy import create_engine,event
import pandas as pd
from utils import engine

@event.listens_for(engine, 'before_cursor_execute')
def plugin_bef_cursor_execute(conn, cursor, statement, params, context,executemany):
   if executemany:
       cursor.fast_executemany = True  # replace from execute many to fast_executemany.
       cursor.commit()

table = "table_name"
column_name = ["Some column_names"] #selecting some columns names

column_obj = {"a":"A",
              "b":"B",
              "c":"C"
              }

staging_standardisation(engine=engine, table_name=table, column_names=column_name, column_obj=column_obj)

staging_standardisation法

def staging_standardisation(engine, table_name, column_names, column_obj, **kwargs):
    try:
        query = "SELECT * FROM  dbo.License_Search"
        sql_df = pd.read_sql(query, engine)
        df = sql_df[column_names]
        df = df.rename(index=str, columns=column_obj)
        try:
            if 'authority_id' in column_obj.values():
                pass
            else:

                df['authority_id'] =53

        except KeyError:
            df['authority_id'] = None
    try:
        df.to_sql(name=staging_table, con=engine, index=False, if_exists='append') # this line causing error.

    except Exception as exe:
        print(table_name, exe)

抛掷误差

(pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
The server failed to resume the transaction. Desc:8c00000007. 
(3971) (SQLEndTran); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The 
transaction active in this session has been committed or aborted by another session. (3926)')
(Background on this error at: http://sqlalche.me/e/f405)

Answer 1

问题是铸造的问题。在dataframe中，日期(列)类型是字符串。然后，我将列类型转换为DateTime。

FYI: vs代码显示的错误是有限的。我们在jupyter笔记本上运行了一个单独的测试，在那里我们有完全的错误，并且发现它是日期列。

def staging_standardisation(engine, table_name, column_names, column_obj, **kwargs):
    try:
        query = "SELECT * FROM  dbo.License_Search"
        sql_df = pd.read_sql(query, engine)
        df = sql_df[column_names]
        df = df.rename(index=str, columns=column_obj)
        df['Date'] = pd.to_datetime(df['Date'])
        try:
            if 'authority_id' in column_obj.values():
                pass
            else:

                df['authority_id'] =53

        except KeyError:
            df['authority_id'] = None
    try:
        df.to_sql(name=staging_table, con=engine, index=False, if_exists='append') # this line causing error.

    except Exception as exe:
        print(table_name, exe)

欲了解更多信息，请阅读本文。