How to Use MySQL with Python

Published on June 9, 2022•Updated on November 21, 2023

Introduction

Python is a general-purpose programming language that focuses on code readability. It's suitable for web development, API design, command-line tools, and more. The Create, Read, Update, and Delete (CRUD) operations refer to the following functions for implementing persistent data storage applications:

create: A function that adds data to a database using the INSERT command.
read: A function that retrieves data from a database using the SELECT command
update: A function that modifies the details of a record using the UPDATE command.
delete: A function that removes a record from a database table using the DELETE command.

You can use Python to interact with most database management systems. This guide focuses on implementing a CRUD application with Python and MySQL on the Ubuntu 20.04 server.

Prerequisites

Before you proceed:

1. Verify the Python Version and Install Dependency Packages and Libraries

By default, Ubuntu 20.04 ships with Python. Follow the steps below to verify your Python version and download the necessary dependency package and libraries required to run this application:

Verify the Python version.
```
 $ python3 -V
```
Output.
```
 Python 3.8.10
```
Install pip, a tool for installing Python modules/libraries.
```
 $ sudo apt update
 $ sudo apt install -y python3-pip
```
Use the pip package to install the mysql-connector-python library. The mysql-connector-python library is a self-contained driver for communicating to MySQL from Python.
```
 $ pip install mysql-connector-python
```

2. Set Up a MySQL Database

You now have the correct Python environment and a driver for connecting to the MySQL server. For this project, you require a sample database and a table. Execute the steps below to set up the database:

Enter the password when prompted and press Enter to proceed. Then, issue the SQL commands below to create a sample e_commerce database and an e_commerce_user account. Replace EXAMPLE_PASSWORD with a strong password.

 mysql> CREATE DATABASE e_commerce;
        CREATE USER 'e_commerce_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
        GRANT ALL PRIVILEGES ON e_commerce.* TO 'e_commerce_user'@'localhost';           
        FLUSH PRIVILEGES;

Output.

 ...
 Query OK, 0 rows affected (0.00 sec)

Switch to the new e_commerce database.
```
 mysql> USE e_commerce;
```
Output.
```
 Database changed
```

Create a customers table with three columns. The customer_id is the PRIMARY KEY. Use the AUTO_INCREMENT keyword to allow MySQL to assign a new customer_id every time you insert a new record.

 mysql> CREATE TABLE customers (
            customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            first_name  VARCHAR(50),
            last_name  VARCHAR(50)    
        ) ENGINE = InnoDB;

Output.

 Query OK, 0 rows affected (0.01 sec)

Don't insert any records into the customers table. The customers table later receives data from a Python script.
Log out from the MySQL database server.
```
 mysql> QUIT;
```
Output.
```
 Bye
```

2. Create the Source Code Files

This step focuses on creating the necessary modules for accepting data and passing it to the MySQL server through some Python code. Create the source code files by following the steps below:

Create a new project directory for the sample application. This directory helps to keep your application organized.
```
 $ mkdir project
```
Switch to the new project directory.
```
 $ cd project
```
Review the layout of the source code files from the illustration below. After creating all the source code files, you should have the following files in the project directory.
```
 project
 ----database_gateway.py
 ----customers.py
 ----index.py
```

2.1. Create a `database_gateway` Module

In Python, a single file is a module. A module can have one or several classes that you can reuse in your project. Follow the steps below to create a separate database_gateway module for performing database operations:

Open a new database_gateway.py file in a text editor.
```
 $ nano database_gateway.py
```

Enter the following information into the file and replace EXAMPLE_PASSWORD with the correct value depending on the database user account credentials.

 import mysql.connector
 import json

 class DatabaseGateway:

     def db_connect(self):

         db_con = mysql.connector.connect(
                     host     = "localhost",
                     user     = "e_commerce_user",
                     password = "EXAMPLE_PASSWORD",
                     database = "e_commerce"
                  )

         # db_cursor = db_con.cursor()

         return db_con

     def db_query(self, query_string):

         my_db    = self.db_connect()
         mycursor = my_db.cursor()
         mycursor.execute(query_string)

         columns = mycursor.description
         data  = []

         for row in mycursor.fetchall():

             row_data = {}

             for (column_name, column_value) in enumerate(row):

                 row_data[columns[column_name][0]] = column_value

             data.append(row_data)

         json_object = json.dumps(data)

         return json.dumps(json.loads(json_object), indent = 2) 

     def db_execute(self, query_string, data):

         my_db = self.db_connect()
         mycursor = my_db.cursor()
         mycursor.execute(query_string, data)
         my_db.commit()

         self.lastrowid = str(mycursor.lastrowid)

Save and close the database_gateway.py file.

The database_gateway.py file explained:

The DatabaseGateway is the only class in the database_gateway.py file.
The DatabaseGateway class has three methods:
- The db_connect(self) method connects to the MySQL server and return a connection using the return db_con statement.
- The db_query(self, query_string) method queries the MySQL database to retrieve records from the customers table, and returns data as a JSON object.
- The db_execute(self, query_string, data) method allows the application to create, update, and delete records. The db_execute function takes a query_string and the data that you want the application to execute.

2.2. Create a `customers` Module

In a production environment, a project may contain dozens or even hundreds of tables connected to URL endpoints. This guide has one table. This step describes how to create a customers module that interacts with the customers table through the database_gateway module that you've already created.

Use a text editor to open a new customers.py file under your project directory.
```
 $ nano customers.py
```

Enter the following information into the customers.py file.

 import json

 import database_gateway

 class Customers:

     def create_record(self, args):

         dg = database_gateway.DatabaseGateway()

         query_string = "insert into customers (first_name, last_name) values (%s, %s)"
         data = (args["first_name"], args["last_name"])

         dg.db_execute(query_string, data)     

         return self.read_records(dg.lastrowid)

     def update_record(self, args, resource_id):

         dg = database_gateway.DatabaseGateway()

         query_string = "update customers set first_name = %s, last_name = %s where customer_id = %s"

         data = (args["first_name"], args["last_name"], resource_id)

         dg.db_execute(query_string, data)     

         return self.read_records(resource_id)

     def delete_record(self, resource_id):

         dg = database_gateway.DatabaseGateway()

         query_string = "delete from customers where customer_id = %s"

         data = (resource_id,)

         dg.db_execute(query_string, data) 

         resp = ("Success",)

         json_object = json.dumps(resp)

         return json.dumps(json.loads(json_object), indent = 2)

     def read_records(self, resource_id):

         dg = database_gateway.DatabaseGateway()

         if resource_id == "" :
             query_string = "select * from customers"
         else:
             query_string = "select * from customers where customer_id = '" + str(resource_id) + "'"

         resp = dg.db_query(query_string)     

         return resp

Save and close the file.

The customers.py file explained:

The Customers is the only class in the customers module.
The import database_gateway statement imports the database_gateway module for connecting to the database.
The Customers class has four main methods:
- The create_record(self, args) method calls the db_execute(query_string, data) function in the database_gateway module to insert a new record into the customers table.
- The update_record(self, args, resource_id) method updates a customer's record where the customer_id matches the value of the resource_id.
- The delete_record(self, resource_id) method deletes a customer's record where the customer_id matches the value of the resource_id.
- The read_records(self, resource_id) method retrieves records from the customers table. If the value of the resource_id argument is empty, the method returns all records.

2.3. Create an `index` File

Every Python application must have a main file that executes when you run the application. This guide uses an index.py file. Create the file by following the steps below:

Open a new index.py file in a text editor.
```
 $ nano index.py
```

Enter the following information into the index.py file.

 import http.server
 from http import HTTPStatus
 import socketserver
 import json

 import customers

 class Handler(http.server.SimpleHTTPRequestHandler): 

     def do_POST(self):

         content_length = int(self.headers['Content-Length'])
         post_data = self.rfile.read(content_length)

         args = json.loads(post_data)  

         self.send_response(HTTPStatus.OK)
         self.send_header('Content-type', 'application/json')
         self.end_headers()

         obj  = customers.Customers()
         data = obj.create_record(args)        

         self.wfile.write(bytes(data, "utf8"))  

     def do_PUT(self):

         content_length = int(self.headers['Content-Length'])
         post_data = self.rfile.read(content_length)

         resource_id = ""

         if len(self.path.split("/")) >= 3:
             resource_id = self.path.split("/")[2] 

         args = json.loads(post_data)  

         self.send_response(HTTPStatus.OK)
         self.send_header('Content-type', 'application/json')
         self.end_headers()

         obj  = customers.Customers()
         data = obj.update_record(args, resource_id)        

         self.wfile.write(bytes(data, "utf8"))   

     def do_DELETE(self):   

         resource_id = ""

         if len(self.path.split("/")) >= 3:
             resource_id = self.path.split("/")[2] 

         self.send_response(HTTPStatus.OK)
         self.send_header('Content-type', 'application/json')
         self.end_headers()

         obj  = customers.Customers()
         data = obj.delete_record(resource_id)        

         self.wfile.write(bytes(data, "utf8"))  

     def do_GET(self):

         self.send_response(HTTPStatus.OK)
         self.send_header('Content-type', 'application/json')
         self.end_headers()

         resource_id = ""

         if len(self.path.split("/")) >= 3:
             resource_id = self.path.split("/")[2]      

         obj =  customers.Customers()

         data = obj.read_records(resource_id)

         self.wfile.write(bytes(data, "utf8")) 

 httpd = socketserver.TCPServer(('', 8080), Handler)

 try:
     httpd.serve_forever()
 except KeyboardInterrupt: 
     httpd.server_close()
     print("The server is stopped.")

Save and close the file.

The index.py file explained:

The import http.server and import socketserver statements import the basic functionalities for running a web server for testing the application.
The import customers statement loads the customers module that you created earlier.
The Handler is the only class in the index.py file.
The Handler class has four main methods:
- The do_POST(self) method takes a JSON payload and passes it to the customers module using the obj.create_record(args) statement to INSERT a new record into the customers table.
- The do_PUT(self) method calls the obj.update_record(args, resource_id) function to update a record in the customers table where the customer_id matches the `resource_id' value.
- The do_DELETE(self) method executes the obj.delete_record(resource_id) function to delete a record where the customer_id matches the `resource_id' value.
- The do_GET(self) method calls the obj.read_records(resource_id) function to retrieve the records from the customers table. If the resource_id is empty, the script returns all records.
The resource_id = self.path.split("/")[2] helps to retrieve the value of the resource_id from the URL. For instance, if you execute the query http://localhost:8080/customers/3, the Python split(...) function retrieves 3 as the resource_id.

3. Test the Application

Your application is now ready for testing. Execute the following steps to run and perform basic CRUD operations.

Run the index.py file.
```
 $ python3 index.py
```
The above command has a blocking function that runs a web server under port 8080. Don't enter any other commands in your active SSH connection.

Establish another SSH connection and use curl to execute the following POST commands to INSERT data into the database.

 $ curl -X POST http://localhost:8080/ -H 'Content-Type: application/json' -d '{"first_name": "JOHN", "last_name": "DOE"}'

 $ curl -X POST http://localhost:8080/ -H 'Content-Type: application/json' -d '{"first_name": "MARY", "last_name": "SMITH"}'

 $ curl -X POST http://localhost:8080/ -H 'Content-Type: application/json' -d '{"first_name": "STEVE", "last_name": "KINGS"}'

After running each command, you should get the following output. The application returns a new customer_id for each record.

 [
   {
     "customer_id": 1,
     "first_name": "JOHN",
     "last_name": "DOE"
   }
 ]

 [
   {
     "customer_id": 2,
     "first_name": "MARY",
     "last_name": "SMITH"
   }
 ]

 [
   {
     "customer_id": 3,
     "first_name": "STEVE",
     "last_name": "KINGS"
   }
 ]

Run the following PUT command to update the details of a customer with a unique customer_id of 3.

 $ curl -X PUT http://localhost:8080/customers/3 -H 'Content-Type: application/json' -d '{"first_name": "STEVE", "last_name": "KINGSTON"}'

Output.

 [
   {
     "customer_id": 3,
     "first_name": "STEVE",
     "last_name": "KINGSTON"
   }
 ]

Retrieve customers from the application by executing the GET statement below.

 $ curl -X GET http://localhost:8080/customers

Output.

 [
   {
     "customer_id": 1,
     "first_name": "JOHN",
     "last_name": "DOE"
   },
   {
     "customer_id": 2,
     "first_name": "MARY",
     "last_name": "SMITH"
   },
   {
     "customer_id": 3,
     "first_name": "STEVE",
     "last_name": "KINGSTON"
   }
 ]

Use the DELETE command below to remove a customer from the database.
```
 $ curl -X DELETE http://localhost:8080/customers/3
```
Output.
```
 [
   "Success"
 ]
```
Try retrieving the customer from the database to ensure the delete operation was successful.
```
 $ curl -X GET http://localhost:8080/customers/3
```
You should get an empty response showing that you've successfully deleted the customer.
```
 []     
```

Your application is working as expected.

Conclusion

In this guide, you've implemented and tested a CRUD application with Python and MySQL on the Ubuntu 20.04 server. Add more database tables depending on your business logic to create a fully functional application. Remember to merge each table to a separate Python class to make your application easier to maintain in the future.

How to Use MySQL with Python

Introduction

Prerequisites

1. Verify the Python Version and Install Dependency Packages and Libraries

2. Set Up a MySQL Database

2. Create the Source Code Files

2.1. Create a `database_gateway` Module

2.2. Create a `customers` Module

2.3. Create an `index` File

3. Test the Application

Conclusion

Products

Features

Marketplace

Resources

Company

How to Use MySQL with Python

Introduction

Prerequisites

1. Verify the Python Version and Install Dependency Packages and Libraries

2. Set Up a MySQL Database

2. Create the Source Code Files

2.1. Create a database_gateway Module

2.2. Create a customers Module

2.3. Create an index File

3. Test the Application

Conclusion

2.1. Create a `database_gateway` Module

2.2. Create a `customers` Module

2.3. Create an `index` File