Looping through SQLDW table with Python

Question

I have a table in a Microsoft Azure SQLDW that has a date field, as well as other columns. There is a row for each day in the past 10 years of business days. Currently the table has not been stored in such a way like "order by date" etc. Below is the code I have so far:

import pyodbc driver = '{ODBC Driver 13 for SQL Server}'
conn = pyodbc.connect('DRIVER='+driver+';
PORT=1433;SERVER='+server+‌​';
DATABASE‌​='+database+';
UID='+‌​username+';
PWD='+ password) 
cursor = conn.cursor() cursor.execute("SELECT * FROM index_att") i = 0 for row in cursor: i += 1 print(i)

If I'm using python to loop through every row in this table and I want to go in chronological order (from oldest date to current date) does the table need to be stored in a way that it is ordered by date? And do I need to add an additional incremental ID that starts at 1 with the oldest date and goes up each day?

Or is there a way to do this loop through dates with the data not sorted?

Answer 1

Currently the table has not been stored in such a way like "order by date" etc.

How the data is stored is irrelevant. If you want ordered data, the client just needs to request it with an ORDER BY clause on a SELECT query.

Answer 2

I would also ask you to review your process because doing row level operations on a Azure SQL Data Warehouse can cause large amounts of data movement.

Answer 3

Add an order by to your select statement:

cursor = conn.cursor() 
cursor.execute("SELECT * FROM index_att ORDER BY [MyDate]") 
i = 0 
for row in cursor: 
    i += 1 print(i)