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

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+")"