13. Database Programming in Python

  • Home
  • Blog
  • 13. Database Programming in Python
Featured

13. Database Programming in Python

May 25, 2026 in Python

Database programming allows Python applications to:

  • Store data permanently
  • Retrieve records
  • Update information
  • Delete records

Python supports many databases such as:

  • SQLite
  • MySQL
  • PostgreSQL
  • MongoDB

SQLite with Python

SQLite is a lightweight built-in database.

Features:

  • No separate server required
  • Easy to use
  • Stored in a single file

Python provides:

sqlite3

module.

Import SQLite

import sqlite3

Create Database Connection

import sqlite3

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

print("Database connected")

This creates:

students.db

file.

Create Cursor Object

Cursor executes SQL queries.

cursor = conn.cursor()

Create Table

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

conn.commit()

Insert Data

cursor.execute("""
INSERT INTO students(name, age)
VALUES (?, ?)
""", ("Aditya", 25))

conn.commit()

Read Data

cursor.execute("SELECT * FROM students")

rows = cursor.fetchall()

for row in rows:
    print(row)

Output Example:

(1, 'Aditya', 25)

Update Data

cursor.execute("""
UPDATE students
SET age = 26
WHERE name = 'Aditya'
""")

conn.commit()

Delete Data

cursor.execute("""
DELETE FROM students
WHERE name = 'Aditya'
""")

conn.commit()

Close Connection

conn.close()

Complete SQLite Example

import sqlite3

conn = sqlite3.connect("school.db")

cursor = conn.cursor()

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

cursor.execute("""
INSERT INTO students(name, marks)
VALUES (?, ?)
""", ("Rahul", 90))

conn.commit()

cursor.execute("SELECT * FROM students")

for row in cursor.fetchall():
    print(row)

conn.close()

MySQL Connectivity

MySQL is a popular relational database management system.

Python connects to MySQL using:

mysql-connector-python

Install MySQL Connector

pip install mysql-connector-python

Import MySQL Connector

import mysql.connector

Connect to MySQL

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="school"
)

print("Connected")

Create Cursor

cursor = conn.cursor()

Create Table in MySQL

cursor.execute("""
CREATE TABLE IF NOT EXISTS students(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
)
""")

Insert Data in MySQL

sql = """
INSERT INTO students(name, age)
VALUES (%s, %s)
"""

values = ("Aditya", 25)

cursor.execute(sql, values)

conn.commit()

Read Data

cursor.execute("SELECT * FROM students")

for row in cursor.fetchall():
    print(row)

Update Data

sql = """
UPDATE students
SET age = %s
WHERE name = %s
"""

values = (26, "Aditya")

cursor.execute(sql, values)

conn.commit()

Delete Data

sql = """
DELETE FROM students
WHERE name = %s
"""

cursor.execute(sql, ("Aditya",))

conn.commit()

Close MySQL Connection

conn.close()

CRUD Operations

CRUD stands for:

OperationMeaning
CreateInsert data
ReadRetrieve data
UpdateModify data
DeleteRemove data

CRUD Example with SQLite

import sqlite3

conn = sqlite3.connect("employee.db")

cursor = conn.cursor()

# Create
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees(
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary INTEGER
)
""")

# Insert
cursor.execute("""
INSERT INTO employees(name, salary)
VALUES (?, ?)
""", ("Rahul", 50000))

# Read
cursor.execute("SELECT * FROM employees")

print(cursor.fetchall())

# Update
cursor.execute("""
UPDATE employees
SET salary = 60000
WHERE name = 'Rahul'
""")

# Delete
cursor.execute("""
DELETE FROM employees
WHERE name = 'Rahul'
""")

conn.commit()

conn.close()

ORM Basics

ORM stands for:

Object Relational Mapping

ORM allows working with databases using Python objects instead of SQL queries.

Popular Python ORM:

  • SQLAlchemy
  • Django ORM

Advantages of ORM

✅ Less SQL code
✅ Easier database handling
✅ Better readability
✅ Database portability

SQLAlchemy Installation

pip install sqlalchemy

SQLAlchemy Example

from sqlalchemy import create_engine

engine = create_engine("sqlite:///students.db")

print(engine)

Define ORM Model

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class Student(Base):

    __tablename__ = "students"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

Create Table

Base.metadata.create_all(engine)

Insert Data Using ORM

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

session = Session()

student = Student(
    name="Aditya",
    age=25
)

session.add(student)

session.commit()

Fetch Data Using ORM

students = session.query(Student).all()

for student in students:
    print(student.name, student.age)

Django ORM Example

from django.db import models

class Student(models.Model):

    name = models.CharField(max_length=100)
    age = models.IntegerField()

Run Migrations

python manage.py makemigrations
python manage.py migrate

Practical Example

Student Record System

import sqlite3

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

cursor = conn.cursor()

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

name = input("Enter name: ")
marks = int(input("Enter marks: "))

cursor.execute("""
INSERT INTO students(name, marks)
VALUES (?, ?)
""", (name, marks))

conn.commit()

cursor.execute("SELECT * FROM students")

for row in cursor.fetchall():
    print(row)

conn.close()
🇮🇳 Amazon India 🌍 Amazon Global

Best Practices

✅ Use parameterized queries
✅ Close database connections
✅ Use ORM for large projects
✅ Handle exceptions properly
✅ Avoid hardcoded credentials

Summary

In this chapter you learned:

✅ SQLite with Python
✅ MySQL connectivity
✅ CRUD operations
✅ ORM basics
✅ SQLAlchemy basics
✅ Django ORM basics

aditya singh