SQL query with Python doesn't work

Question

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

@RaduGheorghiu: if you are going to arbitrarily reformat the SQL, at least don't break Python syntax. — Martijn Pieters
– Martijn Pieters, Commented Dec 10, 2015 at 11:35
@MartijnPieters It was't arbitrarily, but it's good to know that multi-line strings requires escaping the quotes, in Python. We can't all know every standard / syntax caveat. — Radu Gheorghiu
– Radu Gheorghiu, Commented Dec 10, 2015 at 11:37
@RaduGheorghiu: very few programming languages allow for newlines as literals in string definitions. C-like languages usually use the \n escape sequence to encode those, and using a literal newline is a syntax error. — Martijn Pieters
– Martijn Pieters, Commented Dec 10, 2015 at 11:40
@MartijnPieters Now that you mention it, I believe using a `\` at the end of every line-break was also possible? Or was that just for the code and not variable values? — Radu Gheorghiu
– Radu Gheorghiu, Commented Dec 10, 2015 at 11:41

Martijn Pieters · Accepted Answer · 2015-12-10 11:45:57Z

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

Collectives™ on Stack Overflow

SQL query with Python doesn't work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related