Module-5

Database Programming: Introduction,

Python Database Application Programmer’s
Interface (DB-API), Object Relational
Managers (ORMs), Related Modules.
 1. What is Database Programming?
Database programming refers to the process of creating,
accessing, and managing data stored in databases using
programming languages.

It allows applications to interact with databases — for storing,

retrieving, updating, and deleting data efficiently and
securely. It forms the backbone of most modern applications,
such as:
• Banking systems
• E-commerce websites
• School management software
• Hospital record systems
• Social media platforms
2. What is a Database?
A database is an organized collection of data
that is stored and accessed electronically.
It can be thought of as a digital filing system
that stores information in a structured way.
Example:
ID Name Course Marks
1 Abhishek B.Tech 85
2 Aditi M.Tech 90

This is a table inside a database.

3. Why Database Programming?
Reason Description

Organizes and handles large amounts

Data Management
of data efficiently.

Ensures only authorized access to

Data Security
data.

Maintains accuracy and consistency

Data Integrity
of stored data.

Allows developers to automate data

Automation
operations through code.

Enables applications to use real-time

Integration data (like stock updates or user
accounts).
4. Components of Database Programming
(a) Database Management System (DBMS)
Software that manages data and provides tools
for interaction.
Examples: MySQL, Oracle, PostgreSQL, SQLite,
MS SQL Server.
(b) Query Language
Used to perform operations on the database.
The most common one is SQL (Structured Query
Language).
 Examples of SQL commands:
SELECT * FROM students;
INSERT INTO students VALUES (1, 'Neel', 'B.Tech',
85);
UPDATE students SET marks = 90 WHERE id = 1;
DELETE FROM students WHERE id = 2;
(c) Programming Language Interface
A general-purpose programming language (like
Python, Java, or C#) communicates with the
database through APIs or connectors.
 5. Database Programming with Python
Python provides several libraries to connect and
work with databases:
Library Used For

sqlite3 Lightweight local database

mysql.connector / PyMySQL MySQL and MariaDB databases

psycopg2 PostgreSQL
cx_Oracle Oracle Database

ORM (Object Relational

SQLAlchemy
Mapping) for multiple DBs
Example: Using MySQL in Python
6. Key Concepts in Database Programming
Concept Description

Establish link between program and DB

Connection
server.

Cursor Object used to execute SQL queries.

Running SQL statements from within the

Query Execution
program.

Ensures all database operations complete

Transaction Management
successfully (commit / rollback).

Managing database errors gracefully in

Error Handling
code.
 7. Advantages of Database Programming

• Centralized data management

• Improved performance and security
• Real-time data access
• Reduced redundancy
• Scalable for large applications
8. Applications of Database Programming

• Online shopping platforms (Amazon, Flipkart)

• Banking and ATM systems
• Hospital management software
• Library management systems
• College admission and attendance systems
 Python Database Application
Programmer’s Interface (DB-API)
1. Introduction
• The Python Database API (DB-API) is a standard
interface that allows Python programs to connect
to and interact with relational databases (like
MySQL, PostgreSQL, SQLite, Oracle, etc.) in a
consistent way — regardless of the database being
used.
• It is defined in PEP 249 (Python Enhancement
Proposal 249). This standard ensures that all
database modules behave similarly, making it easy
to switch databases with minimal code changes.
 Python Database Application
Programmer’s Interface (DB-API)
2. Why Use DB-API?
• Provides a consistent programming interface for
all databases.
• Makes it easier to write portable and reusable
code.
• Handles connections, queries, transactions, and
error management efficiently.
• Allows integration of database operations into
Python applications seamlessly.
3. DB-API Architecture
The DB-API architecture consists of three main
layers:
4. Main Objects and Functions in DB-API
(a) connect()
Used to establish a connection to the database.
4. Main Objects and Functions in DB-API
(b) cursor()
A cursor object allows interaction with the database
— you can execute SQL commands and fetch results.

(c) execute()
Executes an SQL query.

(d) fetchone(), fetchall(), fetchmany()

Retrieve query results.
4. Main Objects and Functions in DB-API
(e) commit() and rollback()
Used for transaction control.

(f) close()
Always close the cursor and connection after
completing operations.
5. DB-API Modules in Python
Database DB-API Module
SQLite sqlite3
mysql.connector,
MySQL
PyMySQL
PostgreSQL psycopg2
Oracle cx_Oracle
MS SQL Server pyodbc
All these modules follow the same DB-API structure,
which ensures code uniformity.
7. Advantages of Using DB-API
• Uniform interface across databases
• Reduces development time
• Easier migration between databases
• Handles errors and transactions reliably
• Well-supported by most Python database modules
8. Summary

Feature Description
PEP 249 Defines the DB-API standard

connect() Establishes database connection

Executes and manages SQL
cursor()
queries
execute() Runs SQL commands

fetch Retrieves data

commit()/rollback() Transaction control

close() Releases database resources

 Object Relational Managers (ORMs)
Definition:
An Object Relational Manager (ORM) is a
programming technique that acts as a bridge
between object-oriented programming (OOP)
languages (like Python, Java, etc.) and relational
databases (like MySQL, PostgreSQL, or SQLite).
It allows developers to interact with the
database using Python objects and classes
instead of writing raw SQL queries.
1. Purpose of ORM
• The main goal of ORM is to:
• Eliminate the need to write complex SQL
queries.
• Simplify data manipulation using Python’s
object-oriented approach.
• Maintain consistency between the program’s
objects and database tables.
Example (Using SQLAlchemy ORM in
Python)
 5. Popular ORM Frameworks in Python
ORM Tool Description
Most widely used ORM in Python,
SQLAlchemy
supports multiple databases.

Built into Django web framework;

Django ORM
simplifies database operations.

Peewee Lightweight ORM for small projects.

Uses Python-like queries for better

PonyORM
readability.

Modern, asyncio-based ORM for

Tortoise ORM
Python 3.7+.
6. ORM Architecture
ORM typically consists of:
• Object Mapper: Maps classes ↔ tables,
attributes ↔ columns.
• Query Builder: Converts Python expressions to
SQL statements.
• Session/Unit of Work: Tracks object changes and
handles transactions.
• Dialect: Translates ORM commands to a specific
database language (MySQL, SQLite, etc.).
 Related Modules- Data Base
Programming
• These modules allow you to connect Python
programs (including GUI apps) to databases,
execute SQL commands, and manage data
efficiently.
 Related Modules in Database
Programming (Python)
1. sqlite3 (Built-in Module)
Purpose: Interface to the lightweight SQLite
database (no server required).
Usage: Perfect for standalone applications and
Tkinter-based desktop apps.
Features:
– Built into Python (no installation needed)
– Stores database in a single .db file
– Fully supports SQL commands
Example:
 Related Modules in Database
Programming (Python)
2. MySQL Connector / PyMySQL
Purpose: To connect Python with MySQL
databases.
Common Libraries:
– mysql.connector (official Oracle connector)
– PyMySQL (pure Python version)
Features:
– Used in enterprise and web applications
– Supports parameterized queries and transactions
– Compatible with XAMPP/WAMP MySQL servers
Example:
 Related Modules in Database
Programming (Python)
3. cx_Oracle
Purpose: Connects Python to Oracle Database.
Use Case: Enterprise-grade applications.
Features:
– Supports Oracle’s advanced data types
– Provides fast data fetching and transactions
Example:
 Related Modules in Database
Programming (Python)
4. psycopg2 (PostgreSQL)
Purpose: Interface for PostgreSQL, a robust
open-source SQL database.
Features:
– Fully compliant with Python DB-API
– Supports transactions and connection pooling
Example:
 Related Modules in Database
Programming (Python)
5. SQLAlchemy (ORM Layer)
Purpose: High-level Object Relational Mapper
(ORM) built on top of DB-API.
Use Case: Avoids writing raw SQL; interacts with
databases using Python objects.
Supports: MySQL, SQLite, PostgreSQL, Oracle,
etc.
Example:
 Related Modules in Database
Programming (Python)
6. PyODBC
Purpose: Connects to any ODBC-compliant
database (SQL Server, Access, etc.).
Features:
– Cross-database compatibility
– Uses ODBC drivers installed on the system
Example:
 Summary Table
Database
Module Name Key Feature Built-in
Supported
Lightweight, file-
sqlite3 SQLite Yes
based DB
Official MySQL
mysql.connector MySQL No
support
Pure Python
PyMySQL MySQL No
MySQL driver
Enterprise-level
cx_Oracle Oracle No
DB interface
Most popular
psycopg2 PostgreSQL PostgreSQL No
adapter
ORM abstraction
SQLAlchemy Multi-DB ORM No
layer
Connects to many
PyODBC ODBC Databases DBs via ODBC No
drivers