Python for Data Science & Automation — 7.6 Python Database Views, Stored Procedures & Advanced SQL | Tutorials

7.6 Python Database Views, Stored Procedures & Advanced SQL

As database applications grow, placing every SQL operation directly inside Python code can make the application difficult to maintain. Database features such as views, stored procedures, stored functions, and advanced SQL queries can move appropriate data-processing logic closer to the database.

Python can execute these database objects just like other SQL operations. This is particularly useful for reporting systems, ERP applications, school-management systems, dashboards, and automated data-processing workflows.

Core Idea:

Use Python for application orchestration and business workflows, while using database features appropriately for reusable data access, reporting, and database-side operations.

1. Learning Objectives

By the end of this lesson, you will be able to:

Understand database views.
Create SQL views.
Query views from Python.
Use views for reporting.
Understand stored procedures.
Create stored procedures.
Call stored procedures from Python.
Understand stored functions.
Use conditional SQL expressions.
Use subqueries.
Use Common Table Expressions.
Combine SQL aggregation with Python.
Build reusable reporting queries.
Understand when database-side logic is appropriate.

2. What Is a Database View?

A view is a database object that presents the result of a SQL query as a virtual table.

Instead of repeatedly writing a complex query, an application can query the view.

Complex SQL Query
       ↓
     VIEW
       ↓
Python Application

3. Why Use Views?

Simplify complex queries.
Reuse frequently required reporting logic.
Provide a consistent data interface.
Hide unnecessary table complexity from application code.
Support reporting and dashboards.
Restrict exposed columns where appropriate.

4. Creating a Simple View

CREATE VIEW student_class_view AS

SELECT
    s.id AS student_id,
    s.name AS student_name,
    c.class_name

FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id;

The view now provides a reusable representation of student and class information.

5. Querying a View

SELECT *
FROM student_class_view;

A view can be queried much like a table.

6. Querying a View from Python

cursor.execute(
    """
    SELECT *
    FROM student_class_view
    ORDER BY student_name
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

7. View with Filtering

cursor.execute(
    """
    SELECT *
    FROM student_class_view
    WHERE class_name = %s
    """,
    (
        "Class XII A",
    )
)

rows = cursor.fetchall()

A view does not prevent the application from applying additional filtering, sorting, grouping, or other query operations.

8. View for Academic Reporting

CREATE VIEW student_marks_view AS

SELECT
    s.id AS student_id,
    s.name AS student_name,
    c.class_name,
    sub.subject_name,
    e.exam_name,
    m.marks

FROM marks AS m

INNER JOIN students AS s
    ON m.student_id = s.id

INNER JOIN classes AS c
    ON s.class_id = c.id

INNER JOIN subjects AS sub
    ON m.subject_id = sub.id

INNER JOIN exams AS e
    ON m.exam_id = e.id;

This type of view can provide a standardized source for academic reports.

9. Using the Academic View in Python

import pandas as pd


query = """
SELECT
    student_name,
    class_name,
    subject_name,
    exam_name,
    marks

FROM student_marks_view

ORDER BY
    class_name,
    student_name,
    subject_name
"""


df = pd.read_sql(
    query,
    connection
)


print(df)

10. Views for School Dashboards

A reporting view can provide the data required by a dashboard without requiring the dashboard code to understand every underlying table relationship.

Database Tables
       ↓
Reporting View
       ↓
Python / pandas
       ↓
Dashboard
       ↓
Charts & KPIs

Examples include class-wise strength, subject averages, examination performance, attendance summaries, and result statistics.

11. Aggregated Reporting View

CREATE VIEW class_result_summary AS

SELECT
    c.id AS class_id,
    c.class_name,
    COUNT(m.id) AS assessment_count,
    AVG(m.marks) AS average_marks

FROM classes AS c

LEFT JOIN students AS s
    ON c.id = s.class_id

LEFT JOIN marks AS m
    ON s.id = m.student_id

GROUP BY
    c.id,
    c.class_name;

12. Reading the Summary View in Python

cursor.execute(
    """
    SELECT *
    FROM class_result_summary
    ORDER BY average_marks DESC
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

13. Updating a View

A view definition can be modified using database-specific syntax such as CREATE OR REPLACE VIEW, where supported.

CREATE OR REPLACE VIEW student_class_view AS

SELECT
    s.id AS student_id,
    s.name AS student_name,
    c.class_name

FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id;

14. Removing a View

DROP VIEW student_class_view;

Dropping a view removes the view definition. It does not delete the underlying base-table records.

15. What Is a Stored Procedure?

A stored procedure is a named collection of SQL statements stored in the database and executed when called.

Python Application
       ↓
CALL Procedure
       ↓
Database
       ↓
SQL Operations
       ↓
Result

16. Why Use Stored Procedures?

Encapsulate reusable database operations.
Centralize selected database-side logic.
Reduce repeated SQL in applications.
Provide a consistent database operation.
Support complex multi-step database workflows.
Can simplify application-level database calls.

17. Creating a Stored Procedure

DELIMITER //

CREATE PROCEDURE get_student_by_id(
    IN p_student_id INT
)

BEGIN

    SELECT
        id,
        name,
        class_id

    FROM students

    WHERE id = p_student_id;

END //

DELIMITER ;

The IN parameter allows the caller to provide a value to the procedure.

18. Calling a Stored Procedure in SQL

CALL get_student_by_id(10);

19. Calling a Stored Procedure from Python

cursor.callproc(
    "get_student_by_id",
    (10,)
)

MySQL Connector/Python provides callproc() for calling stored procedures.

20. Reading Stored Procedure Results

results = cursor.stored_results()

for result in results:

    rows = result.fetchall()

    for row in rows:

        print(row)

The exact handling of returned result sets depends on the connector and procedure design.

21. Stored Procedure with Multiple Operations

DELIMITER //

CREATE PROCEDURE increase_stock(
    IN p_product_id INT,
    IN p_quantity INT
)

BEGIN

    UPDATE products

    SET quantity = quantity + p_quantity

    WHERE id = p_product_id;

END //

DELIMITER ;

22. Calling the Stock Procedure from Python

cursor.callproc(
    "increase_stock",
    (
        10,
        25
    )
)

connection.commit()

If the procedure performs data-changing operations, transaction handling should be designed deliberately rather than assuming every procedure call is automatically committed.

23. Stored Procedure with Validation

DELIMITER //

CREATE PROCEDURE add_stock(
    IN p_product_id INT,
    IN p_quantity INT
)

BEGIN

    IF p_quantity <= 0 THEN

        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT =
            'Quantity must be positive';

    END IF;


    UPDATE products

    SET quantity = quantity + p_quantity

    WHERE id = p_product_id;

END //

DELIMITER ;

MySQL's SIGNAL statement can be used to raise a database-side error for invalid conditions.

24. Handling a Procedure Error in Python

try:

    cursor.callproc(
        "add_stock",
        (
            10,
            -5
        )
    )

    connection.commit()

except mysql.connector.Error as error:

    connection.rollback()

    print(
        "Database operation failed:",
        error
    )

25. Procedure Parameters

Parameter	Purpose
IN	Receives input from the caller
OUT	Returns a value through an output parameter
INOUT	Receives and can return an updated value

26. OUT Parameter Concept

DELIMITER //

CREATE PROCEDURE get_student_count(
    OUT p_count INT
)

BEGIN

    SELECT COUNT(*)
    INTO p_count
    FROM students;

END //

DELIMITER ;

An OUT parameter allows a stored procedure to return a value to the caller.

27. INOUT Parameter Concept

DELIMITER //

CREATE PROCEDURE add_bonus(
    INOUT p_amount DECIMAL(10,2)
)

BEGIN

    SET p_amount =
        p_amount + 100;

END //

DELIMITER ;

An INOUT parameter can receive an initial value and return a modified value.

28. What Is a Stored Function?

A stored function is a database routine designed to return a value.

It can be useful for reusable calculations that belong naturally within the database layer.

CREATE FUNCTION
    calculate_grade(...)

RETURNS ...

BEGIN

    ...

END

29. Stored Function Example

DELIMITER //

CREATE FUNCTION calculate_grade(
    p_marks DECIMAL(5,2)
)

RETURNS VARCHAR(2)

DETERMINISTIC

BEGIN

    IF p_marks >= 90 THEN

        RETURN 'A+';

    ELSEIF p_marks >= 80 THEN

        RETURN 'A';

    ELSEIF p_marks >= 70 THEN

        RETURN 'B';

    ELSEIF p_marks >= 60 THEN

        RETURN 'C';

    ELSE

        RETURN 'D';

    END IF;

END //

DELIMITER ;

30. Using a Stored Function

SELECT
    student_id,
    marks,
    calculate_grade(marks) AS grade
FROM marks;

31. Calling the Function from Python

cursor.execute(
    """
    SELECT
        student_id,
        marks,
        calculate_grade(marks) AS grade
    FROM marks
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

32. SQL CASE Expression

SQL's CASE expression can perform conditional calculations directly inside a query.

SELECT
    student_id,
    marks,

    CASE

        WHEN marks >= 90 THEN 'A+'

        WHEN marks >= 80 THEN 'A'

        WHEN marks >= 70 THEN 'B'

        WHEN marks >= 60 THEN 'C'

        ELSE 'D'

    END AS grade

FROM marks;

For a simple query, a CASE expression may be preferable to creating a stored function.

33. CASE with Python Reporting

query = """
SELECT
    s.name AS student_name,
    m.marks,

    CASE

        WHEN m.marks >= 90 THEN 'A+'
        WHEN m.marks >= 80 THEN 'A'
        WHEN m.marks >= 70 THEN 'B'
        WHEN m.marks >= 60 THEN 'C'
        ELSE 'D'

    END AS grade

FROM marks AS m

INNER JOIN students AS s
    ON m.student_id = s.id
"""


df = pd.read_sql(
    query,
    connection
)

34. What Is a Subquery?

A subquery is a query nested inside another SQL statement.

SELECT
    name

FROM students

WHERE id IN (

    SELECT student_id
    FROM marks
);

35. Subquery with Aggregation

SELECT
    student_id,
    marks

FROM marks

WHERE marks > (

    SELECT AVG(marks)
    FROM marks

);

This returns marks that are above the overall average.

36. Correlated Subquery

A correlated subquery references a value from the outer query.

SELECT
    s.name

FROM students AS s

WHERE EXISTS (

    SELECT 1

    FROM marks AS m

    WHERE m.student_id = s.id

);

The inner query uses s.id from the outer query.

37. EXISTS

EXISTS tests whether a subquery returns at least one row.

SELECT
    s.name

FROM students AS s

WHERE EXISTS (

    SELECT 1

    FROM marks AS m

    WHERE m.student_id = s.id
);

38. NOT EXISTS

NOT EXISTS can identify records without a related record.

SELECT
    s.name

FROM students AS s

WHERE NOT EXISTS (

    SELECT 1

    FROM marks AS m

    WHERE m.student_id = s.id
);

This can identify students who do not yet have marks recorded.

39. Common Table Expressions

A Common Table Expression, or CTE, uses the WITH clause to define a temporary named result for a query.

WITH student_average AS (

    SELECT
        student_id,
        AVG(marks) AS average_marks

    FROM marks

    GROUP BY student_id
)

SELECT
    s.name,
    sa.average_marks

FROM student_average AS sa

INNER JOIN students AS s
    ON sa.student_id = s.id;

40. Why Use CTEs?

Improve readability of complex queries.
Break complex logic into logical steps.
Reuse a derived result within a query.
Make reporting SQL easier to maintain.

41. CTE with Python

query = """
WITH student_average AS (

    SELECT
        student_id,
        AVG(marks) AS average_marks

    FROM marks

    GROUP BY student_id
)

SELECT
    s.name AS student_name,
    sa.average_marks

FROM student_average AS sa

INNER JOIN students AS s
    ON sa.student_id = s.id

ORDER BY
    sa.average_marks DESC
"""


df = pd.read_sql(
    query,
    connection
)

42. Window Functions

Window functions perform calculations across related rows while retaining individual result rows.

SELECT
    student_id,
    marks,

    AVG(marks) OVER () AS overall_average

FROM marks;

43. Ranking Students

SELECT
    student_id,
    AVG(marks) AS average_marks,

    RANK() OVER (
        ORDER BY AVG(marks) DESC
    ) AS student_rank

FROM marks

GROUP BY student_id;

This type of query is useful for analytical reports and ranking calculations.

44. Partitioning a Window Function

SELECT
    student_id,
    subject_id,
    marks,

    RANK() OVER (
        PARTITION BY subject_id
        ORDER BY marks DESC
    ) AS subject_rank

FROM marks;

PARTITION BY allows the calculation to restart for each group.

45. Window Functions in Python

query = """
SELECT
    student_id,
    subject_id,
    marks,

    RANK() OVER (
        PARTITION BY subject_id
        ORDER BY marks DESC
    ) AS subject_rank

FROM marks
"""


df = pd.read_sql(
    query,
    connection
)


print(df)

46. SQL Aggregation vs pandas Aggregation

SQL	pandas
GROUP BY	`groupby()`
AVG()	`mean()`
COUNT()	`count()`
SUM()	`sum()`
ORDER BY	`sort_values()`

47. Where Should Aggregation Happen?

The choice between SQL and pandas depends on the workload.

Large Database
      ↓
Filter / Aggregate in SQL
      ↓
Smaller Result
      ↓
pandas
      ↓
Analysis / Visualization

Performing appropriate filtering and aggregation in the database can reduce the amount of data transferred to Python.

48. Parameterized SQL with Advanced Queries

Parameterized queries should continue to be used even when the SQL becomes complex.

query = """
SELECT
    s.name,
    AVG(m.marks) AS average_marks

FROM students AS s

INNER JOIN marks AS m
    ON s.id = m.student_id

WHERE s.class_id = %s

GROUP BY
    s.id,
    s.name

HAVING AVG(m.marks) >= %s

ORDER BY
    average_marks DESC
"""


cursor.execute(
    query,
    (
        12,
        75
    )
)

49. Avoid SQL String Concatenation

# Avoid

class_name = input(
    "Enter class: "
)

query = (
    "SELECT * FROM students "
    "WHERE class_name = '"
    + class_name
    + "'"
)

Build queries with parameters instead.

cursor.execute(
    """
    SELECT *
    FROM students
    WHERE class_id = %s
    """,
    (
        class_id,
    )
)

50. Database Logic vs Application Logic

Database Layer	Python Application Layer
Joins	User interface
Aggregation	Application workflow
Constraints	Input handling
Views	Reporting presentation
Stored procedures	Service orchestration
Indexes	API integration

The boundary should be chosen according to application architecture, maintainability, security, performance, and team requirements.

51. When Views Are Useful

Repeated reporting queries.
Complex multi-table joins.
Standardized reporting interfaces.
Dashboards.
Read-heavy applications.
Providing controlled access to selected columns.

52. When Stored Procedures Are Useful

Complex database-side operations.
Reusable multi-step database workflows.
Legacy systems that already rely heavily on procedures.
Centralized database operations required by multiple clients.
Operations that naturally belong within the database layer.

53. Potential Drawbacks of Stored Procedures

Database-specific syntax can reduce portability.
Business logic may become distributed across layers.
Testing can become more complex.
Version control requires disciplined database migration practices.
Debugging can require knowledge of both application and database code.

Best Practice:

Do not put every piece of application logic into stored procedures. Use them deliberately where they provide a clear architectural benefit.

54. View vs Stored Procedure vs Function

Feature	View	Procedure	Function
Main Purpose	Reusable query result	Reusable database operation	Reusable calculation returning a value
Called with	SELECT	CALL	SQL expression
Returns	Rows/columns	May return result sets/output values	A value
Typical Use	Reports	Operations	Calculations

55. Calling a View from a Python Reporting Function

def get_result_report(
    connection
):

    query = """
    SELECT
        student_name,
        class_name,
        subject_name,
        exam_name,
        marks

    FROM student_marks_view

    ORDER BY
        class_name,
        student_name
    """

    return pd.read_sql(
        query,
        connection
    )

56. Building a Reusable Report

def generate_class_report(
    connection,
    class_name
):

    query = """
    SELECT
        student_name,
        subject_name,
        exam_name,
        marks

    FROM student_marks_view

    WHERE class_name = %s

    ORDER BY
        student_name,
        subject_name
    """

    return pd.read_sql(
        query,
        connection,
        params=(
            class_name,
        )
    )

57. Advanced Reporting Workflow

MySQL
  ↓
Views / SQL Queries
  ↓
Python
  ↓
pandas
  ↓
Data Cleaning
  ↓
Aggregation
  ↓
Matplotlib / Seaborn / Plotly
  ↓
Report / Dashboard

This architecture combines the strengths of relational databases, Python, pandas, and visualization libraries.

58. Performance Considerations

Advanced SQL should be written with performance in mind.

Select only required columns.
Filter data appropriately.
Use suitable indexes.
Avoid unnecessary joins.
Understand query execution plans.
Avoid transferring millions of unnecessary rows to Python.
Use pagination for large result sets where appropriate.

59. EXPLAIN

MySQL's EXPLAIN statement can help inspect how the database plans to execute a query.

EXPLAIN
SELECT
    s.name,
    c.class_name

FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id

WHERE c.id = 12;

The execution plan can help identify inefficient scans, join strategies, and index usage.

60. Python Performance Workflow

Slow Approach

Database
   ↓
Huge Result
   ↓
Python
   ↓
Filter
   ↓
Aggregate


Better Approach

Database
   ↓
Filter
   ↓
JOIN
   ↓
Aggregate
   ↓
Smaller Result
   ↓
Python
   ↓
Analysis

61. Common Advanced SQL Mistakes

Mistake	Problem	Better Practice
Overusing stored procedures	Logic becomes difficult to maintain	Use procedures selectively
Repeated complex queries	Code duplication	Consider an appropriate view
Fetching unnecessary rows	Higher memory and network usage	Filter in SQL
Fetching unnecessary columns	Extra data transfer	Select required columns
Ignoring indexes	Queries can become slow	Analyze query workload and indexes
Building SQL with user input	Security risk	Use parameterized queries
Duplicating business rules	Different layers may disagree	Define clear ownership of rules

62. Practical Project — School Result Reporting System

Project Objective

Build a Python reporting application that retrieves academic data from MySQL and produces class-wise and student-wise performance reports.

Database Objects

students
classes
subjects
exams
marks
student_marks_view

63. Project Workflow

MySQL Tables
     ↓
Create Reporting View
     ↓
Python Connection
     ↓
Read View
     ↓
pandas DataFrame
     ↓
Clean / Transform
     ↓
Calculate Statistics
     ↓
Create Charts
     ↓
Generate Report

64. Project Python Example

import pandas as pd


query = """
SELECT
    student_name,
    class_name,
    subject_name,
    exam_name,
    marks

FROM student_marks_view
"""


df = pd.read_sql(
    query,
    connection
)


class_summary = (
    df
    .groupby("class_name")["marks"]
    .mean()
    .reset_index()
)


print(class_summary)

65. Practical Examination Tasks

Task 1 — Create a View

Create a view that displays student names, classes, subjects, examinations, and marks.

Task 2 — Python View Query

Write a Python program that reads the view and displays the result.

Task 3 — Stored Procedure

Create a stored procedure that accepts a student ID and returns the student's academic records.

Task 4 — Stored Function

Create a stored function that converts marks into a grade.

Task 5 — CTE

Use a Common Table Expression to calculate student averages and then retrieve students above a selected threshold.

Task 6 — Python + pandas

Load the final query into a DataFrame and create a class-wise performance summary.

66. Interview Questions

Q1. What is a database view?

View Answer

A view is a database object that presents the result of a stored query as a virtual table.

Q2. Why are views useful?

View Answer

Views can simplify complex queries, centralize reusable reporting logic, and provide a consistent data interface.

Q3. What is a stored procedure?

View Answer

A stored procedure is a named collection of SQL statements stored in the database and executed when called.

Q4. How can Python call a MySQL stored procedure?

View Answer

MySQL Connector/Python provides the callproc() method for calling stored procedures.

Q5. What is a stored function?

View Answer

A stored function is a database routine designed to return a value and can be used within SQL expressions.

Q6. What is a CTE?

View Answer

A Common Table Expression is a named temporary result defined using the WITH clause for use within a SQL statement.

Q7. What is a subquery?

View Answer

A subquery is a SQL query nested inside another SQL statement.

Q8. What is a window function?

View Answer

A window function performs a calculation across related rows while retaining the individual rows in the result.

Q9. When might a view be preferable to repeating a complex JOIN?

View Answer

When the same complex query is used repeatedly, a view can provide a reusable and standardized query interface.

Q10. Should all business logic be placed in stored procedures?

View Answer

No. Stored procedures should be used selectively. The appropriate division between database logic and application logic depends on architecture, maintainability, portability, security, and performance requirements.

67. Examination MCQs

Q1. Which database object behaves like a virtual table?

View
Index
Trigger
Schema

Answer: A

Q2. Which Python method can call a stored procedure using MySQL Connector/Python?

callproc()
callsql()
runproc()
executeproc()

Answer: A

Q3. Which SQL keyword is used to define a CTE?

DEFINE
WITH
CTE
TEMP

Answer: B

Q4. Which SQL expression is useful for conditional values?

CASE
SWITCH
IFPYTHON
WHENONLY

Answer: A

Q5. Which SQL feature can calculate rankings while retaining individual rows?

Window function
DROP
TRUNCATE
VIEW only

Answer: A

Q6. Which clause starts a Common Table Expression?

START
WITH
CREATE
TEMP

Answer: B

Q7. Which parameter type supplies input to a stored procedure?

IN
OUT
RETURN
INPUTONLY

Answer: A

Q8. Which SQL feature can provide a reusable complex query interface?

View
Variable
Comment
Alias only

Answer: A

Q9. What does EXPLAIN help analyze?

Python indentation
SQL execution plans
HTML structure
Excel formatting

Answer: B

Q10. Which approach is recommended for user-supplied SQL values?

String concatenation
Parameterized queries
Manual escaping only
Removing quotation marks

Answer: B

68. Coding Challenge — Python Academic Analytics

Challenge

Build a Python application that uses MySQL views and advanced SQL to generate an academic-performance report.

Requirements

Create a student marks view.
Query the view from Python.
Load the results into pandas.
Calculate student averages.
Calculate class averages.
Rank students within each subject.
Identify students above the overall average.
Generate a class-wise summary.
Export the final DataFrame to Excel.

69. Self-Assessment Checklist

You should now be able to:

☐ Explain database views.
☐ Create and query views.
☐ Use views from Python.
☐ Explain stored procedures.
☐ Create basic stored procedures.
☐ Call procedures from Python.
☐ Understand IN, OUT, and INOUT parameters.
☐ Explain stored functions.
☐ Use SQL CASE expressions.
☐ Write subqueries.
☐ Use EXISTS and NOT EXISTS.
☐ Write CTEs.
☐ Use window functions.
☐ Combine advanced SQL with pandas.
☐ Understand database/application logic boundaries.
☐ Consider SQL query performance.

70. Key Takeaway

Advanced database features can make Python applications more structured and maintainable. Views simplify reusable queries, stored procedures encapsulate database operations, functions handle reusable calculations, and advanced SQL features such as CTEs and window functions support sophisticated reporting.

For real-world Python systems, the goal is not to move all logic into SQL or all logic into Python. Instead, design a clear boundary between application logic and database logic based on maintainability, performance, security, and business requirements.