How to Execute SQL queries from CGI scripts

Last Updated : 04 Jan, 2024

In this article, we will explore the process of executing SQL queries from CGI scripts. To achieve this, we have developed a CGI script that involves importing the database we created. The focus of this discussion will be on writing code to execute SQL queries in Python CGI scripts. The initial step involves creating a database, and it’s worth noting that prebuilt databases can also be utilized for this purpose in Python.

What are SQL Queries?

SQL (Structured Query Language) is a programming language designed for managing and manipulating relational database systems. It provides a standardized way to interact with databases, allowing users to perform various operations such as querying data, updating records, inserting new data, and deleting information (CRUD Operation). SQL is widely used in the field of database management and is supported by most relational database management systems (RDBMS).

Execute SQL Queries From CGI Scripts

Below is a step-by-step guide to understanding how to execute SQL queries from CGI scripts in Python. Let’s begin.

Required Installation

Here, we will install the following things to start with generating webpages using CGI scripts:

Create Folder

In this article, we will explore the process of executing SQL queries from CGI (Common Gateway Interface) scripts. To begin, let’s create a directory named “database” to house our CGI script. Within this folder, we’ll generate a Python file, ‘python.py’, wherein our CGI script will be written. Additionally, we’ll save our database within the same directory. The following provides a step-by-step guide to learning how to execute SQL queries from CGI scripts.

save

Write Python CGI Script to Execute SQL Queries

In this example the Python CGI script establishes a connection to an SQLite database and defines a function (`execute_query`) for executing SQL queries and retrieving results. It creates a table named “users” and inserts two sample records into the database.

Now, The script then retrieves form data using the `cgi` module, particularly an SQL query provided in the form. The HTML and CSS markup define a simple web form for entering SQL queries and displaying the results.

Upon form submission, the script executes the SQL query, handles any errors, and dynamically generates an HTML response, showcasing the query results or error messages. Finally, the script closes the database connection.

This code serves as a basic example of a CGI script for interacting with an SQLite database through a web interface.

Python3

#!C:\Program Files\Python311\python.exe
 
print("Content-type: text/html\n\n")
import cgi
import sqlite3
 
# Connect to the SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
 
# Function to execute SQL query and retrieve results
def execute_query(query, params=None):
    try:
        if query:
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
 
            result = cursor.fetchall()
            return result
        else:
            return "No query provided."
    except Exception as e:
        return f"Error executing query: {str(e)}"
     
# Create a table and insert two users into the database
create_table_query = '''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL
    )
'''
 
insert_users_query = '''
    INSERT INTO users (username, email) VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com')
'''
 
# Execute the table creation and user insertion queries
execute_query(create_table_query)
execute_query(insert_users_query)
 
# Get form data
form = cgi.FieldStorage()
 
# Retrieve SQL query from the form
sql_query = form.getvalue('sql_query')
 
# HTML and CSS for the form and result display
html = f"""
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CGI Database Query</title>
    <style>
        body {{
            font-family: 'Arial', sans-serif;
            background-color: #f4f4f4;
            margin: 20px;
        }}
 
        h1 {{
            color: #333;
            text-align: center;
        }}
 
        form {{
            background-color: #fff;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            max-width: 600px;
            margin: 20px auto;
        }}
 
        label {{
            display: block;
            margin-bottom: 8px;
        }}
 
        textarea {{
            width: 100%;
            padding: 8px;
            margin-bottom: 16px;
            box-sizing: border-box;
            border: 1px solid #ccc;
            border-radius: 4px;
        }}
 
        input[type="submit"] {{
            background-color: #4caf50;
            color: #fff;
            padding: 10px 15px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            font-size: 16px;
        }}
 
        input[type="submit"]:hover {{
            background-color: #45a049;
        }}
 
        h2 {{
            color: #333;
            margin-top: 20px;
        }}
 
        pre {{
            white-space: pre-wrap;
            background-color: #f9f9f9;
            padding: 15px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }}
 
        p.error {{
            color: #ff0000;
            font-weight: bold;
        }}
    </style>
</head>
<body>
    <h1>CGI Database Query</h1>
    <form method="post" action="/database/python.py"> <!-- Corrected form action -->
        <label for="sql_query">Enter SQL Query:</label>
        <textarea name="sql_query" rows="4" cols="10" required>{sql_query}</textarea><br>
        <input type="submit" value="Execute Query">
    </form>
 
    <h2>Query Results:</h2>
"""
 
# Execute the SQL query and handle errors
try:
    result = execute_query(sql_query)
    html += f"<pre>{result}</pre>"
except Exception as e:
    html += f"<p class='error'>Error: {str(e)}</p>"
 
html += """
</body>
</html>
"""
 
# Display the HTML
print(html)
 
# Close the database connection
conn.close()

Output

sql

Configuration and Start the Xampp Server

You can refer Create a CGI Script for complete configuration and how we can start the server to run out CGI Script.

Step 5: Run the Script

In this step, we will run the CGI Script by using the following command in your web browser

http://127.0.0.1/database/python.py

Video Demonstration

Conclusion

In conclusion, the process of executing SQL queries from CGI scripts provides a dynamic and interactive means to interact with databases through web interfaces. This article demonstrated a step-by-step guide, illustrating how to create a CGI script in Python to handle SQL queries. By establishing a connection to an SQLite database, defining a function for query execution, and incorporating HTML/CSS for a user-friendly form, users can seamlessly input SQL queries and receive real-time results or error feedback

Suggest improvement

How to Execute a SQL Script File using JDBC?

Share your thoughts in the comments