How to handle long string SQL statement in Python

Question

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

I managed to connect and get data when the SQL statement was simple, for example

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

However, when I move on to a more complex expression, I get the error message 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 the Microsoft SQL 2008 client, but not in python.

What am I doing wrong? For complex statements, should I use SQLAlchemy?

http://www.sqlalchemy.org/

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 AS PJO ON PJI.PjbId = PJO.PjbId where jobnum = 1205992""") # Fetch rows data = cursor.fetchall() print "Info : %s " % str(data) 

Answer 1

Your python string is concatenated without newlines, so there is no space before the where keyword. It is better to use triple quotes when working with multiline 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 quotes support newlines:

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

If this is one of you, you do not have to use SQLAlchemy, but as your project grows, you will find that this library offers many advantages, including simplifying conditional logic (adding additional WHERE clauses based on if / then branches, etc. )

Answer 2

Put a space before the where keyword. Python does not add spaces when using \:

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

To complement Martijn Pieters answer, if you use triple quotation marks, you need to remove \ using how you will not get newline characters:

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