intro
In this blog we are exploring MySQL stored procedures, their integration with a Python application, and testing them with POSTMAN, followed by database visualization using DbVisualizer.
Stored procedures are powerful tools for database developers to simplify the complexity of database operations. They allow database developers to write reusable code, reduce the amount of data transfer between the database server and the client, and improve the overall performance of database operations.
In this comprehensive guide, we will cover the basics of calling stored procedures in Python, connecting to a MySQL database, creating procedures, testing the procedures using POSTMAN, and managing your databases with DbVisualizer.
Prerequisites
Before we begin, you will need to have the following tools installed:
You will also need access to a database engine that supports stored procedures. For this tutorial, we will be using MySQL, but you can use any database engine that supports stored procedures.
What is POSTMAN?
POSTMAN is a popular API development tool that allows developers to test their API endpoints and create collections of requests for automated testing. Additionally, Postman provides a user-friendly interface for sending HTTP requests and inspecting responses, making it an essential tool for API development and testing.
Creating a Sample Database
To demonstrate how to test stored procedures with POSTMAN and DbVisualizer, we will create a sample database that contains a stored procedure. You can achieve this by either using the DbVisualizer’s GUI or by entering the SQL code in the SQL Commander. For this tutorial, we will be using the SQL Commander.
Click on the play icon found at the left top corner of the application to create a new SQL Commander tab.
Next, enter the SQL code below in your new SQL Commander tab to create the sample database:
1
- - Create the sample database
2
CREATE DATABASE postman_db;
3
- - Switch to the sample database
4
USE postman_db;
5
- - Create a table to store the data
6
CREATE TABLE customers (
7
id INT NOT NULL AUTO_INCREMENT,
8
name VARCHAR(50) NOT NULL,
9
email VARCHAR(50) NOT NULL,
10
PRIMARY KEY (id)
11
);
12
13
- - Insert some sample data
14
INSERT INTO customers (name, email)
15
VALUES
16
('John', 'john@gmail.com'),
17
('Jane Doe', 'jane.doe@example.com'),
18
('Bob Smith', 'bob.smith@example.com'),
19
('Alice Johnson', 'alice.johnson@example.com');
In the code above, we create a database called postman_db and a table called customers that contains some sample data.
If the query ran successfully, you should see a similar response as the one above in your DbVisualizer SQL commander and a customer table with the data below:
Creating a Stored Procedure
Next, we will create a stored procedure that accepts two parameters and returns a result set. Enter the SQL code below into your SQL commander and run it to create the stored procedure:
1
- - Create a stored procedure that accepts two parameters and returns a result set
2
@delimiter %%%;
3
CREATE PROCEDURE get_customers_by_name_and_email (
4
IN p_name VARCHAR(50),
5
IN p_email VARCHAR(50)
6
)
7
BEGIN
8
SELECT * FROM customers
9
WHERE name = p_name AND email = p_email;
10
END;
11
%%%
This stored procedure accepts two input parameters (p_name and p_email) and returns all rows from the customers table where the name column matches p_name and the email column matches p_email. We will use this stored procedure to demonstrate how to test stored procedures with POSTMAN and visualize the results with DbVisualizer.
Building a Flask Application to Use the Stored Procedure
Now that we have created the stored procedure, we can build a Flask application that will use it to retrieve customer data from the database. Before we jump straight to the code, we will need to install a few libraries by running the commands below in your terminal.
1
pip3 install flask
2
pip3 install mysql.connector
With the necessary modules installed, we will need to import them:
1
from flask import Flask, jsonify, request
2
import mysql.connector
Next, we will create a Flask application instance and define the URL of our database:
1
app = Flask(__name__)
2
# Define the database URL
3
db_url = "mysql+mysqlconnector://<username>:<password>@localhost:3306/ContactManager"
We will also define a function that connects to the database and executes the stored procedure:
1
def execute_stored_procedure(name, email):
2
try:
3
# Connect to the database using the database URL
4
cnx = mysql.connector.connect(user=<username>, password=<password>, host='localhost', database='ContactManager')
5
# Prepare the SQL query
6
7
query = "CALL get_customers_by_name_and_email(%s, %s)"
8
9
# Execute the query with the provided parameters
10
cursor = cnx.cursor()
11
cursor.execute(query, (name, email))
12
# Fetch the results of the stored procedure
13
results = cursor.fetchall()
14
# Close the database connection and return the results
15
cursor.close()
16
cnx.close()
17
return results
18
except Exception as e:
19
# Log any errors that occur
20
print(e)
21
return None
This function connects to the database using the URL we defined earlier and prepares a SQL query to call the stored procedure with the provided parameters. It then executes the query and fetches the results before closing the cursor and database connection and returning the results.
We can now define a Flask route that maps to this function and retrieves customer data from the database:
1
@app.route('/api/customers', methods=['GET'])
2
def get_customers():
3
try:
4
# Get the query parameters from the request URL
5
name = request.args.get('name')
6
email = request.args.get('email')
7
# Call the stored procedure function with the query parameters
8
results = execute_stored_procedure(name, email)
9
# Convert the results to a JSON response
10
response = jsonify(results)
11
return response
12
except Exception as e:
13
# Log any errors that occur
14
print(e)
15
return jsonify({'error': 'An error occurred'})
16
if __name__ == '__main__':
17
app.run()
This route retrieves the name of the query parameter and email from the request URL, calls the execute_stored_procedure function with these parameters, and then converts the results to a JSON response that is returned to the client.
Finally, we can start the Flask application from the command line:
1
python3 flaskapp.py
This will start the Flask development server and allow us to test our application by sending HTTP requests to the specified endpoint.
With this Flask application in place, we can now easily retrieve customer data from our database by simply sending a request to the /api/customers endpoint with the desired query parameters.
Testing the stored procedure with POSTMAN
Now that we have created our stored procedure and called it in our Python application, we can use POSTMAN to test the stored procedure and make sure it is working correctly. Here is how to test the stored procedure with POSTMAN:
If the stored procedure is working correctly, POSTMAN should return the expected result set. If there are any issues with the stored procedure, POSTMAN will display an error message that can help you diagnose the problem.
Conclusion
In this tutorial, we have explored how to call stored procedures in Python and test using POSTMAN and DbVisualizer. We covered the basics of creating procedures, calling stored procedures in Python, connecting to a MySQL database, and testing the procedures using both POSTMAN and DbVisualizer.
By the end of the tutorial, we have learned how to leverage stored procedures in Python applications, test them with POSTMAN, and manage databases with DbVisualizer. Using POSTMAN, we were able to make HTTP requests to our API and retrieve data from the database through our Flask application that calls a stored procedure. Furthermore, we created a stored procedure and visualized the results using DbVisualizer. Why not try DbVisualizer today and see how it can benefit you? Visit our blog for more information and until next time.
FAQ
What is a stored procedure?
A stored procedure is a set of pre-compiled SQL statements stored in the database. It allows you to encapsulate complex database operations and execute them with a single call. Stored procedures offer reusability, improve performance, and reduce data transfer between the database and the client.
How can I call a MySQL-stored procedure in Python?
To call a MySQL stored procedure in Python, you need to establish a connection to the database using a MySQL connector library. Prepare a SQL query that invokes the stored procedure with the required parameters. Execute the query using the connector's cursor, fetch the results if applicable, and handle any errors or exceptions.
Can I test a stored procedure using POSTMAN?
Yes, you can test a stored procedure using POSTMAN. POSTMAN is an API development tool that allows you to send HTTP requests and inspect responses. Set up a GET request to your Python API endpoint that calls the stored procedure, providing the necessary parameters. POSTMAN will display the response returned by the API, enabling you to validate the functionality of the stored procedure.
How can DbVisualizer help visualize database results?
DbVisualizer is a database management and visualization tool. It provides a user-friendly interface to explore and interact with databases. After executing a stored procedure that returns results, you can use DbVisualizer to connect to your database and view the returned data in a structured manner. DbVisualizer offers powerful data analysis and visualization capabilities to help you interpret and analyze the database results.
Can I use a different database engine instead of MySQL?
Yes, you can adapt the concepts and code demonstrated in the tutorial to work with different database engines that support stored procedures. While the tutorial focuses on MySQL, you can modify the code to suit other database engines like PostgreSQL, Oracle, SQL Server, or others. Keep in mind that syntax and specific features may vary between different database engines.
