Lesson 23: Database Connectivity in Python

🎯 Lesson Objective

To understand how to connect Python with a database, perform CRUD operations (Create, Read, Update, Delete), and handle data efficiently using the sqlite3 module.

---

🧩 1. What Is Database Connectivity?

Database connectivity allows a Python program to:

• Connect to a database (like SQLite, MySQL, PostgreSQL)
• Execute SQL queries
• Store, retrieve, and manage structured data

Python provides a built-in module called sqlite3 that works with SQLite — a lightweight database stored in a single file.

---

⚙️ 2. Importing the sqlite3 Module

import sqlite3

---

🧱 3. Connecting to a Database

import sqlite3

conn = sqlite3.connect("students.db")  # Creates a database file
print("Database connected successfully!")
conn.close()

Output:

Database connected successfully!

✅ If the file doesn’t exist, it will be automatically created.

---

🧰 4. Creating a Table

import sqlite3

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER,
    grade TEXT
)
""")

print("Table created successfully!")
conn.commit()
conn.close()

---

📝 5. Inserting Data into Table

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

cursor.execute("INSERT INTO students (name, age, grade) VALUES (?, ?, ?)", 
               ("Ali", 20, "A"))
cursor.execute("INSERT INTO students (name, age, grade) VALUES (?, ?, ?)", 
               ("Sara", 21, "B"))

conn.commit()
print("Data inserted successfully!")
conn.close()

---

📖 6. Reading (Fetching) Data

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

Output:

(1, 'Ali', 20, 'A')
(2, 'Sara', 21, 'B')

---

✏️ 7. Updating Data

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

cursor.execute("UPDATE students SET grade = ? WHERE name = ?", ("A+", "Sara"))

conn.commit()
print("Record updated successfully!")
conn.close()

---

❌ 8. Deleting Data

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

cursor.execute("DELETE FROM students WHERE name = ?", ("Ali",))

conn.commit()
print("Record deleted successfully!")
conn.close()

---

🔐 9. Using with Statement (Auto-Close Connection)

import sqlite3

with sqlite3.connect("students.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM students")
    print(cursor.fetchall())

✅ Using with automatically commits changes and closes the connection.

---

💡 10. Real-Life Example – Employee Database

import sqlite3

def create_table():
    conn = sqlite3.connect("company.db")
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        department TEXT,
        salary REAL
    )
    """)
    conn.commit()
    conn.close()

def insert_employee(name, department, salary):
    conn = sqlite3.connect("company.db")
    cursor = conn.cursor()
    cursor.execute("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)",
                   (name, department, salary))
    conn.commit()
    conn.close()

def view_employees():
    conn = sqlite3.connect("company.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM employees")
    for row in cursor.fetchall():
        print(row)
    conn.close()

# Usage
create_table()
insert_employee("Sameer", "IT", 75000)
insert_employee("Aisha", "HR", 50000)
view_employees()

Output:

(1, 'Sameer', 'IT', 75000.0)
(2, 'Aisha', 'HR', 50000.0)

---

📦 11. Using Pandas with SQLite

You can also read data directly into a DataFrame.

import pandas as pd
import sqlite3

conn = sqlite3.connect("company.db")
df = pd.read_sql_query("SELECT * FROM employees", conn)
print(df)
conn.close()

Output:

   id    name department   salary
0   1  Sameer        IT  75000.0
1   2   Aisha        HR  50000.0