Python调用SQL Server查询报错，请求技术排查指导

原本在SQL Server上正常运行的查询语句，通过pandas的read_sql_query方法执行时出现语法错误，报错提示：

'[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'SERVER1'. (102) (SQLExecDirectW)'

因紧急项目需求，暂未深入研究pandas相关SQL知识，恳请指明解决方向。

相关代码 #

import pyodbc
import os
import pandas as pd
conn = pyodbc.connect(r'driver={SQL Server Native Client 11.0}; SERVER=SERVER1.cc.net; Trusted_Connection=yes')

sql1 = "select case when AP.IS_C_ACCOUNT = 1 then 'Yes' else 'No' end  as  Account_C_Indicator , A.acct as AcctNum, coalesce(AGT.LegacyPGType,'NONRG') as AcctG_Type,
    coalesce(AGT.Description,'NONRgdD') as AccountType,  case when A.acct_type in (4) then 'CName' else 'Nom' end as AccountHS,
    case when FBM.[F B Cat] is not null then FBM.[F B Cat]  else 'CmAccount' end as AccountRType,
    case when FBM.[Account Type] is not null        then FBM.[Account Type]         else 'CmAccount' end as AccountRBrand,
    case when FBM.[Man Name] is not null        then FBM.[Man Name]         else '' end as AcctPortType,A.open_date as AccountSetupDate,
    case when DATEPART(dayofyear, getdate()) < DATEPART(dayofyear, A.open_date) then YEAR(getdate()) - YEAR(A.open_date) - 1 else YEAR(getdate()) - YEAR(COALESCE(A.open_date, 0)) end  as AccountTYears
    from SERVER1.DB1.dbo.TABLE_C as A
    inner join SERVER1.DB1.dbo.TABLE_P as AP            on AP.acct = A.acct 
    left join SERVER1.DB1.dbo.TABLE_K as KYC            on KYC.ACCT_NUM = A.acct
    left join SERVER1.DB2.dbo.TABLE_G as AGT            on AGT.AcctSType = A.acct_sub
    left join SERVER1.DB2.dbo.TABLE_M as FBM    on FBM.[Man Code] = A.port_type"

df = pd.read_sql_query(sql1, conn)

完整报错堆栈信息 #

ProgrammingError                          Traceback (most recent call last)
File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:2020, in SQLiteDatabase.execute(self, *args, **kwargs)
   2019 try:
-> 2020     cur.execute(*args, **kwargs)
   2021     return cur

ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'SERVER1'. (102) (SQLExecDirectW)")

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

DatabaseError                             Traceback (most recent call last)
Input In [13], in <cell line: 13>()
      1 sql1 = "...

df = pd.read_sql_query(sql1, conn)

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:399, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)
    341 """
    342 Read SQL query into a DataFrame.
    343 
   (...)
    396 parameter will be converted to UTC.
    397 """
    398 pandas_sql = pandasSQL_builder(con)
--> 399 return pandas_sql.read_query(
    400     sql,
    401     index_col=index_col,
    402     params=params,
    403     coerce_float=coerce_float,
    404     parse_dates=parse_dates,
    405     chunksize=chunksize,
    406     dtype=dtype,
    407 )

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:2080, in SQLiteDatabase.read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize, dtype)
   2068 def read_query(
   2069     self,
   2070     sql,
   (...)
   2076     dtype: DtypeArg | None = None,
   2077 ):
   2079     args = _convert_params(sql, params)
-> 2080     cursor = self.execute(*args)
   2081     columns = [col_desc[0] for col_desc in cursor.description]
   2083     if chunksize is not None:

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:2032, in SQLiteDatabase.execute(self, *args, **kwargs)
   2029     raise ex from inner_exc
   2031 ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 2032 raise ex from exc

解决方向 #

• 问题原因：当前连接已经通过SERVER=SERVER1.cc.net指定了目标服务器，SQL语句中重复在表名前添加SERVER1.前缀属于冗余写法，导致SQL Server解析语法错误。
• 修复方案：
  1. 删除SQL语句中所有表名前的SERVER1.前缀，例如将SERVER1.DB1.dbo.TABLE_C修改为DB1.dbo.TABLE_C，所有关联表执行相同修改。
  2. 验证连接账号拥有访问DB1和DB2两个数据库的权限（若之前在SQL Server客户端能正常执行，此步骤可跳过）。

修改后的SQL语句示例：

sql1 = "select case when AP.IS_C_ACCOUNT = 1 then 'Yes' else 'No' end  as  Account_C_Indicator , A.acct as AcctNum, coalesce(AGT.LegacyPGType,'NONRG') as AcctG_Type,
    coalesce(AGT.Description,'NONRgdD') as AccountType,  case when A.acct_type in (4) then 'CName' else 'Nom' end as AccountHS,
    case when FBM.[F B Cat] is not null then FBM.[F B Cat]  else 'CmAccount' end as AccountRType,
    case when FBM.[Account Type] is not null        then FBM.[Account Type]         else 'CmAccount' end as AccountRBrand,
    case when FBM.[Man Name] is not null        then FBM.[Man Name]         else '' end as AcctPortType,A.open_date as AccountSetupDate,
    case when DATEPART(dayofyear, getdate()) < DATEPART(dayofyear, A.open_date) then YEAR(getdate()) - YEAR(A.open_date) - 1 else YEAR(getdate()) - YEAR(COALESCE(A.open_date, 0)) end  as AccountTYears
    from DB1.dbo.TABLE_C as A
    inner join DB1.dbo.TABLE_P as AP            on AP.acct = A.acct 
    left join DB1.dbo.TABLE_K as KYC            on KYC.ACCT_NUM = A.acct
    left join DB2.dbo.TABLE_G as AGT            on AGT.AcctSType = A.acct_sub
    left join DB2.dbo.TABLE_M as FBM    on FBM.[Man Code] = A.port_type"

内容的提问来源于stack exchange，提问作者BigPicture