IronPython, MS SQL, and PEP 249

Article
03/17/2008

Over the past week and a half I've spent a little bit of time getting Django 0.96.1 running on IronPython. The 1st step in doing this was getting a database provider that would run on .NET that would work with Django. For DB backends Django basically follows PEP 249 with a few extensions. Here's the basic DB provider I came up with. It's not quite complete but it was good enough for me to get Django's tutorial running. To use this you just need to copy one of the existing DB backends and replace the code in base.py with the code below. For the rest of the code you can pull it from ado_mssql.

Basically this just uses the .NET System.Data namespace to do the communication with the database server.

import clr
clr.AddReference('System.Data')
from System import Data
from System.Data import SqlClient
import System

DatabaseError = Data.DataException

from threading import local

class SqlCursor(object):
    arraysize = 1

    def __init__(self, connection):
        self.connection = connection
        self.transaction = None
        self.reader = None
        self.record_enum = None

    def execute(self, sql, params=()):
        parameters = []

        # translate to named parameters
        if type(params) in (list, tuple):
            # indexed params, replace any %s w/ @GeneratedName#
            if sql.find('%s') != -1:
                cmd = ''
                sqlSplit = sql.split('%s')
                for text, value, index in zip(sqlSplit, params, range(len(params))):
                    cmd += text + <'@GeneratedName'> + str(index)
                    parameters.Add(SqlClient.SqlParameter(<'@GeneratedName'> + str(index), str(value)))

                sql = cmd + sqlSplit[-1]
        else:
            for name, value in params.iteritems():
                sql = sql.replace('%(' + name +')s', <'@'> + name)
                parameters.Add(SqlClient.SqlParameter(<'@'> + name, str(value)))

        command = SqlClient.SqlCommand(sql, self.connection)

        for param in parameters: command.Parameters.Add(param)

        self.record_enum = None
        self.reader = command.ExecuteReader()

    def close(self):
        self.reader.Close()

    def executemany(self, sql, param_list):
        res = []
        for s in sql:
            res.append(execute(s, param_list))
        return res

    def fetchall(self):
        return [self._make_record(record) for record in self.reader]

    def fetchone(self):
        if self.record_enum is None:
            self.record_enum = iter(self.reader)
        if self.record_enum.MoveNext():
            return self._make_record(self.record_enum.Current)
        return None

    def fetchmany(self, size=None):
        if size is None: size = SqlCursor.arraysize
        res = []
        for i in range(size):
            x = self.fetchone()

            if x is None: break

            res.append(x)
        return res

def _make_record(self, record):
return tuple((self._fix_one_record(record[i]) for i in xrange(record.FieldCount)))

    def _fix_one_record(self, record):
        if type(record) is System.DateTime:
            return datetime.datetime(record)

        return record

    @property
    def rowcount(self):
        if self.record is not None:
            return self.reader.RecordsAffected
        return -1

class DatabaseWrapper(local):
    def __init__(self, **kwargs):
        self.connection = None
        self.queries = []
        self.transaction = None

    def cursor(self):
        from django.conf import settings
        if self.connection is None:
            if not settings.DATABASE_HOST:
                settings.DATABASE_HOST = "(local)"
            if settings.DATABASE_NAME == '' or settings.DATABASE_USER == '':
                conn_string = "Data Source=%s;Initial Catalog=%s;Integrated Security=SSPI;MultipleActiveResultSets=True" % (settings.DATABASE_HOST, settings.DATABASE_NAME)
            else:
                conn_string = "Data Source=%s;Initial Catalog=%s;User ID=%s;Password=%s;Integrated Security=SSPI;MultipleActiveResultSets=True" % (settings.DATABASE_HOST, settings.DATABASE_NAME, settings.DATABASE_USER, settings.DATABASE_PASSWORD)

            self.connection = SqlClient.SqlConnection(conn_string)
            self.connection.Open()

        cursor = SqlCursor(self.connection)
        if settings.DEBUG:
            return util.CursorDebugWrapper(cursor, self)
        return cursor

    def _commit(self):
        if self.transaction is not None:
            return self.transaction.Commit()

    def _rollback(self):
        if self.transaction is not None:
            return self.transaction.Rollback()

    def close(self):
        if self.connection is not None:
            self.connection = None
            self.transaction = None

IronPython, MS SQL, and PEP 249

Additional resources