pymssql（Python模块）无法使用临时表

Question

这不是一个问题，更像是提前给出的答案。（我在这个网站上得到了很多帮助，想要回馈一下。）

我之前在用python通过pymssql运行一个很大的SQL查询时遇到了麻烦，这个查询在直接通过MS SQL运行时没有问题。（比如，我在用MS SQL Server Management Studio在python之外运行它时，一切正常。）

后来我终于发现了问题：pymssql无法处理临时表。至少我用的版本是1.0.1，确实不行。

为了证明这一点，这里有一段我的代码，稍微修改过以保护知识产权问题：

conn = pymssql.connect(host=sqlServer, user=sqlID, password=sqlPwd, \
                       database=sqlDB)
cur = conn.cursor()
cur.execute(testQuery)

上面的代码会失败（具体来说是没有返回数据，如果你调用cur.fetchone()，会报错"pymssql.OperationalError: No data available."），如果我把testQuery定义成下面这样：

testQuery = """
CREATE TABLE #TEST (
[sample_id] varchar (256)
,[blah] varchar (256) )
INSERT INTO #TEST
SELECT DISTINCT
        [sample_id]
        ,[blah]
FROM [myTableOI]
WHERE [Shipment Type] in ('test')

SELECT * FROM #TEST
"""

但是，如果testQuery定义成下面这样，它就能正常工作。

testQuery = """
SELECT DISTINCT
        [sample_id]
        ,[blah]
FROM [myTableOI]
WHERE [Shipment Type] in ('test')

"""

我在谷歌和Stack Overflow上搜索过，也没有找到关于这个特定问题的信息。我还查看了pymssql的文档和常见问题，网址是http://code.google.com/p/pymssql/wiki/FAQ，也没有看到提到不允许使用临时表的内容。所以我想把这个“问题”补充一下。

Answer 1

对于那些遇到这个问题并可能有类似困扰的人，我想分享一下我从原帖中学到的东西。原来在 pymssql 中确实可以使用临时表，但在处理提交时必须非常小心。

我先通过一个例子来解释。以下代码是可以正常工作的：

testQuery = """
CREATE TABLE #TEST (
[name] varchar(256)
,[age] int )

INSERT INTO #TEST
values ('Mike', 12)
,('someone else', 904)

"""

conn = pymssql.connect(host=sqlServer, user=sqlID, password=sqlPwd, \
    database=sqlDB) ## obviously setting up proper variables here...
conn.autocommit(1)
cur = conn.cursor()
cur.execute(testQuery)
cur.execute("SELECT * FROM #TEST")
tmp = cur.fetchone()
tmp

这段代码会返回第一个项目（后续的获取会返回其他项目）：

('Mike', 12)

但是下面这段代码就不行：

testQuery = """
CREATE TABLE #TEST (
[name] varchar(256)
,[age] int )

INSERT INTO #TEST
values ('Mike', 12)
,('someone else', 904)

SELECT * FROM #TEST

"""

conn = pymssql.connect(host=sqlServer, user=sqlID, password=sqlPwd, \
    database=sqlDB) ## obviously setting up proper variables here...
conn.autocommit(1)
cur = conn.cursor()
cur.execute(testQuery)
tmp = cur.fetchone()
tmp

这会报错，提示 "pymssql.OperationalError: No data available."。原因我能理解的是，不管你是否开启了自动提交，或者你是否手动提交，所有表在尝试读取之前都必须明确创建并提交。

在第一个例子中，你会注意到有两个 "cur.execute(...)" 的调用。第一个是用来创建临时表的。在完成 "cur.execute()" 后，由于开启了自动提交，SQL脚本被提交，临时表就创建好了。然后再调用另一个 cur.execute() 来读取这个表的数据。在第二个例子中，我尝试“同时”创建和读取表（至少在 pymssql 的理解中是这样……在 MS SQL Server Management Studio 中是可以正常工作的）。因为这个表之前没有被创建和提交，所以我无法查询它。

哇……发现这个问题真是麻烦，而且我需要调整我最初在 MS SQL Server Management Studio 上开发的代码，以便它能在脚本中正常工作。唉……

Answer 2

更新：2016年7月

之前被接受的答案现在已经不再适用了。第二个“不会工作的”例子其实在Python 2.7.11下，使用pymssql 2.1.1是可以正常工作的（只要把conn.autocommit(1)换成conn.autocommit(True)，这样就不会出现“类型错误：无法将整数转换为布尔值”的问题）。