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