Of course! Here is a comprehensive guide to setting up and using MySQL with Python on a Mac. This guide covers everything from installation to writing your first script, with a focus on the modern best-practice approach.
Summary of the Best Method
For any new project, the recommended approach is:
- Install MySQL Server: The database itself.
- Install a MySQL Connector Library: The Python library that acts as a bridge between your Python code and the MySQL server. The best choice is
mysql-connector-pythonfrom Oracle. - Use an Object-Relational Mapper (ORM) like SQLAlchemy: For most applications, this is the recommended way to interact with the database. It provides a high-level, Pythonic way to query the database and abstracts away the raw SQL.
Step 1: Install the MySQL Server
You need a MySQL server running for your Python script to connect to. The easiest way to install and manage MySQL on a Mac is using Homebrew.
Install Homebrew (if you don't have it)
Open your Terminal (you can find it in /Applications/Utilities/ or search with Spotlight) and paste the following command:
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
Install MySQL Server using Homebrew
Once Homebrew is installed, run this command:
brew install mysql
Start and Secure the MySQL Installation
After installation, you need to start the MySQL service and run a security script.
-
Start the MySQL service:
brew services start mysql
This will start MySQL and ensure it runs automatically on startup.
-
Run the security script:
（图片来源网络，侵删）mysql_secure_installation
This script will guide you through:
- Setting a root password (highly recommended).
- Removing anonymous users.
- Disallowing remote root login.
- Removing test databases.
- Reloading the privileges. For a development machine, you can typically answer "Y" (yes) to all the prompts.
You now have a MySQL server running on your Mac!
Step 2: Install the Python Connector Library
This library is the official driver from Oracle that allows Python to communicate with MySQL.
Option A (Recommended): mysql-connector-python
This is the pure Python implementation. It's easy to install and works universally.
pip3 install mysql-connector-python
(Use pip if you have both Python 2 and 3 and pip3 is linked to your Python 3 installation).
Option B: mysqlclient
This is a more performant alternative as it's a wrapper around the native C library used by the MySQL client. It can be faster but might be slightly trickier to install due to C compiler dependencies.
pip3 install mysqlclient
If you run into issues, you might need to install Xcode Command Line Tools first: xcode-select --install.
Step 3: Connect to MySQL from Python
Let's write a simple Python script to connect to your MySQL server.
- Create a new file named
connect.py. - Add the following code, replacing
'your_password'with the root password you set duringmysql_secure_installation.
# connect.py
import mysql.connector
from mysql.connector import Error
def create_server_connection(host_name, user_name, user_password):
"""Establishes a connection to the MySQL server."""
connection = None
try:
connection = mysql.connector.connect(
host=host_name,
user=user_name,
passwd=user_password
)
print("MySQL connection successful")
except Error as e:
print(f"Error: '{e}'")
return connection
# --- Main execution ---
if __name__ == "__main__":
# Connection details for the server itself
server_connection = create_server_connection("localhost", "root", "your_password")
if server_connection and server_connection.is_connected():
# You can now execute server-level commands, like creating a database
cursor = server_connection.cursor()
try:
# Create a new database
cursor.execute("CREATE DATABASE IF NOT EXISTS python_db")
print("Database 'python_db' created successfully.")
except Error as e:
print(f"Error creating database: {e}")
finally:
cursor.close()
server_connection.close()
print("MySQL server connection closed.")
To run this script, open your terminal in the same directory and execute:
python3 connect.py
If you see "MySQL connection successful" and the database creation message, your setup is working!
Step 4: Perform Basic CRUD Operations
Now, let's connect to a specific database and perform Create, Read, Update, and Delete (CRUD) operations.
Here is a complete example. You can replace the connection details in the main block.
# crud_example.py
import mysql.connector
from mysql.connector import Error
def create_connection(host_name, user_name, user_password, db_name=None):
"""Establishes a connection to a specific MySQL database."""
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" + (f" to database '{db_name}'" if db_name else ""))
except Error as e:
print(f"Error: '{e}'")
return connection
def create_table(connection):
"""Creates a table in the database."""
cursor = connection.cursor()
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
join_date DATE
)
"""
try:
cursor.execute(create_table_query)
print("Table 'users' created successfully.")
except Error as e:
print(f"Error creating table: {e}")
def insert_user(connection, name, email, join_date):
"""Inserts a new user into the users table."""
cursor = connection.cursor()
insert_user_query = "INSERT INTO users (name, email, join_date) VALUES (%s, %s, %s)"
user_data = (name, email, join_date)
try:
cursor.execute(insert_user_query, user_data)
connection.commit() # Commit the transaction
print(f"User '{name}' inserted successfully.")
except Error as e:
print(f"Error inserting user: {e}")
finally:
cursor.close()
def fetch_users(connection):
"""Fetches and prints all users from the users table."""
cursor = connection.cursor()
try:
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
print("\n--- Current Users ---")
for row in results:
print(row)
print("----------------------\n")
except Error as e:
print(f"Error fetching users: {e}")
finally:
cursor.close()
def update_user_email(connection, user_id, new_email):
"""Updates a user's email."""
cursor = connection.cursor()
update_query = "UPDATE users SET email = %s WHERE id = %s"
try:
cursor.execute(update_query, (new_email, user_id))
connection.commit()
print(f"User ID {user_id}'s email updated to {new_email}.")
except Error as e:
print(f"Error updating user: {e}")
finally:
cursor.close()
def delete_user(connection, user_id):
"""Deletes a user from the table."""
cursor = connection.cursor()
delete_query = "DELETE FROM users WHERE id = %s"
try:
cursor.execute(delete_query, (user_id,))
connection.commit()
print(f"User ID {user_id} deleted successfully.")
except Error as e:
print(f"Error deleting user: {e}")
finally:
cursor.close()
# --- Main execution ---
if __name__ == "__main__":
# IMPORTANT: Replace with your actual credentials
HOST = "localhost"
USER = "root"
PASSWORD = "your_password"
DATABASE = "python_db"
# Connect to the specific database
db_connection = create_connection(HOST, USER, PASSWORD, DATABASE)
if db_connection and db_connection.is_connected():
# 1. Create table
create_table(db_connection)
# 2. Insert some data
insert_user(db_connection, "Alice", "alice@example.com", "2025-01-15")
insert_user(db_connection, "Bob", "bob@example.com", "2025-02-20")
insert_user(db_connection, "Charlie", "charlie@example.com", "2025-03-10")
# 3. Read (Fetch) all users
fetch_users(db_connection)
# 4. Update a user's email
update_user_email(db_connection, 2, "bob.smith@example.com")
# 5. Fetch users again to see the update
fetch_users(db_connection)
# 6. Delete a user
delete_user(db_connection, 3)
# 7. Fetch users one last time
fetch_users(db_connection)
# Close the connection
db_connection.close()
print("MySQL connection closed.")
Run this script with python3 crud_example.py. You will see the output of each operation in your terminal.
Step 5: The Modern Approach - Using an ORM (SQLAlchemy)
For any non-trivial application, writing raw SQL is cumbersome and error-prone. An ORM (Object-Relational Mapper) lets you interact with your database using Python objects and classes.
Install SQLAlchemy
pip3 install SQLAlchemy
A Simple SQLAlchemy Example
SQLAlchemy can work with many database backends. You just need to provide the correct "dialect" and "driver". For MySQL with mysql-connector-python, it's mysql+mysqlconnector.
# sqlalchemy_example.py
import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# --- 1. Setup ---
# Replace with your actual credentials
DATABASE_URL = "mysql+mysqlconnector://root:your_password@localhost/python_db"
# Create the engine
engine = create_engine(DATABASE_URL)
# Create a "Session" class bound to the engine
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# Create a base class for our models
Base = declarative_base()
# --- 2. Define the Model (Python class that maps to a table) ---
class User(Base):
__tablename__ = "users" # The name of the table in the database
id = Column(Integer, primary_key=True, index=True)
name = Column(String(255), nullable=False)
email = Column(String(255), unique=True, nullable=False)
join_date = Column(Date)
# --- 3. Create the table in the database ---
Base.metadata.create_all(bind=engine)
# --- 4. Use the session to interact with the database ---
def manage_users():
# Get a session
db = SessionLocal()
try:
# CREATE
new_user = User(name="David", email="david@example.com", join_date="2025-04-01")
db.add(new_user)
db.commit()
db.refresh(new_user)
print(f"Created new user with ID: {new_user.id}")
# READ
print("\n--- Reading all users ---")
users = db.query(User).all()
for user in users:
print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")
# UPDATE
user_to_update = db.query(User).filter(User.name == "Alice").first()
if user_to_update:
user_to_update.email = "alice.new@example.com"
db.commit()
print(f"\nUpdated Alice's email to: {user_to_update.email}")
# DELETE
user_to_delete = db.query(User).filter(User.name == "Bob").first()
if user_to_delete:
db.delete(user_to_delete)
db.commit()
print(f"Deleted user: {user_to_delete.name}")
finally:
# Always close the session
db.close()
if __name__ == "__main__":
manage_users()
Notice how much cleaner the Python code is. You're working with User objects, not writing SQL strings directly. SQLAlchemy handles translating your Python commands into the appropriate SQL.
Troubleshooting Common Issues
-
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306'- Cause: The MySQL server is not running.
- Solution: Start it with
brew services start mysql.
-
mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'localhost'- Cause: You are using the wrong password for the
rootuser. - Solution: Double-check your password in the Python script. If you've forgotten it, you may need to reset the MySQL root password.
- Cause: You are using the wrong password for the
-
ImportError: No module named 'mysql.connector'- Cause: The connector library is not installed or you're using the wrong Python interpreter.
- Solution: Make sure you installed it with
pip3and that you are running your script withpython3. You can check installed packages withpip3 list.
