无需本地运行MySQL服务器，能否通过Python脚本生成SQL或CSV导出文件？

Absolutely! You don’t need a local MySQL server at all to generate exportable SQL or CSV files from your Excel data. Here are two straightforward, memory-only approaches that fit your workflow perfectly:

Approach 1: Generate a Complete SQL Export File (Create Tables + Insert Data) #

This method builds all the necessary CREATE TABLE and INSERT INTO statements directly in memory, then writes them to a .sql file that you can easily import into any MySQL server later. We’ll use pandas (since you’re working in Jupyter, this is likely already part of your setup) to handle Excel loading and data processing.

Step 1: Load and Process Your Excel Data #

First, read your Excel files into pandas DataFrames and do any cleaning/transformations you need:

import pandas as pd

# Load your 5 Excel files (adjust filenames as needed)
df_customers = pd.read_excel('customers.xlsx')
df_orders = pd.read_excel('orders.xlsx')
df_products = pd.read_excel('products.xlsx')
df_order_items = pd.read_excel('order_items.xlsx')
df_categories = pd.read_excel('categories.xlsx')

# Add your data processing steps here (e.g., cleaning nulls, renaming columns)

Step 2: Write Helper Functions to Generate SQL Statements #

Create reusable functions to convert your DataFrames into valid MySQL syntax:

def generate_create_table(df, table_name, constraints=None):
    """Generate a MySQL CREATE TABLE statement from a DataFrame."""
    # Map pandas data types to MySQL equivalents
    dtype_mapping = {
        'int64': 'INT',
        'float64': 'DECIMAL(12,2)',
        'object': 'VARCHAR(255)',
        'datetime64[ns]': 'DATETIME',
        'bool': 'TINYINT(1)'
    }

    # Build column definitions
    columns = []
    for col, dtype in df.dtypes.items():
        mysql_type = dtype_mapping.get(str(dtype), 'VARCHAR(255)')  # Fallback to varchar if unknown
        columns.append(f"`{col}` {mysql_type}")

    # Add any constraints (e.g., primary keys, foreign keys) if provided
    if constraints:
        columns.append(constraints)

    # Assemble the full CREATE TABLE statement
    create_stmt = f"CREATE TABLE `{table_name}` (\n  {',\n  '.join(columns)}\n);\n\n"
    return create_stmt

def generate_insert_statements(df, table_name):
    """Generate MySQL INSERT INTO statements from a DataFrame."""
    # Escape single quotes in string values to avoid SQL errors
    df_escaped = df.apply(lambda x: x.str.replace("'", "''") if x.dtype == 'object' else x)

    # Get column names
    columns = ', '.join([f"`{col}`" for col in df.columns])

    # Build each INSERT statement
    insert_stmts = []
    for _, row in df_escaped.iterrows():
        values = []
        for val in row.values:
            if pd.isna(val):
                values.append('NULL')
            elif isinstance(val, pd.Timestamp):
                values.append(f"'{val.strftime('%Y-%m-%d %H:%M:%S')}'")
            elif isinstance(val, str):
                values.append(f"'{val}'")
            else:
                values.append(str(val))
        values_str = ', '.join(values)
        insert_stmts.append(f"INSERT INTO `{table_name}` ({columns}) VALUES ({values_str});")

    return '\n'.join(insert_stmts) + '\n\n'

Step 3: Generate and Write the SQL File #

Map your DataFrames to table names, then write all the SQL statements to a file:

# Define your table names and corresponding DataFrames
table_mapping = {
    'customers': df_customers,
    'orders': df_orders,
    'products': df_products,
    'order_items': df_order_items,
    'categories': df_categories
}

# Write everything to a single SQL export file
with open('database_export.sql', 'w', encoding='utf-8') as sql_file:
    for table_name, df in table_mapping.items():
        # Add CREATE TABLE statement (with constraints if needed)
        # Example: Add a primary key to the customers table
        if table_name == 'customers':
            create_stmt = generate_create_table(df, table_name, constraints="`customer_id` INT PRIMARY KEY AUTO_INCREMENT")
        else:
            create_stmt = generate_create_table(df, table_name)
        sql_file.write(create_stmt)

        # Add INSERT statements
        sql_file.write(generate_insert_statements(df, table_name))

Once this runs, you’ll have a database_export.sql file that can be imported into any MySQL server using commands like:

source /path/to/database_export.sql;

Or via tools like phpMyAdmin or MySQL Workbench.

Approach 2: Export Directly to CSV Files #

If you prefer (or need) to import data via CSV (which is often faster for large datasets), you can skip SQL generation entirely and export each DataFrame directly to a CSV file:

# Export each table to its own CSV file
for table_name, df in table_mapping.items():
    df.to_csv(f"{table_name}.csv", index=False, encoding='utf-8')

You can then import these CSVs into MySQL using LOAD DATA INFILE or the import wizard in your MySQL tool of choice.

Bonus: Adding Relational Constraints #

To maintain the relational database structure (e.g., foreign keys), just include them in the constraints parameter of generate_create_table. For example:

# Add a foreign key to the orders table linking to customers
create_orders_stmt = generate_create_table(
    df_orders, 
    'orders', 
    constraints="`order_id` INT PRIMARY KEY AUTO_INCREMENT, FOREIGN KEY (`customer_id`) REFERENCES `customers`(`customer_id`)"
)

Both approaches let you work entirely in memory without needing a local MySQL server. Just run the script in Jupyter, grab the generated files, and transfer them to your MySQL-enabled machine for import.

内容的提问来源于stack exchange，提问作者swift--help