Python基于pyodbc+SQLAlchemy向SQL Server插数据偶发10060通信失败报错求助

• Python版本：Python 3.8.5 (default, Sep 3 2020, 21:29:08) [MSC v.1916 64 bit (AMD64)]，运行于win32平台
• pyodbc版本：4.0.0-unsupported
• 操作系统：Windows
• 数据库版本：SQL Server 12.0.200.8
• 数据库驱动：ODBC Driver 17 for SQL Server

向MS SQL数据库批量推送数据时，不定期出现无固定触发规律的OperationalError报错，具体报错栈如下：

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP 提供程序：连接尝试失败，因为连接方在一段时间后未正确响应，或已建立的连接失败，因为连接主机未响应。\r\n (10060) (SQLExecute); [08S01] [Microsoft][ODBC Driver 17 for SQL Server]通信链路故障 (10060)')
[SQL: INSERT INTO randata.[RAN_regional] (mbnl_cell_id, nominal_id, code, activity_id, acquisition_partner, dici_partner, design_partner, build_partner, [MS1_PreRequisites_ACT], [MS2_Nomination_ACT], [SurveyInstructed_ACT], [MS3_DesignSurveyComplete_FC], [MS3_DesignSurveyComplete_ACT], [SSR_Uploaded], [GA_Instructed], [GA_Submitted_FC], [GA_Submitted_ACT], [GA_ApprovedAcq_ACT], [GA_ApprovedRadio_ACT], [GA_ApprovedBuild_ACT], [GA_Approved_T&T_ACT], [GA_Approved_ALL_ACT], [GA_Rejected_ACT], [GA_RejectionReason], [GDC_Required], [GDC_Instructed_ACT], [GDC_Approved_ACT], [DD_Instructed_ACT], [DD_Submitted_FC], [DD_Submitted_ACT], [DD_Rejected_ACT], [DD_ApprovedBuild_ACT], [DD_Approved_TnT_ACT], [DD_Approved_ALL_ACT], [DNO_AC_ExistingSupply], [REC_UpgradeRequired], [DNO_AC_ProposedSupply], [ServiceRequestFormIssued_SMS], [InstructionToObtainQuote_ACT], [DNO], [DNOConfirmPaymentReceived_ACT], [LeaseReadInstructed_ACT], [LeaseReadComplete_ACT], [ACQ_LeaseExpiryDate], [ACQ_AgreementType_5G], [ACQ_WithinRights], [ACQ_TechnologyRestriction_5G], [ACQ_Antenna_DishRights_5G], [ACQ_Cabinet_CabinRights_5G], [ACQ_AssAprtStlwSprtRights_5G], [ACQ_RequiredUpgradeRights_5G], [AcquisitionInstructed_ACT], [PlanningTypeSSR], [PlanningConstraints], [PlanningTypeSubmitted], [PlanningSubmitted_FC], [PlanningSubmitted_ACT], [PlanningApproved_FC], [PlanningApproved_ACT], [PlanningRefused], [PlanningAppealSubmitted], [AcqPathSSR], [AcqPath], [LLAgent], [HOTsComplete_FC], [HOTsComplete_ACT], [SolicitorsInstrA1submtd_ACT], [LegalsInstructed_ACT], [TypeofLegalsInstructed], [EngrossmentIssued], [EngrossmentReturned], [LegalCompletion_FC], [LegalCompletion_ACT], [AHOP_Submitted_ACT], [AHOP_Approved_ACT], [Ready_to_Access_FC], [Ready_to_Access_ACT], [MS5_SiteAccessed_FC], [MS5_SiteAccessed_ACT], [MS5_ATRISK_SiteAccessed_ACT], [CallOffReceivedfromSWC], [CallOffSubmitted], [MS7_BuildInstructed_FC], [MS7_BuildInstructed_ACT], [BuildStart_FC], [BuildStart_ACT], [CivilsComplete_FC], [CivilsComplete_ACT], [RiggingComplete_FC], [RiggingComplete_ACT], [PowerCabInstall_FC], [PowerCabInstall_ACT], [Jointing_FC], [Jointing_ACT], [Metering_FC], [Metering_ACT], [FuseUpgradeComplete_FC], [FuseUpgradeComplete_ACT], [MS8_BuildComplete_FC], [MS8_BuildComplete_ACT], [DataFillRequested_ACT], [DataFillIssued_ACT], [DIC_FC], [DIC_ACT]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: (('AAB002', '', '', 'AAB002_55', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, None, None, None, '', '', '', '', '', '', '', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('AAB014', '', '', 'AAB014_55', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, None, None, None, '', '', '', '', '', '', '', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('AAB026', '', '', 'AAB026_55', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, None, None, None, '', '', '', '', '', '', '', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('AAB063', '', '', 'AAB063_55', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, None, None, None, '', '', '', '', '', '', '', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('AAB068', '', '', 'AAB068_55', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, None, None, None, '', '', '', '', '', '', '', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('ABC002', '', '', 'ABC002_14', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, datetime.datetime(2020, 6, 17, 0, 0), datetime.datetime(2020, 6, 18, 0, 0), datetime.datetime(2012, 6, 23, 0, 0), '租约已过期', '', '未详细说明', '10A', '未明确', '不清晰', '是', None, '', '无已知问题', '', None, None, None, None, '', None, '有条件同意(NTBUWOD)', '', '', None, None, None, None, None, None, None, None, None, None, None, datetime.datetime(2030, 12, 31, 0, 0), None, datetime.datetime(2030, 12, 31, 0, 0), None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('ABC015', '', '', 'ABC015_55', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, datetime.datetime(2020, 3, 2, 0, 0), datetime.datetime(2020, 6, 2, 0, 0), datetime.datetime(2020, 6, 2, 0, 0), '租约有效', '是', '未详细说明', '10A', '未明确', '不清晰', '否', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('ABC018', '', '', 'ABC018_55', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, datetime.datetime(2020, 3, 2, 0, 0), datetime.datetime(2020, 4, 17, 0, 0), datetime.datetime(2018, 6, 22, 0, 0), '租约已过期', '', '未详细说明', '2A/1D', '设备机房', '不清晰', '否', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None) ... 共3432条绑定参数集，仅展示10条 ... ('', '', '', '', '', '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, None, None, None, '', '', '', '', '', '', '', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None), ('', '', '', '', '', '', '', '', None, None, None, None, None, None, None, None, datetime.datetime(2021, 3, 2, 0, 0), None, None, None, None, None, None, '', '', None, None, None, None, None, None, None, None, None, '', '', '', None, None, None, None, None, None, None, '', '', '', '', '', '', '', None, '', '', '', None, None, None, None, '', None, '', '', '', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None)]

• 涉及写入的表规模差异较大，最小的表数据量极少，最大的表约有10000-20000行、150列左右。
• 使用完全相同的运行参数时，错误会随机出现或消失，没有明确的触发逻辑。

为定位问题，对配置做过如下修改：

1. SQLAlchemy引擎初始化阶段：尝试切换驱动配置、将fast_executemany参数在True/False之间切换调整，引擎初始化的代码如下：

driver = "ODBC Driver 17 for SQL Server"
server = 'server'
username = os.getenv('MSSQL_USER')
password = os.getenv('MSSQL_PASSWORD')
database = 'db'

odbc_str = f'DRIVER={driver};SERVER={server};PORT=1433;UID={username};DATABASE={database};PWD={password}'
connect_str = f'mssql+pyodbc:///?odbc_connect={urllib.parse.quote_plus(odbc_str)}'

engine = sqlalchemy.create_engine(connect_str, fast_executemany=False)

2. 调用DataFrame向SQL Server写入数据的阶段：尝试调整参数，包括将if_exists设置为'replace'/'append'、切换index参数为True/False、调整schema参数为'randata'/不指定schema，写入逻辑的代码如下：

df.to_sql(sql_table_name, 
          general.get_engine(), 
          if_exists='replace', 
          index=False,
          dtype=db_schemas.MS1_MS8_SCHEMA_MAPPING,
          schema='data')

按优先级从高到低调整：

1. 调整数据库连接串配置
   原有连接串未设置超时、连接保活参数，批量写入大表时容易因为连接闲置被防火墙/数据库主动断开，新增以下参数：

   odbc_str = f'DRIVER={driver};SERVER={server};PORT=1433;UID={username};DATABASE={database};PWD={password};Connection Timeout=30;KeepAlive=1;KeepAliveInterval=3;Login Timeout=30'
   

   • KeepAlive=1 开启TCP层保活，避免空闲连接被中间网络设备切断
   • 超时参数适配长批量写入的耗时需求，避免默认超时触发报错

2. 优化引擎连接池配置
   原有未指定连接池参数，默认连接数过多或连接复用老化会触发随机断开，初始化引擎时新增：

   engine = sqlalchemy.create_engine(
       connect_str, 
       fast_executemany=True,
       pool_pre_ping=True,
       pool_size=5,
       max_overflow=2,
       pool_recycle=3600
   )
   

   大表写入优先开启fast_executemany=True，可减少网络往返次数，