Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to handle a long SQL statement string in Python

Tags:

python

sql

I am trying 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 incorrect? For complex statements should I be using 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)
like image 418
Tristan Forward Avatar asked May 28 '12 15:05

Tristan Forward


People also ask

How do you create a multiline query in Python?

Moreover, backslash works as a line continuation character in Python. You can use it to join text on separate lines and create a multiline string. Finally, there is string join() function in Python which is used to produce a string containing newlines.

How do you pass a list of strings in SQL query in Python?

How to pass a python list of strings to SQL query such as select * from table where name in (names_from_python_list) where names_from_python_list is comma separated strings from python list? Doing ','. join(name for name in name_list) gives all the names in the list as a string i.e.


1 Answers

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).

like image 192
Martijn Pieters Avatar answered Oct 16 '22 01:10

Martijn Pieters