Python TSQL语句在SQL Server中工作，但当我在Python中使用它时，它总是出错

Question

我能够通过Python连接SQL Server数据库并执行基本查询，但一旦我开始向查询添加INNER JOIN等Python错误，就会出现以下错误：

Incorrect syntax near the keyword 'Order'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n

我将把代码放在下面，但我认为一定有一些Python格式我弄错了，因为查询使用其他SQL工具工作

在我的搜索中，我看到在Python中使用T-SQL时INNER JOIN应该缩进ON部分。既然我有这么多INNER JOIN，也许我缩进不正确？我还看到，在Python中分解SQL时，必须在每行末尾加一个\

任何帮助或链接都将不胜感激

    import pymssql

    conn = pymssql.connect(server= 'xxx',
                           user= 'xxx',
                           password= 'xxx',
                           database= 'xxx'
                           )

    cursor = conn.cursor()

    sql = "SELECT PatientInfo.MRN, AccountPersonalInfo.LastName, Visit.VisitNumber, PatientInfo.FirstName, PatientInfo.LastName, AccountPersonalInfo.FirstName, Report.LastSignDate, Order.ProcedureDescList, Visit.Facility, Order.CompleteDate, Order.FillerOrderNumber \
FROM ((Comm4.dbo.Order Order  INNER JOIN Comm4.dbo.Report Report \
                                  ON Order.ReportID=Report.ReportID) \
      INNER JOIN (Comm4.dbo.PatientInfo PatientInfo INNER JOIN Comm4.dbo.Visit Visit \
                                                        ON PatientInfo.PatientID=Visit.PatientID) \
          ON Order.VisitID=Visit.VisitID) INNER JOIN Comm4.dbo.AccountPersonalInfo AccountPersonalInfo \
                                              ON Report.SignerAcctID=AccountPersonalInfo.AccountID \
WHERE  PatientInfo.MRN<>'TEMPORARY' AND Report.LastSignDate>={ts '2020-09-01 00:00:00'} AND Report.LastSignDate<{ts '2020-10-01 00:00:00'}) \
ORDER BY Report.LastSignDate, PatientInfo.MRN"

     cursor.execute(sql)

     row = cursor.fetchone()

     conn.close()

     print(row) 
     

Answer 1

因为Order是一个SQL Server keyword，所以需要用方括号转义标识符：[Order]。否则，编译器会认为您正在尝试调用ORDER BY命令。最好使用表别名以避免重复较长的表名：

sql = """SELECT pi.mrn
                , a.lastname
                , v.visitnumber
                , pi.firstname 
                , pi.lastname 
                , a.firstname 
                , r.lastsigndate
                , o.proceduredesclist
                , v.facility
                , o.completedate
                , o.fillerordernumber 
         FROM 
             (
               (comm4.dbo.[ORDER] o
                INNER JOIN comm4.dbo.report r
                      ON o.reportid = r.reportid) 
             INNER JOIN 
               (comm4.dbo.patientinfo pi
                INNER JOIN comm4.dbo.visit v 
                      ON pi.patientid = v.patientid) 
               ON o.visitid = v.visitid
             ) 
         INNER JOIN comm4.dbo.accountpersonalinfo a
               ON r.signeracctid = a.accountid 
         WHERE pi.mrn <> 'TEMPORARY' 
           AND r.lastsigndate >= {ts '2020-09-01 00:00:00'} 
           AND r.lastsigndate <  {ts '2020-10-01 00:00:00'}
         ORDER BY r.lastsigndate
                  , pi.mrn
     """

虽然带括号的嵌套联接是允许的（让人想起MS Access SQL），但您可以避免这种嵌套，因为所有联接都是INNER。更平坦的SQL语句可以提高可读性和可维护性

sql = """SELECT pi.mrn
                , a.lastname
                , v.visitnumber
                , pi.firstname 
                , pi.lastname 
                , a.firstname 
                , r.lastsigndate
                , o.proceduredesclist
                , v.facility
                , o.completedate
                , o.fillerordernumber 
         FROM comm4.dbo.[ORDER] o
         INNER JOIN comm4.dbo.report r
               ON o.reportid = r.reportid
         INNER JOIN comm4.dbo.visit v 
               ON o.visitid = v.visitid 
         INNER JOIN comm4.dbo.patientinfo pi
               ON pi.patientid = v.patientid
         INNER JOIN comm4.dbo.accountpersonalinfo a
               ON r.signeracctid = a.accountid 
         WHERE pi.mrn <> 'TEMPORARY' 
           AND r.lastsigndate >= {ts '2020-09-01 00:00:00'} 
           AND r.lastsigndate <  {ts '2020-10-01 00:00:00'}
         ORDER BY r.lastsigndate
                  , pi.mrn
     """

Answer 2

sql语法中有几个错误，不需要额外的括号（（“在执行联接时。您不必担心sql语句的缩进，但是python缩进和换行可能有点棘手。为了简化代码，您可以在python中使用多行字符串（即使用""" some string """）

import pymssql

conn = pymssql.connect(server= 'xxx',
                           user= 'xxx',
                           password= 'xxx',
                           database= 'xxx'
                           )

cursor = conn.cursor()

sql = """
SELECT 
    PatientInfo.MRN, 
    AccountPersonalInfo.LastName, 
    Visit.VisitNumber, 
    PatientInfo.FirstName, 
    PatientInfo.LastName, 
    AccountPersonalInfo.FirstName, 
    Report.LastSignDate, 
    Orders.ProcedureDescList, 
    Visit.Facility, 
    Orders.CompleteDate, 
    Orders.FillerOrderNumber 
FROM 
    Comm4.dbo.Order Orders  
INNER JOIN 
    Comm4.dbo.Report Report ON Orders.ReportID=Report.ReportID
INNER JOIN 
    Comm4.dbo.Visit Visit ON Orders.VisitID=Visit.VisitID
INNER JOIN 
    Comm4.dbo.PatientInfo PatientInfo ON PatientInfo.PatientID=Visit.PatientID
INNER JOIN 
    Comm4.dbo.AccountPersonalInfo AccountPersonalInfo ON 
    Report.SignerAcctID=AccountPersonalInfo.AccountID 
WHERE  
   PatientInfo.MRN<>'TEMPORARY' AND 
   Report.LastSignDate>={ts '2020-09-01 00:00:00'} AND 
   Report.LastSignDate<{ts '2020-10-01 00:00:00'}

ORDER BY 
   Report.LastSignDate, PatientInfo.MRN
"""

cursor.execute(sql)

row = cursor.fetchone()

conn.close()

print(row)