pymssql ( python module ) unable to use temporary

Question

This isn't a question, so much as a pre-emptive answer. (I have gotten lots of help from this website & wanted to give back.)

I was struggling with a large bit of SQL query that was failing when I tried to run it via python using pymssql, but would run fine when directly through MS SQL. (E.g., in my case, I was using MS SQL Server Management Studio to run it outside of python.)

Then I finally discovered the problem: pymssql cannot handle temporary tables. At least not my version, which is still 1.0.1.

As proof, here is a snippet of my code, slightly altered to protect any IP issues:

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

The above code FAILS (returns no data, to be specific, and spits the error "pymssql.OperationalError: No data available." if you call cur.fetchone() ) if I call it with testQuery defined as below:

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
"""

However, it works fine if testQuery is defined as below.

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

"""

I did a Google search as well as a search within Stack Overflow, and couldn't find any information regarding the particular issue. I also looked under the pymssql documentation and FAQ, found at http://code.google.com/p/pymssql/wiki/FAQ, and did not see anything mentioning that temporary tables are not allowed. So I thought I'd add this "question".

Answer 1

For those who run across this question and might have similar problems, I thought I'd pass on what I'd learned since the original post. It turns out that you CAN use temporary tables in pymssql, but you have to be very careful in how you handle commits.

I'll first explain by example. The following code WILL work:

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

This will then return the first item (a subsequent fetch will return the other):

('Mike', 12)

But the following will NOT work

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

This will fail saying "pymssql.OperationalError: No data available." The reason, as best I can tell, is that whether you have autocommit on or not, and whether you specifically make a commit yourself or not, all tables must explicitly be created AND COMMITTED before trying to read from them.

In the first case, you'll notice that there are two "cur.execute(...)" calls. The first one creates the temporary table. Upon finishing the "cur.execute()", since autocommit is turned on, the SQL script is committed, the temporary table is made. Then another cur.execute() is called to read from that table. In the second case, I attempt to create & read from the table "simultaneously" (at least in the mind of pymssql... it works fine in MS SQL Server Management Studio). Since the table has not previously been made & committed, I cannot query into it.

Wow... that was a hassle to discover, and it will be a hassle to adjust my code (developed on MS SQL Server Management Studio at first) so that it will work within a script. Oh well...

Answer 2

Update: July 2016

The previously-accepted answer is no longer valid. The second "will NOT work" example does indeed work with pymssql 2.1.1 under Python 2.7.11 (once conn.autocommit(1) is replaced with conn.autocommit(True) to avoid "TypeError: Cannot convert int to bool").