easy operat mysqldb

dbquery.py

import MySQLdb

class Row(dict):
    """A dict that allows for object-like property access syntax."""
    def __getattr__(self, name):
        try:
            return self[name]
        except KeyError:
            raise AttributeError(name)
            
class Gdbm(object):
    """通用数据库连接管理 general database mananger"""
    def __init__(self, host, user, passwd, db, charset="utf8"):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.conn = MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.db,charset=charset)
        self.cursor = self.conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

    def query(self, sql):
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return [Row(d) if type(d) is dict else d for d in data]

    def execute(self, sql):
        self.cursor.execute(sql)
        pk = self.conn.insert_id()
        self.conn.commit()
        return pk

    def execute_no_commit(self, sql):
        """execute many sql then commit by hand"""
        self.cursor.execute(sql)

    def query_as_dict(self, sql):
        """result is [dict{},...]"""
        self.cursor.execute(sql)
        return dictfetchall(self.cursor)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def close(self):
        self.cursor.close()
        self.conn.close()
        
    
def dictfetchall(cursor):
    "Returns all rows from a cursor as a dict"
    desc = cursor.description
    return [
        Row(zip([col[0] for col in desc], row))
        for row in cursor.fetchall()
    ]