Ryan Robitaille

This seems good, but I am having trouble finding the Tde file it generated….
I have non to basic knowledge on Python

Nice post(s).

I’m using the Extract API for a nightly refresh, and looking for a way to (programmatically) validate the success of a run. I trap for errors thrown by the API, but I’d like to get a row count, column summation over rows, that sort of thing. Thoughts? Is this possible?

Thank you

Is there an easy way to extract from TDE to CSV as well ?

Ryan, is it possible to convert TDE to MDB using this process or at all?

I realise this is old, but for new viewers I will respond to “Love to get some feedback so I can make it better”

Comments apply equally to the new hyper format for extracts.

In pymssql and other DBAPI compliant python libraries, cursor.description will tell you both name and a type_code for each column so no need for the for loop to read the first row and the str(type()) shenanigans.

Alternatively you can query information_schema tables to discover the SQL datatypes and base your mapping off those metadata tables. Most relational databases have those tables ( MSSQL, Oracle, Postgres, MySQL, DB2).

You can also do what Tableau does internally, and issue the SQL query twice, the first time wrapping it in “SELECT TOP 0 * FROM(” + yourquery + “)” or on other databases apppending “LIMIT 0″ which will return a dataset with no rows , and do it very quickly, but will still allow you to get the schema metadata so you can prepare for receiving the real data.

The main loop runs for each row in the cursor, then for each field in fieldlist.
Firstly it does the split function twice, when it could be done once using tuple unpacking e.g. `fieldtype, fieldname = fieldlist.split(‘|’)` , but that would still be doing it redundantly for each cell in each row, i.e. it calls split function (2 * columncount * rowcount) when instead of a list of pipe-separated-strings it could have been a list of tuples up front. e.g. fieldlist.append((fieldname, fieldtype)) then referenced as fieldlist[0] and fieldlist[1]. That’s still not much better though, because string manipulation in python is cheap.

Looping over that fieldlist is done as a series of `if` statements, and it will check all types for every cell, even if it hits a match on the first test for str and has already set the field, it will continue to check if the value is an int. Put common datatypes at the top and use elif to short-circuit the loop.

A better approach is to avoid testing types since they are known. You could generate a function dynamically based on the schema. You can use a factory function to loop over the schema by column ordinal and return a function that works on a whole cursor row at a time, with the appropriate set{type} functions for the exact set of columns in your dataset, instead of testing every field in every row. Apart from the network time taken to download data, this looping and redundant datatype checking is the biggest performance bottleneck.

Calling print function to show dots is also useful for development but should be removed for unattended usage.Function calls are slow, especially IO like print.

Instead of using a counter for each row, just use the API to connect to the final extract file created and use the inbuilt function that tells you rowcount.

For large rowcounts, flush the file every 1 gig or so, by closing & reopening the extract file. The API will append data so it’s fine to do this. Note, equally it won’t protect you from duplicating data so be careful not to append the same data.. You can guess when it is 1gig by estimating using a rowcount or some other method. If you don’t do this you’ll out-of-memory and the extract won’t be written.

If you’re copying & pasting this into your environment, remove the comments where things are referred to a b*** and all the sh** and f*** swearing, it’s juvenile at the least and offensive at worst; no need to add to the BRO-grammer problem. A bit of humour in a tutorial blog is great but this just detracts from the usefulness of this to the community.