python操作数据库

import pymysql
import pymssql
import json
import datetime

'''
1,连接Mysql数据库的相关操作


'''


# 定义jsonEncoder默认时间格式
class DateEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime("%Y-%m-%d %H:%M:%S")
        else:
            return json.JSONEncoder.default(self,obj)


class DBMain():
    def __init__(self,dbType, host, user, password, db, port=None):
        self.dbType = dbType.upper() # 定义数据库类型
        self.host = host     # 连接地址
        self.port = port     # 端口
        self.user = user
        self.password = password
        self.db = db
        self.con = self.connect()
        self.cur = self.cursor()


    def __del__(self):
            self.cur.close()
            self.con.close()

    def cursor(self):
        return self.connect().cursor()

    def connect(self):
        dbTypeDict = {
            'MYSQL': pymysql.connect,
            'SQLSERVER': pymssql.connect
        }
        try:
            con = dbTypeDict[self.dbType]
            connect = con(host='%s:%s' % (self.host, self.port), user=self.user, password=self.password, db=self.db, charset='utf8')
        except Exception as e:
            connect = e
        return connect

    # SQL 增删改查基本操作，并返回结果
    def edit(self, sql):
            result = self.cur.execute(sql)
            self.con.commit()
            return result

    # 查询方法，并将结果以json返回
    def selectForJson(self, sql):
        selectData = self.edit(sql)
        dataForJson = {}
        colNames = self.cur.description
        cols = len(colNames)
        rowData = self.cur.fetchone()
        while rowData:
            dict = {}
            for i in range(cols):
                k = str(colNames[i][0])
                value = rowData[i]
                dict[k] = value
            dataForJson[dict['id']] = dict
            rowData = self.cur.fetchone()
        # 将数据以json格式返回
        return json.dumps(dataForJson, cls=DateEncoder, sort_keys=True, indent=2, ensure_ascii=False)