用ChatGPT开发一个MySQL表信息查询系统

MySQL数据库联盟 2023-05-08

702

这一节内容，我们来尝试让ChatGPT帮我们开发一个MySQL的表状态查询系统。

首先问ChatGPT：

借助gradio实现一个页面，有三个下拉框，一个框是选择MySQL的IP，一个框选择MySQL的状态，一个框是填写库名，状态包括每张业务表的数据量、表大小、碎片率、主键自增值。状态获取均通过SQL语句实现。一个输出框，输出下拉框选择查看的内容结果。用一个Python文件实现

返回的代码如下（小编只修改了用户名、密码和host）：

import gradio as gr
import mysql.connector


# 连接MySQL数据库
def connect_to_mysql(ip, db_name):
    try:
        cnx = mysql.connector.connect(user='martin', password='uqatcdaT12',
                                      host=ip, database=db_name)
        return cnx
    except Exception as e:
        print("Unable to connect to MySQL database: ", str(e))
        return None


# 获取业务表状态信息
def get_table_status(ip, db_name, status):
    cnx = connect_to_mysql(ip, db_name)
    if not cnx:
        return "Failed to connect to MySQL database."


    cursor = cnx.cursor()
    query = ""
    if status == "Table data count":
        query = "SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s"
    elif status == "Table size":
        query = "SELECT TABLE_NAME, (DATA_LENGTH + INDEX_LENGTH) AS `Size` FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s"
    elif status == "Fragmentation rate":
        query = "SELECT table_name, CONCAT(ROUND((data_free/(data_length+index_length))*100), '%') AS `Fragmentation Rate` FROM information_schema.tables WHERE table_schema = %s"
    elif status == "Primary key auto increment value":
        query = "SELECT TABLE_NAME, AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s AND AUTO_INCREMENT IS NOT NULL"
    
    cursor.execute(query, (db_name,))
    result = cursor.fetchall()
    if not result:
        return "No tables found in the database."
    
    table_info = ""
    if status == "Table data count":
        for row in result:
            table_name = row[0]
            table_rows = row[1]
            table_info += f"{table_name}: Rows - {table_rows}\n"
    elif status == "Table size":
        for row in result:
            table_name = row[0]
            table_size = row[1]
            table_info += f"{table_name}: Size - {table_size} bytes\n"
    elif status == "Fragmentation rate":
        for row in result:
            table_name = row[0]
            frag_rate = row[1]
            table_info += f"{table_name}: Fragmentation rate - {frag_rate}\n"
    elif status == "Primary key auto increment value":
        for row in result:
            table_name = row[0]
            auto_inc = row[1]
            table_info += f"{table_name}: Primary key auto increment value - {auto_inc}\n"
    
    return table_info


# 定义页面输入项
ip_dropdown = gr.inputs.Dropdown(choices=["127.0.0.1", "your_mysql_ip_2", "your_mysql_ip_3"],
                                 label="Select MySQL IP address")
db_name_input = gr.inputs.Textbox(label="Enter database name")
status_dropdown = gr.inputs.Dropdown(choices=["Table data count", "Table size", "Fragmentation rate", "Primary key auto increment value"],
                                     label="Select the status to view")


# 定义页面输出项
output_text = gr.outputs.Textbox(label="MySQL status information")


# 定义应用程序
def mysql_status_app(ip, db_name, status):
    output = get_table_status(ip, db_name, status)
    return output


# 启动应用程序
app_title = "MySQL Status Viewer"
app_description = "View different status of your MySQL database tables."
examples = [["your_mysql_ip_1", "your_database_name", "Table data count"]]
gr.Interface(fn=mysql_status_app, inputs=[ip_dropdown, db_name_input, status_dropdown], 
             outputs=output_text, title=app_title, description=app_description,
             examples=examples).launch()