Python - Database Programming

Before learning Python Database Programming, first we should understand where data is stored in an application and why databases are required.

As part of an application, we may need to store different types of information.

• Customer Information
• Billing Information
• Calls Information
• Other Application Data

To store this information, we require Storage Areas.

Application
    │
    ▼
Generates Data
    │
    ▼
Needs Storage
    │
    ▼
Storage Areas
        

A Storage Area is a location where application data can be stored.

Storage Areas are divided into two types:

1. Temporary Storage Areas
2. Permanent Storage Areas

                 Storage Areas
                      │
          ┌───────────┴───────────┐
          │                       │
          ▼                       ▼
Temporary Storage          Permanent Storage
     Areas                       Areas
        

The major difference between them is how long the stored data remains available.

Temporary Storage Areas are memory locations where data is stored only for a short period of time.

In Python, examples include:

• List
• Tuple
• Dictionary

These Python objects hold data while the program is running.

students = ["Durga", "Ravi", "Sunny"]

employee = (100, "Durga", 1000)

customer = {
    "id": 101,
    "name": "Durga"
}
        

All these objects can hold information during program execution. They are considered temporary because the data exists only in program memory and is not automatically persisted after the program finishes.

Once the Python program completes its execution:

• The objects are destroyed automatically.
• The stored data is lost.

Program Running
      │
      ▼
List / Tuple / Dictionary
      │
      ▼
Data Available
      │
      ▼
Program Completes
      │
      ▼
Objects Destroyed
      │
      ▼
Stored Data Lost
        

Therefore, Temporary Storage Areas are not suitable for storing data permanently.

Characteristic	Description
Storage Duration	Short-term
Data Availability	Normally available while the program is running
Examples	List, Tuple, Dictionary
After Program Ends	Objects are destroyed and non-persisted data is lost
Permanent Storage	No

Permanent Storage Areas are also called Persistent Storage Areas.

These storage areas are used when application data must be stored permanently.

Examples include:

• File Systems
• Databases
• Data Warehouses
• Big Data Technologies

             Permanent Storage Areas
                       │
        ┌──────────────┼──────────────┐
        │              │              │
        ▼              ▼              ▼
   File Systems    Databases    Data Warehouses
                                      │
                                      ▼
                            Big Data Technologies
        

Unlike temporary storage, persistent storage keeps data available even after the application stops.

Temporary Storage	Permanent Storage
Stores data temporarily	Stores data persistently
Data is associated with program execution	Data remains available after program execution
Examples: List, Tuple, Dictionary	Examples: File System, Database, Data Warehouse, Big Data Technologies
Not suitable for permanent application records	Suitable for persistent application records

A File System is one type of Permanent Storage Area.

A File System is provided by the local operating system.

It is suitable for storing a small amount of information.

Application
    │
    ▼
Operating System
    │
    ▼
File System
    │
    ▼
Files
    │
    ▼
Persistent Data
        

Although File Systems provide permanent storage, they have several limitations for larger and more complex data-management requirements.

There are four major limitations of File Systems:

1. Cannot Store Huge Amount of Information
2. No Query Language Support
3. No Security
4. Duplicate Data Problem

               File System
                    │
      ┌─────────────┼─────────────┐
      │             │             │
      ▼             ▼             ▼
Limited for      No Query      No Database-
Huge Data        Language      Level Security
                    │
                    ▼
             Duplicate Data
                    │
                    ▼
           Data Inconsistency
        

A File System is not suitable for storing a very large amount of data, does not provide SQL-style query processing automatically, lacks a database-level security mechanism, and has no constraint mechanism to prevent duplicate records.

For example, the same record may be stored multiple times:

100, Durga, 1000, Hyd
100, Durga, 1000, Hyd
100, Durga, 1000, Hyd
        

Repeated information can create a Data Inconsistency Problem.

To overcome the limitations of File Systems, we can use Databases.

Databases provide better facilities for storing, organizing, searching, securing, and managing application data.

File System Problems
        │
        ├── Limited for huge data
        ├── No query-language support
        ├── Limited database-level security
        └── Duplicate-data problems
        │
        ▼
Need Better Data Management
        │
        ▼
     Database
        

A Database is a Permanent Storage Area used to store and manage application data in an organized manner.

Databases provide several advantages over File Systems:

1. Can Store Huge Amount of Information - a Database can store a very large amount of structured information compared with ordinary File System storage.
2. Query Language Support - every Database provides query-language support. For relational databases, SQL is commonly used, which makes operations such as Create, Insert, Select, Update, and Delete easier.
3. Data Security - to access data present inside a Database, valid credentials such as a username and password are required. Invalid credentials are denied.
4. No Data Duplication - while designing database tables, techniques such as Normalization, Unique Key Constraints, and Primary Key Constraints help control duplicate data.

Database Table Design
        │
        ├── Normalization
        ├── Unique Key
        └── Primary Key
        │
        ▼
Reduce / Prevent
Unwanted Duplication
        │
        ▼
Better Data Consistency
        

Consider an employee table:

eno	ename	esal	eaddr
100	Durga	1000	Hyd
200	Ravi	2000	Mumbai

If eno is declared as a Primary Key, duplicate employee numbers are not allowed.

eno = 100
    │
    ▼
Already Exists
    │
    ▼
Duplicate Primary-Key Value
Not Allowed
        

File System	Database
Suitable for comparatively small/simple storage requirements	Suitable for managing large structured datasets
No built-in SQL-style query language	Provides query-language support
No database-level security by itself	Provides authentication/security mechanisms
Duplicate-data control is difficult	Constraints and normalization help control duplication
Complex data operations may require custom programming	Queries make many data operations easier

Although Databases solve many File System problems, they also have some limitations:

1. Cannot Hold Extremely Huge Data - a traditional Database is not intended for extremely huge amounts of information such as very large Terabyte-scale datasets.
2. Supports Mainly Structured Data - traditional relational Databases mainly support structured, tabular, and relational data arranged in rows and columns.

To overcome these limitations, more advanced storage technologies such as Big Data Technologies and Data Warehouses are used.

Data Type	Examples
Structured Data	Tables, rows, columns, relational records
Semi-Structured Data	XML Files
Unstructured Data	Video Files, Audio Files, Images

Sometimes, as part of programming requirements, we need to connect a Python program with a Database.

After establishing the connection, the Python application can perform different database operations:

• Creating Tables
• Inserting Data
• Updating Data
• Deleting Data
• Selecting Data

The process of communicating with a Database from a Python program is called Python Database Programming.

Python Program
      │
      ▼
Database Connection
      │
      ▼
Database
      │
      ├── Create
      ├── Insert
      ├── Update
      ├── Delete
      └── Select
        

Operation	Purpose
Create	Create database objects such as tables
Insert	Add new records
Update	Modify existing records
Delete	Remove records
Select	Retrieve records

We use SQL to communicate with relational Databases. SQL stands for Structured Query Language.

Python is used to send these SQL commands to the Database through an appropriate database driver/module.

• SQL describes the Database operation.
• Python sends or executes those SQL commands through the database interface.

Python Program
      │
      ▼
SQL Command
      │
      ▼
Database Driver
      │
      ▼
Database
      │
      ▼
Operation Performed
        

For example, suppose Python sends the following SQL command:

SELECT * FROM employees;
        

The Database executes the query and returns the employee records.

Technology	Responsibility
Python	Application logic and sending/executing SQL through a database module
SQL	Describes operations to perform on relational database data
Database	Stores and manages persistent data and executes supported queries

Several databases can be accessed from a Python program:

• Oracle
• MySQL
• SQL Server
• GadFly
• SQLite

                   Python
                      │
      ┌───────────────┼───────────────┐
      │               │               │
      ▼               ▼               ▼
    Oracle           MySQL         SQL Server
      │                               │
      └──────────┐           ┌────────┘
                 ▼           ▼
               GadFly      SQLite
        

Python database communication requires an appropriate database-specific module or driver.

Database	Module
Oracle Database	cx_Oracle
Microsoft SQL Server	pymssql

For example, a Python program uses cx_Oracle to communicate with an Oracle Database, and pymssql to communicate with Microsoft SQL Server.

The overall execution flow of Python database programming looks like this:

Program Starts
      │
      ▼
Python Application
      │
      ▼
Connect to Database
      │
      ▼
Send SQL Command
      │
      ▼
Database Executes Command
      │
      ▼
Database Operation
      │
      ├── Create Table
      ├── Insert Data
      ├── Update Data
      ├── Delete Data
      └── Select Data
      │
      ▼
Return Result if Required
      │
      ▼
Python Program
      │
      ▼
Program Ends
        

The exact connection and execution steps are covered in the next parts of this tutorial.