I am speaking at IIUG 2012 about using Python with Informix

The 2012 IIUG (International Informix User Group) conference will be in San Diego, California from April 22 - 25 2012. All three of my talk proposals have been accepted, and one of these is about using Python with Informix. As well as preparing my presentation, I have been working on a number of Python Open Source projects either adding or improving their support for Informix access. So hopefully around the time of the conference I will be able to release a more complete django-informix and support for Informix in Gerald, a database schema comparison tool.

The abstract of my Python talk is as follows:

Add a Python to your Informix programming toolbox

Python is an open-source, dynamically typed, object-oriented programming
language. It offers an easy learning curve and access to a vast array of libraries
including Informix database access. With implementations available for most
operating systems as well as the versions that run under the Java and .NET
virtual machines, you can add Python to your Informix database administration
and application programming toolbox. The presentation will give an overview of
the Python programming language, discuss the Python DBI and the libraries that
allow access to Informix databases.

Python Informix Database Connection Options

I am currently at the International Informix Users Group Conference (http://www.iiug.org/index.php) in Kansas. In the opening keynote by Jerry Keesee, there some discussion about IBM's Open Source Initiatives for Informix. On the accompanying slide, Python and Django were listed. This reminded me that I hadn't taken stock of what the Informix DB connections options were for the Python user lately. A quick "google" produced the following:

Python DB-API (http://www.python.org/dev/peps/pep-0249/) adaptors

1. InformixDB http://informixdb.sourceforge.net/ The original DBAPI compliant database adaptor that wraps the Informix CliSDK. BSD License.
2. ibm-db http://code.google.com/p/ibm-db/ IBM sponsored connector that provides Python, Django and SQLAlchemy support for IBM DB2 and Informix. Connects via the IBM DataServer component. Apache 2.0 License.
3. mxODBC http://www.egenix.com/products/python/mxODBC/ Provides an interface to databases via an ODBC interface so supports Informix via Informix CLiSDK. Commerical License.

I have used both items 1 & 3 and they do work very well. Haven't used ibm-db as our sites normally only have CliSDK installed not IBM DataServer.

My preferred combination for Python Informix database access is using InformixDB in conjunction with SQLAlchemy.  SQLAlchemy allows me to choose how abstracted I want to be with my database, allowing direct SQL thru to utilizing an ORM.

IronPython and ADO.NET Part 2

This is the second in a series of posts about database access with IronPython and ADO.NET. This post will discuss connecting to the database and executing basic DDL and SQL statements via the Python DB-API instead of directly accessing ADO.Net. So that the examples can run on Windows and non Windows systems, they will support either SQLite3 via the Mono.Data.SQLiteCilent ADO provider or Microsoft Access via the System.Data.Odbc provider.

The Python DB-API is a specification created by the Python Database SIG for a consistent interface to relational databases. For CPython there is at least one DB-API compliant library for most of the relational database engines that are used today. As part of his fepy project, Seo Sanghyeon has created a set of wrappers that provide DB-API support for MySQL, PostgreSql, SQLite, Microsoft SQL Server and ODBC ADO.NET database drivers. Since one of the features of ADO.Net is to also provide a consistent interface to relational databases, you may question why do you need to use another layer for database access with IronPython. The answer is simple, the DB-API allows you to focus on the actual access and manipulation of the data and hides the low-level ADO.Net setup and management code. To show how the DB-API can simpify your IronPython code, the examples from the first post of this series have been modified to use the DB-API.

If you want try the example or use the DB-API with IronPython you will need to install it. You can either download the modules from here and copy to the IronPython Lib directory or build and/or install the IronPython Community Edition which includes the DB-API.

Creating a table example

The first section of code is only required so the examples will work on either *ix or windows platforms. In normal usage, there is no need to import dbapi.py directly, just import the DB-API module for the database you want to use.

import dbapi
try:
  import sqlite3 as db
  dbapi._load_type(db.assembly,db.typename)
  connectstr = 'ip2country.db'
  ip2country_create_table_ddl = '''
    CREATE TABLE ip2country (
      ipfrom          INTEGER,
      ipto            INTEGER,
      countrycode2    CHAR(2),
      countrycode3    CHAR(3),
      countryname     VARCHAR(50),
      PRIMARY KEY (ipfrom,ipto)
    )
'''
except:
  import odbc as db
  dbapi._load_type(db.assembly,db.typename)
  connectstr = 'DSN=ip2country'
  ip2country_create_table_ddl = '''
    CREATE TABLE ip2country (
      ipfrom          DOUBLE,
      ipto            DOUBLE,
      countrycode2    CHAR(2),
      countrycode3    CHAR(3),
      countryname     VARCHAR(50),
      CONSTRAINT ip2country_pk PRIMARY KEY (ipfrom,ipto)
    )
'''

Comparing this code with the ADO.Net example you see that the opening of the database connection, and create a command instance is automatically done by the DB-API module. The Python DB-API PEP specifies implicit transactions that are started automatically and committed or rolled back on demand, so compared to ADO.Net example , a commit is required.

dbcon = db.connect(connectstr)

cursor = dbcon.cursor()

cursor.execute(ip2country_create_table_ddl)

dbcon.commit()

dbcon.close()

Load the data example.

Compared to the ADO.Net example, the DB-API allows the IronPython code to be simpler as it handles the creation of Parameters. (Note to self: MS Access, bulk inserts and transactions == very slow)

dbcon = db.connect(connectstr)

import re
re_csv = re.compile(',(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))')

cursor = dbcon.cursor()
insert_statement=  '''
INSERT INTO ip2country (
ipfrom, ipto, countrycode2, countrycode3, countryname
) VALUES ( ?,?,?,?,? )
'''

f = open("ip-to-country.csv")
print "Loading..."
for line in f.readlines():
  if line.endswith("\r\n"):
    line = line[:-2] # remove \r\n
  else:
    line = line[:-1] # just remove \n
  print line
  ipf, ipt, cc2, cc3, cn = re_csv.split(line)
  cursor.execute(insert_statement,(ipf[1:-1],ipt[1:-1],cc2[1:-1],cc3[1:-1],cn[1:-1]))
  dbcon.commit()

f.close()
dbcon.close()

Select some data example.

Instead of using the ExecuteReader as in the ADO.Net example, the fetch method of the cursor instance is used to get the query result.

def ip2number(ipaddress):
  '''
  Convert dotted IP address to number
  '''
  A,B,C,D = ipaddress.split(".")
  return (int(A) * 16777216) + (int(B) * 65536) + (int(C) * 256) + int(D)

dbcon = db.connect(connectstr)

cursor = dbcon.cursor()

try:
  ipaddress = sys.argv[1]
  # Convert dotted ip address to number
  ipnumber = ip2number(ipaddress)
except:
  print "Error - An IP Address is required"
  sys.exit(1)

select_statement = '''SELECT * FROM ip2country
WHERE ipfrom <= %s     AND ipto >= %s
''' % (ipnumber, ipnumber)

cursor.execute(select_statement)

row = cursor.fetchone()
print "The location of IP address %s is %s." % (ipaddress, row[4])

dbcon.close()

IronPython and ADO.NET Part 1

This is the first in a series of posts about database access with IronPython and ADO.NET. This post will discuss connecting to the database and executing basic DDL and SQL statements. So that the examples can run on Windows and non Windows systems, they will support either SQLite3 via the Mono.Data.SQLiteCilent ADO provider or Microsoft Access via the System.Data.Odbc provider.

Firstly we need some data to play with. A number of Python Web frameworks have been adding support for displaying the flag of the country against weblog comments. The country is identified from the remote IP address of the users browser. So we use IronPython to create a table and load it with the data from the ip-to-country cvs file which can be download from here. The full source of the IronPython script is here.

Creating a table

The first section of code references and imports the assemblies required for connecting and accessing the database. So the script can support either SQLite or Access, it tries to reference and import the SQLite ADO provider first, if this fails it then attempts to import the ODBC provider. The scripts uses an import alias so that we can refer to the database connection method by the same name independant of what ADO provider is being used. Also the database specific connection strings and table creation statements are defined here.

import clr
import System
clr.AddReference("System.Data")
import System.Data
try:
   clr.AddReference("Mono.Data.SqliteClient")
   from Mono.Data.SqliteClient import SqliteConnection as dbconnection
   connectstr = 'URI=file:ip2country.db,version=3'
   ip2country_create_table_ddl = '''
   CREATE TABLE ip2country (
       ipfrom          INTEGER,
       ipto            INTEGER,
       countrycode2    CHAR(2),
       countrycode3    CHAR(3),
       countryname     VARCHAR(50),
       PRIMARY KEY (ipfrom,ipto)
   )
   '''
except:
   from System.Data.Odbc import OdbcConnection as dbconnection
   connectstr = 'DSN=ip2country'
   ip2country_create_table_ddl = '''
   CREATE TABLE ip2country (
       ipfrom          DOUBLE,
       ipto            DOUBLE,
       countrycode2    CHAR(2),
       countrycode3    CHAR(3),
       countryname     VARCHAR(50),
       CONSTRAINT ip2country_pk PRIMARY KEY (ipfrom,ipto)
   )
   '''

The last section of the script, connects to the database, opens the connection and creates the table ip2country using the database specific DDL.

dbcon = dbconnection(connectstr)

dbcon.Open()

dbcmd = dbcon.CreateCommand()
dbcmd.CommandText = ip2country_create_table_ddl

dbcmd.ExecuteNonQuery()

dbcon.Close()

So once you run the script, you will either have an SQLite database ip2country.db or Access database ip2country.mdb with a single empty table called ip2country.

Load the data

The full source of the IronPython script for loading the CSV data can be found here. We use the same code from the previous script to setup access to the data. The ip-to-country.csv file contains data of the following format:

"33996344","33996351","GB","GBR","UNITED KINGDOM"
"50331648","69956103","US","USA","UNITED STATES"
"69956104","69956111","BM","BMU","BERMUDA"

To parse each line of the csv file, a regular expression is used.

import re
re_csv = re.compile(',(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))')

Rather than dynamically creating the SQL insert statement using string concatenation, the script uses variable placeholders in the insert statement and data parameters. In theory, this should be more efficent with the database only needing to prepare the insert statement once, and then binding the data parameters on each insert. But not sure if SQLite or Access does this type of optimisation. At least it means there will be no problems with country names like COTE D'IVOIRE that contain quotes.

dbcmd = dbcon.CreateCommand()
insert_statement=  '''
   INSERT INTO ip2country (
       ipfrom, ipto, countrycode2, countrycode3, countryname
       ) VALUES ( ?,?,?,?,? )
'''
# Create empty parameters for insert and attach to db command
p1 = dbparam()
dbcmd.Parameters.Add(p1)
p2 = dbparam()
dbcmd.Parameters.Add(p2)
p3 = dbparam()
dbcmd.Parameters.Add(p3)
p4 = dbparam()
dbcmd.Parameters.Add(p4)
p5 = dbparam()
dbcmd.Parameters.Add(p5)

dbcmd.CommandText = insert_statement

Next the script opens the csv file and reads it line by line. After removing the line separator(s), the line is split into individual fields using the compiled regular expression. The value of each field is then assigned to the insert parameter with the delimiting double quotes removed. And the data is inserted into the ip2country table by calling the ExecuteNonQuery Method.

f = open("ip-to-country.csv")
print "Loading..."
for line in f.readlines():
   if line.endswith("\r\n"):
       line = line[:-2] # running on a posix platform so remove \r\n
   else:
 line = line[:-1] # must be windows, just remove \n
   print line
   ipf, ipt, cc2, cc3, cn = re_csv.split(line)
   p1.Value = ipf[1:-1]
   p2.Value = ipt[1:-1]
   p3.Value = cc2[1:-1]
   p4.Value = cc3[1:-1]
   p5.Value = cn[1:-1]
   dbcmd.ExecuteNonQuery()

f.close()
dbcon.Close()

To run the script the ip-to-country.csv file must be in the current directory, and since it contains 65,000+ lines of data, it will take a while to run.

ipy.exe load_op2country.py

Select some data

Now the ip2country table should contain some data we can query. Let's create a simple script that when passed an IP address, it prints the country location of the IP address. The source of the script can be found here.
To find the location, the script first converts the IP address to the numeric equivalent used in the ip2country data using the function ip2number. A SQL select statement is defined using the numeric ip address as the bounds for the where clause. Then an ExecuteReader instance is created and the results processed in a while loop.

dbcon.Open()

dbcmd = dbcon.CreateCommand()

try:
    ipaddress = sys.argv[1]
    # Convert dotted ip address to number
    ipnumber = ip2number(ipaddress)
except:
    print "Error - An IP Address is required"
    sys.exit(1)


dbcmd.CommandText = '''
    SELECT * FROM ip2country 
    WHERE ipfrom <= %s
    AND ipto >= %s
''' % (ipnumber, ipnumber)


reader = dbcmd.ExecuteReader()

while reader.Read():
    print "The location of IP address %s is %s." % (ipaddress, reader[4])

reader.Close()
dbcon.Close()

The country name is accessed from row result by column number. I would prefer to get the value of the column via it's name e.g.

print "The location of IP address %s is %s." % (ipaddress, row['countryname'])

and this problem has been addressed by a Greg Stein's dtuple Python module. You will find a version of the find location script that uses dtuple here.
Hopefully this post has given you some insight in how to use IronPython with ADO.Net.

Django with an IBM Informix backend

Update: This version of the django informix backend only works with Django pre 1.0 due to changes in Django database api. I have created a version that is known to work with Django 1.1+ and it's code can be found at: http://code.google.com/p/django-informix/

At work I needed to quickly be able to publish some technical bulletins on the web and provide a web interface for addition of new content. In the past we have used Zope for most of our in-house web development but I wanted the content stored in a "real" database. This meant deciding on a Python Web Framework that used a RDBMS out of the box. Part of the reason for selecting Django over the others, was the built-in database admin interface which meant one less set of controllers and views to develop.

Initial development was done using a SQLite backend and I had a working application deployed under lighty and fastcgi in just under a day. During this time I was also learning the framework so very impressed with the productivity. Of course good documentation and Google helped.

At work we deploy our applications on IBM Informix, Oracle and MS SQL Server databases, but for development and in-house projects use Informix. So I really needed a Django database adaptor for Informix. This would be my fourth Informix DA for a python project as I have written them for Zope2/3, and SQLObject. Writing the DA wasn't too hard but I had to do some interesting things with regular expressions to allow datetimes to work correctly and to remove some unsupported SQL syntax from the Django ORM created DDL. Also handling of TEXT data types is abit of a hack until I get a chance to re-factor the code. So if you are one of the few people in the world that still use Informix and want to use it with Django, I have put the code here. Due to Django's ORM, using some long table names you will have to use either Informix IDS 9 or 10.

SQLAlchemy, more than an ORM

I remember seeing references to Michael Bayers' SQLAlchemy and thinking yet another Python ORM to look at sometime in the future. I have used SQLObject for various hobby projects, but since I deal with legacy databases (some database schema's just do not map well to an ORM) at the day job, I have tended to use SQL and the Python DBI for my database access work. Part of my job is to evaluate solutions to make my team more productive, and in reviewing where we allocate our time during development, I noted that a good portion is ensuring our SQL works across multiple RDBMS's. So I have been looking for Python solutions that abstract the creation of SQL against different RDBMS's. On investigating SQLAlchemy I was surprised to see that it you can use it to do exactly what I wanted. Also one of goals of Ian Bickings' SQL-API is to offer this functionality, but what follows is a discussion of how SQLAlchemy could help.

First connect to a database

from sqlalchemy import *
engine = create_engine("sqlite://filename=mydb",echo=True)

The option echo is set so we can view the generated SQL.

Now describe the tables using using table metadata objects

entries = Table('entries', engine,
        Column('entry_id', Integer, primary_key = True),
        Column('title', String(255), nullable = True),
        Column('content', String(), nullable = True),
        Column('status', String(10), nullable = False),
        Column('creation_date', DateTime()),
        Column('publish_date', DateTime())
    )

comments = Table('comments', engine,
        Column('comment_id', Integer, primary_key = True),
        Column('entry_id', Integer, ForeignKey("entries")),
        Column('comment', String(255), nullable = False),
        Column('submitter', String(60), nullable = False),
        Column('submit_date', DateTime())
    )

# Create the tables
entries.create()
comments.create()

Now load some data.

entries.insert().execute(entry_id=1,
            title="My first blog entry",
            content="A blogging I will go.....",
            status="published",
            creation_date="2006-04-17 08:15:30",
            publish_date="2006-04-17 08:25:00")

entries.insert().execute(entry_id=2,
            title="My second blog entry",
            content="Another day, another blog entry....",
            status="draft",
            creation_date="2006-04-18 09:35:30")

comments.insert().execute(comment_id=1,
            entry_id=1,
            comment="The entry needs more substance.",
            submitter="joe.blogger@home.net",
            submit_date="2006-04-17 09:05:00")

comments.insert().execute(comment_id=2,
            entry_id=1,
            comment="I disagree with the first comment.",
            submitter="jane.doe@rip.net",
            submit_date="2006-04-17 09:05:00")

Get some data with a simple select with where clause and order by

cursor = entries.select(entries.c.status=='published',
                order_by=[entries.c.publish_date]).execute()

rows = cursor.fetchall()
for row in rows:
    # Get column data by index
    title = row[1]
    # by column name
    content = row['content']
    # by column accessor
    status = row.status

Now a simple join

cursor = comments.select(comments.c.entry_id==entries.c.entry_id).execute()

which generates the following SQL:

SELECT comments.comment_id, comments.entry_id, comments.comment, 
comments.submitter, comments.submit_date
FROM comments, entries
WHERE comments.entry_id = entries.entry_id

Of course, this quick overview only scratches the surface of what you can do with SQLAlchemy. It also has support for outer joins, subqueries, unions, updates and deletes. See here for more in depth documentation.

Database design for newbies

Found this whitepaper on database design by Ian Bicking & Brian Moloney. A good explanation of Dr Codd's rules but in a language a newbie could understand.

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")