Of course! Using PyCharm with MySQL and Python is a very common and powerful combination. This guide will walk you through the entire process, from setup to executing your first query, covering both the PyCharm Professional (easier) and Community (more manual) editions.

Overview of the Workflow

1. Install MySQL Server: Have a running MySQL server on your machine or a remote server.
2. Install a MySQL Driver: Python needs a library to communicate with MySQL. We'll use mysql-connector-python.
3. Configure PyCharm: Set up a Data Source in PyCharm to connect to your MySQL database.
4. Write and Execute Python Code: Use the driver in your Python script to query the database.

Prerequisites

1. PyCharm: Installed (either Professional or Community edition).
2. MySQL Server: Installed and running on your local machine or accessible remotely.
   • How to check if it's running? Open your terminal or command prompt and type mysql -u root -p. If it prompts for a password and connects, you're good to go.
   • If you don't have it: Download and install the official MySQL Community Server. During installation, make sure to note the root password you set.

Part 1: Setting Up the MySQL Driver

This step is the same for both PyCharm editions. You need to install the Python library that acts as a bridge between your Python code and the MySQL database.

Recommended Driver: mysql-connector-python (officially supported by Oracle).

Method 1: Using PyCharm's Terminal (Recommended)

1. Open PyCharm.
2. Go to View -> Tool Windows -> Terminal.
3. In the terminal that opens, use pip to install the driver:

   pip install mysql-connector-python

   If you have multiple Python versions in PyCharm, make sure the terminal is using the correct one (check the path in the terminal prompt).

   
   （图片来源网络，侵删）

Method 2: Using PyCharm's UI

1. Go to File -> Settings (on Windows/Linux) or PyCharm -> Preferences (on macOS).
2. Navigate to Project: your_project_name -> Python Interpreter.
3. Click the (Install) button.
4. In the search box, type mysql-connector-python.
5. Select it from the list and click Install Package.

Part 2: Connecting PyCharm to MySQL (The Easy Way - PyCharm Professional)

This is the biggest advantage of the Professional edition. It provides a visual database interface.

1. Open the Database Tool Window:

   • Go to View -> Tool Windows -> Database.

2. Add a New Data Source:

   
   （图片来源网络，侵删）
   • Click the icon in the Database tool window.
   • Select MySQL from the list.

3. Configure the Connection:

   • Name: Give your connection a memorable name (e.g., local_mysql).
   • Host: localhost (if it's on your machine) or your server's IP address.
   • Port: 3306 (this is the default MySQL port).
   • User: root (or another user you've created).
   • Password: Enter the password for the user.
   • Default Schema: This is optional. You can select a database here to automatically connect to it, or leave it blank and choose later.

4. Test and Apply:

   • Click the Test Connection button. If everything is correct, you'll see a green "OK" message.
   • Click OK to save the data source.

PyCharm will now download the necessary JDBC drivers and connect. You can now browse your databases, tables, data, and even run SQL queries directly in the PyCharm UI! This is fantastic for exploration and testing.

Part 3: Connecting to MySQL in Python Code (Both Editions)

Now, let's write the Python code to interact with the database.

Step 1: Create a Sample Database and Table

You can do this directly in PyCharm's built-in SQL console (Professional) or using any MySQL client.

-- Run this in your MySQL client or PyCharm's SQL console
CREATE DATABASE IF NOT EXISTS my_company;
USE my_company;
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 90000.00),
('Bob', 'Marketing', 65000.00),
('Charlie', 'Engineering', 110000.00);

Step 2: Write the Python Script

Create a new Python file (e.g., db_test.py) in your project and add the following code.

Important: Replace the placeholder values with your actual MySQL host, user, password, and database name.

import mysql.connector
from mysql.connector import Error
def create_connection(host_name, user_name, user_password, db_name=None):
    """ Create a database connection to the MySQL server """
    connection = None
    try:
        if db_name:
            connection = mysql.connector.connect(
                host=host_name,
                user=user_name,
                passwd=user_password,
                database=db_name
            )
        else:
            connection = mysql.connector.connect(
                host=host_name,
                user=user_name,
                passwd=user_password
            )
        print("Connection to MySQL successful" if db_name else "Connection to MySQL server successful")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection
def execute_query(connection, query, data=None):
    """ Execute a single query """
    cursor = connection.cursor(dictionary=True) # dictionary=True gives rows as dicts
    try:
        if data:
            cursor.execute(query, data)
        else:
            cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
        return cursor
    except Error as e:
        print(f"The error '{e}' occurred")
def execute_read_query(connection, query):
    """ Execute a read query and fetch the results """
    cursor = connection.cursor(dictionary=True)
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")
# --- Main execution ---
if __name__ == "__main__":
    # --- CONFIGURATION ---
    # Replace with your MySQL details
    HOST = "localhost"
    USER = "root"
    PASSWORD = "your_mysql_password" # <--- CHANGE THIS
    DATABASE = "my_company"
    # 1. Create a connection to the specific database
    connection = create_connection(HOST, USER, PASSWORD, DATABASE)
    if connection and connection.is_connected():
        # 2. Define and execute a SELECT query
        select_employees = "SELECT * FROM employees"
        employees = execute_read_query(connection, select_employees)
        print("\n--- All Employees ---")
        for employee in employees:
            print(employee)
        # 3. Define and execute an INSERT query
        insert_query = "INSERT INTO employees (name, department, salary) VALUES (%s, %s, %s)"
        new_employee_data = ("David", "Sales", 70000.00)
        execute_query(connection, insert_query, new_employee_data)
        # 4. Verify the insert by reading the data again
        print("\n--- Employees After Insert ---")
        updated_employees = execute_read_query(connection, select_employees)
        for employee in updated_employees:
            print(employee)
        # 5. Close the connection
        connection.close()
        print("\nMySQL connection is closed.")

To run this code:

1. Make sure you've replaced "your_mysql_password" with your actual password.
2. Right-click anywhere in the db_test.py file in PyCharm.
3. Select Run 'db_test'.

You should see the output printed in the Run tool window at the bottom of PyCharm, showing the employee data before and after the insertion.

Troubleshooting Common Issues

• mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306'

  • Cause: Your MySQL server is not running.
  • Solution: Start your MySQL server. You might need to do this through your system's services manager, XAMPP/MAMP control panel, or a command like sudo systemctl start mysql (on Linux).

• mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

  • Cause: The username or password is incorrect.
  • Solution: Double-check your username and password. If you've forgotten the root password, you'll need to reset it.

• ModuleNotFoundError: No module named 'mysql'

  • Cause: You haven't installed the mysql-connector-python driver, or PyCharm is using a different Python interpreter than the one you installed it into.
  • Solution: Go back to Part 1 and ensure the driver is installed for the correct Python interpreter in your project.

PyCharm Community Edition Workflow

If you're using the Community Edition, you don't have the visual Database tool. The workflow is slightly different:

1. Install the driver as described in Part 1.
2. Write the Python code exactly as in Part 3. This is your primary way to interact with the database.
3. Use an external tool for database exploration, like:
   • DBeaver (Free, cross-platform, excellent for managing any database).
   • MySQL Workbench (Official, powerful but can be slower).
   • TablePlus (Modern, fast, has a free version).

You would use one of these external tools to create your database, run complex SQL queries, and manage your schema, while using PyCharm to write the Python application logic.