How to handle a long SQL statement string in Python

Question

I am trying to get information from an SQL database using python

I was able to connect and retrieve data when the SQL statement was simple such as

cursor.execute("SELECT * FROM Client WHERE UsesTimesheet = 1 ORDER BY ClientName")

However, when I move to a more complex statement I get the error shown below

Traceback (most recent call last):
 File "F:\Python\Test - AutoCad.py", line 30, in <module>
where jobnum = 1205992")
File "C:\Python26\ArcGIS10.0\lib\site-packages\pymssql.py", line 196, in execute
raise OperationalError, e[0]
OperationalError: SQL Server message 102, severity 15, state 1, line 1:
Incorrect syntax near 'jobnum'.

This statement works when I use Microsoft SQL 2008 Client but not in Python.

What am I doing incorrectly? For complex statements should I be using SQLAlchemy?

Current Code Below

import pymssql
import _mssql
import sys

# Connect to db using Windows Integrated Authentication.
conn = _mssql.connect(server='000.000.0.0', database='Mydb', trusted=True)
conn = pymssql.connect(host='000.000.0.0', database='Mydb', trusted=True)

# prepare a cursor object using cursor() method
cursor = conn.cursor()

cursor.execute("SELECT PJI.*, PJO.*, \
        CST.ABCGS \
FROM  dbo.Traverse AS TRE \
              LEFT OUTER JOIN dbo.TraversePreEntry AS TPE \
                    ON TRE.JobNum = dbo.GetJobNumberFromGroupId(TPE.GroupId)\
              LEFT OUTER JOIN AutoCADProjectInformation AS PJI\
                    ON TRE.JobNum = PJI.JobNumber \
              LEFT OUTER JOIN CalculationStorageReplacement AS CST \
                    ON CST.ProjectNumber = dbo.GetJobNumberFromGroupId(TPE.GroupId)\
              LEFT OUTER JOIN dbo.TraverseElevations AS TEV\
                  ON TRE.TraverseId = TEV.TraverseId\
              LEFT OUTER JOIN VGSDB.dbo.ProjectOffice PJO\
                    ON PJI.PjbId = PJO.PjbId\
where jobnum = 1205992")

# Fetch rows
data = cursor.fetchall()

print "Info : %s " % str(data)

Martijn Pieters · Accepted Answer · 2012-05-28 15:59:22Z

Your python string is being joined together without newlines, thus there is no space before the where keyword. Better use triple-quoted strings when working with multi-line string literals:

cursor.execute("""\
SELECT PJI.*, PJO.*, 
        CST.ABCGS 
FROM  dbo.Traverse AS TRE 
              LEFT OUTER JOIN dbo.TraversePreEntry AS TPE 
                    ON TRE.JobNum = dbo.GetJobNumberFromGroupId(TPE.GroupId)
              LEFT OUTER JOIN AutoCADProjectInformation AS PJI
                    ON TRE.JobNum = PJI.JobNumber
              LEFT OUTER JOIN CalculationStorageReplacement AS CST
                    ON CST.ProjectNumber = dbo.GetJobNumberFromGroupId(TPE.GroupId)
              LEFT OUTER JOIN dbo.TraverseElevations AS TEV
                  ON TRE.TraverseId = TEV.TraverseId
              LEFT OUTER JOIN VGSDB.dbo.ProjectOffice PJO
                    ON PJI.PjbId = PJO.PjbId
where jobnum = 1205992""")

Triple-quoted strings maintain newlines:

>>> "one\
... two"
"onetwo"
>>> """one
... two"""
"one\ntwo"

If this is a one-of you do not necessarily need to use SQLAlchemy, but as your project grows you'll find that that library offers many advantages, including making conditional logic much easier (adding further WHERE clauses based on if/then branches, etc).

dusan · Accepted Answer · 2012-05-28 15:52:46Z

6

Put a space before the where keyword. Python doesn't add spaces when using \:

In [5]: print "a\
   ...: b"
ab

To complement Martijn Pieters answer, if you use triple quoted strings you have to remove the \, using both you don't get newlines:

In [6]: """a\
b"""
Out[6]: 'ab'

edited May 28, 2012 at 15:52

answered May 28, 2012 at 15:44

dusan

9,3033 gold badges39 silver badges56 bronze badges

Collectives™ on Stack Overflow

How to handle a long SQL statement string in Python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related