data-service/dbOperation.py

import pymysql
import db_config
import re

class dbOperation:

     #建立数据库连接，新建一个查询页面
    def __init__(self, dbName):
        # port要转为int
        # print("连接数据库")
        self.mysql = pymysql.connect(host = db_config.host # 连接名称，默认127.0.0.1 
                                    ,port = db_config.port # 端口，默认为3306
                                    ,user = db_config.user # 用户名
                                    ,passwd = db_config.passwd # 密码
                                    ,db = dbName # 数据库名称
                                    ,charset = db_config.charset # 字符编码
                                    )
        self.cursor = self.mysql.cursor(pymysql.cursors.DictCursor)   # fetchone 是字典 fetchall是list套dict

    def table_exists(self,table_name):        #这个函数用来判断表是否存在
        sql = "show tables;"
        self.cursor.execute(sql)
        tables = [self.cursor.fetchall()]
        table_list = re.findall('(\'.*?\')',str(tables))
        table_list = [re.sub("'",'',each) for each in table_list]
        if table_name in table_list:
            return 1        #存在返回1
        else:
            return 0        #不存在返回0

    def created_table(self, sql, tableName):
        self.cursor.execute("show tables;")
        tables = [self.cursor.fetchall()]
        tableList = re.findall('(\'.*?\')',str(tables))
        tableList = [re.sub("'",'',each) for each in tableList]
        if tableName in tableList:
            print("该表已经存在，无需创建")
        else:
            # self.cursor.execute("DROP " + tableName + " IF EXISTS EMPLOYEE") #创建表前 先删除表
            self.cursor.execute(sql)
            print("该表创建成功")

    #获取查询结果,结果是dict,上面建立游标指定了是dict
    def query_one(self, sql):
        self.cursor.execute(sql)
        data = self.cursor.fetchone()
        return data

    #获取查询结果,结果是dict,上面建立游标指定了是dict
    def query_list(self, sql):
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    def insert(self, sql):
        try:
            # 执行sql语句
            self.cursor.execute(sql)
            # 提交到数据库执行
            self.mysql.commit()
        except Exception as e:
            # Rollback in case there is any error
            # print("失败" + str(Exception))
            print ('str(e):\t\t', str(e))   #输出 str(e):		integer division or modulo by zero
            self.mysql.rollback()

    def batchInsert(self, tableName, key, vlues):
        try:
            sql = "insert into `" + tableName + "`(" + key + ") values " + vlues +";"
            print(sql)
            # text = input("press any key to cancel csq \r\n")
            # 执行sql语句
            self.cursor.execute(sql)
            # 提交到数据库执行
            self.mysql.commit()
        except Exception as e:
            # Rollback in case there is any error
            # print("失败" + str(Exception))
            print ('str(e):\t\t', str(e))   #输出 str(e):		integer division or modulo by zero
            self.mysql.rollback()

    def dele(self, sql):
        try:
            self.cursor.execute(sql) # 执行插入的sql语句
            self.mysql.commit() # 提交到数据库执行
        except:
            self.mysql.rollback()# 如果发生错误则回滚

    def update(self, sql):
        try:
            self.cursor.execute(sql) # 执行插入的sql语句
            self.mysql.commit() # 提交到数据库执行
        except:
            self.mysql.rollback()# 如果发生错误则回滚

    #关闭查询页面，关闭连接
    def close(self):
        self.cursor.close()
        self.mysql.close()