FlaskProject/files.py

# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import Error

# 配置数据库连接
DB_CONFIG = {
    'host': '60.204.139.57',
    'user': 'root',
    'password': '1',
    'port': 6003,
    'database': 'files'  # 替换为数据库名称
}

# 初始化数据库
def initialize_files_database():
    try:
        # 连接 MySQL 服务器（不指定数据库）
        connection = mysql.connector.connect(
            host=DB_CONFIG['host'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            port=DB_CONFIG['port']
        )

        if connection.is_connected():
            print("成功连接到 MySQL 服务器")
            cursor = connection.cursor()

            # 检查数据库是否存在
            cursor.execute(f"SHOW DATABASES LIKE '{DB_CONFIG['database']}';")
            result = cursor.fetchone()

            if result:
                print(f"数据库 {DB_CONFIG['database']} 已存在。")
            else:
                print(f"数据库 {DB_CONFIG['database']} 不存在，正在创建...")
                cursor.execute(f"CREATE DATABASE {DB_CONFIG['database']};")
                print(f"数据库 {DB_CONFIG['database']} 创建成功。")

    except Error as err:
        print(f"连接 MySQL 时发生错误：{err}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL 服务器连接已关闭")

# 初始化表
def initialize_files_table(table_name=None):
    if not table_name:
        table_name = 'filePath'

    # 验证表名是否合法（这里的规则可以根据你的需求做调整）
    allowed_tables = ['fruit', 'filePath']  # 你可以在这里列出允许的表名
    if table_name not in allowed_tables:
        raise ValueError(f"不允许使用此表名：{table_name}")

    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            cursor = connection.cursor()

            # 动态创建表
            create_table_query = f'''
                CREATE TABLE IF NOT EXISTS `{table_name}` (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(100)
                )
            '''

            cursor.execute(create_table_query)
            print(f"--- 创建 {table_name} 表 ---")
    except Error as err:
        print(f"初始化表时发生错误：{err}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL 连接已关闭")

# 插入数据
def insert_url(name):
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            cursor = connection.cursor()

            # 插入数据
            cursor.execute("INSERT INTO filePath (name) VALUES (%s)", (name,))
            connection.commit()
            print(f"插入数据成功：({name})")
    except Error as err:
        print(f"插入数据时发生错误：{err}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL 连接已关闭")

# 插入数据
def insert_data(name, origin):
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            cursor = connection.cursor()

            # 插入数据
            cursor.execute("INSERT INTO filePath (name, origin) VALUES (%s, %s)", (name, origin))
            connection.commit()
            print(f"插入数据成功：({name}, {origin})")
    except Error as err:
        print(f"插入数据时发生错误：{err}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL 连接已关闭")

def delete_data(name):
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            cursor = connection.cursor()

            # 删除数据
            cursor.execute("DELETE FROM filePath WHERE name = %s", (name,))
            connection.commit()
            print(f"成功删除名称为 {name} 的记录")
    except Error as err:
        print(f"删除数据时发生错误：{err}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL 连接已关闭")


# 查询数据
def fetch_data():
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            cursor = connection.cursor()

            # 查询数据
            cursor.execute("SELECT * FROM filePath")
            rows = cursor.fetchall()
            print("--- 查询结果 ---")
            for row in rows:
                print(row)
            return rows
    except Error as err:
        print(f"查询数据时发生错误：{err}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL 连接已关闭")

# 主函数
if __name__ == "__main__":
    # 初始化数据库和表
    initialize_files_database()
    initialize_files_table()

    # 插入示例数据
    insert_data('Apple', 'China')
    insert_data('Banana', 'Ecuador')

    # 查询数据
    fetch_data()