PythonMyMySQLdb

"""
MySQLdb doesn't support python 2.2 so this is a simple interface 
using the command line tool.  The interface is based on MySQLdb
and pyscopg but is done from memory so the modules do not exactly line up.

Note that this only supports local databases
@author cpepe@tearsoffire.org
@date   23 July 2008
"""
import re
import commands

_mode = 'prod'

def syscall(cmd):
    """
    Execute a system call and do what you can with it
    """
    status, output = commands.getstatusoutput(cmd)
    if status is not 0:
        #error occured
        raise ValueError('System call failed to execute: %s' % ('sorry, unable to read stderr so no error details'))
        output = None
    return output

class MySQLCursor(object):
    def __init__(self, db, dbUser=None, dbPass=None):
        mine = [None, None, None]
        yours = [db, dbUser, dbPass]
        for i in range(0, len(mine) ):
            if type(yours[i]) == type(str()):
                mine[i] = yours[i]
            else:
                mine[i] = None
        self.db, self.dbUser, self.dbPass = mine
        if self.db is None:
            raise TypeError('db must be the database name as a string not type %s' % (type(db)))
        try:
            self.connected()
        except Exception, e:
            raise Exception('Unable to query the queue database, please check setup: %s' % (e))
   
    def connected(self):
        """
        Test the connection to the database to be sure we can execute queries 
        in the first place
        """
        result = self.execute('show tables;')
        if result is not None:
            return True
        else:
            return False

    def execute(self, query):
        """
        Execute an arbitrary query after escaping all quotes to prepare the 
        query for use on the commandline.
        """
        if type(query) != type(str()):
            #error
            raise TypeError('query must be a string')
        s_quote_regex = re.compile('[\']')
        d_quote_regex = re.compile('[\"]')
        c_quote_regex = re.compile('[\$]')
        #substitute escaped quotes for all ' or " i.e. patty o'hara -> patty o\'hara
        query = s_quote_regex.sub('\\\'', query )
        query = d_quote_regex.sub('\\\"', query )
        query = c_quote_regex.sub('\\\$', query )
        #do it
        cmd = "echo \"%s\" | mysql -u %s --password=%s -D %s" % (query, self.dbUser, self.dbPass, self.db)
        if _mode == 'prod':
            result = syscall(cmd)
        else:
            result = True
            print cmd
        return result
            
    def fetchOne(self, query):
        """
        the SQL is executed and the result comes back as col_id\ndata\ndata...
        to get just one result we only take the first 2 lines and ignore the rest
        then split by newlines to seperate them.  Next each value is a tab seperated 
        string so we split on tabs to get at the data.  Lastly the col_id and values
        are combined into an easy to use dictionary.
        """
        try:
            fpass = self.execute(query)
            spass = fpass.split('\n')[:2]
            keys = spass[0].split('\t')
            vals = spass[1].split('\t')
            return dict(zip(keys, vals))
        except Exception, e:
            #there were most likely no results from the query
            return None

    def fetchAll(self, query):
        return 'Function not implemented'