Python 3操作MySQL PyMySQLパッケージに基づくクラス

Python 3操作MySQL PyMySQLパッケージに基づくクラス
 データベースを操作するフレームワークを使用してプロジェクトを開発していない場合、私たちは自分でデータベース接続の問題を処理する必要があります.今日はPythonのプレゼンテーションプロジェクトをして、MySQLデータベースを操作するクラスを書いて、PyMySQLライブラリに基づいてPython 3で実現します.ビジネスロジックコードを書くときは、便利で、時間関係がよく、あまり完璧ではなく、よく使われる操作だけを書くことができます.
  直接コード:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

'Python    MySQL         (  Python3)'

import pymysql.cursors


class Database:
    """ Python    MySQL          """
    """
    conf:    ，            ， host、port、user、pw、db、charset(  ,  utf8)
    connected:   ，True       ，False    

    insert(self, table, val_obj):   ，        
        table:      
        val_obj:                  
          :              ID，    False

    update(self, table, val_obj, range_str):   ，         
        table:      
        val_obj:                  
        range_str:             
          :          ，    False

    delete(self, table, range_str):   ，         
        table:      
        range_str:             
          :          ，    False

    select_one(self, table, factor_str, field='*'):   ，               
        table:      
        factor_str:            
        field:           ，       ，    ，        
          :                    ，    False

    select_more(self, table, range_str, field='*'):   ，             
        table:      
        range_str:          
        field:           ，       ，    ，        
          :                    ，    False

    count(self, table, range_str='1'):   ，              
        table:      
        range_str:          ，    ，        
          :            ，    False

    sum(self, table, field, range_str='1'):   ，              
        table:      
        field:        ，            
        range_str:             ，    ，       
          :         ，    False

    close(self):   ，       ，           ，            
    """
    connected = False
    __conn = None

    #     ，           
    def __init__(self, conf):
        if type(conf) is not dict:
            print('  :         ！')
        else:
            for key in ['host', 'port', 'user', 'pw', 'db']:
                if key not in conf.keys():
                    print('  :        %s' % key)
            if 'charset' not in conf.keys():
                conf['charset'] = 'utf8'
        try:
            self.__conn = pymysql.connect(
                host=conf['host'],
                port=conf['port'],
                user=conf['user'],
                passwd=conf['pw'],
                db=conf['db'],
                charset=conf['charset'],
                cursorclass=pymysql.cursors.DictCursor)
            self.connected = True
        except pymysql.Error as e:
            print('       :', end='')

    #         
    def insert(self, table, val_obj):
        sql_top = 'INSERT INTO ' + table + ' ('
        sql_tail = ') VALUES ('
        try:
            for key, val in val_obj.items():
                sql_top += key + ','
                sql_tail += val + ','
            sql = sql_top[:-1] + sql_tail[:-1] + ')'
            with self.__conn.cursor() as cursor:
                cursor.execute(sql)
            self.__conn.commit()
            return self.__conn.insert_id()
        except pymysql.Error as e:
            self.__conn.rollback()
            return False

    #         
    def update(self, table, val_obj, range_str):
        sql = 'UPDATE ' + table + ' SET '
        try:
            for key, val in val_obj.items():
                sql += key + '=' + val + ','
            sql = sql[:-1] + ' WHERE ' + range_str
            with self.__conn.cursor() as cursor:
                cursor.execute(sql)
            self.__conn.commit()
            return cursor.rowcount
        except pymysql.Error as e:
            self.__conn.rollback()
            return False

    #          
    def delete(self, table, range_str):
        sql = 'DELETE FROM ' + table + ' WHERE ' + range_str
        try:
            with self.__conn.cursor() as cursor:
                cursor.execute(sql)
            self.__conn.commit()
            return cursor.rowcount
        except pymysql.Error as e:
            self.__conn.rollback()
            return False

    #            
    def select_one(self, table, factor_str, field='*'):
        sql = 'SELECT ' + field + ' FROM ' + table + ' WHERE ' + factor_str
        try:
            with self.__conn.cursor() as cursor:
                cursor.execute(sql)
            self.__conn.commit()
            return cursor.fetchall()[0]
        except pymysql.Error as e:
            return False

    #            
    def select_more(self, table, range_str, field='*'):
        sql = 'SELECT ' + field + ' FROM ' + table + ' WHERE ' + range_str
        try:
            with self.__conn.cursor() as cursor:
                cursor.execute(sql)
            self.__conn.commit()
            return cursor.fetchall()
        except pymysql.Error as e:
            return False

    #             
    def count(self, table, range_str='1'):
        sql = 'SELECT count(*)res FROM ' + table + ' WHERE ' + range_str
        try:
            with self.__conn.cursor() as cursor:
                cursor.execute(sql)
            self.__conn.commit()
            return cursor.fetchall()[0]['res']
        except pymysql.Error as e:
            return False

    #      （       ）    
    def sum(self, table, field, range_str='1'):
        sql = 'SELECT SUM(' + field + ') AS res FROM ' + table + ' WHERE ' + range_str
        try:
            with self.__conn.cursor() as cursor:
                cursor.execute(sql)
            self.__conn.commit()
            return cursor.fetchall()[0]['res']
        except pymysql.Error as e:
            return False

    #             
    def __del__(self):
        try:
            self.__conn.close()
        except pymysql.Error as e:
            pass

    #        
    def close(self):
        self.__del__()