Peter's Blog

I haven't posted any code for a while.

Here is a python module called DBTable to encapsulate the odbc module. It works with dictionarys of field name->value mappings which can be fetched from the database, inserted into the database, queried for or deleted.

   1  #
   2  # Database wrapper class.
   3  #
   4  import odbc
   5  import os
   6  import traceback
   7  
   8  class DBTable:
   9      """
  10      Wrapper for database table
  11      """
  12      FIELD_TYPE = 0
  13  
  14      def __init__( self, oConnection, strTable):
  15          self.oConnection = oConnection
  16          self.strTable = strTable
  17  
  18          oCursor = oConnection.cursor()
  19          oCursor.execute( "SELECT * FROM %s" % strTable)
  20  
  21          self.oFields = [ oField[0] for oField in oCursor.description]
  22          self.oFieldDescription = dict( [ (oField[0],
  23                                        oField[1:]) for oField in oCursor.description])
  24  
  25      def Select( self, strQuery):
  26          """
  27          Select records from query
  28  
  29          Takes either SQL of select statement or a dictionary containing
  30          field names and values to find.
  31          """
  32          self.oCursor = self.oConnection.cursor()
  33          if type( strQuery) == type(""):
  34              self.oCursor.execute( strQuery)
  35          else:
  36              #
  37              # assume query is a dict
  38              #
  39              self.oCursor.execute( "SELECT * FROM %s WHERE %s" % (self.strTable,
  40                                                          self.DictToWhere( strQuery)))
  41  
  42      def FetchOne( self):
  43          """
  44          Get next row of results
  45          Returns a dictionary holding field names and values.
  46          """
  47          oRow = self.oCursor.fetchone()
  48          if oRow:
  49              """
  50              Build a dictionary to map field name->value
  51              """
  52              return dict([(self.oFields[i], oRow[i]) for i in range(len(oRow))])
  53          else:
  54              return None
  55  
  56      def Insert( self, oDict):
  57          """
  58          Insert a row in the database
  59          Takes a dictionary holding field names and values.
  60          """
  61          strFields = oDict.keys()
  62          strValues = []
  63          for strField in strFields:
  64              strValue = oDict[strField]
  65              strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
  66  
  67              strValues.append( self.FormatField( strField, strValue))
  68  
  69          strSQL = "INSERT INTO %s ( %s) VALUES(%s);" % ( self.strTable,
  70                                                           ", ".join( strFields),
  71                                                           ", ".join( strValues))
  72          print strSQL
  73          oCursor = self.oConnection.cursor()
  74          oCursor.execute( strSQL)
  75          self.oConnection.commit()
  76  
  77      def Delete( self, oDict):
  78          """
  79          Delete row based on dictionary contents
  80          Takes a dictionary holding field names and values.
  81          """
  82          strSQL = "DELETE FROM %s WHERE %s;" % ( self.strTable, self.DictToWhere( oDict))
  83          print strSQL
  84          oCursor = self.oConnection.cursor()
  85          oCursor.execute( strSQL)
  86          self.oConnection.commit()
  87  
  88      def DictToWhere( self, oDict):
  89          """
  90          Convert dictionary to WHERE clause.
  91          """
  92          strFields = oDict.keys()
  93          strExpressions = []
  94  
  95          for strField in strFields:
  96              strValue = oDict[strField]
  97              strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
  98  
  99              strValue = self.FormatField( strField, strValue)
 100  
 101              strExpressions.append( '%s = %s' % (strField, strValue))
 102  
 103          return " AND ".join( strExpressions)
 104  
 105      def FormatField( self, strField, strValue):
 106          """
 107          Format a field for an sql statement.
 108          """
 109          strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
 110          if strType == 'STRING':
 111              return "'%s'" % str(strValue).replace( "'", "''")
 112          elif strType == 'NUMBER':
 113              return '%d' % int( strValue)
 114          else:
 115              raise 'unknown field type %s' % strType
 116  
Toggle Line Numbers

Here is something that uses it. Run from the command line as

DBView.py <odbc connection name> <table name>

it will create an html dump of the specified table in the specified odbc connection and will display the html in your default browser. This is useful if, for example, you are working on a project that uses Access 2003 database but you only have Access 97.

   1  #
   2  # Dump database tables in html format.
   3  #
   4  import odbc
   5  import os
   6  import traceback
   7  import DBTable
   8  
   9  def DumpDBTable( oConnection, strTable):
  10      "Dump specified table from ODBC connection"
  11  
  12      #
  13      # Connect to database
  14      #
  15      oCursor = oConnection.cursor()
  16  
  17      #
  18      # Start building html output.
  19      #
  20      strRows = []
  21  
  22      strRows.append( u"""<head>
  23      <title>Dump of %s</title>
  24      </head>
  25      <body>
  26  """ % (strTable))
  27  
  28      strRows.append( "<h1>%s</h1>" % (strTable,))
  29      strRows.append( '<table border="1" cellspacing="0">')
  30      try:
  31  
  32          oDbTable = DBTable.DBTable( oConnection, strTable)
  33          oDbTable.Select( 'SELECT * FROM %s' % strTable)
  34  
  35          strRow = "<tr>"
  36  
  37          for strField in oDbTable.oFields:
  38              strRow += "<th>%s</th" % strField
  39  
  40          strRow += "</tr>"
  41          strRows.append( strRow)
  42  
  43          while 1:
  44              oRow = oDbTable.FetchOne()
  45              if oRow == None:
  46                  break
  47  
  48              #
  49              # Walk through the columns in each row of the table.
  50              #
  51              strRow = "<tr>"
  52  
  53              for strField in oDbTable.oFields:
  54                  #
  55                  # Convert the contents of each field to an entry in the table.
  56                  # These will all be unicode.
  57                  #
  58                  strRow += "<td>%s</td>" % str( oRow[strField])
  59  
  60              strRow += "</tr>"
  61              strRows.append( strRow)
  62      except:
  63          print "Failed on %s" % (strTable)
  64          traceback.print_exc()
  65          return
  66  
  67      #
  68      # Terminate the html.
  69      #
  70      strRows.append( "</table>")
  71      strRows.append( "</body>")
  72  
  73      return "\n".join( strRows)
  74  
  75  import BaseHTTPServer
  76  import webbrowser
  77  
  78  def LoadInDefaultBrowser(html):
  79      """Display html in the default web browser without creating a temp file.
  80  
  81      Instantiates a trivial http server and calls webbrowser.open with a URL
  82      to retrieve html from that server.
  83      """
  84  
  85      class RequestHandler(BaseHTTPServer.BaseHTTPRequestHandler):
  86          def do_GET(self):
  87              bufferSize = 1024*1024
  88              for i in xrange(0, len(html), bufferSize):
  89                  self.wfile.write(html[i:i+bufferSize])
  90  
  91      server = BaseHTTPServer.HTTPServer(('127.0.0.1', 0), RequestHandler)
  92      webbrowser.open('http://127.0.0.1:%s' % server.server_port)
  93      server.handle_request()
  94  
  95  import sys
  96  import odbc
  97  
  98  oConnection = odbc.odbc( sys.argv[1])
  99  LoadInDefaultBrowser( DumpDBTable( oConnection, sys.argv[2]))
Toggle Line Numbers

Trying to write to a database via odbc from python as follows:

>> import odbc
>> o = odbc.odbc( '<odbc connectionname>')
>> c = o.cursor()
>> c.execute( "INSERT INTO Sequence (Description) VALUES( 'Blah')")

and getting this error:

dbi.operation-error: [Microsoft][ODBC Microsoft Access Driver] Operation
must use an updateable query.

The reason is obvious: the .mdb file was read only.

The odbc wrapper appears to come with the pythonwin stuff and looks ok as a library. I still have the usual odbc problem, no standard way to inspect the schema, i.e. I have to know what the table and field names are.

Update: from this article I have determined that there is a hidden property in the cursor object that contains all the field information that I need:

>>> c.execute( 'select * from Sequence')
0
>>> c.description
[('SequenceID', 'NUMBER', 11, 10, 0, 0, 1),
('Description', 'STRING', 100, 100, 0, 0, 1),
('DownloadToMicrocontroller', 'STRING', 25, 1, 0, 0, 0),
('Comments', 'STRING', 0, 1073741823, 0, 0, 1)]

Unfortunately, python's usual introspection mechanisms do not work for this property, which is why I couldn't find it:

>>> dir(c)
['close', 'execute', 'fetchall', 'fetchmany', 'fetchone',
'setinputsizes', 'setoutputsize']
>>> help(c)
Help on odbccur:

<odbccur object>