A quick MySQLdb intro for jason

DBHandler.py

__author__="mark"
__date__ ="$Jan 12, 2012 5:02:38 PM$"

import MySQLdb as mysql
from MySQLdb import connections
from MySQLdb import cursors

class DBHandler(mysql.connections.Connection):
    def __init__(self,dhost=db_host,duser=db_user,dpass=db_pass,dbname=db,cursor='DictCursor'):
        super(DBHandler,self).__init__(
                      host = dhost,
                      user = duser,
                      passwd = dpass,
                      db = dbname,
                      cursorclass=getattr(mysql.cursors, cursor)
            )
            
    def getall(self,q,params=None):
        try:
            cur = self.cursor()
            cur.execute(q,params)
            res = cur.fetchall()
            return res
        except mysql.OperationalError:
            try:
                self.__init__(self.cursorclass)
                cur = self.cursor()
                cur.execute(q,params)
                res = cur.fetchall()
                return res
            except:
                #blackout fail
                return [] 

    def getone(self,q,params=None):
        try:
            cur = self.cursor()
            cur.execute(q,params)
            res = cur.fetchone()
            if res != None:
                    return res.values()[0]
            else:
                    return None
        except mysql.OperationalError:
            try:
                self.__init__(self.cursorclass.__name__)
                cur = self.cursor()
                cur.execute(q,params)
                res = cur.fetchone()
                if res != None:
                    return res.values()[0]
                else:
                    return None
            except Exception as e:
                #fail
                return None

    def getrow(self,q,params=None):
        try:
            cur = self.cursor()
            cur.execute(q,params)
            res = cur.fetchone()
            return res
        except mysql.OperationalError:
            try:
                self.__init__(self.cursorclass.__name__)
                cur = self.cursor()
                cur.execute(q,params)
                res = cur.fetchone()
                return res
            except:
                return None

    def execute(self,q,params=None):
        try:
            cur = self.cursor()
            cur.execute(q,params)
            self.commit()
            return cur.lastrowid
        except mysql.OperationalError:
            try:
                self.__init__(self.cursorclass.__name__)
                cur = self.cursor()
                cur.execute(q,params)
                self.commit()
                return cur.lastrowid
            except:
                return None

    def close(self):
        super(mysql.connections.Connection, self).close()

    def __del__(self):
        if self.open:
            self.close()

if __name__ == "__main__":
    print db_host
    print db
    print db_user

mysql_intro.py

import sys,os
from models.DBHandler import DBHandler

import MySQLdb
from MySQLdb.cursors import DictCursor
from DBHandler import DBHandler

GET_USER = 'select * from users where id = %s'
UPDATE_USER_NAME = 'update users set username = %s where id = %s'

def oldway():
	con = MySQLdb.connect(host='127.0.0.1',user='uname',passwd='passwd',db='dbname',cursorclass=DictCursor)
	curs = con.cursor()
	curs.execute('select * from users where id = 4;')

	print 'Number of results %s' % curs.rowcount

	row = curs.fetchone()
	jason = dict()

	jason['id'] = row['id']
	jason['name'] = row['username']
	jason['age'] = row['age']
	jason['pets'] = []

	curs.execute('select pets.name as pet_name from pets join users on pets.user_id = users.id where users.id = %s;',jason['id'])
	for x in range(0,curs.rowcount):
		jason['pets'].append(curs.fetchone()['pet_name'])

	print jason

def newway(uid):
	db = DBHandler(dhost='127.0.0.1',duser='uname',dpass='passwd',dbname='dbname',cursor='DictCursor')
	db.execute(UPDATE_USER_NAME,("bagger",4))
	vinny = db.getrow(GET_USER,uid)
	print vinny

if __name__ == '__main__':
	newway(4)