Python + MySQL CRUD Examples

By /

In the previous post, I covered the CRUD using PHP and MySQL. In this post, I would like to cover with the use of Python. Same concept as the information I put in this post but this time, we will be using Python.

Setting up the Environment

Make sure you have the following setup.

Database Server

In this post, I will be using local MySQL Server on macOS machine. However, you can use the database from your web hosting or on the Cloud such as AWS RDS

If you need to download and install the local MySQL server, download the installer here. And then follow the instructions. Please check out this post which I have brief step by step instruction on installing the MySQL server.

Python3

Make sure your system has Python3 available. Follow the document here to have your system equipped with Python3.

IDE

You need to pick your IDE. I personally use Visual Studio Code. It is free and it supports multiple scripting languages. You can also use Other IDE such as Sublime Text, Pycharm, and many others. Please check out this post for additional info about IDE.

Database Setup

You can make up your own database name, table, etc. In this post, I will be using the example I created in this post.

Basically, I will be using the below:

Database (Schema): test_db
Table: cars

cars table entities

id – Auto increment. This is the unique identifier.
car_model – is a VARCHAR where I store the model name of the car.
car_brand – is a VARCHAR where I store the brand name of the car.

If you want to create a table using SQL statement, you can use something like below:

CREATE TABLE `test_db`.`cars` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `car_model` VARCHAR(45) NULL,
  `car_brand` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE);

Python Environment Setup

Once you have your database setup, make sure your Python environment is also good to go.

PyMySQL library

Check if you have PyMySQL library in your system.

Run a pip3 list and look for PyMySQL from the list. If you cannot find it, install the library.

pip3 install PyMySQL

Scripting

Let’s now start creating the CRUD operations. Let’s start with create operation.

Create Record Example

Using the INSERT INTO SQL Statement, it will insert (create) a new record into the table.

SQL = """INSERT INTO test_db.cars(car_model, car_brand) VALUES ('accord', 'honda')"""

Below is the create_record.py file demonstrating the example of creating a new record.

create_record.py

""" 

This file will create a new record in the database table 

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/13/2019

"""

import pymysql.cursors

SERVER_URL = "localhost"
DB = "test_db"
USER_NAME = "yourusername"
PASSWORD = "yourpassword"

SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                    user=USER_NAME,
                    passwd=PASSWORD,
                    db=DB,
                    charset='utf8mb4',
                    cursorclass=pymysql.cursors.DictCursor,
                    autocommit=True)

SQL = """INSERT INTO test_db.cars(car_model, car_brand) VALUES ('accord', 'honda')"""

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print("Record Added")
        else:
            print(sql_exec)
            print("Not Added")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    finally:
        SQL_CONNECTION.close()

Read Record Example

using SELECT * FROM SQL statement, it will query all the records in the table.

Below is the sample code of reading the records from the database table.

read_record.py

"""

This file will read records from the database table 

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/13/2019

"""

import pymysql.cursors

SERVER_URL = "localhost"
DB = "test_db"
USER_NAME = "yourusername"
PASSWORD = "yourpassword"

SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                    user=USER_NAME,
                    passwd=PASSWORD,
                    db=DB,
                    charset='utf8mb4',
                    cursorclass=pymysql.cursors.DictCursor,
                    autocommit=True)

SQL = """SELECT * FROM test_db.cars"""

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print(cursor.fetchall())
        else:
            print(sql_exec)
            print("No Record")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')
    
    finally:
        SQL_CONNECTION.close()

Update Record Example

Using UPDATE test_db.cars SET... SQL statement, it will update the information of existing data in the table.

Below is the sample code of updating the record.

update_record.py

""" 

This file will update the specified record

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/13/2019

"""

import pymysql.cursors

SERVER_URL = "localhost"
DB = "test_db"
USER_NAME = "yourusername"
PASSWORD = "yourpassword"

SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                    user=USER_NAME,
                    passwd=PASSWORD,
                    db=DB,
                    charset='utf8mb4',
                    cursorclass=pymysql.cursors.DictCursor,
                    autocommit=True)

SQL = """UPDATE test_db.cars SET car_model = 'explorer', car_brand = 'ford' WHERE id = '3'"""

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print("Record Upadted")
        else:
            print(sql_exec)
            print("Not Upadted")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    finally:
        SQL_CONNECTION.close()

Delete Record Example

Using DELETE FROM and WHERE id =... SQL statement, it will delete the specified record.

Below is the sample code of deleting the specified record.

delete_record.py

""" 

This file will delete a selected record from the database table 

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/13/2019

"""

import pymysql.cursors

SERVER_URL = "localhost"
DB = "test_db"
USER_NAME = "yourusername"
PASSWORD = "yourpassword"

SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                    user=USER_NAME,
                    passwd=PASSWORD,
                    db=DB,
                    charset='utf8mb4',
                    cursorclass=pymysql.cursors.DictCursor,
                    autocommit=True)

SQL = """DELETE FROM test_db.cars WHERE id = '4'"""

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print("Record Deleted")
        else:
            print(sql_exec)
            print("Not Deleted")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    finally:
        SQL_CONNECTION.close()

Sample Codes Download

You can download the sample codes covered in this post from here.

Feel free to share this post!

Scroll to Top