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:
- Install Python
- Install Python CGI
- SQLite
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.
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', '[email protected]'),
('user2', '[email protected]')
'''
# 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
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
Similar Reads
How to Create Form using CGI script HTML forms are a fundamental part of web development, allowing users to input data and interact with web applications. Web developers often use Common Gateway Interface (CGI) scripts to process and send the data submitted through these forms. In this article, we'll explain what Python CGI scripts ar
3 min read
How to create a simple CGI script? In this article, we will explore how to create a simple CGI (Common Gateway Interface) script on a Windows machine but before that, we need some basic ideas about these technologies ( CGI Script, HTTP, Web Server ).CGI Script: A CGI script is a program that runs on a web server and generates dynamic
2 min read
How to Execute a Script in SQLite using Python? In this article, we are going to see how to execute a script in SQLite using Python. Here we are executing create table and insert records into table scripts through Python. In Python, the sqlite3 module supports SQLite database for storing the data in the database. Approach Step 1: First we need to
2 min read
Extracting SQL Queries from Django QuerySets Django, a high-level Python web framework, simplifies the development of secure and maintainable websites. One of its powerful features is the Object-Relational Mapping (ORM) system, which allows developers to interact with the database using Python code instead of raw SQL. However, there are times
3 min read
How to execute an SQL query and fetch results using PHP ? In this article, we will discuss how to execute an SQL query and how to fetch its result? We can perform a query against the database using the PHP mysqli_query() method. Syntax: We can use the mysqli_query( ) method in two ways: Object-oriented styleProcedural style Parameters: connection: It is r
3 min read