Skip to main content
 首页 » 编程设计

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

2025年05月04日10三少

我编写这段代码是为了从表中删除重复项。 在 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() 

请您参考如下方法:

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