python - 使用 Python 的 SQL 查询不起作用

Question

我编写这段代码是为了从表中删除重复项。在 Microsoft SQL Server Management Studio 中执行时运行良好但我无法使用 Python 执行它。

没有错误发生，它只是不工作......我也尝试执行其他查询，没有任何问题。

有人知道哪里出了问题吗？我正在使用 Python 2.7

import pymssql    
import time

conn = pymssql.connect(server='rfhete755', database='EEX')

c = conn.cursor()

p = """
    SELECT [ID]
        ,[Operator]
        ,[Source]
        ,[Timestamp]
        ,ROW_NUMBER() OVER (
            PARTITION BY [Operator]
            ,[Source]
            ,[Timestamp] ORDER BY [Timestamp]
            ) AS Rnum
    FROM [EEX].[dbo].[Wind_Solar];

    WITH CTE
    AS (
        SELECT [Operator]
            ,[Source]
            ,[Timestamp]
            ,ROW_NUMBER() OVER (
                PARTITION BY [Operator]
                ,[Source]
                ,[Timestamp] ORDER BY [Timestamp]
                ) AS Rnum
        FROM [EEX].[dbo].[Wind_Solar]
        )
    DELETE
    FROM CTE
    WHERE Rnum <> 1"""

print p

c.execute(p)

time.sleep(2)

conn.commit()

time.sleep(1)

c.close()
conn.close()

Answer 1

cursor.execute() 可以执行一个语句。您正在传递多个语句。将您的操作拆分为单独的 cursor.execute() 调用:

select_all = """
    SELECT [ID]
        ,[Operator]
        ,[Source]
        ,[Timestamp]
        ,ROW_NUMBER() OVER (
            PARTITION BY [Operator]
            ,[Source]
            ,[Timestamp] ORDER BY [Timestamp]
            ) AS Rnum
    FROM [EEX].[dbo].[Wind_Solar]"""
c.execute(select_all) 

delete_all_but_first = """
    WITH CTE
    AS (
        SELECT [Operator]
            ,[Source]
            ,[Timestamp]
            ,ROW_NUMBER() OVER (
                PARTITION BY [Operator]
                ,[Source]
                ,[Timestamp] ORDER BY [Timestamp]
                ) AS Rnum
        FROM [EEX].[dbo].[Wind_Solar]
        )
    DELETE
    FROM CTE
    WHERE Rnum <> 1"""
c.execute(delete_all_but_first)

很可能您并不是要包含第一个 SELECT。

我不确定您为什么在 Python 代码中使用 time.sleep() 语句，这些语句不是确保正确执行所必需的。

如果您使用连接对象和游标作为上下文管理器，它们将自动关闭(不幸的是，pymmsql 项目错过了在其上下文管理器实现中包含事务处理的机会):

with pymssql.connect(server='rfhete755', database='EEX') as conn:
    with conn.cursor() as c:
       c.execute(delete_all_but_first)
    conn.commit()