Peter's Blog

I realised that a lot of my old postings were not tagged by awtags because I hadn't been through them to categorise them and, worst still, I couldn't be bothered. This meant that the postings weren't indexed unless someone went way back through the blog history.

I decided to create a new tag for them called untagged. I wrote a python script to look for awtags with no tags assigned and to assign them to this new tag. I used python because I am far more confident in it than I am in php. There is not much point in making this a module or anything because it only needs doing once if I am methodical about giving tags to new postings. I could also have done this in raw SQL but the version of MySQL on Site5 does not support nested selects.

Once the 'untagged' tag is in place the only chore is to remove this tag from postings that I generate new tags for using my search facility. This can be done through the awtags administration interface (e.g. search for tag 'whatever' and remove tag 'untagged'). Also, now I can easily list the untagged articles, it is much easier to see what tags need adding.

   1  #
   2  # Assign a tag to nodes with no awtags
   3  #
   4  import MySQLdb
   5  import DBTable
   6  
   7  o = MySQLdb.Connect( 'localhost', '<mysql user name', '<password>', '<mysql database name>')
   8  
   9  oAwNodeDB = DBTable.DBTable( o, 'awtags_node')
  10  
  11  oAwNodeDB.Select()
  12  
  13  oTaggedNodes = {}
  14  
  15  while 1:
  16      oRow = oAwNodeDB.FetchOne()
  17      if not oRow:
  18          break
  19      oTaggedNodes[oRow['nid']] = 1
  20  
  21  oNodeDB = DBTable.DBTable( o, 'node')
  22  
  23  oNodeDB.Select( "SELECT * FROM node WHERE node.type = 'blog'")
  24  
  25  oNodes = {}
  26  
  27  while 1:
  28      oRow = oNodeDB.FetchOne()
  29      if not oRow:
  30          break
  31      oNodes[oRow['nid']] = 1
  32  
  33  for nNid in oNodes.keys():
  34      if not oTaggedNodes.has_key( nNid):
  35          oDict = { 'nid': nNid,
  36                          'tid': 84}
  37          oAwNodeDB.Insert( oDict)
Toggle Line Numbers

This assumes the 'untagged' tag has a tid of 84: you should create your own tag and see what number it is.

This uses the DBTable module I wrote a while back. I discovered to my delight that the python odbc and MySQLdb modules had virtually identical interfaces so this module worked largely unchanged. I had to tweek it a bit because the field types were recorded as numbers instead of strings. Here is the modified version. It should work with odbc as well.

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