Author: Francis Ndungu
Last Updated: Thu, Jun 9, 2022Python 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.
Before you proceed:
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
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:
Log in to the MySQL server as a root
user.
$ sudo mysql -u root -p
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
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
database_gateway
ModuleIn 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.
customers
ModuleIn 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.
index
FileEvery 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
.
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.
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.