如何在Python中处理长SQL语句字符串

I am trying get information from an SQL database using python

我正在使用python从一个SQL数据库中获取信息。

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

当SQL语句很简单时，我能够连接和检索数据。

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

当我使用Microsoft SQL 2008客户端而不是python时，这个语句就可以工作。

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

我做错了什么?对于复杂语句，我应该使用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)

2 个解决方案

#1

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:

您的python字符串在没有换行的情况下被连接在一起，因此where关键字之前没有空格。使用多行字符串时，最好使用三引号字符串:

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

如果这是一个您不需要使用SQLAlchemy的库，但是随着项目的增长，您将发现该库提供了许多优势，包括使条件逻辑变得更容易(进一步添加基于If /then分支的WHERE子句，等等)。

#2

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

在where关键字前放一个空格。当使用\:

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:

为了补充Martijn Pieters的答案，如果你使用三重引用的字符串，你必须删除\，使用两个你没有得到换行:

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

#1