Coming off the excitement of my last post about writing a simple bare-bones python usage of Tableau's brand-new Data Extracts API from version 8.0 - I figured that it was time to build on that. Let's take a step forward and get a little more complicated - AND a little more useful.

Oh, noes!

I'm encapsulated in a proprietary binary data file OF EMOTION!

Save me, Cringer!

Wait. This could be MORE useful then my last post?

You mean your business doesn't run on a billion rows of dummy shit data? Weird.

Anyways, this time we're going to extract data from SQL Server with a single SQL query and populate a new TDE file. This script has 3 main features: [unordered_list style="tick"]

• Can run any query you want (even 'select * from' queries)
• You don't have to pre-define the field names
• You don't have to pre-define the data types

[/unordered_list]

I got you, shawty. I GOT YOU.

All of which we had to explicitly state in the last script (even as a 'proof of concept'), and as you can imagine - with a wide result set, defining every little thing and then inserting every little thing can be a huge pain in the ass (not to mention ugly). With my approach, it's literally plug and play - err, paste and play. You can even run queries like "select * from " it's not even necessary to use the field names (assuming they are all unique, that is - more on that later) - and yes, I mentioned that twice for EMPHASIS!

Ye gods! No scripting out my lame_ass_field_names? Huzzah!

Basically, the script works like this:

• Connect to SQL Server
• Execute your SQL query
• Look at the field names and first row data
• Have Python try to guess what data types they are (based on that first row)
• Map those data types and field names to a TDE schema definition (TableDefinition())
• Loop through the entire result set, calling each field by name and type so the correct insert method is used (setDouble, setInteger, setNull, setDateTime, setBoolean, etc)
• Do a little headbanging as you watch the text fly by!
• Close the file (write the TDE to disk)
• Close the SQL connection
• Wipe hands on pants

Booya. Done.

And you didn't have to do a damn thing except give it a server connection and a SQL query. Now you can open that fresh TDE file and have yourself a little data party. { Pants optional } [box type="tick" style="rounded" border="full"]Requires: Python 2.7.X+, the DataExtracts module, and pymssql module (pre-compiled binaries here). Oh, and it would help to have a SQL Server and Tableau Desktop as well. ;)[/box] Here it is! Cut, Paste, and give it a try, gosh darnint! [box type="info" style="rounded" border="full"]UPDATE: Added var (rowoutput = True / False) for turning off the debug row / column output - good for looking into errors but slowed the script down like 10x[/box] [cc lang="python"]

ryan robitaille 12/6/2012

simple Tableau Data Extract creation from a single microsoft sql server sql statement

import dataextract as tde # saves some typing, cause i'm a lazy fucker import os, time, pymssql # for file manipulation, script timing (not necc), database access! ###################### FOR YOUR PARAMETERS, SON! ###################### tdefilename = 'ufo_datas.tde' sql = "select * from UFOdata.dbo.Sightings" # whatever sqlserverhost = 'localhost' sqlusername = 'sa' sqlpassword = 'passy' sqldatabase = 'UFOdata' rowoutput = False # for DEBUGGING data errors / slows shit down 10X however ###################### FOR YOUR PARAMETERS, SON! ###################### dotsevery = 75 start_time = time.time() # simple timing for test purposes mssql_db = pymssql.connect(host=sqlserverhost, user=sqlusername, password=sqlpassword, database=sqldatabase, as_dict=True) # as_dict very important mssql_cursor = mssql_db.cursor() mssql_cursor.execute(sql) print ' ' print '[ Note: Each . = ' +str(dotsevery)+ ' rows processed ]' fieldnameslist = [] # define our empty list #go through the first row to TRY to set fieldnames and datatypes for row in mssql_cursor: itemz = len(row.keys())/2 # because the dict rowset includes BOTH number keys and fieldname keys for k in row.keys(): fieldnameslist.append(str(k) + '|' + str(type(row[k])).replace("","").replace("Current Known Issues:

1. I'm only looking at the first row for data type guessing. I really should be taking a much larger sample of the data.
2. If you do a bunch of joins, don't explicitly define the field names in the query, and have duplicate field names in the result set - the file will miss some fields, or even bomb out completely. i.e. - Don't do a:

select a.*, b.*, c.* from table1 a, table2 b, table3 c 
            where a.id = b.id and a.id = c.id

But you wouldn't do that fucking nonsense anyways - right? ;) [/box] I'm also thinking of making a version of this with command line parameters instead of file editing.

i.e. Python TDE_from_mssql.py 
--file=belly.tde 
--server=servername 
--username=sa 
--database=db45 
--sql="select * from bellybutton"

What's next for TDE file population posts? Web scraping, MySQL, Oracle, flat files? Might as well hit 'em all.