Creating an ODBC data source using Python

For the cross-platform installer I am creating, I need to create ODBC data sources for Windows installations. Thanks to the Python ctypes module the solution was as simple as:

import ctypes

ODBC_ADD_DSN = 1        # Add data source
ODBC_CONFIG_DSN = 2     # Configure (edit) data source
ODBC_REMOVE_DSN = 3     # Remove data source
ODBC_ADD_SYS_DSN = 4    # add a system DSN
ODBC_CONFIG_SYS_DSN = 5 # Configure a system DSN
ODBC_REMOVE_SYS_DSN = 6 # remove a system DSN

def create_sys_dsn(driver, **kw):
    """Create a  system DSN
    Parameters:
        driver - ODBC driver name
        kw - Driver attributes
    Returns:
        0 - DSN not created
        1 - DSN created
    """
    nul = chr(0)
    attributes = []
    for attr in kw.keys():
        attributes.append("%s=%s" % (attr, kw[attr]))
    
    return ctypes.windll.ODBCCP32.SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, driver, nul.join(attributes))

if __name__ == "__main__":
    print create_sys_dsn("SQL Server",SERVER="(local)", DESCRIPTION="SQL Server DSN", DSN="SQL SERVER DSN", Database="mydatabase", Trusted_Connection="Yes")
    print create_sys_dsn("mySQL",SERVER="local", DESCRIPTION="mySQL Server Test1", DSN="mySQL DSN", DATABASE="mySQLDb", UID="username", PASSWORD="password", PORT="3306", OPTION="3")

Require a cross-platform installer, Python to the rescue

The company I work for develops an ERP system that has it's heritage in implementations for medium size companies running on UNIX systems. As the global ERP vendors have moved in our space, we have had to move into the SME market which means running on Linux or Windows server systems. And since some of our new clients cannot afford the cost of a technical resource going on-site to install and configure the technical infrastructure, we need a cross-platform installer. As our application is not written in Java, we are not interested in a Java based installer. But since our supporting scripts are written in Python, and I have had some success creating a cross-platform installer in Python to install and build the the required 3rd party packages for Python Paste, I decided Python could provide a solution. Currently a work in progress, I have a Python script that installs our complete ERP app, it's supporting runtimes and configures it to run in under 10 minutes. Still need to get database setup working, and as our app can run under Oracle, IBM Informix or MS SQL Server 2000 there is abit of work to be done.

Of course to make it look like a "real" installer under Windows I use Inno Setup, and call an executable version of the python script created by py2exe. Rather try trying to write in Delphi to get custom forms for the installor I used ctypes and the Windows port of EasyDialogs to collect site specific setup data. So thanks to the hard work of the creators of the various tools I have used and Python, it looks like we will have our cross-platform installer.

build-pk.py now part of Python Paste

Ian has given me check-in access to the PythonPaste SVN, so I have checked in my cross-platform build-pkg.py script. Also looking at making a distribution of the 3rd party packages using Python Eggs.

Installing WSGIKit, opps I mean Python Paste under Win32

Ian has been unhappy with the name WSGIKit, so it has been renamed Python Paste I think the name better matches what the package does, the WSGI glue that sticks all the python components together. Anyway have made some minor changes to my cross-platform build-pkg.py script so that it works more like Ian's shell script. Have sent a copy to him, so who knows, maybe it will be part of Python Paste.

                                                                               
#!/usr/bin/env python
"""
Install required third party packages for Python Paste.

Known limitations

 - Running under Windows, it must be run with Python 2.3+
   as it requires the tarfile module. On Unix/Linix platform
   will fallback to tar program.
"""
import os
import sys
import errno
import urllib
import shutil

TMP="/tmp"
for tmp in ['TEMP','TMPDIR','TMP']:
    if os.environ.has_key(tmp):
        TMP = os.environ[tmp]
        break
TMP = TMP + os.sep + "tmp-build"

BASE=os.path.dirname(sys.argv[0])
if BASE == '' or BASE == ".":
    BASE = os.getcwd()

THIRD = BASE + "/paste/3rd-party"

def basename(filename, ext):
    try:
        i = filename.index(ext)
        base = filename[0:i]
    except ValueError:
        base = filename
        pass

    return base

def mkdirs(dirname, mode=0777):
    # Python Cookbook Edition 1 Recipe 4.17
    try:
        os.makedirs(dirname)
    except OSError, err:
            if err.errno != errno.EEXIST or not os.path.isdir(TMP):
                raise

def delete_pyc(arg, dirname, files):
    """
    Called by os.path.walk to delete .pyc and .pyo files
    """
    for name in files:
        if name.endswith(".pyc") or name.endswith(".pyo"):
            os.remove(os.path.join(dirname, name))

def get_file(zip_type, filename, url):
    """
    If required, download requested third party package and extract.
    On exit, the script is in the extracted package directory.
    """
    mkdirs(TMP)
    os.chdir(TMP)
    if not os.path.exists(filename):
        print "Download %s ..." % filename
        urllib.urlretrieve(url, filename)
    DIR=basename(filename, ".tar.gz")
    DIR=basename(DIR, ".tar.bz2")
    DIR=basename(DIR, ".tgz")
    if not os.path.exists(DIR):
        try:
            import tarfile
            tar = tarfile.open(filename, "r:" + zip_type)
            for file in tar.getnames():
                tar.extract(file)
        except ImportError:
            # No tarfile module, so use actual tar program
            if zip_type == "gz":
                zip_type = "z"
            else:
                zip_type = "j"
            os.system('tar fx%s "%s"' % (zip_type, filename))

    try:
        os.chdir(DIR)
    except OSError:
        pass

def installer(name):
    mkdirs(THIRD + "/" + name +"-files")
    cmd = '%s setup.py install -f --install-lib="%s/%s-files" --install-scripts="%s/%s-files/scripts" --no-compile' % (sys.executable, THIRD, name, THIRD, name)
    print cmd
    os.system(cmd)

get_file("gz","WSGI Utils-0.5.tar.gz",
        "http://www.owlfish.com/software/wsgiutils/downloads/WSGI%20Utils-0.5.tar.gz")
installer("wsgiutils")

get_file("gz", "Component-0.1.tar.gz", 
        "http://webwareforpython.org/downloads/Component-0.1.tar.gz")
DEST = THIRD + "/Component-files/Component"
mkdirs(DEST)
shutil.rmtree(DEST,ignore_errors=1)
os.chdir(THIRD)
shutil.copytree(TMP + os.sep +"Component-0.1", DEST)

zptkit_tmpdir = TMP + "/" + "ZPTKit"
if os.path.exists(zptkit_tmpdir):
    os.chdir(zptkit_tmpdir)
    cmd ="svn up"
else:
    cmd ="svn co http://svn.w4py.org/ZPTKit/trunk %s/ZPTKit" % TMP
os.system(cmd)
DEST = THIRD + "/ZPTKit-files/ZPTKit"
mkdirs(DEST)
shutil.rmtree(DEST,ignore_errors=1)
os.chdir(THIRD)
shutil.copytree(TMP + "/ZPTKit", DEST)

get_file("gz", "ZopePageTemplates-1.4.0.tar.gz", 
        "http://belnet.dl.sourceforge.net/sourceforge/zpt/ZopePageTemplates-1.4.0.tgz")
os.chdir("ZopePageTemplates")
installer("ZopePageTemplates")

get_file("gz", "scgi-1.2.tar.gz", 
        "http://www.mems-exchange.org/software/files/scgi/scgi-1.2.tar.gz")
installer("scgi")

# Do not clean up compiled python files for now
#os.path.walk(THIRD,delete_pyc,None)

sqlobject_tmpdir = TMP + "/" + "SQLObject"
if os.path.exists(sqlobject_tmpdir):
    os.chdir(sqlobject_tmpdir)
    cmd ="svn up"
else:
    cmd ="svn co http://svn.colorstudy.com/trunk/SQLObject %s/SQLObject" % TMP
os.system(cmd)
os.chdir(sqlobject_tmpdir)
installer("sqlobject")
mkdirs(THIRD + "/sqlobject-files/scripts")
shutil.copy(TMP + "/SQLObject/scripts/sqlobject-admin", 
           THIRD + "/sqlobject-files/scripts")


if not os.path.exists(TMP + "/PySourceColor.py"):
    urllib.urlretrieve("http://bellsouthpwp.net/m/e/mefjr75/python/PySourceColor.py", 
           TMP + "/PySourceColor.py")
mkdirs(THIRD + "/PySourceColor-files")
shutil.copy(TMP + "/PySourceColor.py", THIRD + "/PySourceColor-files")

Installing WSGIKit on a Win32 platform

So I could have a play with WSGIKit and look at how I could make ISAPI_WSGI work with it, I decided to install it on my home Windows XP box. As part of the svn checkout, there is a shell script called build-pkg which will download the required third party packages and install them under WSGIKit for you. Tried to run the script with a standard install of Cygwin but it failed due to various errors. I could have debugged my way thru the errors but thought why not create a truly cross-platform version of buld-pkg in python. So an hour later, the following script did the job for me. It currently lacks the checks to see if the package has already been downloaded, but it did the job for me.

import os
import sys
import errno
import tarfile
import urllib
import shutil

TMP="/tmp/tmp-build"
BASE=os.path.dirname(sys.argv[0])

if BASE == "" or BASE == ".":
    BASE = os.getcwd()

THIRD = BASE + "/wsgikit/3rd-party"

def basename(filename, ext):
    try:
        i = filename.index(ext)
        base = filename[0:i]
    except ValueError:
        base = filename
        pass

    return base

def mkdirs(dirname, mode=0777):
    # Python Cookbook Recipe edition 1 4.17
    try:
        os.makedirs(dirname)
    except OSError, err:
            if err.errno != errno.EEXIST or not os.path.isdir(TMP):
                raise

def get_file(zip_type, filename, url):
    print "Getting %s ..." % filename
    mkdirs(TMP)
    os.chdir(TMP)
    # TODO: check for existing package, download if not there
    urllib.urlretrieve(url, filename)
    DIR=basename(filename, ".tar.gz")
    DIR=basename(DIR, ".tar.bz2")
    DIR=basename(DIR, ".tgz")
    tar = tarfile.open(filename, "r:" + zip_type)
    for file in tar.getnames():
        tar.extract(file)
    try:
        os.chdir(DIR)
    except OSError:
        pass

def installer(name):
    mkdirs(THIRD + "/" + name +"-files")
    cmd = 'python setup.py install -f --install-lib="%s/%s-files" --install-scripts="%s/%s-files/scripts" --no-compile' % (THIRD, name, THIRD, name)
    os.system(cmd)

get_file("gz","WSGI Utils-0.5.tar.gz",
        "http://www.owlfish.com/software/wsgiutils/downloads/WSGI%20Utils-0.5.tar.gz")
installer("wsgiutils")

get_file("gz", "Component-0.1.tar.gz", 
        "http://webwareforpython.org/downloads/Component-0.1.tar.gz")
mkdirs(THIRD + "/Component-files")
DEST = THIRD + "/Component-files/Component"
shutil.rmtree(DEST,ignore_errors=1)
try:
    shutil.move(TMP + "/Component-0.1", DEST)
except OSError:
    pass

get_file("gz", "ZPTKit-0.1.tar.gz", 
        "http://imagescape.com/software/ZPTKit/ZPTKit-0.1.tar.gz")
mkdirs(THIRD + "/ZPTKit-files")
DEST = THIRD + "/ZPTKit-files/ZPTKit"
shutil.rmtree(DEST,ignore_errors=1)
try:
    shutil.move(TMP + "/ZPTKit-0.1", DEST)
except OSError:
    # To handle permission errors under Windows
    pass

get_file("gz", "ZopePageTemplates-1.4.0.tar.gz", 
        "http://belnet.dl.sourceforge.net/sourceforge/zpt/ZopePageTemplates-1.4.0.tgz")
os.chdir("ZopePageTemplates")
installer("ZopePageTemplates")

get_file("gz", "scgi-1.2.tar.gz", 
        "http://www.mems-exchange.org/software/files/scgi/scgi-1.2.tar.gz")
installer("scgi")

cmd ="svn co http://svn.colorstudy.com/trunk/SQLObject %s/SQLObject" % TMP
os.system(cmd)
os.chdir(TMP + "/SQLObject")
installer("sqlobject")
mkdirs(THIRD + "/sqlobject-files/scripts")
shutil.copy(TMP + "/SQLObject/scripts/sqlobject-admin", 
           THIRD + "/sqlobject-files/scripts")


urllib.urlretrieve("http://bellsouthpwp.net/m/e/mefjr75/python/PySourceColor.py", 
                TMP + "/PySourceColor.py")
mkdirs(THIRD + "/PySourceColor-files")
shutil.copy(TMP + "/PySourceColor.py", THIRD + "/PySourceColor-files")

Back to WSGI land

It's been over a month since my last entry as family and work have consumed most of my time. Some interesting developments for WSGI over the last month. Ian Bickings PyCon presentation provides a very good description of what WSGI is and what it is not. He used his WSGIKit for the usage examples. And just in case, we didn't appreciate what you can do with WSGIKit, a week later he provided an online tutorial on how to implement something like the famous Ruby on Rails ToDo List example with it. The WSGIKit tutorial can be found here.

Also came across some work that Allan Saddi has done. At last I can simply use FastCGI with my WSGI enabled web scripts. So using either isapi_wsgi or his fcgi.py I can run my scripts as long running processes on both IIS and Apache with no real extra effort.

ZopeX3 learning from others.

As I said in a previous post, the documentation for ZopeX3 is much better than what came with Zope 1. I found the programmers tutorial a very good starting point. Also some googling found this on-going series of blog entries creating a Todo. Nothing like learning about something by example.

Zope Deja vu

It's 1999, I have downloaded Zope 1, and I managed to create a simple website that gets it's data using SQLMethods from a database. I had to learn a new markup language called dtml to get my dynamic webpages. It has no session management but not an issue at the time. To enhance it's functionality I need to create a product, that's were it starts to get hard. The documentation is minimal. But I stick with it, and six years later Zope 2 is still the Web Application Framework at work.

It's today, I have downloaded ZopeX3, and I managed to create a simple website that gets it's data using SQLMethods from a database. I had to learn a new markup language called zcml to get my dynamic webpages. It has no session management but not an issue at the time. To enhance it's functionality I need to create a product, that's were it starts to get hard.

The documentation is much better, but I seem to spend more time editting xml than actually writing code in the python. At least the coding seems more like normal python programming, and the use of components, interfaces, adaptors and testing looks promising and maybe Zope will once again lead the way as the Python Web Application Framework. The bigger question for me is, should we be considering it as a replacement for Zope 2 at work or wait and have a look in another 3 months. Or maybe we should look at Five, the Zope 3 in Zope 2 project. Just like I did with Zope 1, I will stick with it and keep playing, and hopefully it will deliver like Zope 1 and 2 did.

Python and Beer, it must be a Python Meetup Group

Last thursday was the bi-monthly Sydney Python Meetup. Excellent beer, interesting talks and people. Thanks for organising the meeting Alan.

What's all the excitement about XMLHttpRequest

The buzz on many blogs is Google's use of XMLHttpRequest to allow the browser to do requests of the backend without a page refresh. To the best of my knowledge XMLHttpRequest or similar functionality has been part of javascript support in IE and Mozilla for a couple of years. We have been using it for an application at work for the last 20 months to make XMLRPC calls to dynamically update the browser and a SVG view. The biggest issue in the past is getting it to work across browsers, and rather than writing javascript with lots of "if (browser == 'X')" code I would recommend taking at look at the joslait javascript library. As well as providing a set of routines which allow XMLHttpRequest functionality on many browser versions and a XMLRPC client, it has a number of other useful routines.

WSGI Wiki Application

For my WSGI talk at the Sydney Python Meetup I decided that showing a simple "hello world" or echo WSGI application would not hammer home why WSGI is so good. So I decided to create a WSGI wiki app. Also to show what is needed to convert an existing Python CGI script I decided to base it on Ian Bicking's CGI wiki in the Web Framework Shootout. So after about 80 minutes work this is what I came up with and was able to run under isapi_wsgi. It still needs some final touches but it works.

import cgi, os
import cgitb; cgitb.enable()
import Wiki


# There's several different actions that may occur -- we
# may just display the page, we may edit it, save the edits
# that were submitted, or show a preview.  We define each of
# these as a function:

def printPage(page):
    pp = []
    if not page.exists():
        pp.append("This page does not yet exist.  Create it:")
        pp.append(printEdit(page))
    else:
        pp.append(page.html)
        pp.append('<hr noshade>')
        pp.append('<a href="%s?edit=yes">Edit this page</a>'               % page.name)
    return "".join(pp)

def printEdit(page, text=None):
    # The text argument is used in previews -- you want the
    # textarea to be loaded with the same text they submitted.
    pe = []
    if text is None:
        # This isn't a preview, so we get the page's text.
        text = page.text
    pe.append('<h1>Edit %s</h1>' % page.title)
    pe.append('<form action="%s" method="POST">' % page.name)
    pe.append('<textarea name="text" rows=10 cols=50 style="width: 90%%">'           '%s</textarea><br>' % cgi.escape(text))
    pe.append('<input type="submit" name="save" value="Save">')
    pe.append('<input type="submit" name="preview" value="Preview">')
    pe.append('</form>')
    return "".join(pe)

def printSave(page, form):
    ps = []
    page.text = form['text'].value
    ps.append(printPage(page))
    return "".join(ps)

def printPreview(page, form):
    ps = []
    ps.append('<h1>Preview %s</h1>' % page.title)
    ps.append(page.preview(form['text'].value))
    ps.append('<hr noshade>')
    ps.append(printEdit(page, form['text'].value))
    return "".join(ps)

def application(environ, start_response):
    # PATH_INFO contains the portion of the URL that comes after
    # script name.  For all actions, this is the page we are using.
    pagename = environ['PATH_INFO'][1:]
    if len(pagename) == 0:
        pagename = "frontpage"
    page = Wiki.WikiPage(pagename)
    form = cgi.FieldStorage(fp=environ['wsgi.input'],environ=environ)
    start_response('200 OK', [('Content-type','text/html')])
    response = []
    response.append("<html><head><title>%s</title>" % page.title)
    response.append(page.css)
    response.append("</head><body>")
    # Here we use fields to determine what action we should take:
    # edit, save, preview, or display
    if form.has_key('edit'):
        response.append(printEdit(page))
    elif form.has_key('save'):
        response.append(printSave(page, form))
    elif form.has_key('preview'):
        response.append(printPreview(page, form))
    else:
        response.append("<h1>%s</h1>" % page.title)
        response.append(printPage(page))
    response.append("</body></html>")
    return response

dotNet client testing of python web services

As I said in a previous post I have been testing .NET client calls of our financial app web services. I found a good "free" tool called WebServiceStudio that allows quick and easy interactive testing of web services. Our web services use the Python ZSI web service package and this seems to work fine with dotNET clients, as long as you keep the parameter and return types simple. Otherwise you will spend alot of time handcrafting WSDL and for that you need a good WSDL editor.

As if I haven't learnt enough programming languages..

As much as I enjoy programming in Python and feel it is the closet fit for how my brain works, I still have the need to checkout other languages.

After listening to Dr Chris Wright at OSDC last year, I made a mental note to checkout Scheme and also learn about continuations. After finding some tutorials on the web, I started playing with PyScheme It was great for trying out simple examples and made me interested enough for something to try out more complex things. I found Dr Scheme which is an interactive, integrated, graphical programming environment for the Scheme, MzScheme, and MrEd programming languages. It is multi-platform running on Windows (95 and up), Mac OS X, and Unix/X. It has great interactive syntax checking which is very helpful when debugging complex scheme code. There is even talk of a plug-in that will allow you to use the tool to program in Python called PLT Spy

At work I needed to check that some web services for our financial app worked when called from .NET As we currently only support RPC style, writing WS clients in C# is not much fun. I really needed an agile programming language. I could have used PythonNet but really wanted to try out a CLI language. As IronPython seems to be going nowhere, I decided to try out Boo Since some of the syntax is based on Python, I feel at home using it. And based on one days use, it makes working in .NET fun.

ISAPI-WSGI - Runs a WSGIUtils application

As part of researching for my WSGI talk I have been working with Colin Stewart's WSGIUtils. It is a package of wsgi helper libraries that should make the development of wsgi enabled apps easier. The two main components are:

wsgiServer is a multi-threaded WSGI web server based on SimpleHTTPServer.

wsgiAdaptor is a simple WSGI application that provides a Basic authentication, signed cookies and persistent sessions.

After getting his examples working under wsgiServer, I decided to see if the wsgiAdaptor examples worked under isapi_wsgi. Apart from needing to make a change to where the session dbm is created (due to permission issues) and an issue with how my copy of IE 6.0 handles cookies :-(, it worked very well. Below is how to do it:

""" Basic Example

  Copyright (c) 2004 Colin Stewart (http://www.owlfish.com/)
  All rights reserved.
    
  THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
  IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
  OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
  IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
  NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
  THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  
  If you make any bug fixes or feature enhancements please let me know!
  
  A simple example - a counting web site.
  Note that increments in modern browsers go up twice because two
  requests are made - one for '/' and one for '/favicon.ico'
"""
import logging, time
import isapi_wsgi

from wsgiutils import SessionClient, wsgiAdaptor, wsgiServer
class TestApp:
    def requestHandler (self, request):
        session = request.getSession()
        count = session.get ('counter', 0)
        count += 1
        session ['counter'] = count
        request.sendContent ("<html><body><h1>Visits: %s</h1><body></html>" % str (count), "text/html")
  
 
def __ExtensionFactory__():
    return isapi_wsgi.ISAPISimpleHandler(test = testadaptor.wsgiHook)

testclient = SessionClient.LocalSessionClient('T:/session.dbm', 'testappid')
testadaptor = wsgiAdaptor.wsgiAdaptor (TestApp(), 'siteCookieKey', testclient)

if __name__=='__main__':
    # If run from the command-line, install ourselves.
    from isapi.install import *
    params = ISAPIParameters()
    # Setup the virtual directories - this is a list of directories our
    # extension uses - in this case only 1.
    # Each extension has a "script map" - this is the mapping of ISAPI
    # extensions.
    sm = [
        ScriptMapParams(Extension="*", Flags=0)
    ]
    vd = VirtualDirParameters(Name="isapi-wsgi-wsgiutils",
                              Description = "ISAPI-WSGI WSGIUtils Test",
                              ScriptMaps = sm,
                              ScriptMapUpdate = "replace"
                              )
    params.VirtualDirs = [vd]
    HandleCommandLine(params)