mysql导表自定义模板

import mysql.connector
from mysql.connector import Error

def export_table(host, database, user, password, table_name, template):
    try:
        connection = mysql.connector.connect(
            host=host,
            database=database,
            user=user,
            password=password
        )

        cursor = connection.cursor()
        cursor.execute(f"SELECT * FROM {table_name}")

        with open(f"{table_name}.sql", "w", encoding="utf8mb4") as file:
            file.write(template.format(table_name=table_name))
            for row in cursor:
                file.write(f"INSERT INTO {table_name} VALUES ({','.join(map(lambda x: f'"{x}"', row))});\n")

        print(f"Table {table_name} exported successfully.")

    except Error as e:
        print(f"Error: {e}")

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# 自定义模板
template = """
-- Table structure for {table_name}
CREATE TABLE IF NOT EXISTS `{table_name}` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data for table {table_name}
"""

# 导出表
export_table("localhost", "testdb", "root", "password", "users", template)