Posts
Software Engineering
CRUD operations in MySQL with Python

CRUD operations in MySQL with Python

April 1, 2023

Let’s delve into the core CRUD operations (Create, Read, Update, Delete) in MySQL using Python. We’ll cover setting up MySQL with Docker Compose, establishing a connection between MySQL and Python, and executing Python code to perform data insertion, retrieval, updating, and deletion.

Setup MySQL

Using Docker-compose is highly recommended for local development as it significantly reduces the setup time for MySQL. Provided below is a sample Docker-compose file that can be used to set up MySQL locally. 

version: '3.8'
services:
  mysql_db:
    image: mysql
    cap_add:
      - SYS_NICE
    restart: always
    environment:
      - MYSQL_DATABASE=test_db
      - MYSQL_ROOT_PASSWORD=admin
      - MYSQL_USER=admin
      - MYSQL_PASSWORD=admin
    ports:
      - '3306:3306'
    volumes:
      - mysql_db3:/var/lib/mysql
volumes:
  mysql_db3:
    driver: local

Start the mysql container with command
docker compose -f mysql_compose.yml up
And then you can interact with this mysql container with
docker exec -it container_id mysql -uroot -p.

# Install the pip package
!pip install custom-utils[mysql]

# Setup MySQL Connection
from custom_utils.connector.mysql import MySQL
user = "admin"
password = "admin"
host = "127.0.0.1"
port = "3306"
database = "test_db"
db_string = f"mysql+mysqlconnector://{user}:{password}@{host}:{port}/{database}"
mysql = MySQL(db_string=db_string)

Create

First things first, you’ll need to install this amazing pip package called custom-utils. It’s got all the essential database connectors you’ll need.

## Create Table
create_query = """CREATE TABLE avengers (
                                    real_name VARCHAR(255),
                                    made_up_name VARCHAR(255),
                                    power VARCHAR(255)
                                    );"""
mysql.execute_query(create_query)

## Insert Rows
insert_query = """INSERT INTO avengers (real_name, made_up_name, power)
VALUES
  ('Steve Rogers', 'Captain America', 'Shield'),
  ('Tony Stark', 'Iron Man', 'Armour'),
  ('Thor', 'Thor', 'Hammer');
"""
mysql.execute_query(insert_query)

## Insert Dataframe in mysql as table
mysql.push_data(dataframe, 'table_name', 'replace') # mode can be replace or append

Read 

## Read Operation
read_query = "select * from avengers;"
df = mysql.pull_data(read_query)

Update 

## Update Operation
update_query = """UPDATE avengers
                    SET power = 'Mjolnir'
                    WHERE power = 'Hammer';
                    """
mysql.execute_query(update_query)

Delete 

## Delete Operation
delete_query = """DELETE FROM avengers
                WHERE real_name = 'Thor';
                """
mysql.execute_query(delete_query)
Git BasicsFast API Deployment in K8s