Data Analytics Automation Script with SQL Stord Procedure

Image by editor

. Introduction

Data has become an easy commodity to store in the current digital period. With the benefit of having sufficient data for business, analysis of data has become more important than ever before.

In most businesses, data is stored in a structural database, and SQL is used to achieve it. With the SQL, we can inquire from the data in the form we want, unless the script is correct.

The problem is that, sometimes, the data we want to get the data is complicated and not dynamic. In this case, we can use SQL storage procedures to smooth the painful script in simple callables.

This article discusses the creation of data analtics automation script with the SQL stord procedure.

Careful How is it here

. SQL STORD Method

The SQL Street method is a set of SQL questions that are stored directly within the database. If you are an expert in the midwife, you can think of them as functions: they wrap up a series of tasks in a single processing unit that we can call at any time. This is beneficial because we can make it vibrant.

That is why the SQL is helpful in understanding the storage procedure, which allows us to simplify the code and automatically automatically work.

Let’s try it with an example. In this tutorial, I will use SQL For the database and Stock data From Kagal for an example of the table. Set up an SQL work bench on your local machine and create a scheme where we can store the table. In my example, I created a database named finance_db Called with a table stock_data.

We can inquire from data as we can use the following.

USE finance_db;

SELECT * FROM stock_data;

Generally, the stored procedure contains the following structure.

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

As you can see, the stored procedure can receive parameters that are transferred to our inquiry.

Let’s look at a real implementation. For example, we can create stored procedures to collect the stock matrix for a specified date limit.

USE finance_db;
DELIMITER $$
CREATE PROCEDURE AggregateStockMetrics(
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    SELECT
        COUNT(*) AS TradingDays,
        AVG(Close) AS AvgClose,
        MIN(Low) AS MinLow,
        MAX(High) AS MaxHigh,
        SUM(Volume) 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 ;

In the aforementioned question, we created the designated stored procedure AggregateStockMetrics. This procedure accepts a starting date and end date as parameters. The parameters are then used as conditions to filter the data.

You can call the stored procedure like this:

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

The procedure will be implemented with the parameters we have. Since the stored procedure is stored in the database, you can use it from any script that is connected to the database containing the procedure.

With stored procedures, we can easily reuse logic in other environments. For example, I will call the procedure using SQL connector.

To do this, install the first library:

pip install mysql-connector-python

Then, create a function that is connected to the database, call the stored procedure, recover the result, and close the connection.

import mysql.connector

def call_aggregate_stock_metrics(start_date, end_date):
    cnx = mysql.connector.connect(
        user="your_username",
        password='your_password',
        host="localhost",
        database="finance_db"
    )
    cursor = cnx.cursor()
    try:
        cursor.callproc('AggregateStockMetrics', (start_date, end_date))
        results = ()
        for result in cursor.stored_results():
            results.extend(result.fetchall())
        return results
    finally:
        cursor.close()
        cnx.close()

The result will be like an output below.

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

That’s all you need to know about the SQL Street procedure. You can further expand this by using the shadower in your pipeline.

. Wrap

The SQL stord procedure provides complex questions to stimulate, covering in single unit functions that can be reused for repeated data analytics. The procedure is stored within the database and is easy to use from various scripts or applications such as applications.

I hope it has helped!

Cornelius Yodha Vijaya Data Science is Assistant Manager and Data Writer. Elijan, working in Indonesia for a full time, likes to share indicators of data and data through social media and written media. CorneLius writes on various types of AI and machine learning titles.