How to Run a Python Script Connecting to Mysql in Debian 12 Using Apache Cgi

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"}
comments powered by Disqus
Citizix Ltd
Built with Hugo
Theme Stack designed by Jimmy