How to Run a Python Script Connecting to MySQL on Debian 12 Using Apache CGI

This guide shows how to serve a Python CGI script on Apache in Debian 12, and connect it to MySQL using PyMySQL.

Note: CGI works and is useful for demos and simple scripts, but for real applications you should strongly consider running Python behind Apache using WSGI (mod_wsgi) or behind a reverse proxy (Gunicorn/Uvicorn + Nginx/Apache). CGI spawns a new process per request, which is not ideal for performance.

Set up MySQL on Debian 12

Ensure the OS is up to date:

1
2
sudo apt update
sudo apt upgrade -y

Install MySQL Server

Debian ships MariaDB by default. If you specifically want Oracle MySQL Community Server, you can install it from the official MySQL APT repo.

Download the repo setup package (check the latest version on the MySQL downloads page if needed):

1
curl -LO https://repo.mysql.com/mysql-apt-config_0.8.29-1_all.deb

Install the repo config:

1
sudo apt install ./mysql-apt-config_0.8.29-1_all.deb

Update and install the server:

1
2
3
sudo apt update

sudo apt install -y mysql-server

You will be prompted to set up root password.

Confirm MySQL is running:

 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.

Ensure it starts on boot:

1
sudo systemctl enable mysql

Set up apache

Install Apache:

1
sudo apt install -y 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.

Ensure it starts on boot:

1
sudo systemctl enable apache2

Enabling CGI

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

Confirm CGI is enabled:

1
sudo apachectl -M | grep cgi

If you hit errors (HTTP 500), check Apache logs:

1
sudo tail -f /var/log/apache2/error.log

After enabling CGI, scripts are allowed to execute under /usr/lib/cgi-bin/ by default on Debian.

To confirm the CGI path on your system:

1
grep -i -r 'ScriptAlias' /etc/apache2/*

Setting up python

Install Python:

1
sudo apt install -y python3

Install dependencies

Prefer Debian packages when available (avoids pip --break-system-packages):

1
sudo apt install -y python3-pymysql python3-cryptography

If your environment doesn’t have these packages, you can use pip, but it’s better to install into a virtualenv.

1
sudo python3 -m pip install pymysql cryptography --break-system-packages

Creating database and table

Create database + user (recommended)

Avoid using root from your web script. Create a dedicated database user with limited permissions.

Connect to MySQL:

1
mysql -uroot -h 127.0.0.1 -p

Create database

1
CREATE DATABASE tasksapi;

Create a user and grant privileges:

1
2
3
CREATE USER 'tasksapi'@'127.0.0.1' IDENTIFIED BY 'CHANGE_ME_STRONG_PASSWORD';
GRANT ALL PRIVILEGES ON tasksapi.* TO 'tasksapi'@'127.0.0.1';
FLUSH PRIVILEGES;

Then create the 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

Create the CGI script in the default CGI directory:

1
sudo vim /usr/lib/cgi-bin/tasks-api.py

Add this code and set the DB credentials via environment variables (recommended):

  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
151
152
153
154
155
156
157
158
159
160
161
#!/usr/bin/python3

import json
import os
import pymysql
from urllib.parse import unquote_plus

DB_USER = os.getenv("DB_USER", "tasksapi")
DB_PSWD = os.getenv("DB_PSWD", "")
DB_NAME = os.getenv("DB_NAME", "tasksapi")
DB_HOST = os.getenv("DB_HOST", "127.0.0.1")
DB_PORT = int(os.getenv("DB_PORT", "3306"))

def db_connect():
    return pymysql.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PSWD,
        database=DB_NAME,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True,
    )


# Create a new task
def create_task(title):
    try:
        with db_connect() as db:
            with db.cursor() as cursor:
                cursor.execute("INSERT INTO tasks (title) VALUES (%s)", (title,))
                new_id = cursor.lastrowid
        resp = get_task(new_id)
        return (resp[0], 201)
    except Exception as e:
        return ({"error": str(e)}, 500)


# Get all tasks
def get_tasks():
    try:
        with db_connect() as db:
            with db.cursor() as cursor:
                cursor.execute(
                    "SELECT id, title, DATE_FORMAT(created, '%%Y-%%m-%%d %%H:%%i') AS created FROM tasks ORDER BY id DESC"
                )
                return (cursor.fetchall(), 200)
    except Exception as e:
        return ({"error": str(e)}, 500)


# Get an individual task
def get_task(id):
    try:
        with db_connect() as db:
            with db.cursor() as cursor:
                cursor.execute(
                    "SELECT id, title, DATE_FORMAT(created, '%%Y-%%m-%%d %%H:%%i') AS created FROM tasks WHERE id=%s",
                    (int(id),),
                )
                row = cursor.fetchone()
                return (row if row is not None else {"error": "Not Found"}, 200 if row is not None else 404)
    except Exception as e:
        return ({"error": "Not Found"}, 404)


# Update an existing task
def update_task(id, title):
    try:
        with db_connect() as db:
            with db.cursor() as cursor:
                cursor.execute("UPDATE tasks SET title=%s WHERE id=%s", (title, int(id)))
        return get_task(id)
    except Exception as e:
        return ({"error": str(e)}, 500)


# Delete an existing task
def delete_task(id):
    try:
        resp = get_task(id)
        if resp[1] != 200:
            return resp
        with db_connect() as db:
            with db.cursor() as cursor:
                cursor.execute("DELETE FROM tasks WHERE id=%s", (int(id),))
        return ("", 204)
    except Exception as e:
        return ({"error": 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 unquote_plus(query_string)


# 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) >= 1 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 in ("DELETE", "PUT") and id is None:
    resp = ({"error": "Task id is required"}, 400)
elif method == "DELETE":
    resp = delete_task(id)
elif not title_is_valid(title):
    resp = ({"error": "Title is required (1-255 chars)"}, 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]))

Make the script executable:

1
sudo chmod +x /usr/lib/cgi-bin/tasks-api.py

Set database credentials for Apache

The CGI script reads DB credentials from environment variables. You can set them in Apache config, for example:

Create /etc/apache2/conf-available/tasks-api.conf:

1
2
3
4
5
SetEnv DB_HOST 127.0.0.1
SetEnv DB_PORT 3306
SetEnv DB_NAME tasksapi
SetEnv DB_USER tasksapi
SetEnv DB_PSWD CHANGE_ME_STRONG_PASSWORD

Enable it and restart Apache:

1
2
sudo a2enconf tasks-api
sudo systemctl restart apache2

Testing the code

Get all tasks

1
2
3
curl -i http://127.0.0.1/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 -i -X POST "http://127.0.0.1/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 -i -X POST "http://127.0.0.1/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/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/cgi-bin/tasks-api.py?1

{"id": 1, "title": "First Task", "created": "2024-03-13 16:05"}

Delete a task:

1
curl -i -X DELETE "http://127.0.0.1/cgi-bin/tasks-api.py?1"