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?

• bated: In order to help we need to see your code ...
• wBob: You should look at Azure SQL Data Warehouse Workload Patterns and Anti-Patterns.

Answer 1

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.

• user8598455: Even If no data is being inserted/updated/deleted in the actual DB, but results of calculations are being outputted?

Answer 2

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.

• user8598455: Right, understood I can ORDER BY to get the output in an ordered format. But if somebody wants to run calculations comparing day to day, in order (Example: for each day in the DB compare the price of a stock to the previous day price) how can that be done if the data is not ordered and there is no incremental ID field?
• David Browne - Microsoft: You can use the TSQL Windowing Functions. See learn.microsoft.com/en-us/sql/t-sql/queries/…
• user8598455: Some of our users will be accessing the data through Python only. Would you recommend adding row ID's based on (ORDER BY date) to the DB so they don't have to do it themselves in their code?
• David Browne - Microsoft: For something like this: "for each day in the DB compare the price of a stock to the previous day price) how can that be done if the data is not ordered and there is no incremental ID field?" adding an row number column is not going to eliminate the need to write the logic in TSQL. They can't fetch all the DW data to python to do the processing, for one.

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)