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?

You should look at Azure SQL Data Warehouse Workload Patterns and Anti-Patterns. — wBob
– wBob, Commented Sep 12, 2017 at 21:17

Allan Miller · Accepted Answer · 2017-09-12 16:09:19Z

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.

answered Sep 12, 2017 at 16:09

Allan Miller

211 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

user8598455 Over a year ago

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

David Browne - Microsoft · Accepted Answer · 2017-09-12 15:45:31Z

0

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.

answered Sep 12, 2017 at 15:45

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

4 Comments

user8598455 Over a year ago

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 Over a year ago

You can use the TSQL Windowing Functions. See learn.microsoft.com/en-us/sql/t-sql/queries/…

user8598455 Over a year ago

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 Over a year ago

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.

Antimony · Accepted Answer · 2017-11-15 20:01:13Z

0

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)

edited Nov 15, 2017 at 20:01

Antimony

2,2403 gold badges28 silver badges39 bronze badges

answered Nov 15, 2017 at 19:20

James.Smith

1392 silver badges7 bronze badges

Collectives™ on Stack Overflow

Looping through SQLDW table with Python

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related