I always say that “ease of use” and “flexibility” are at opposite ends of the spectrum – when one goes up, the other goes down. WYSIWYG applications have their place (I suppose), I prefer coding by hand in a text editor anyday.

Anyways, today we’re going to make a simple Excel data export with pythons xlwt module (which is a more updated fork of the old pyExcelerator module). I’ll try and comment well so you can cut-n-paste to your hearts content. As well all know, there’s nothing better than Googling for a solution and finding one that is 90% what you need and being able to just hack that last 10% in – saves time and sanity. Some purists would argue that its wrong… maybe, but there is a time and place for everything.

Anyways, here we go – as always, you can go step-by-step (like the Full House theme song) with me through the code, or skip to the bottom and just download the whole file. Its small, but at least this saves you from having to do a ton of Ctrl-C and Ctrl-V…

Ok, Lets get (motherfuckin’) steppin’… (pardon me – its a live Metallica reference, can’t help it, I’m a product of the 80s and 90s whether I like it or not)

Standard stuff – just loading some modules that we’re going to need. Time and String are part of the standard Python distriution (I’m using 2.6 in this example), cx_Oracle I already discussed HERE, and xlwt can be found HERE (http://pypi.python.org/pypi/xlwt) grab the packages for your platform of choice and go nuts.

There is a bit more going on here, but nothing really complex – setting up our Oracle connection, timestamping the START of the script, creating our Oracle cursor, defining our SQL query (just some random Oracle generated nonsense for the sake of this demo using the awesome and under-used ‘DBMS_RANDOM’ functions), and getting that result set into a python object.

Here we start some Excel formatting. Formatting is optional, but I figured that I’d throw in a little bit for some flavor. I can’t stand when older Excel uses that horrible “OCR A” font as the default. So what I’m doing is defining “styles” that can applied to each cell as we write data out to them. I’m using the same font throughout (in this case Calibri), but I’m making the header row bold and having a grey cell background.

Confused? Don’t worry – It will make a lot more sense in a moment.

Here we are looping through the result set. I’m starting the rows at “row 1″, which is actually row number 2 in Excel – we need the space in row number 1 in order to put our header row at. Otherwise we would start the output on row_number 0.

For each row in out result set, we have to write out all the fields in their individual columns. It sounds like a lot of work, but Python makes it easy. Just remember to increment your column numbers out and then reset them to 0 (aka literal column 1) when you start the next row.

If we wanted to get a little more tricky and “automatically” set the column width too. That takes a bit of finesse though, since not all field string lengths are created equally…

Still with me? Good. (if not, let me know in the comments and I’ll try and clarify things more).

Here we are writing the header row (with our fancy “style” attribute), we are explicitly writing it on the first column (the one we skipped earlier when writing out the data).

Then we save our excel file as ’sample_output.xls’ or ‘angry_beavers.xls’ or ‘chiminny_changa.xls’, whatever floats your boat. Just for shits and giggles, I’m taking the start timestamp and and the end timestamp and showing how long the script too to execute, but its pretty much useless in this case and olney serves to have some (not so) interesting output for me to look at.

Is anyone confused, angry, irate, excited? Let me know in the comments!

Here is the entire commented script file for downloading.

It never really mattered what kind of beast you were up against – Access, Paradox, Oracle, MySQL, Excel, delimited files, or just text files with shitty formatting. Say what you want about SQL Server 2000 and DTS – but it was hella flexible (probably unmatched in ease and flexibility still to this day, IMHO).

Although I spend a good deal of time with Oracle these days – I have recently taken on some large migrations using SQL Server 2008, which pushed me to dig up my archaic library of SQL code I used to cut-n-paste back and forth in the 2000 days in order to get just-about-anythinhg done. I’m going to be posting some of the more useful and interesting ones

It may be simple, or it may be complicated – but when you’re searching for a hammer – the last thing you want is a bunch of meat tenderizers. Its damned close, but not exactly what you need. Today I’m posting a simple SQL Server cursor loop statement that you can add to your collection of templates.

Disclaimer: Now some of you might be asking: Isn’t this pretty elementary? Yes, but maybe not everyone reading this has your “l33t hax0r skillz”, ok there, Captain Ahab?

Screw that noise. I’m all about creating specific solutions to specific problems – and nothing is more usable, extensible, and straight-up customizable than a simple T-SQL cursor loop. It doesn’t matter if youre updating rows on a separate table, doing a complicated INSERT INTO, doing some multi-variable lookups, or hell – maybe all of the above. Use your imagination. A good starting point prevents you from getting “code-blocked” and staring at a blank text editor or query screen as your jaws hangs open like a common mouth breather.

I write complicated ass SQL all day, and I open templates like this to modify all the time. I’ve got a whole library of them just for that purpose. I mean, c’mon lets get real, who actually sits down and writes stuff from scratch EVERY time? I’m all about the self-plagiarism, especially if it saves me time at the end of the day.

This example is supposed to be a simple as possible, while also being semi-plausible. Hypothetically, lets say you were building a summary table of all employees broken down by office, salary, department, etc. What we are doing is setting a True flag in a field on the summary table for the employee who has the highest salary for that particular office (I know it sounds odd – but like I said, its hypothetical, this could just as easily be doing a top flag based on department, or number of sales, seniority, etc. – I just thought that using salary by office is an easily understood one).

Here is an explanation in English about what the example is doing – data-wise…

First, we grab all the individual Office Ids – then go one by one through each of the different offices ranking the employees by salary, taking the highest paid Employee ID and then updating that same tables “Top Salary in the Office” field for that particular employee.

Depending on your data – when building a summary table (or Data Warehouse Fact table, or Analysis Services Cube for that matter) these types of “flag” fields often make reporting on such items much easier.

Cartoons have steered me wrong once again.

Besides… the supervising Goat master scolded me before the Goat had a chance to fully understand the situation he was being out in. Maybe that particular Goat had diabetes and wasn’t allowed to eat my tuna can. I’ll never be sure. I generally don’t eat tin cans, but if I did, it would have been delicious, I can tell.

I have to say, honestly, If there is anything better than a goat, its a goat that you can walk up to and pet on the back of the head. Actually, its a goat that you can walk up to, pet on the back of the head and then proceed to feed garbage to – while obviously being protected by a sturdy ‘Goat Barrier’, because I don’t know how the goat feels about killing me. Hopefully, he’s against it… much like I am.

Imagine that. Killed by a goat. There’d be no evidence left.

Not to mention that fact the prosecutors wouldn’t be able to get a single goat to take the stand in my defense. I think its a union thing, they can’t turn on their own. I wouldn’t rule out a Billygoat doing it, but they have much less credibility than Goats.

In this multi-part post I’m going to start with a simple data transport across data platforms (with minimal exception handling), and later mix in some fancier ETL functions and “robustness” (if that’s even a word – I know I’ve heard Cheese-Nip Sales guys use it before). I guarantee its a hell of a lot faster and more reliable than any of the commercial products out there (I know, because I’ve use them all).

In my not-so-humble opinion, you should NEVER trade point-and-click usability and prettiness for rock-solid reliability and flexibility.

Note: I’m going to be doing this in a nice and easy step-by-step way – I’m not trying to insult anyone’s intelligence, but I find that when people gloss over things because “everyone knows XYZ”, it alienates people and makes shit downright confusing for others – so if you’re a python smarty pants and already know all this, just skip that section and go buy yourself a cup of green tea (or whatever the hell you genius types drink these days). I want these posts to be a definitive guide, and the only way to do that is be thorough.

Ok, here is the task we are completing in this post – Writing a simple (non-interactive) Python script to pull data from an Oracle table and insert it into a SQL Server table (and/or another Oracle database table). You can run it by hand on the command line or schedule it and it will always work the same – all the while using very little overhead.

I spend a lot of time in an old school Oracle shop and you’d be shocked how many people simply cannot do things this without resorting to ridiculous (and idiotic) methods (writing PL/SQL out to a flat file on the server, reading flat file in as an external table, etc.) – well, enough of that madness. Think of Python as the “glue” to stick things together (WITHOUT ODBC or other shitty platform-dependent “connection technologies”).

If you’ve ever done any programming in the past, you’ll find that Python is a really easy and flexible language, which makes it very useful for doing just about any back-end task you’d want to accomplish – which (thankfully) includes grabbing extracts from virtually any database you can find a module for (and trust me, there is a lot) and then inserting that resultset into another database type.

I know I’ve defined the “task” above, but what my example script does specifically (in Part 1) is: Select some rows from an Oracle table (10g) put them into a list, and then use that list to insert them all into destination tables on Microsoft SQL Server (2005 / 2008) and another Oracle instance (after truncating the destination tables, of course). “Trunc-n-load!”

First we need set up the environment – for this example I’m going to be using a windows box (since we need to connect to a SQL Server instance), you could do it all with a linux machine too, but for this particular example it’ll be on windows (Linux tutorials coming up in the future).

I’m using Python 2.6.2 which is the latest stable release in the 2.6 tree as of this writing. You can install it from the Python.org site using the MSI package if you want a nice and easy install.

Now we need 2 Python modules for all this shiz-nit to work properly.

cx_Oracle (for, you guessed it, connecting to Oracle) – They’ve even got a fancy MSI for that too. I’m impressed.

Pymssql (for connecting to Microsoft SQL Server instances) – Download the install file and run it. Its a lot easier than compiling the module from scratch (esp since doing that requires Microsoft Visual Studio 2008).

Once those 2 are installed successfully – lets make sure they work.

Open up a command line Python interpreter… (you know python.exe)

>>> import pymssql, cx_Oracle

You shouldn’t get any errors or response of any kind, just a new ‘>>>’ line below it. If so VIOLA! Its installed.

Now on to building the actual script.

I’m going to go through the code chunk by chunk and comment on each section as we go, just in case you’re new to Python, but don’t worry I’ll have the entire file at the end for download (with comments included in the code).

Ok, first we’ve got our modules from above, and we’re using ’string’ just for some output stuff that we’re doing later (just to check if its working) (update: I didn’t realize that I didn’t use the string module when I edited the script, I’ll still leave it in though – since we’ll need it in later more complicated posts). Then we’re defining the connection string and then defining a cursor that uses each connection – so we’re ready for hot SQL action.

Truncate our destination tables (no mystery there). Notes: In this example I’m writing data to BOTH an Oracle table and a MS SQL table just to show you the different type of “insert many” that we have to use.

Ok, here is a simple select query into Oracle – I’m fetching some source rows to play with. This is just using some Oracle functions that generate random (crap) data – just for the sake of example.

I find it helpful in testing sometimes because using DBMS_RANDOM.STRING(’P',40) generates all kinds of characters that tend to break crappy apps and webapps due to un-escaped characters.

Basically, here I create an empty list, called ‘ResultSet_Py_List’, and then iterate through all the fields and rows in the cursor (which we filled up with data earlier) and add them to it.

Why? Well, first of all its easier to handle than the raw cursor object which can get tricky (esp if we’re going to access the data twice), and secondly because if we’re going to do any data transformation later, it’ll be a fun party trick stunt as opposed to tragic.

When the cursor is empty, we have to do an except: pass so that the interpreter doesn’t freak out.

This is pretty simple. I’m doing a count of all the rows in our list, just to make sure that its all loaded and ready to go. When I write command line apps (even simple ones), I like to throw in logging and verbose stuff like this so I can better troubleshoot later. In this case we should see 3,000 rows in that baby.

Ok, time for the INSERT into our Oracle DB table. You’ll see that we need to run a prepare with our parametrized query – then we can run an executemany with the list to kick it off. Then, bingo – commit it.

Now its time for the MSSQL insertion, as you can see its a little different. Then we commit, and as they say – “Bob’s your Uncle”.

All I’m doing here is counting the rows in each destination table and printing them on the screen, just so I’m SURE that everything went where it was supposed to be.

Now this was exceeding simplistic on purpose. I was people to be able to cut-n-paste properly and modify for their own liking. In the next post in the series, its going to get a bit more complicated, but this script is the “base” we’re going to build on for our Python-based ETL empire. So when people ask you what “ETL Tool” you use, you can say..“Umm, a text editor, bitch!”

Here is the entire commented script file for downloading (who wants to cut and paste over and over again from that shit above, I wouldn’t)!

They’re like cockroaches with designer purses and huge sunglasses.

(think Gossip Girl meets Cruel Intentions meets House Bunny)

But then a funny thing happened – she must have seen something across the street, or thought of something disagreeable and suddenly her face twisted into a snarl that I can only describe as utter disgust (and before you say it, No, its NOT because she saw ME either – she was totally oblivious to any type of automobile, mobile or stationary, like a typical Albany pedestrian). It was like in ‘The Ring’ when they find that dead High School girl in the closet – just a twisted Stan Winston monster make-up face.

Bam.

This one look totally changed my opinion of her. She went from “Cute” to a “Spoiled Asshole” in the blink of an eye. Granted, I don’t know this person – and never will, but it just goes to show you how powerful your body language is at all times.

Why did this happen? I’ll tell you.

You face is just a reflection of whats behind it. If you look dumb all the time, well… chances are, you aren’t the sharpest crayon in the box.

When you scowl constantly for seemingly no reason – it tells me 3 things:

1. you’re constantly thinking of things that displease you (or EVERYTHING displeases you)
2. you really don’t appreciate anything
3. are generally a super negative asshole

Think I’m over-reacting? Maybe a tad, but the very fact that I don’t even KNOW this person makes my case much stronger. Why?

Its all about PERCEPTION, baby.

It doesn’t matter if she’s the nicest girl in the world who happened to step on skunk and made the face out of pure olfactory reactions. I saw what I saw and labeled her an ahole. Simple as that.

People who don’t know us make snap judgements all day long – and I’d be willing to bet that they’ve got a 50% error rate, which is pretty good. That alone should tell you something about the importance of human communications through body language.

So do me a favor, next time your walking down the street and you’re aware that people are looking at you.

Smile for a change.

You never know what kind of opportunity might knock when you aren’t scaring people away with your jerky facial attacks.

Well, then you have experienced the “State of Flow“. Some people call it different weird things, such as “In The Zone” or “In The Moment” – but I think that “Flow” sums it up the best.

It could be anything, it could be painting, photography, gardening, whatever it is that really makes your brain come alive and completely DEVOUR the task at hand. This is the kind of activity that makes you think, challenges you, and makes you thrive at the same time. Its the challenge portion that I always enjoy – you can’t enter that state by doing easy tasks. It has to be just challenging enough to both be do-able AND be difficult at the same time, as paradoxical (is that a word?) as it may sound.

The key to long term happiness is finding something that consistently does this for you. If you can actually make a living doing that “Flow task” then that’s pretty much the Holy Grail right there.

Currently, I make my living as a programmer (by “living” I mean it pays the bills – it doesn’t make me “live” or come “alive” by any stretch of the imagination).

To be completely honest, I used to love it (or I at least THOUGHT that I did at the time), and even today when presented with a small project that has a lot of problem solving and intricate detail in it – I almost always enter a state of Flow for a period of time. It has to be something complicated but not huge and unwieldy – and also something that can be knocked off in a day or two that requires no other person to work on it except for me (waiting for other idiots to do their jobs properly is a huge “Flow Breaker” for me).

So that’s awesome, right? I’ve won – since I get flow at my day job?

Nope, Not at all.

After being in this business for 13 years now, I’ve come to realize that most of what was so novel and intriguing to me in the late 90s (when I came on board in this industry as a young pup) is now just mundane regurgitated bullshit. Not only that, but my little islands of “Flow” that I DO manage to get these days probably only really happen 10% of the time – the other 90% is pretty excruciating (project meetings, arguing about specs, team infighting, political state-related policy changes, the utter stagnation of the IT department heads, etc).

It makes me think that the last industry I left (being an auto mechanic for a well-known local Volkswagen dealership) actually had more ‘happiness merit’ than my much higher paying IT jobs that followed it (Systems Integrator, System / Network Admin, Senior Programmer, Consultant). I was able to achieve Flow there too, but it had to be on something interesting and unknown that required a good amount of digging and testing – and yes, that job had a good deal of the mundane and menial tasks as well.

What are similarities? They are both rooted in problem solving for (mostly cause and effect based) complicated systems.

So if you can find a generalized idea like that – how do we extract it and apply it to something that we WANT to do AND can get paid for?

Its a good question, and one I’m currently chewing on over the past month or so.

I think we have to go back to the beginning and try to re-discover our passions in life. It sounds pretty corny, but its true. If your job is just “paying the bills” and you aren’t happy, maybe its worth a shot. Think back to when you were little – kids have this awesome ability to just list things that they “love” to do. Adults tend to over-think it and come up with answers that are much less honest…

Try it – ask the little kid inside you, “What do you love to do?” – Write them down and give them a try, you might be surprised.

What can you re-discover?

It might just change your outlook on your CURRENT life.

(image from mr_fairuz’s Flickr)

Before you think I’m just writing a rant (which it is), take a minute to think about how this relates in the marketing world. Its somewhere between an industrial accident and a PR stunt, depending on where the truth lies and who you believe. Either way, its creating a new branding message in the WRONG direction (in my opinion).

Now I know this subject has been beaten to death since the fight on Saturday night (which I paid $45 to watch at home in lieu of ‘hitting the town’ for it and probably ended up SAVING myself money, but I digress). I’m actually glad that I waited and let the whole situation “marinade” in my mind for a few days first.

Straight-up, I don’t like Brock Lesnar. I’ve been that way since he came up and tried to make a fool out of Couture a few months ago. He’s just seems like a big loud bully (ok, maybe ‘big’ is an understatement), which all works fine in the WWE where he’s basically a trumped-up character actor with some beefcake acrobatics thrown in. The UFC is real life and we expect a little more depth in our competitors – Honestly, I don’t think thats asking for too much, is it?

Yet, he continues to be the stereotypical ‘bad guy’ that we’d expect from the theatrical ‘performances’ that WWE (and WWF in the old days) deliver. He pretty much proved this on Saturday night with his bizarre victory speech with Joe Rogan (I mean, really, are supposed to give a fuck that Bud Light didn’t show him any sponsorship love and Coors did? No. Why is he even fishing for sponsors at this stage in his UFC career? I like to actually semi-respect the people that schlep the crap that I buy. Just do your job, asshole. Oh yeah, nice job at calling out a big UFC corporate sponsor at the same time too.)

I also like the little weak attempt to retract the outburst on Sunday, which is just a day-after attention-whoring move to get the last drop out of it. “What did he say? OMG!” Blah, Blah, Blah.

They (He) can play their game, it may get some media coverage initially and people might be talking about it – but I personally think that its an extremely bad move, especially now in what is a VERY important time for UFC and MMA as a professional sport on the whole. They are on the cusp of, dare I say it, mainstream legitimacy – and who is our champion? Who rides on white stallions bravely carrying the shield of his beloved sport?

Some badly tattooed asshole who looks like he’s one of Hitler’s undead Aryan minions from ‘Return to Castle Wolfenstein’? Good god, I hope to hell not.

Now I’m all for the UFC and Dana White making money. He can have diamond toilet seats for all I care – but if he’s at all concerned with the ‘big picture’ and his legacy with the sport, he needs to rein this clown in now and do some serious marketing to try and prove to us that he’s an actual interesting human being.

In a sport that naturally generates actual human drama, we don’t need artificial voltage like boobie tassels, “Yo Momma so fat” moments, or fake ego battles.

People are speculating that Dana White did all this on purpose in order to try and capitalize on the type of viewership (and affiliated merchandising) that the WWE crowd generates. If thats true, if just hope that he realizes that he’s taking his (and the UFCs) credibility down several critical notches at the same time.

Maybe I’m wrong, and its all really a big “mis-understanding” and “mis-communication”.

I guess we’ll all know the answer to that is on the next Pay-Per-View event Brock rides up to the Octagon on a Harley-Davidson with a big snake around his shoulders and his silicone encumbered ‘wife’ on the back (oh, and don’t forget the Coors Light girls behind them).

Yikes. Then maybe its time start watching something else instead.