A complete walkthrough for setting up a Python project with MySQL, covering environment setup, database connection, and full CRUD operations using best practices.
1. Install Python
Windows
- Download Python from the official website: Python Downloads.
- Run the installer and ensure you check the box that says "Add Python to PATH".
- Follow the installation prompts.
macOS
-
Open Terminal.
-
Install Python using Homebrew (if Homebrew is not installed, first install it from Homebrew):
brew install python
Linux
-
Open Terminal.
-
Install Python using your package manager. For example, on Ubuntu:
sudo apt update sudo apt install python3 python3-pip -
Verify the installation:
python --version
2. Install pip
pip is usually installed with Python, but if it’s not installed, you can install it manually.
Windows
python -m ensurepip --upgrademacOS/Linux
python3 -m ensurepip --upgradeVerify pip installation:
pip --version3. Create a Virtual Environment
Windows
-
Open Command Prompt or PowerShell.
-
Navigate to your project directory:
cd path\to\your\project -
Create a virtual environment:
python -m venv env
macOS/Linux
-
Open Terminal.
-
Navigate to your project directory:
cd /path/to/your/project -
Create a virtual environment:
python3 -m venv venv
4. Activate the Virtual Environment
Windows
.\venv\Scripts\activatemacOS/Linux
source venv/bin/activateWhen the virtual environment is activated, you should see (venv) preceding the command prompt.
5. Install Required Packages
-
Install the necessary packages including
mysql-connector-pythonandpython-dotenv:pip install mysql-connector-python python-dotenv
6. Set Up the MySQL Database
Create a MySQL Database and Table
-
Log in to your MySQL server:
mysql -u root -p -
Create a new database:
CREATE DATABASE mydatabase; -
Use the newly created database:
USE mydatabase; -
Create a table for CRUD operations:
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) );
7. Create a .env File
-
In the root of your project directory, create a file named
.env. -
Add your MySQL connection details to this file. For example:
DB_HOST=localhost DB_USER=root DB_PASSWORD=yourpassword DB_NAME=mydatabase
8. Load Environment Variables and Connect to MySQL
- In your Python script, use
python-dotenvto load environment variables from the.envfile and connect to the MySQL database.
Example config.py:
-
Create a file named
config.pyin your project directory and add the following code:import os from dotenv import load_dotenv import mysql.connector # Load environment variables from .env file load_dotenv() # Connect to MySQL database connection = mysql.connector.connect( host=os.getenv('DB_HOST'), user=os.getenv('DB_USER'), password=os.getenv('DB_PASSWORD'), database=os.getenv('DB_NAME') ) cursor = connection.cursor()
9. Perform CRUD Operations
-
Create (Insert Data)
def create_user(name, email): sql = "INSERT INTO users (name, email) VALUES (%s, %s)" values = (name, email) cursor.execute(sql, values) connection.commit() print(f"User {name} added successfully.") # Example usage create_user('John Doe', 'john@example.com') -
Read (Retrieve Data)
def get_users(): cursor.execute("SELECT \* FROM users") result = cursor.fetchall() for row in result: print(row) # Example usage get_users() -
Update (Modify Data)
def update_user(user_id, name, email): sql = "UPDATE users SET name = %s, email = %s WHERE id = %s" values = (name, email, user_id) cursor.execute(sql, values) connection.commit() print(f"User ID {user_id} updated successfully.") # Example usage update_user(1, 'Jane Doe', 'jane@example.com') -
Delete (Remove Data)
def delete_user(user_id): sql = "DELETE FROM users WHERE id = %s" values = (user_id,) cursor.execute(sql, values) connection.commit() print(f"User ID {user_id} deleted successfully.") # Example usage delete_user(1)
10. Run and Test the Python Program
-
Ensure your virtual environment is activated.
-
Run the Python program:
python config.py -
The program will perform the CRUD operations on the MySQL database.
11. Close the Database Connection
-
Always close the database connection when done:
cursor.close() connection.close()
You can save this content as README.md in your project directory for a comprehensive guide on setting up a Python project for CRUD operations with a MySQL database.



