python:使用mysql-connector驱动器操作数据库

#!python3
# 使用 MySQL 官方提供的 mysql-connector 驱动器操作数据库
# 在我前期发表的【python读取超大excel文件入库（实测有效）】有使用方法

import mysql.connector


"""
为避免中文乱码
mysql请用如下配置：
    数据库字符集：utf8
    数据库校对规则：utf_general_ci
    建表时：ENGINE=INNODB DEFAULT CHARSET=utf8
"""

class mysqlHelper:
    host = ''
    port = 3306
    user = ''
    passwd = ''
    database = ''
    db = None

    # 定义构造方法
    def __init__(self, host, port, user, passwd, database):
        # print(host, port, user, passwd, database)
        self.host = host,
        self.port = port,
        self.user = user,
        self.passwd = passwd,
        self.database = database

        self.db = mysql.connector.connect(
            host=host,
            port=port,
            user=user,
            passwd=passwd,
            database=database
        )

    def execute(self, sql, data):
        """
        插入单笔数据、更新、删除
        :param sql:
        :param data: 元组
        :return:
        """
        try:
            myCursor = self.db.cursor()

            # sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
            # data = ("RUNOOB", "https://www.runoob.com")
            myCursor.execute(sql, data)

            self.db.commit()  # 数据表内容有更新，必须使用到该语句

            #  在数据记录插入后，获取该记录的主键 ID
            # rowid = myCursor.lastrowid

            # print("execute执行成功")
        except Exception as exc:
            self.db.rollback()
            # print("execute执行失败：" + exc)

    def insertDatas(self, sql, datas):
        """
        插入批量数据
        :param sql:
        :param datas: 包含元组的列表
        :return:
        """
        try:
            myCursor = self.db.cursor()

            # sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
            # datas = [
            #     ('Google', 'https://www.google.com'),
            #     ('Github', 'https://www.github.com'),
            # ]
            myCursor.executemany(sql, datas)  # 此处插入多笔数据与插入单笔数据方法有区别

            self.db.commit()  # 数据表内容有更新，必须使用到该语句

            # print("批量数据插入成功")
        except Exception as exc:
            self.db.rollback()
            # print("批量数据插入失败：" + exc)

    def queryData(self, sql, conditions):
        """
        查询数据
        :param sql:
        :param conditions: 元组
        :return:
        """
        try:
            myCursor = self.db.cursor()
            # sql = "SELECT * FROM sites where id >= %s and id <= %s"
            # conditions = (2, 5)
            myCursor.execute(sql, conditions)
            result = myCursor.fetchall()  # fetchall() 获取所有记录
            return result
        except Exception as exc:
            print("查询数据失败：" + exc)
            return None