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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

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

1
2
3

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

Update

1
2
3
4
5
6

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

Delete

1
2
3
4
5

## Delete Operation
delete_query = """DELETE FROM avengers
                WHERE real_name = 'Thor';
                """
mysql.execute_query(delete_query)