Wednesday, October 15, 2025

Information Analytics Automation Scripts with SQL Saved Procedures


Information Analytics Automation Scripts with SQL Saved ProceduresPicture by Editor

 

Introduction

 
Information has turn into a neater commodity to retailer within the present digital period. With the benefit of getting plentiful information for enterprise, analyzing information to assist firms acquire perception has turn into extra important than ever.

In most companies, information is saved inside a structured database, and SQL is used to accumulate it. With SQL, we will question information within the type we wish, so long as the script is legitimate.

The issue is that, generally, the question to accumulate the information we wish is advanced and never dynamic. On this case, we will use SQL saved procedures to streamline tedious scripts into easy callables.

This text discusses creating information analytics automation scripts with SQL saved procedures.

Curious? Right here’s how.

 

SQL Saved Procedures

 
SQL saved procedures are a group of SQL queries saved straight throughout the database. If you’re adept in Python, you’ll be able to consider them as features: they encapsulate a sequence of operations right into a single executable unit that we will name anytime. It’s useful as a result of we will make it dynamic.

That’s why it’s useful to grasp SQL saved procedures, which allow us to simplify code and automate repetitive duties.

Let’s strive it out with an instance. On this tutorial, I’ll use MySQL for the database and inventory information from Kaggle for the desk instance. Arrange MySQL Workbench in your native machine and create a schema the place we will retailer the desk. In my instance, I created a database known as finance_db with a desk known as stock_data.

We are able to question the information utilizing one thing like the next.

USE finance_db;

SELECT * FROM stock_data;

 

Basically, a saved process has the next construction.

DELIMITER $$
CREATE PROCEDURE procedure_name(param_1, param_2, . . ., param_n)
BEGIN
    instruct_1;
    instruct_2;
    . . .
    instruct_n;
END $$
DELIMITER ;

 

As you’ll be able to see, the saved process can obtain parameters which can be handed into our question.

Let’s study an precise implementation. For instance, we will create a saved process to mixture inventory metrics for a selected date vary.

USE finance_db;
DELIMITER $$
CREATE PROCEDURE AggregateStockMetrics(
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    SELECT
        COUNT(*) AS TradingDays,
        AVG(Shut) AS AvgClose,
        MIN(Low) AS MinLow,
        MAX(Excessive) AS MaxHigh,
        SUM(Quantity) AS TotalVolume
    FROM stock_data
    WHERE 
        (p_StartDate IS NULL OR Date >= p_StartDate)
      AND (p_EndDate IS NULL OR Date <= p_EndDate);
END $$
DELIMITER ;

 

Within the question above, we created the saved process named AggregateStockMetrics. This process accepts a begin date and finish date as parameters. The parameters are then used as circumstances to filter the information.

You may name the saved process like this:

CALL AggregateStockMetrics('2015-01-01', '2015-12-31');

 

The process will execute with the parameters we cross. Because the saved process is saved within the database, you should utilize it from any script that connects to the database containing the process.

With saved procedures, we will simply reuse logic in different environments. For instance, I’ll name the process from Python utilizing the MySQL connector.

To do this, first set up the library:

pip set up mysql-connector-python

 

Then, create a perform that connects to the database, calls the saved process, retrieves the end result, and closes the connection.

import mysql.connector

def call_aggregate_stock_metrics(start_date, end_date):
    cnx = mysql.connector.join(
        consumer="your_username",
        password='your_password',
        host="localhost",
        database="finance_db"
    )
    cursor = cnx.cursor()
    strive:
        cursor.callproc('AggregateStockMetrics', [start_date, end_date])
        outcomes = []
        for lead to cursor.stored_results():
            outcomes.lengthen(end result.fetchall())
        return outcomes
    lastly:
        cursor.shut()
        cnx.shut()

 

The end result will probably be much like the output under.

[(39, 2058.875660431691, 1993.260009765625, 2104.27001953125, 140137260000.0)]

 

That’s all it’s worthwhile to learn about SQL saved procedures. You may lengthen this additional for automation utilizing a scheduler in your pipeline.

 

Wrapping Up

 
SQL saved procedures present a technique to encapsulate advanced queries into dynamic, single-unit features that may be reused for repetitive information analytics duties. The procedures are saved throughout the database and are simple to make use of from completely different scripts or purposes similar to Python.

I hope this has helped!
 
 

Cornellius Yudha Wijaya is a knowledge science assistant supervisor and information author. Whereas working full-time at Allianz Indonesia, he likes to share Python and information ideas through social media and writing media. Cornellius writes on quite a lot of AI and machine studying matters.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest Articles