Howto generlize database descriptions?

[Sam Penrose]

On 17 Sep 2001, Danny Kohn wrote:

>> Guess that tuples or tuples in tuples may be a way to describe it but
then,
>> how efficient with the program execution be with all these references
being
>> used every time. I'm looking for something like define/struc in C
where I can
>> build my database from and then use the exact same data to access it.

Ignacio Vazquez-Abrams recommended:
> Look into using dictionaries.

What he said. Personally I keep table creation statements in files which
I pipe into MySQL, but in general dictionaries are the natural way to
handle data in Python that has is going to be passed to/from an SQL
INSERT, UPDATE, or SELECT statement.

Here is a hastily edited excerpt from our database abstraction layer. I
did the editing in my email client and so the spacing may not be right;
there may be NameErrors, etc. USE AT OWN RISK. If there is sufficient
interest I will try to post a complete and cleaned up version. The
printQuery method is a rather miserable hack; I'm sure there are other
infelicities.

import MySQLdb, types
ProgrammingError = MySQLdb.ProgrammingError
MySQLdb.type_conv[MySQLdb.FIELD_TYPE.LONG] = int 
# so that MySQL int(11) values 
# will be returned as ints, not longs. Would cause a ValueError 
# if you actually had a 9+ digit integer stored in there.

import masterSharedPath, DateTime 
# masterSharedPath puts DateTime on path; this is here to remind y'all
# that MySQLdb's use of mxDateTime and some design choices in both
# MySQLdb and mxDateTime can give you grief when handling SQL date/time
# objects

class MySQLConnection(SQLConnection):
    def __init__(self, database, user, passwd, host='localhost'):
        """Usage: db = MySQLConnection('database', 'user',
'passwd')."""
        self.connection = MySQLdb.connect(
               host=host, user=user, passwd=passwd, db=database)
        self.cursor = self.connection.cursor()
        self.dictCursor = self.connection.cursor(MySQLdb.DictCursor)

class SQLConnection:
    """An abstract class to hold methods that construct query strings;
base classes must create the self.connection and self.cursor objects
in their __init__s."""
    connection = None
    cursor = None
    dictCursor = None
    debug = 0 # see printQuery

    def _select(self, table, fields, whereClause, sort, limit,
cursor=None):
        """Return up to <limit> results of
        SELECT fields FROM table WHERE whereClause sortBy;
        (where 'fields' is a string or list of strings, whereClause and
sortBy are both optional but sort has to begin with 'order by'
or 'group by' or another valid SQL specifier) as a tuple
containing either tuples or dictionaries, depending on which
cursor we are using.

        Limit can be an integer (inside a string or otherwise), or a
string such as '10, 5' which will return records 11, 12, 13, 14,
and 15 (like the Python slice [10:10+5]."""
        if not cursor: 
            cursor = self.cursor
        if whereClause: 
            whereClause = 'WHERE ' + whereClause
        if limit: 
            limit = 'LIMIT %s' % limit
        fields = stringFields(fields)
        query = 'SELECT %s FROM %s %s %s %s' % (
             fields, table, whereClause, sort, limit)
        if self.debug: 
            print query
        else:
            cursor.execute(query)
            return cursor.fetchall()
    
    def select(self, table, fields, whereClause='', sort='', limit=''):
        """Return a tuple of Python dictionaries, one dictionary per row
        whose keys are the field names and values the corresponding
values.

        MySQLdb will strip off the table names if you pass them in
unless they are ambiguous,
        in which case it will attach *some* of the table names for
*some* of the ambiguous fields. This gets ugly. Solution: 
        do not use this query if you are joining tables
        and selecting same-named columns from > 1 table."""
        return self._select(table, fields, whereClause, sort, limit,
self.dictCursor)
        
    def selectTuples(self, table, fields, whereClause='', sort='',
limit=''):
        """Return a tuple of tuples."""
        return self._select(table, fields, whereClause, sort, limit)

    def singleSelect(self, table, field, whereClause=''):
        """Return the value of a single field in a single row, with no
tuple- or dictionary-wrapping."""
        results = self._select(table, field, whereClause, '', '')
        if len(results) != 1:
            message = 'singleSelect returned %s results' % len(results)
            raise ProgrammingError, message
        return results[0][0]
    
    def selectList(self, table, field, whereClause='', sort='',
limit=''):
        """Return a list of the values of a single field according to a
whereClause; raise error if asked for more than one field.

        Commonly you want to get, say, all the primary keys. select and
        selectTuples will package them into single-item dictionaries or
tuples, respectively, and return those in a list. This method
will return a list containing the bare values."""
        userField = field # used if error
        field = stringFields(field)
        if ',' in field:
            message = '''selectList takes only one field; you passed it
            "%s".''' % userField
            raise ProgrammingError, message
        
        tupleOfTuples = self._select(
                table, field, whereClause, sort, limit)
        return [t[0] for t in tupleOfTuples]

    def printQuery(self, selfDotMethod, args):
        """Print to screen the string you have been sending to the
database.

        Example:
            db = MySQLConnection('database', 'user',
'passwd')
            db.select('table', 'fields', 'whereClause')
            ...incomprehensible error raised; what's going on here?...
            db.printQuery(db.select, ('table', 'fields',
'whereClause'))
        """
        self.debug = 1
        apply(selfDotMethod, args)
        self.debug = 0


def stringFields(fields):
    '''Take in either a list or a string of field names and return a
string.'''
    if type(fields) is types.StringType: 
        return fields
    return ', '.join(fields)