Skip to content

Model Context Protocol

Step-by-Step Guide - Setting Up a Python Project for CRUD Operations with MySQL

A complete walkthrough for setting up a Python project with MySQL, covering environment setup, database connection, and full CRUD operations using best…

By Deepak Kamboj 4 min read

Step-by-Step Guide - Setting Up a Python Project for CRUD Operations with MySQL
On this page

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 --upgrade

macOS/Linux

  python3 -m ensurepip --upgrade

Verify pip installation:

  pip --version

3. 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\activate

macOS/Linux

  source venv/bin/activate

When the virtual environment is activated, you should see (venv) preceding the command prompt.

5. Install Required Packages

  • Install the necessary packages including mysql-connector-python and python-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-dotenv to load environment variables from the .env file and connect to the MySQL database.

Example config.py:

  • Create a file named config.py in 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.

Get new articles by newsletter

Follow the newsletter on LinkedIn to get new posts on AI agents, MCP and test automation.

Subscribe on LinkedIn (opens in a new tab)

Model Context Protocol

Comparison of pip and npm Commands

This document compares the commands used by pip (Python package manager) and npm (Node.js package manager) to perform similar tasks.

5 min read
  • Model Context Protocol
  • Distributed Systems
  • Scalability

Model Context Protocol

Introduction to Tailwind CSS

Key Features: - Utility-First: Build custom designs without writing CSS. - Responsive Design: Mobile-first approach with responsive utilities. -…

4 min read
  • Model Context Protocol
  • Distributed Systems
  • Scalability

Model Context Protocol

Implementing Redux in React vs NextJS

In React, Redux is commonly used for global state management, often integrated using middleware like Redux Saga or Thunk. When transitioning to Next.js,…

5 min read
  • Model Context Protocol
  • Distributed Systems
  • Scalability

All articles