LightMysql: Python class encapsulated to facilitate MySQL operations,

LightMysql: Python class encapsulated to facilitate MySQL operations,
Original article: Workshop. However, I think it is not easy to use it. To this end, I encapsulated a Python class LightMysql on the basis of mysqldb.Let's take a look at how to use

Example. py

#! /Usr/bin/env python #-*-coding: UTF-8-*-from LightMysql import LightMysqlif _ name _ = '_ main _': # configuration information, here, the host, port, user, passwd, and db are required. dbconfig = {'host': '2017. 0.0.1 ', 'Port': 3306, 'user': 'danfenggcao', 'passwd': '000000', 'db': 'test', 'charset ': 'utf8'} db = LightMysql (dbconfig) # create a LightMysql object. If the connection times out, it will automatically reconnect # query (select, show) uses query () function SQL _select = "SELECT * FROM Customer" result_all = db. query (SQL _select) # returns all data result_count = db. query (SQL _select, 'Count') # returns the number of rows result_one = db. query (SQL _select, 'one') # returns a row # the dml () function SQL _update = "update Customer set Cost = 2 where Id = 2" result_update = db is used for addition, deletion, and modification. dml (SQL _update) SQL _delete = "delete from Customer where Id = 2" result_delete = db. dml (SQL _delete) db. close () # The operation ends and the object is closed.

Install

This class depends on MySQLdb, so you need to install the MySQLdb package first.

• Install python Package Manager (easy_install or pip)

Easy_install mysql-python or pip install MySQL-python

• Or manually compile and install

http://mysql-python.sourceforge.net/

Complete LightMysql code

#!/usr/bin/env python# -*- coding: utf-8 -*-import MySQLdbimport time, reclass LightMysql:    """Lightweight python class connects to MySQL. """    _dbconfig = None    _cursor = None    _connect = None    _error_code = '' # error_code from MySQLdb    TIMEOUT_DEADLINE = 30 # quit connect if beyond 30S    TIMEOUT_THREAD = 10 # threadhold of one connect    TIMEOUT_TOTAL = 0 # total time the connects have waste    def __init__(self, dbconfig):        try:            self._dbconfig = dbconfig            self.dbconfig_test(dbconfig)            self._connect = MySQLdb.connect(                host=self._dbconfig['host'],                port=self._dbconfig['port'],                user=self._dbconfig['user'],                passwd=self._dbconfig['passwd'],                db=self._dbconfig['db'],                charset=self._dbconfig['charset'],                connect_timeout=self.TIMEOUT_THREAD)        except MySQLdb.Error, e:            self._error_code = e.args[0]            error_msg = "%s --- %s" % (time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())), type(e).__name__), e.args[0], e.args[1]            print error_msg            # reconnect if not reach TIMEOUT_DEADLINE.            if self.TIMEOUT_TOTAL < self.TIMEOUT_DEADLINE:                interval = 0                self.TIMEOUT_TOTAL += (interval + self.TIMEOUT_THREAD)                time.sleep(interval)                return self.__init__(dbconfig)            raise Exception(error_msg)        self._cursor = self._connect.cursor(MySQLdb.cursors.DictCursor)    def dbconfig_test(self, dbconfig):        flag = True        if type(dbconfig) is not dict:            print 'dbconfig is not dict'            flag = False        else:            for key in ['host','port','user','passwd','db']:                if not dbconfig.has_key(key):                    print "dbconfig error: do not have %s" % key                    flag = False            if not dbconfig.has_key('charset'):                self._dbconfig['charset'] = 'utf8'        if not flag:            raise Exception('Dbconfig Error')        return flag    def query(self, sql, ret_type='all'):        try:            self._cursor.execute("SET NAMES utf8")            self._cursor.execute(sql)            if ret_type == 'all':                return self.rows2array(self._cursor.fetchall())            elif ret_type == 'one':                return self._cursor.fetchone()            elif ret_type == 'count':                return self._cursor.rowcount        except MySQLdb.Error, e:            self._error_code = e.args[0]            print "Mysql execute error:",e.args[0],e.args[1]            return False    def dml(self, sql):        '''update or delete or insert'''        try:            self._cursor.execute("SET NAMES utf8")            self._cursor.execute(sql)            self._connect.commit()            type = self.dml_type(sql)            # if primary key is auto increase, return inserted ID.            if type == 'insert':                return self._connect.insert_id()            else:                return True        except MySQLdb.Error, e:            self._error_code = e.args[0]            print "Mysql execute error:",e.args[0],e.args[1]            return False    def dml_type(self, sql):        re_dml = re.compile('^(?P<dml>\w+)\s+', re.I)        m = re_dml.match(sql)        if m:            if m.group("dml").lower() == 'delete':                return 'delete'            elif m.group("dml").lower() == 'update':                return 'update'            elif m.group("dml").lower() == 'insert':                return 'insert'        print "%s --- Warning: '%s' is not dml." % (time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())), sql)        return False    def rows2array(self, data):        '''transfer tuple to array.'''        result = []        for da in data:            if type(da) is not dict:                raise Exception('Format Error: data is not a dict.')            result.append(da)        return result    def __del__(self):        '''free source.'''        try:            self._cursor.close()            self._connect.close()        except:            pass    def close(self):        self.__del__()

 

Clone LightMysql code

Reference