Python 3.把对数据库的操作封装成一个类

最新推荐文章于 2024-10-18 14:19:55 发布

柳絮9712

最新推荐文章于 2024-10-18 14:19:55 发布

阅读量4.5k

点赞数 4

分类专栏： Python 文章标签： Python

本文链接：https://blog.csdn.net/qq_32539403/article/details/83343581

版权

Python 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Python 3.把对数据库的操作封装成一个类：

#把对数据库的操作封装成一个类
import pymysql

class DBUtil:
    _db =None
    #在这里配置自己的SQL服务器
    _config = {
        'host':"localhost",
        'port':3306,
        'username':"root",
        'password':'123456',
        'database':"air",
        'charset':"utf-8"
    }

    def __connect(self):
        if(self._db == None):
            self._db = pymysql.connect(
                host = self._config['host'],
                port = self._config['port'],
                user = self._config['username'],
                passwd = self._config['password'],
                db = self._config['database'],
                charset = self._config['charset']
            )
        return self._db

    def __init__(self):
        self.__connect()

    def __del__(self):
        if(self._db is not None):
            self._db.close()

    def query(self,_sql):
        cursor = self.__connect().cursor()
        try:
            cursor.execute(_sql)
            data = cursor.fetchall()
            #提交到数据库执行
            self.__connect().commit()
        except:
            #如果发生错误则回滚
            self.__connect().rollback()
            return False
        return data

    def query_dic(self,_sql_dic):
        if('select' in _sql_dic.keys()):
            sql = "SELECT"+_sql_dic['select']+"FROM"+_sql_dic['from']+self.where(_sql_dic['where'])
            print(sql)
            return self.query(sql)
        elif('insert' in _sql_dic.keys()):
            sql = "INSERT INTO"+_sql_dic['insert']+self.quote(_sql_dic['domain_array'],type_filter=False+"VALUES"+self.quote(_sql_dic['value_array']))
            print(sql)
            return self.query(sql)
        if('delete' in _sql_dic.keys()):
            sql = "DELETE FROM"+_sql_dic['delete']+self.where(_sql_dic['where'])
            print(sql)
            return self.query(sql)

        def where(self,_sql):
            if(isinstance(_sql,dict)==False):
                return "WHERE"+ str(_sql)
            if(isinstance(_sql,dict)):
                _sql_dic = _sql
                s = "WHERE"
                index = 0
                for domain in _sql_dic:
                    if(index == 0):
                        s += domain + "=" + str(_sql_dic[domain]) + " "
                        index += 1
                    else:
                        s += "ADD" + domain + "=" + str(_sql_dic[domain]) + " "
                return s

        #为数组加上外括号，并拼接字符串
        def quote(self,_data_array,type_filter=True):
            s = "("
            index = 0
            if(type_filter):
                for domain in _data_array:
                    if(index == 0):
                        if(isinstance(domain,int)):
                            s += str(domain)
                        elif (isinstance(domain,str)):
                            s += "'" + domain + "'"
                        index += 1
                    else:
                        if(isinstance(domain,int)):
                            s += "," + str(domain)
                        elif(isinstance(domain,str)):
                            s += "," + "'" +domain +"'"
            else:
                for domain in _data_array:
                    if(index == 0):
                        s += str(domain)
                        index+=1
                    else:
                        s += "," + domain
            return s+")"