IronPython, MS SQL, and PEP 249


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

Comments (6)

  1. vizcaynot says:

    Dino:

    I was "fighting" many months trying to integrate Ipy with Django. That is the difference between a genius and a simpe mortal that I am 🙁

    I suspect you only tried the Django part referred to DB interfaces. is that right? (not the forms or template system, for example).

    Are you thinking to document your work?  I would for example, like to know how did you integrated Ipy  with some of the libraries and modules of Cpython that are not yet integrated to IPy and which are used by Django? How did you get the connection to IIS?

    Thanks!!

  2. DinoV says:

    vizcaynot,

    My ultimate goal is to not have to document anything because it all just works :).  I had to fix some bugs in IronPython along the way to get to the point where this worked.  It’s also worth noting that I was using 0.96.1 and not the latest from SVN.

    As for the libraries and modules the DB backend was the only thing I encountered which I needed to provide code for.  Everything else was already there.  But I haven’t gone to running the test suite yet, only just working through the Django tutorial that’s available on-line.  I was also just using the built-in development server – which for me is good because we’re running more code :).

    At PyCon I spent my time during the sprint working on getting the latest & greatest running.  There’s some Unicode/ASCII issues which I’m hoping to fix in particular. Those are also in 0.96.1 are actually more problematic in SVN because Django is more Unicode aware.  There were also a few random missing things (nt.access for example).

    Finally there’s sys.settrace support so we can run doctest and really figure out where Django’s broken.  I had really wanted to get to that at PyCon but hitting the other issues first sucked up all my time.  I’m not sure when I’ll get back to it but I have a couple of other things to post about the demo at PyCon and the specific changes made.

  3. I've got a number of emails complaining that folks haven't heard much from the DLR (Dynamic Language

  4. ASPInsiders says:

    I’ve got a number of emails complaining that folks haven’t heard much from the DLR (Dynamic Language

  5. RemLog says:

    One of the biggest privileges I have is the quality of people I get to hang out with here at Microsoft.