python3数据库工具类

安装pymysql类库：
    pip3 install PyMySQL

import pymysql
class MyDb:
    cursor = ''   #句柄
    db = ''       #打开数据库连接
    '''
        定义构造方法
        host：主机名
        username;用户名
        password:密码
        dbname:数据库名
        db:打开数据库连接
        cursor:获取游标句柄
    '''
    def __init__(self,host,username,password,dbname):

        self.host = host
        self.username = username
        self.password = password
        self.dbname = dbname

        self.db = pymysql.connect(self.host,self.username,self.password,self.dbname)
        self.cursor = self.db.cursor()

    #获取所有的结果集
    def getAllResult(self,sql):
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results

    #获取所有的结果集
    def getSignleResult(self,sql):
        self.cursor.execute(sql)
        results = self.cursor.fetchone()
        return results

    #插入或更新数据
    def insertOrUdateInfo(self,sql):
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            # 提交到数据库执行
            self.db.commit()
        except:
            # 发生错误时回滚
            self.db.rollback()
        #返回受影响的行数
        return self.cursor.rowcount

    #关闭链接
    def close(self):
        self.db.close()

test = MyDb('localhost','root','123456','mobile')

# sql = 'select *from md_current'
# res = test.getAllResult(sql)
# for item in res:
#     print (item[0],item[1],item[2],item[3],item[4],item[5],item[6])
# sql = 'select *from md_current where id = 1'
# res = test.getSignleResult(sql)
# print (res[0],res[1],res[2],res[3],res[4],res[5],res[6])
# sql = "update md_areas set area ='东城区' where id = 1"
# res  = test.updateData(sql)
# print(res)
# sql = "INSERT INTO `mobile`.`md_areas` (`areaid`, `area`, `cityid`) VALUES ('20001', '天津市', '200102')"
# res  = test.updateData(sql)
# print(res)

 第二种方式：

import pymysql
import read_config
import tkinter.messagebox
class MyDb:
    cursor = ''  # 句柄
    db = ''      # 打开数据库连接
    '''
        定义构造方法
        host：主机名
        username;用户名
        password:密码
        dbname:数据库名
        db:打开数据库连接
        cursor:获取游标句柄
        selectdb  1:gkantob
                  2:gabd3
    '''

    def __init__(self, selectdb):
        if (selectdb == 1):
            self.host = read_config.gk_ip
            self.username = read_config.gk_user
            self.password = read_config.gk_password
            self.dbname = read_config.gk_name
        else:
            self.host = read_config.ip
            self.username = read_config.user
            self.password = read_config.passwd
            self.dbname = read_config.mydb
        self.selectdb = selectdb

        try:
            self.db = pymysql.connect(self.host, self.username, self.password, self.dbname)
        except Exception as e:
            # 上传完毕弹出完成框
            tkinter.messagebox.showerror("FishC Demo", "连接数据库失败")
        self.cursor = self.db.cursor()



    '''
    拼接数据库查询条件语句
    '''
    def getWhere(self,where):
        mystr = ''
        keys = {'eq':'=','neq':'!=','in':'in','gt':'>','lt':'<','limit':'limit','like':'like'}
        if where:
            for item in where:
                for key in where[item]:
                    if (key in keys):
                        if (item == 'limit'):
                            listWhere = list(where[item][key])
                            mystr = mystr[:-4] + " " + keys[key] + " " + str(listWhere[0]) + "," + \
                                    str(listWhere[1]) + ' and '
                        else:
                            if (isinstance(where[item][key], str)):
                                mystr = mystr + item + " " + keys[key] + " \'" + str(where[item][key]) + '\' and '
                            else:
                                mystr = mystr + item + " " + keys[key] + " " + str(where[item][key]) + ' and '

                    res = mystr[:-4]
        else:
            res = ''
        return res


    '''
    根据不同的筛选条件获取不同的数据

    '''
    def getScodeInfo(self, where={},field="*"):
        if(self.selectdb == 2):
            if where:
                sql = "select "+field+" from tablenames where "+self.getWhere(where)
            else:
                sql = "select " + field + " from tablenames"
        else:
            if where:
                sql = "select " + field + " from tablenames where " + self.getWhere(where)
            else:
                sql = "select " + field + " from tablenames"
        self.cursor.execute(str(sql))
        if(sql.rstrip()[-1:] == '1' and 'limit' in sql):
            results = self.cursor.fetchone()
        else:
            results = self.cursor.fetchall()

        return results


    # 插入或更新数据
    def updateScodeInfo(self, where,data):

        if(self.selectdb == 2):
            sql = "update tablenames set "+self.getWhere(data)+" where "+self.getWhere(where)
        else:
            sql = "update tablenames set " + self.getWhere(data) + " where " + self.getWhere(where)
        try:
            # 执行SQL语句
            self.cursor.execute(str(sql))
            # 提交到数据库执行
            self.db.commit()
        except:
            # 发生错误时回滚
            self.db.rollback()
        # 返回受影响的行数
        return self.cursor.rowcount


    '''
        获取查询信息
        selectdb :2  === gabd3
                  1  === gkangtob
                  
        table_num:1   tablenames1
                  2   tablenames2 
                  3   tablenames3 
    '''
    def getSelectInfo(self,where,field,table_num):
        if table_num == '1' and self.selectdb == 2:
            tablename = 'hd_user_locinfo'
        elif table_num == '2' and self.selectdb == 1:
            tablename = 'gkb_partner'
        elif table_num == '3' and self.selectdb == 1:
            tablename = 'gkb_product_library'

        sql = "select " + field + " from " + tablename + " where " + self.getWhere(where)
        if(table_num == '3'):
            print(sql)
            print(sql.rstrip()[-1:])
        self.cursor.execute(str(sql))
        if(sql.rstrip()[-1:] == '1' and 'limit' in sql):
            results = self.cursor.fetchone()
        else:
            results = self.cursor.fetchall()
        return results



    # 关闭链接
    def close(self):
        self.db.close()