Python - T-SQL statement works in SQL Server, but when I use it in Python it keeps erroring out

Question

I'm able to connect and do a basic query off a SQL Server database via Python but once I start adding INNER JOINs and such to the query Python errors out with:

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

I'll put the code below but I am thinking there must be some Python formatting I am getting wrong because the query works using other SQL tools.

In my searches I see that INNER JOIN should have the ON portion indented when using T-SQL in Python. Since I have so many INNER JOINs, maybe I am indenting incorrectly? I also saw that when you break up SQL in Python you have to put a \ at the end of each line.

Any help or links are appreciated!

    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)

Parfait · Accepted Answer · 2020-10-06 22:19:49Z

Because Order is an SQL Server keyword, you need to escape the identifier such as with square brackets: [Order]. Otherwise the compiler thinks you are trying to invoke the ORDER BY command. Even better use table aliases to avoid repeating longer table names:

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
     """

And while nested joins with parentheses are allowable (reminiscent of MS Access SQL), you can avoid such nesting since all joins are INNER. A flatter SQL statement can facilitate readability and maintainability.

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
     """

ggordon · Accepted Answer · 2020-10-06 22:26:30Z

You have several errors in your sql syntax, you do not need the additional brackets "((" when performing joins. You need not worry about indenting SQL statements however python indentation and newlines can be a bit tricky. To simplify your code, you could utilize multi-line strings (i.e. using """ some string """) in python eg.

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)

Collectives™ on Stack Overflow

Python - T-SQL statement works in SQL Server, but when I use it in Python it keeps erroring out

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related