Python Database Communication (PDBC)

In real-world software, storing data in text files is slow and insecure. Applications store structured data inside Relational Databases like MySQL, SQLite, Oracle, or PostgreSQL.

PDBC (Python Database Communication) is the standard mechanism (defined by Python DB-API 2.0 / PEP 249) that allows a Python program to connect to a database and execute SQL queries (CREATE, INSERT, SELECT, UPDATE, DELETE).

1. Database Drivers Used in PDBC

To connect Python with a specific database, we import its corresponding driver module:

Database System	Python Driver / Module	Installation Command
SQLite (Serverless, File-based)	`sqlite3`	Built-in with Python (No install needed!)
MySQL	`mysql.connector`	`pip install mysql-connector-python`
PostgreSQL	`psycopg2`	`pip install psycopg2`
Oracle	`cx_Oracle` / `oracledb`	`pip install oracledb`

2. The 5 Standard Steps of PDBC (Exam Must-Know!)

Whether you use SQLite or MySQL, every PDBC program follows the exact same 5 steps:

5 Steps of Python Database Communication (PDBC)

Step 1: Import Driver

import sqlite3
or mysql.connector

Step 2: Connect

con = connect(...)
Establishes DB link

Step 3: Create Cursor

cur = con.cursor()
SQL execution handle

Step 4: Execute SQL

cur.execute(sql)
& con.commit()

Step 5: Close

cur.close()
con.close()

3. Important PDBC Methods Explained

Method	Object	Description
`connect()`	Module	Opens a connection between Python and the Database
`con.cursor()`	Connection	Creates a Cursor object (a pointer/workspace used to send SQL queries and fetch results)
`cur.execute(query)`	Cursor	Executes a single SQL query (`CREATE`, `INSERT`, `SELECT`, etc.)
`cur.executemany(q, list)`	Cursor	Executes the same SQL query for multiple rows at once
`cur.fetchone()`	Cursor	Fetches the next single row from a `SELECT` result as a tuple
`cur.fetchall()`	Cursor	Fetches all remaining rows from a `SELECT` result as a list of tuples
`cur.fetchmany(n)`	Cursor	Fetches `n` rows from the query result
`con.commit()`	Connection	Permanently saves changes (`INSERT`, `UPDATE`, `DELETE`) to the database
`con.rollback()`	Connection	Undoes uncommitted changes if an error occurs
`con.close()`	Connection	Closes the database connection and frees resources

4. Complete PDBC Program 1: Using Built-in `sqlite3`

This complete program creates a college.db database, creates a students table, inserts records, and fetches them:

# Step 1: Import the database module
import sqlite3

# Step 2: Establish connection (creates 'college.db' if not present)
con = sqlite3.connect("college.db")

# Step 3: Create a Cursor object
cur = con.cursor()

# Step 4(a): Create Table using execute()
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        roll_no INT PRIMARY KEY,
        name TEXT,
        marks FLOAT
    )
""")

# Step 4(b): Insert Data & Commit Changes
cur.execute("INSERT INTO students VALUES (101, 'Aarav', 88.5)")
cur.execute("INSERT INTO students VALUES (102, 'Ishita', 92.0)")
con.commit()  # Saves the inserted rows permanently!

# Step 4(c): Fetch and Display Records using SELECT and fetchall()
cur.execute("SELECT * FROM students")
rows = cur.fetchall()

print("Roll | Name   | Marks")
print("---------------------")
for row in rows:
    print(f"{row[0]}  | {row[1]} | {row[2]}")

# Step 5: Close Cursor and Connection
cur.close()
con.close()

5. Complete PDBC Program 2: Connecting Python with MySQL

When connecting to a MySQL Server in exams, use mysql.connector:

import mysql.connector

try:
    # Step 1 & 2: Connect to MySQL Server
    con = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="davv_bca_db"
    )

    # Step 3: Create Cursor
    cur = con.cursor()

    # Step 4: Execute Parameterized SQL Query
    sql = "INSERT INTO students (roll_no, name, marks) VALUES (%s, %s, %s)"
    val = (103, "Rohan", 85.0)
    cur.execute(sql, val)

    # Save changes permanently
    con.commit()
    print(cur.rowcount, "record inserted successfully into MySQL!")

except mysql.connector.Error as err:
    print("Database Error:", err)
    con.rollback()  # Undo changes on error

finally:
    # Step 5: Close connection
    if con.is_connected():
        cur.close()
        con.close()

Python Database Communication (PDBC): MySQL & SQLite Connectivity