如何在VS Code中连接Python程序与MySQL Workbench 8.0并上传数据

1. Install the Official MySQL Connector Package #

First, install the official Python connector for MySQL (this is the most reliable option for MySQL 8.0):

pip install mysql-connector-python

2. Connect Function Parameters Breakdown #

The connect() function requires these core parameters (adjust values to match your MySQL setup):

• host: Usually 'localhost' if MySQL is running on your local machine; use the server IP if connecting remotely.
• user: Your MySQL username (e.g., 'root' or a custom user you created).
• password: The password for your MySQL user.
• database: The exact name of the database you’ve created in MySQL Workbench.
• port (optional): Defaults to 3306—only specify if you changed the MySQL port.

3. Full Workflow Code Example #

Here’s a complete, error-handled script to connect and insert data using variables:

import mysql.connector
from mysql.connector import Error

# Define your variables to insert
user_name = "John Doe"
user_email = "john@example.com"
user_age = 30

try:
    # Establish connection to MySQL
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_mysql_password",
        database="your_database_name"
    )

    if conn.is_connected():
        # Create a cursor object to execute queries
        cursor = conn.cursor()

        # Use parameterized query (prevents SQL injection)
        insert_query = """INSERT INTO your_table_name (name, email, age) 
                          VALUES (%s, %s, %s)"""
        # Pass variables as a tuple to execute()
        cursor.execute(insert_query, (user_name, user_email, user_age))

        # Commit the transaction (critical—MySQL doesn't auto-commit by default)
        conn.commit()
        print(f"Record inserted successfully: {cursor.rowcount} row affected")

except Error as e:
    print(f"Error: {e}")
finally:
    # Clean up resources
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL connection closed")

4. Critical Missing Details You Might Overlook #

• Parameterized queries: Never use string formatting (like f-strings) to insert variables—this exposes you to SQL injection attacks. Always use %s placeholders (or %() for named parameters) and pass variables as a tuple to execute().
• Commit transactions: MySQL disables auto-commit by default. If you skip conn.commit(), your inserted data won’t be saved to the database.
• Error handling: Wrap your code in try-except blocks to catch connection failures, invalid queries, or permission issues.
• Server status: Ensure your MySQL server is running (check MySQL Workbench’s connection status—if it’s disconnected, start the server first).
• User permissions: Verify your MySQL user has INSERT privileges for the target table. You can grant this in Workbench with:

  GRANT INSERT ON your_database_name.your_table_name TO 'your_user'@'localhost';
  

• MySQL 8.0 authentication: If you get an error about caching_sha2_password, either update your connector to the latest version (it supports this auth method) or change the user’s auth type to mysql_native_password in Workbench.

内容的提问来源于stack exchange，提问作者Muhammad