腾讯云TDSQL-C Serverless 产品体验

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh |bash 



yum install -y sysbench

sysbench --version

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=gz-cynosdbmysql-grp-d27hp6vl.sql.tencentcdb.com --mysql-port=27529 --mysql-user=root --mysql-password=password --mysql-db=experience-15 --tables=20 --table\_size=1000000 oltp\_read\_write --db-ps-mode=disable prepare

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1  --mysql-host=gz-cynosdbmysql-grp-d27hp6vl.sql.tencentcdb.com --mysql-port=27529 --mysql-user=root --mysql-password=password --mysql-db=experience-15 --tables=20 --table\_size=1000000 oltp\_read\_write --db-ps-mode=disable run

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 -mysql-host=gz-cynosdbmysql-grp-d27hp6vl.sql.tencentcdb.com --mysql-port=27529 --mysql-user=root --mysql-password=password --mysql-db=experience-15  --tables=20 --table\_size=1000000 oltp\_read\_only --db-ps-mode=disable run

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 -mysql-host=gz-cynosdbmysql-grp-d27hp6vl.sql.tencentcdb.com --mysql-port=27529 --mysql-user=root --mysql-password=password --mysql-db=experience-15 --tables=20 --table\_size=1000000 oltp\_insert --db-ps-mode=disable run

import pymysql

import pandas as pd

import os

import wordcloud

import numpy as np

from PIL import Image

import matplotlib.pyplot as plt



# MySQL数据库连接配置

db\_config = {

    'host': "gz-cynosdbmysql-grp-d27hp6vl.sql.tencentcdb.com",  # 主机名

    'port': 27529,  # 端口

    'user': "root",  # 账户

    'password': "pass",  # 密码

    'database': 'experience-16',



}





def create\_table(table\_name, columns):

    # 建立MySQL数据库连接

    conn = pymysql.connect(\*\*db\_config)

    cursor = conn.cursor()

    # 组装创建表的 SQL 查询语句

    query = f"CREATE TABLE IF NOT EXISTS {table\_name} ("

    for col\_name, col\_type in columns.items():

        query += f"{col\_name} {col\_type}, "

    query = query.rstrip(", ")  # 去除最后一个逗号和空格

    query += ")"



    # 执行创建表的操作

    cursor.execute(query)



    # 提交事务并关闭连接

    conn.commit()

    cursor.close()

    conn.close()





def excelTomysql():

    path = '词频'  # 文件所在文件夹

    files = [path + "/" + i for i in os.listdir(path)]  # 获取文件夹下的文件名,并拼接完整路径

    for file\_path in files:

        print(file\_path)

        filename = os.path.basename(file\_path)

        table\_name = os.path.splitext(filename)[0]  # 使用文件名作为表名，去除文件扩展名

        # 使用pandas库读取Excel文件

        data = pd.read\_excel(file\_path, engine="openpyxl", header=0)  # 假设第一行是列名

        columns = {col: "VARCHAR(255)" for col in data.columns}  # 动态生成列名和数据类型



        create\_table(table\_name, columns)  # 创建表

        save\_to\_mysql(data, table\_name)  # 将数据保存到MySQL数据库中，并使用文件名作为表名

        print(filename + ' uploaded and saved to MySQL successfully')





def save\_to\_mysql(data, table\_name):

    # 建立MySQL数据库连接

    conn = pymysql.connect(\*\*db\_config)

    cursor = conn.cursor()

    # 将数据写入MySQL表中（假设数据只有一个Sheet）

    for index, row in data.iterrows():

        query = f"INSERT INTO {table\_name} ("

        for col\_name in data.columns:

            query += f"{col\_name}, "

        query = query.rstrip(", ")  # 去除最后一个逗号和空格

        query += ") VALUES ("

        values = tuple(row)

        query += ("%s, " \* len(values)).rstrip(", ")  # 动态生成值的占位符

        query += ")"

        cursor.execute(query, values)



    # 提交事务并关闭连接

    conn.commit()

    cursor.close()

    conn.close()





def query\_data():

    # 建立MySQL数据库连接

    conn = pymysql.connect(\*\*db\_config)

    cursor = conn.cursor()

    # 查询所有表名

    cursor.execute("SHOW TABLES")

    tables = cursor.fetchall()



    data = []

    dic\_list = []

    table\_name\_list = []

    for table in tables:

        # for table in [tables[-1]]:

        table\_name = table[0]

        table\_name\_list.append(table\_name)

        query = f"SELECT \* FROM {table\_name}"

        # # 执行查询并获取结果

        cursor.execute(query)

        result = cursor.fetchall()

        if len(result) > 0:

            columns = [desc[0] for desc in cursor.description]

            table\_data = [{columns[i]: row[i] for i in range(len(columns))} for row in result]

            data.extend(table\_data)

        dic = {}

        for i in data:

            dic[i['word']] = float(i['count'])

        dic\_list.append(dic)



    conn.commit()

    cursor.close()

    conn.close()

    return dic\_list, table\_name\_list





if \_\_name\_\_ == '\_\_main\_\_':

    ##excelTomysql()方法将excel写入到mysql

    excelTomysql()

    print("excel写入到mysql成功！")

    # query\_data()方法将mysql中的数据查询出来，每张表是一个dic，然后绘制词云

    result\_list, table\_name\_list = query\_data()

    print("从mysql获取数据成功！")

    for i in range(len(result\_list)):

        maskImage = np.array(Image.open('background.PNG'))  # 定义词频背景图

        # 定义词云样式

        wc = wordcloud.WordCloud(

            font\_path='PingFangBold.ttf', # 设置字体

            mask=maskImage,  # 设置背景图

            max\_words=500,  # 最多显示词数

            max\_font\_size=100)  # 字号最大值

        # 生成词云图

        wc.generate\_from\_frequencies(result\_list[i])  # 从字典生成词云

        # 保存图片到指定文件夹

        wc.to\_file("词云图/{}.png".format(table\_name\_list[i]))

        print("生成的词云图【{}】已经保存成功！".format(table\_name\_list[i] + '.png'))

        # 在notebook中显示词云图

        plt.imshow(wc)  # 显示词云

        plt.axis('off')  # 关闭坐标轴

        plt.show()  # 显示图像