Hank's Place

Let's say you want to get the contents of a table from your database, and you don't want a tuple of tuples, the way you (or at least I) usually get stuff back from MySQLdb. Let's say what you really want is dictionary of class instances where the class has attribute names the same as column names from the table. And you want the keys of the dictionary to be the primary key from the database table. Well then your wants have been fulfilled. The following Python code does just that. Note that my database connection wraps around the MySQLdb module and has a method called query that accepts an SQL statement and returns the entire answer as a tuple of tuples where each inner tuple contains the data from one row of the table. Here's the code:

class Item:
    """
    ) Placeholder class that gets mutated as appropriate when being loaded with
    ) various table entries
    """
    def __init__(self):
        pass
        ## __init__ ##

    def __repr__(self):
        ret = []
        for colName in self.__dict__.keys():
            ret.append(" %s = %s" % (colName, self.__dict__[colName]))
        return "<\n" + '\n'.join(ret) + "\n>"
    ## Item ##


def getTable(db, tableName, extraTables = [], extraSQL = ""):
    """
    ) getTable(db, tableName, extraTables = [], extraSQL = "")
    ) 
    ) Given the name of a table loads as much as all of the table into a
    ) dictionary of Item instances where the dictionary is keyed on the primary
    ) key of the table. If the primary key is composed of multiple columns, the
    ) keys are tuples, otherwise the key is just the single value.
    )
    ) Parameters:
    )  db - a connection to the database
    )  tableName the table to be loaded
    )  extraTables - an optional list of table names that need to be part of
    )                the 'from' part of the SQL
    )  extraSQL - an optional string that fine tunes the SQL to reduce the
    )             number of table entries that get loaded
    )
    ) Example: if tableName is 'person' and tableList = ['company'] and
    )          extraSql is "person.companyid = company.id and
    )          company.name = 'Walmart'"
    )          you end up with a query that looks like:
    )          SELECT person.*
    )          FROM person, company
    )          WHERE person.companyid = company.id
    )          AND company.name = 'Walmart'
    )
    )          if tableList and extraSQL are left unset, you end up the SQL
    )          "SELECT person.* FROM person"
    )
    ) Returns: a dictionary of Item instances where each Item gets customized
    )          with the column names of the table. The keys are the primary
    )          keys of the table
    )
    ) Example: Let's say person looks like:
    )          +-----------+-------------+-----+
    )          | Field     | Type        | Key |
    )          +-----------+-------------+-----+
    )          | id        | int(11)     | PRI |
    )          | firstName | varchar(50) |     |
    )          | lastName  | varchar(50) |     |
    )          +-----------+-------------+-----+
    )          you end up with a dictionary keyed on the values from the id
    )          column where the values are instances of the Item class with
    )          attributes id, firstName, and lastName.
    """
    # First get the names of the columns and determine which column(s) is(are) the primary key(s)

    colNames = []
    primaryKey = []
    tableDes = m.query("describe %s" % tableName)
    for column in tableDes:
        colNames.append(column[0])
        if column[3].upper() == 'PRI':
            primaryKey.append(column[0])

    nCols = len(colNames)

    fromStr = ', '.join([tableName] + extraTables)
    sql = """SELECT %s.* FROM %s""" % (tableName, fromStr)
    if extraSQL:
        sql += " WHERE %s" % extraSQL

    dict = {}
    entries = db.query(sql)
    for entry in entries:
        item = Item()
        key = []
        for i in xrange(nCols):
            item.__dict__[colNames[i]] = entry[i]
            if colNames[i] in primaryKey:
                key.append(entry[i])

        if len(key) == 1:
            key = key[0]
        else:
            key = tuple(key)
        dict[key] = item

    return dict
    ## getTable ##

As you can tell, I really like the doc strings. Use them and pydoc and you've got your programming documentation done.