SQL Server临时表在pyodbc代码中不可用 [英] SQL Server temp table not available in pyodbc code

问题描述

我在python中运行一系列复杂的sql查询，它涉及临时表。我的自动提交方法似乎没有工作从临时表中检索数据。我在下面使用的代码片段，这是我得到的输出：

I'm running a series of complex sql queries in python and it involves temp tables. My auto-commit method doesn't seem to be working to retrieve the data from the temp table. The code snippet I'm using below and this is the output I'm getting:

testQuery="""
    Select top 10 *
    INTO #Temp1
    FROM Table1 t1
    JOIN Table2 t2
    on t1.key=t2.key
"""
    cnxn=pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=DB;UID=UID;PWD=PWD')
    cnxn.autocommit=True
    cursor=cnxn.cursor()
    cursor.execute(testQuery)
    cursor.execute("""Select top 10 * from #Temp1""")
    <pyodbc.Cursor at 0x8f78930>


cnxn=pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=DB;UID=UID;PWD=PWD')
cnxn.autocommit=True
cursor=cnxn.cursor()
cursor.execute(testQuery)
cursor.execute("""Select top 10 * from #Temp1""")

推荐答案

即使这个问题有一个使用全局临时表而不是局部临时表，未来的读者可能从理解为什么问题发生在第一位。

Even though this question has a "solution", i.e., using global temp table instead of a local temp table, future readers might benefit from understanding why the problem happened in the first place.

当最后一个临时表使用所述表的连接被关闭。局部临时表（＃Temp1 ）和全局临时表（ ## Temp1 ）之间的区别是：本地临时表只对创建它的连接可见，而现有的全局临时表对任何连接都可用。

A temporary table is automatically dropped when the last connection using said table is closed. The difference between a local temp table (#Temp1) and a global temp table (##Temp1) is that the local temp table is only visible to the connection that created it, while an existing global temp table is available to any connection.

所以下面的代码使用本地临时表失败...

So the following code using a local temp table will fail ...

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT 1 AS foo, 2 AS bar INTO #Temp1
"""
crsr.execute(sql)

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT foo, bar FROM #Temp1
"""
crsr.execute(sql)
row = crsr.fetchone()
print(row)

...而使用全局临时表的完全相同的代码将会成功...

... while the exact same code using a global temp table will succeed ...

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT 1 AS foo, 2 AS bar INTO ##Temp1
"""
crsr.execute(sql)

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT foo, bar FROM ##Temp1
"""
crsr.execute(sql)
row = crsr.fetchone()
print(row)

...因为第二个 pyodbc.connect 调用打开与SQL Server单独的第二个连接，而不关闭第一个连接。

... because the second pyodbc.connect call opens a separate second connection to the SQL Server without closing the first one.

第二个连接无法看到由第一个连接创建的本地临时表。注意，局部临时表仍然存在，因为第一个连接从未关闭，但第二个连接不能看到它。

The second connection cannot see the local temp table created by the first connection. Note that the local temp table still exists because the first connection was never closed, but the second connection cannot see it.

但是，第二个连接查看全局临时表，因为第一个连接从未关闭，因此全局临时表继续存在。

However, the second connection can see the global temp table because the first connection was never closed and therefore the global temp table continued to exist.

这种类型的行为对ORM和其他机制有影响可以隐式地打开和关闭它执行的每个SQL语句的服务器连接。

This type of behaviour has implications for ORMs and other mechanisms that may implicitly open and close connections to the server for each SQL statement that it executes.

这篇关于SQL Server临时表在pyodbc代码中不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！