CODEWITHSUNDEEP
pythonsql
Erik Weber
Erik Weber

Reputation: 53

SQL query with Python doesn't work

I wrote this code to delete duplicates from a table. It works fine when executed in Microsoft SQL Server Management Studio but I can't get it to work executing with Python.

There is no error occuring, it's just not working ... I tried executing other queries aswell without any problems.

Someone an idea what could be wrong ? I'm using 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()

Upvotes: 2

Views: 3181

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1125058

cursor.execute() can execute one statement. You are passing in multiple statements. Split out your operations into separate cursor.execute() calls:

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)

Most likely you didn't mean to include the first SELECT.

I'm not sure why you are using time.sleep() statements in your Python code, those are not needed to ensure correct execution.

If you use the connection object and cursor as context manangers, they'll be automatically closed (unfortunately, the pymmsql project missed an opportunity to include transaction handling in their context manager implementation):

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

Upvotes: 1

Related Questions