封裝了讀取 MySQL 數(shù)據(jù)庫中數(shù)據(jù)的方法

class DbHelper(object):
    """
    MySQ 數(shù)據(jù)庫幫助類
    """

    # 使用方法
    # 1. 實例化對象
    # 2. 查詢，得到結(jié)果
    # 3. 關(guān)閉對象
    """
    db_helper = MysqlDbHelper("localhost", 3306, 'root', '', 'tpshop2.0.5', "utf8")
    for i in range(10000):

        result = db_helper.execute("select * from tp_goods order by 1 desc limit 1000;")
        print("第%d次，結(jié)果是%r" % (i, result))

    db_helper.close()
    """

    connect = None

    def __init__(self, host, port, user, password, database, charset='utf8'):
        """
        構(gòu)造方法
        :param host: 數(shù)據(jù)庫的主機地址
        :param port: 數(shù)據(jù)庫的端口號
        :param user: 用戶名
        :param password: 密碼
        :param database: 選擇的數(shù)據(jù)庫
        :param charset: 字符集
        """
        self.connect = pymysql.connect(host=host, port=port,
                                       user=user, password=password,
                                       db=database, charset=charset)

    def read_sql(self, file, encoding="utf8"):
        """
        從 文件中讀取 SQL 腳本
        :param file: 文件名 + 文件路徑
        :return:
        """
        sql_file = open(file, "r", encoding=encoding)
        sql = sql_file.read()
        sql_file.close()
        return sql

    def execute(self, sql):
        """
        執(zhí)行 SQL 腳本查詢并返回結(jié)果
        :param sql: 需要查詢的 SQL 語句
        :return: 字典類型
            data 是數(shù)據(jù)，本身也是個字典類型
            count 是行數(shù)
        """
        cursor = self.connect.cursor()

        row_count = cursor.execute(sql)
        rows_data = cursor.fetchall()
        result = {
            "count": row_count,
            "data": rows_data
        }

        cursor.close()
        return result

    def close(self):
        """
        關(guān)閉數(shù)據(jù)庫連接
        :return:
        """
        self.connect.close()