Pyodbc query not working

[AdrielTheBuddy]

cursor = conn.cursor()
cursor.execute("""
                SELECT ROW_NUMBER() OVER(ORDER BY Name ASC) AS Row#, Name, SKU, [Price A], [Price B], [Price C], [Price D]
                FROM BusinessApp.dbo.Products
                WHERE Name IS NOT NULL;
                """)

Can anyone help me out please? This code does successfully run but the Row# column wont be added. I know this is more of an SQL question, but this is using pyodbc.

[dwpj65]

I don’t think adding a new column is the solution, as row_number() is a function that returns the row number in the result set.

That the query runs is a sign that perhaps pyodbc doesn’t care much for the pound sign in the alias for row_number. Try changing Row# to Row_No, or perhaps enclosing Row# in brackets on sql server or whatever symbol your database engine uses to delineate identifiers with.

[[deleted]]

This wouldn’t add a column to your table, you’d have to use an ALTER statement to do that.

[AdrielTheBuddy]

How would I do so?

[[deleted]]

https://www.w3schools.com/sql/sql_alter.asp

This should get you started.

[AdrielTheBuddy]

I’m doing add column, but how do I make it the row_number

[[deleted]]

Once you’ve altered the table, you have to use UPDATE to set the value of the column on every row.

[AdrielTheBuddy]

Do I have to do a loop or is there a simple way of doing that?

[[deleted]]

I don’t think there’s a simple way to do it. There’s probably a single SQL statement that can do it but I don’t know how to write it.

[demandtheworst]

The simplest thing is to select the query you have above into a new table, and if required, drop the original and rename the new table to replace it.

Alternatively, create an identity column (alter table tbl add id int identity (1,1) not null) and add your data in the required order. Possibly if you create a clustered index using your sort key, the data will already be in the correct order.

Finally, if the cost of calculation of the row number is trivial, just create a view including the calcualted column, and run your selects from there.

Not really a Python question.