We are going to serve a python script connecting to mysql using CGI (Common Gateway Interface) Scripts in apache and debian 12.
Set up Mysql in Debian 12
Ensure the OS is upto date
1
2
| sudo apt update
sudo apt upgrade
|
Then set up mysql
Download repo set up binary
Mysql downloads here
1
| curl -LO https://repo.mysql.com/mysql-apt-config_0.8.29-1_all.deb
|
Install repo
1
| sudo apt install ./mysql-apt-config_0.8.29-1_all.deb
|
Update repo and install server
1
2
3
| sudo apt update
sudo apt install mysql-server
|
You will be prompted to set up root password.
Confirm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| $ sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Wed 2024-03-13 16:49:39 UTC; 19s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 1670644 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 4619)
Memory: 371.3M
CPU: 1.163s
CGroup: /system.slice/mysql.service
└─1670644 /usr/sbin/mysqld
Mar 13 16:49:38 k8s-node1 systemd[1]: Starting mysql.service - MySQL Community Server...
Mar 13 16:49:39 k8s-node1 systemd[1]: Started mysql.service - MySQL Community Server.
|
To ensure it starts on boot
1
| sudo systemctl enable mysql
|
Set up apache
Install apache2 software, readily available in the repos
1
| sudo apt install apache2
|
It will be started by default, confirm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| $ sudo systemctl status apache2
● apache2.service - The Apache HTTP Server
Loaded: loaded (/lib/systemd/system/apache2.service; enabled; preset: enabled)
Active: active (running) since Wed 2024-03-13 16:51:12 UTC; 33s ago
Docs: https://httpd.apache.org/docs/2.4/
Main PID: 1671916 (apache2)
Tasks: 55 (limit: 4619)
Memory: 9.3M
CPU: 59ms
CGroup: /system.slice/apache2.service
├─1671916 /usr/sbin/apache2 -k start
├─1671918 /usr/sbin/apache2 -k start
└─1671919 /usr/sbin/apache2 -k start
Mar 13 16:51:12 k8s-node1 systemd[1]: Starting apache2.service - The Apache HTTP Server...
Mar 13 16:51:12 k8s-node1 apachectl[1671915]: AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using fe80::4f6:f9ff:fe87:6dd5%ens5. Set the 'ServerN>
Mar 13 16:51:12 k8s-node1 systemd[1]: Started apache2.service - The Apache HTTP Server.
|
To ensure it starts on boot
1
| sudo systemctl enable apache2
|
Enabling CGI
Use this command to enable CGI
1
2
3
4
5
| $ sudo a2enmod cgid
Enabling module cgid.
To activate the new configuration, you need to run:
systemctl restart apache2
|
Restart apache to apply changes
1
| sudo systemctl restart apache2
|
You can confirm that it works
1
| sudo apachectl -M | grep cgi
|
You can also confirm by checking logs
1
| tail -f /var/log/apache2/*.log
|
After enabling CGI, CGI scripts are allowed to execute under [/usr/lib/cgi-bin] directory by default.
The cgi path in my OS is usr/lib/cgi-bin/, that is where we will put our content.
To confirm cgi path in your OS, use this command:
1
| grep -i -r 'ScriptAlias' /etc/apache2/*
|
Setting up python
Install python
1
| sudo apt install python3
|
Install pip for dependencies
1
| sudo apt install python3-pip
|
Install pip dependencies
1
| sudo python3 -m pip install pymysql cryptography --break-system-packages
|
Creating database and table
Set up the DB, connect to db
1
| mysql -uroot -h 127.0.0.1 -p
|
Create database
1
| CREATE DATABASE tasksapi;
|
Then create our table
1
2
3
4
5
6
7
8
9
| USE tasksapi;
CREATE TABLE tasks (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX(created)
);
|
Confirm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tasksapi |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_tasksapi |
+--------------------+
| tasks |
+--------------------+
1 row in set (0.00 sec)
|
Copying the code
Add the code to the right place. Open with your text editor.
1
| sudo vim /usr/lib/cgi-bin/tasks-api.py
|
Add this code, remember to replace db credentials:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
| #!/usr/bin/python3
import json
import os
import pymysql
DB_USER = os.getenv("DB_USER") or "root"
DB_PSWD = os.getenv("DB_PSWD") or "superStrond@123"
DB_NAME = os.getenv("DB_NAME") or "tasksapi"
DB_HOST = os.getenv("DB_HOST") or "127.0.0.1"
db = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PSWD,
database=DB_NAME,
cursorclass=pymysql.cursors.DictCursor,
)
cursor = db.cursor()
# Create a new task
def create_task(title):
try:
cursor.execute("INSERT INTO tasks (title) VALUES (%s)", title)
db.commit()
cursor.execute("SELECT MAX(id) AS id FROM tasks")
row = cursor.fetchone()
resp = get_task(row["id"])
return (resp[0], 201)
except Exception as e:
return (str(e), 500)
# Get all tasks
def get_tasks():
try:
cursor.execute(
"SELECT id, title, date_format(created, '%Y-%m-%d %H:%i') as created FROM tasks"
)
return (cursor.fetchall(), 200)
except Exception as e:
return (str(e), 500)
# Get an individual task
def get_task(id):
try:
cursor.execute(
"SELECT id, title, date_format(created, '%Y-%m-%d %H:%i') as created \
FROM tasks WHERE id="
+ str(id)
)
row = cursor.fetchone()
return (row if row is not None else "", 200 if row is not None else 404)
except Exception as e:
return ("", 404)
# Update an existing task
def update_task(id, title):
try:
cursor.execute("UPDATE tasks SET title=%s WHERE id=%s", (title, id))
db.commit()
return get_task(id)
except Exception as e:
return (str(e), 500)
# Delete an existing task
def delete_task(id):
try:
resp = get_task(id)
if resp[1] == 200:
cursor.execute("DELETE FROM tasks WHERE id=%s", id)
db.commit()
return ("", 200)
else:
return resp
except Exception as e:
return (str(e), 500)
# Returns the HTTP request method
def get_method():
return os.getenv("REQUEST_METHOD") or "GET"
# Returns the query string
def get_query_string():
query_string = os.getenv("QUERY_STRING") or ""
return query_string.replace("%20", " ").replace("%2F", "/").replace("+", " ")
# Returns the task ID if set in the request query string
def get_task_id():
query_string = get_query_string()
qs_parts = query_string.split("/")
return qs_parts[0] if qs_parts[0].isnumeric() else None
# Returns the task title from the query string if set
def get_task_title():
title = None
query_string = get_query_string()
if query_string != "":
qs_parts = query_string.split("/")
title = qs_parts[1] if len(qs_parts) > 1 else qs_parts[0]
title = None if title.isnumeric() else title
return title
# Returns True if title is valid, False otherwise
def title_is_valid(title):
return (
True
if isinstance(title, str) and len(title) >= 6 and len(title) <= 255
else False
)
# Returns a status code method
def get_status_msg(code):
msg = "OK"
msg = "Created" if code == 201 else msg
msg = "Not Found" if code == 404 else msg
msg = "Bad Request" if code == 400 else msg
msg = "Internal Server Error" if code == 500 else msg
return msg
method = get_method()
id = get_task_id()
title = get_task_title()
if method == "GET" and not id is None:
resp = get_task(id)
elif method == "GET":
resp = get_tasks()
elif method == "DELETE":
resp = delete_task(id)
elif not title_is_valid(title):
resp = ("", 400)
elif method == "POST":
resp = create_task(title)
elif method == "PUT":
resp = update_task(id, title)
print("Status: %d %s" % (resp[1], get_status_msg(resp[1])))
print("Content-type: application/json\n")
print(json.dumps(resp[0]))
|
Testing the code
Get all tasks
1
2
3
| $ curl http://127.0.0.1:80/cgi-bin/tasks-api.py
[]
|
Add Task
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| $ curl -X POST -s -D - http://127.0.0.1:8080/cgi-bin/tasks-api.py\?First+Task
HTTP/1.1 201 Created
Date: Wed, 13 Mar 2024 16:05:51 GMT
Server: Apache/2.4.58 (Unix)
Transfer-Encoding: chunked
Content-Type: application/json
{"id": 1, "title": "First Task", "created": "2024-03-13 16:05"}
$ curl -X POST -s -D - http://127.0.0.1:8080/cgi-bin/tasks-api.py?Second+Task
HTTP/1.1 201 Created
Date: Wed, 13 Mar 2024 16:06:19 GMT
Server: Apache/2.4.58 (Unix)
Transfer-Encoding: chunked
Content-Type: application/json
{"id": 2, "title": "Second Task", "created": "2024-03-13 16:06"}
|
Get Tasks
1
2
3
| $ curl http://127.0.0.1:8080/cgi-bin/tasks-api.py
[{"id": 1, "title": "First Task", "created": "2024-03-13 16:05"}, {"id": 2, "title": "Second Task", "created": "2024-03-13 16:06"}]
|
Get task
1
2
3
| $ curl http://127.0.0.1:8080/cgi-bin/tasks-api.py\?1
{"id": 1, "title": "First Task", "created": "2024-03-13 16:05"}
|