Mysql类

#-*-coding:utf-8-*-
import pymysql.cursors
from pymysql.err import *
import Log
from common.setting import *

log = Log.getLogger()

‘‘‘
  MySQL工具类
‘‘‘
class MySqlUtil:

     @staticmethod
     def connect(dbInfo):
         # Connect to the database

         if not dbInfo in DBinfo.keys():
             log.error("数据库不存在: "+dbInfo)
             raise  DatabaseError

         conn = pymysql.connect(
             host=DBinfo[dbInfo][‘数据库hosts‘],
             port=int(DBinfo[dbInfo][‘数据库端口‘]),
             user=DBinfo[dbInfo][‘用户名‘],
             passwd=DBinfo[dbInfo][‘用户密码‘],
             db=DBinfo[dbInfo][‘数据库名称‘],
             charset=‘utf8‘
         )
         log.debug("连接数据库成功，databse="+dbInfo)
         return conn

     @staticmethod
     def selectOne (dbInfo,sql)->tuple:
        conn=MySqlUtil.connect(dbInfo)

        try:
            cursor = conn.cursor()
            log.debug("开始执行sql=｛｝"+sql)
            cursor.execute(sql)
            dbResult = cursor.fetchone()
        except MySQLError as e:
            log.error(str(e))

        except Exception as e:
            log.error(str(e))

        finally:
            cursor.close()
            conn.close()
        return dbResult

     @staticmethod
     def selectAll(dbInfo, sql)->tuple:
         conn = MySqlUtil.connect(dbInfo)

         try:
             cursor = conn.cursor()
             log.debug("开始执行sql=｛｝" + sql)
             cursor.execute(sql)
             dbResult = cursor.fetchall()
         except MySQLError as e:
             log.error(str(e))

         except Exception as e:
             log.error(str(e))

         finally:
             cursor.close()
             conn.close()
         return dbResult

     @staticmethod
     def update(dbInfo, sql):
         conn = MySqlUtil.connect(dbInfo)

         try:
             cursor = conn.cursor()
             log.debug("开始执行sql=｛｝" + sql)
             cursor.execute(sql)
             conn.commit()
         except MySQLError as e:
             log.error(str(e))
             conn.rollback()
         except Exception as e:
             log.error(str(e))
             conn.rollback()
         finally:
             cursor.close()
             conn.close()

     @staticmethod
     def delete(dbInfo, sql):
         conn = MySqlUtil.connect(dbInfo)

         try:
             cursor = conn.cursor()
             log.debug("开始执行sql=｛｝" + sql)
             cursor.execute(sql)
             conn.commit()
         except MySQLError as e:
             log.error(str(e))
             conn.rollback()
         except Exception as e:
             log.error(str(e))
             conn.rollback()
         finally:
             cursor.close()
             conn.close()


     @staticmethod
     def insert(dbInfo, sql):
         conn = MySqlUtil.connect(dbInfo)

         try:
             cursor = conn.cursor()
             log.debug("开始执行sql=｛｝" + sql)
             cursor.execute(sql)

             conn.commit()
         except MySQLError as e:
             log.error(str(e))
             conn.rollback()
         except Exception as e:
             log.error(str(e))
             conn.rollback()
         finally:
             cursor.close()
             conn.close()

     ##批量添加数据，！！！！！数据格式必须list[tuple(),tuple(),tuple()]  或者tuple(tuple(),tuple(),tuple())
     @staticmethod
     def insertBatch(dbInfo, sql,tupleList):
       conn = MySqlUtil.connect(dbInfo)

       try:
           cursor = conn.cursor()
           log.debug("开始执行sql=｛｝" + sql)
           cursor.executemany(sql,tupleList)

           conn.commit()
       except MySQLError as e:
          log.error(str(e))
          conn.rollback()
       except Exception as e:
         log.error(str(e))
         conn.rollback()
       finally:
            cursor.close()
            conn.close()

‘‘‘
ON DUPLICATE KEY UPDATE 
存在重复键值时，覆盖
sql = "INSERT INTO auto_relation (model_name, content,type) 
       VALUES (%s, %s, %s) 
       ON DUPLICATE KEY UPDATE content=VALUES (content)"
       
‘‘‘