I would like to start this note with a fair disclaimer to all those who might know me and my stand about “social networking” that cropt in recent years:

“The only reason why I finally surrendered to so-called micro-blogging is my new phone. That does not mean that my opinion about “twittering” has changed a bit: I still feel pity for all those dummies out there that “follows” others on twitter. Only Facebook sheeps ranks lower in my book :-). That said, I do recognize that micro-blogging sites with well documented API might be useful for pure technical utilization. That’s why I opened account on Twitter that will serve to me and only me. Instead of sending alerts to my phone via SMS messages I’ll write a simple python script that will post “statuses” to my twitter account via API. And since my HTC Hero comes with configured Peeps, client application for Twitter, I decided to give it a try, so I opened “non-public” twitter account where python script will send various non-critical technical alerts. Of course, messages that I’m intending to send to twitter.com will be so obscure that only I and perhaps a couple of my associates will be able to put in the context. Not a chance that any of the messages could jeopardize my client security in any way, even if someone unauthorized could see them.

Enough rumblings, let’s see how easy it is to exploit twitter.com for some productive work.

With some help from the Google I decided to use python-twitter module.

Steps to install python-twitter and a prerequisite, simplejson:

1) download and install appropriate version of setuptools from pipy.python.org.

I downloaded and installed setup tools for python 2.6 win32..

This is a prerequisite for simplejson (setuptools version >= 0.6c7).

2) download and install simplejson

– after extracting simplejson-2.0.9.tar.gz to some temporary directory:

cmd> cd simplejson-2.0.9
cmd> python setup.py install

3) download and install Python Twitter

– after extracting python-twitter-0.6.tar.gz to some temporary directory:

cmd> cd python-twitter-0.6
cmd> python setup.py build
cmd> python setup.py install
cmd> python setup.py test


4) read documentation about twitter module API

5) let’s see python-twitter module at work:

#
# Simple test case to check if python twitter module works.
# 
import twitter

# Twitter login account and password
tusername = 'dbatwitaccount'
tpassword = 'mysecret'

# Initialize authenticated twitter API session
api = twitter.Api(username=tusername,password=tpassword)

def PostTweet(message):
    """
    Function for posting a message. 
    """
    if len(message) < 141:
        status = api.PostUpdate(message)
    else:
        print ('Error: message is longer than 140 characters!')

def PrintUserTweets(tusername, cnt=0):
    """
    Simple function for printing tweets. You can limit the
    number of tweets with the cnt parameter, default is 0
    which means get and print all tweets.
    """
    statuses = api.GetUserTimeline(user=tusername, count=cnt)
    for status in statuses:
        print (status.created_at + ': [' + status.text + ']')

def DeleteAllTweets(tusername):
    """
    Function for deleting all tweets - use with care!
    """
    i = 0
    api.SetCache(None)
    statuses = api.GetUserTimeline(user=tusername, count=0)
    for status in statuses:
        api.DestroyStatus(status.id)
        i = i + 1
    print ("Number of deleted tweets: " + str(i))

# posting a tweet -- in real life scenario DBA should
# be careful to post only obscure enough messages,
# not to reveal such details as SID, service names,
# hostnames, IP's, account names, even errors...
# The example below is only an example, in real-life
# I would not post ORA- message number nor DB name!
PostTweet('ORA-600 @ DB1 / check alert.log /')
               
# print all user tweets
PrintUserTweets(tusername)

# print limited number of tweets (for example last two)
PrintUserTweets(tusername, 2)

# delete all tweets
DeleteAllTweets(tusername)

# End

With above script I only glimpsed over twitter API that is exposed through python twitter module. Refer to documentation for a complete overview.

The other day I was reading an August issue of my favorite magazine, Linux Journal. On page 17 there is a list of the ten most popular articles off all-time on LinuxJournal.com. At the #1 is article written by Eric Raymond, titled Why Python?.

I was pleasantly surprised. It’s precisely this article that ended my ultimate quest for scripting language back in 2004. Frankly, I was sick of Microsoft WSH that I was using back then; besides I needed scripting language that could run equally well on Windows & Linux (support for other major flavors of Unix would be a plus) and with solid support for various text processing/parsing functions, that could allow me to “glue” together some tasks related to ETL (mostly DBA stuff).

I spent considerable amount of the first half of year 2004 evaluating various scripting languages — somehow skipping python at first (can’t remember why I dismissed python so quickly). Since I knew that Perl has strong parsing capabilities (regular expressions etc.), it was this language that I used to “benchmark” all others against.

Perl could probably pass my evaluation if only the written code would not look so wacky (with all due respect to Perl community) – then one day Google returned link to the article written by Eric Raymond, “Why Python?”. It was revealing experience reading this article. I downloaded and installed python immediately after I finished with the article, browsed the manuals and when I saw some code examples, I thought: what a beauty (sorry if it sounds too geeky)! It was love at first sight, I knew I was hooked for life. Since then, I never looked back, python is my favorite scripting language. The only thing I really regret is that I didn’t “find” the python earlier in my career. What a pity!

Here I’ll try to maintain a list of articles published on OTN about python & Oracle:

• Python cx_Oracle 5.0 New Features Overview
• High Concurrency with Python and Oracle Database
• Python Data Persistence with Oracle Database
• Using Python with TurboGears
• Mastering Oracle+Python, Part 1: Querying Best Practices
• Mastering Oracle+Python, Part 2: Working with Times and Dates
• Mastering Oracle+Python, Part 3: Data Parsing
• Mastering Oracle+Python, Part 4: Transactions and large objects
• Mastering Oracle+Python, Part 5: Stored Procedures, Programming Python
• Build a Rapid Web Development Environment for Python Server Pages and Oracle
• Wrapping Your Brain Around Oracle + Python
• Using Python With Oracle 11g

I wrote a simple python script for converting xml export files generated by SQL Developer to csv format.

Let’s first see what is the output from SQL Developer. Export file of the Oracle table EMP generated by SQL Developer looks like this:

<?xml version='1.0'  encoding='Cp1250' ?>
<RESULTS>
	<ROW>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
		<COLUMN NAME="EMPNO"><![CDATA[7369]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[SMITH]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[17.12.1980]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7902]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[800]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="COMM"><![CDATA[300]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
		<COLUMN NAME="EMPNO"><![CDATA[7499]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[ALLEN]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[20.02.1981]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1600]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="COMM"><![CDATA[500]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
		<COLUMN NAME="EMPNO"><![CDATA[7521]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[WARD]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[22.02.1981]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
	</ROW>
</RESULTS>

And my goal was to convert xml file to csv format:

COMM;DEPTNO;EMPNO;ENAME;HIREDATE;JOB;MGR;SAL;
;20;7369;SMITH;17.12.1980;CLERK;7902;800;
300;30;7499;ALLEN;20.02.1981;SALESMAN;7698;1600;
500;30;7521;WARD;22.02.1981;SALESMAN;7698;1250;


In this “exercise” I decided to use minidom for xml parsing which is quite easy to code with, but at the same time is suitable only for small xml files, such as exports of database code books. Perhaps I’ll publish similar python script, where I’ll parse xml with sax module.

# -*- coding: cp1250 -*-
# xml2csv.py -- AlesK, 2009
#
# Purpose:
# simple python code snippet that shows how to use python minidom module
# to convert export file produced by Oracle SQL Developer (Table->Export->to xml)
# to csv format. Note that minidom (DOM) as such is suitable for parsing
# small xml files, such as database codebooks!
#
# Note: Script doesn't even try to do any error handling at all and 
# as such is far away from being anything more than a simple demo.
#
# Example:
# cmd> python xml2csv.py emp.xml > emp.csv
#
import sys
import xml.dom.minidom

doc = xml.dom.minidom.parse(sys.argv[1])

# if encoding is not explicitly declared in xml header then 
# I'm assuming Cp1250 for the output file
if doc.encoding:
    encode = doc.encoding
else:
    # Cp1250 is my personal preference if encoding is not
    # specified in xml header
    encode = 'Cp1250'

# fields delimiter    
csv = ';'    

# during the pass of the first ROW collect
# column names (for csv file header)
firstRowPass = True
header = ''

for row in doc.getElementsByTagName("ROW"):
    row_data = ''
    for column in row.getElementsByTagName("COLUMN"):
        for column_value in column.childNodes:
            row_data += column_value.data
        row_data += csv
        if firstRowPass:
            header += column.getAttribute('NAME') + csv
    if firstRowPass:
        print header.encode(encode)
        firstRowPass = False
    print row_data.encode(encode)

doc.unlink()
#
# End xml2csv.py
#

This is just a short memo about handy python module, called SimpleHTTPServer. Sometimes I need a way to quickly share some files with co-worker, without copying and/or creating shared folder. All I need is a quick way to allow someone read-only access to a specific folder. Let’s say that security is of no concern to me in such situation, since I’m talking about giving access to publicly available files anyway, such as drivers, installation files etc.

One neat way is to use python module SimpleHTTPServer.

All that is needed is this one-liner:

T:\Download>python -m SimpleHTTPServer
Serving HTTP on 0.0.0.0 port 8000 ...
alesk.domain.com - - [02/Feb/2009 18:13:49] "GET / HTTP/1.1" 200 -
alesk.domain.com - - [02/Feb/2009 18:14:03] "GET /Accton/ HTTP/1.1" 200 -
alesk.domain.com - - [02/Feb/2009 18:14:13] "GET /Accton/EN1207DTXv133.zip HTTP/1.1" 200 -

And the files will be available at http://yourhost:8000. Simply close the console after you’re done and http server will stop serving.

Last final note: be extremely careful that you cd to directory of interest, thus limiting access to only a particular directory. Remember that anyone with network access to your machine can access those files without authentication.