Building Mini-Applications Using Databases: A Complete Guide for Python Developers

Table of Contents

• Introduction
• Why Build Mini-Applications?
• Setting Up the Environment
• Project 1: Simple To-Do List App (SQLite)
  • Project Structure
  • Database Schema
  • CRUD Operations
  • Sample Code
• Project 2: Contact Manager App (PostgreSQL)
  • Project Structure
  • Database Schema
  • Key Functionalities
  • Sample Code
• Project 3: Notes API (Flask + MongoDB)
  • Project Structure
  • API Endpoints
  • MongoDB Collections
  • Sample Code
• Best Practices for Database-Driven Applications
• Conclusion

Introduction

Building mini-applications using databases is an excellent way to reinforce your knowledge of Python, SQL, and database management systems.
These projects simulate real-world application development, giving you practical experience with designing schemas, handling connections, performing CRUD operations, and structuring maintainable codebases.

In this module, you will learn how to build database-driven mini-projects step-by-step, covering everything from schema design to application logic implementation.

Why Build Mini-Applications?

Here are several reasons why working on mini-projects is crucial:

• Solidifies Core Concepts: Reinforces learning through application.
• Portfolio Building: Mini-projects make excellent portfolio pieces for job applications.
• Real-World Simulation: Get hands-on experience with scenarios like user management, data validation, transactions, and error handling.
• Database Best Practices: Understand normalization, indexing, and query optimization.

Setting Up the Environment

Make sure you have the following installed:

• Python 3.10+
• pip (Python package installer)
• SQLite3 (built-in with Python)
• PostgreSQL (and psycopg2 package)
• MongoDB (local or cloud instance like Atlas)
• Flask (for building simple APIs)

Install necessary packages:

pip install sqlalchemy psycopg2-binary pymongo flask

Project 1: Simple To-Do List App (SQLite)

Project Structure

todo_app/
├── database.py
├── models.py
├── app.py
└── requirements.txt

Database Schema

We will have a single table called tasks:

CRUD Operations

We will perform:

• Create: Add a new task
• Read: View all tasks
• Update: Mark task as complete
• Delete: Remove a task

Sample Code

database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///tasks.db')
Session = sessionmaker(bind=engine)
session = Session()

models.py

from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Task(Base):
    __tablename__ = 'tasks'

    id = Column(Integer, primary_key=True)
    description = Column(String, nullable=False)
    completed = Column(Boolean, default=False)

app.py

from database import session
from models import Base, Task
from sqlalchemy import create_engine

engine = create_engine('sqlite:///tasks.db')
Base.metadata.create_all(engine)

def add_task(description):
    task = Task(description=description)
    session.add(task)
    session.commit()

def list_tasks():
    tasks = session.query(Task).all()
    for task in tasks:
        print(f"{task.id}: {task.description} [{'Done' if task.completed else 'Pending'}]")

def mark_done(task_id):
    task = session.query(Task).filter_by(id=task_id).first()
    if task:
        task.completed = True
        session.commit()

def delete_task(task_id):
    task = session.query(Task).filter_by(id=task_id).first()
    if task:
        session.delete(task)
        session.commit()

Project 2: Contact Manager App (PostgreSQL)

Project Structure

contact_manager/
├── database.py
├── models.py
├── app.py
└── requirements.txt

Database Schema

Key Functionalities

• Add a new contact
• Search contacts by name
• Update contact information
• Delete a contact

Sample Code

database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = 'postgresql+psycopg2://username:password@localhost/contactdb'
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)
session = Session()

models.py

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Contact(Base):
    __tablename__ = 'contacts'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    phone = Column(String, unique=True)
    email = Column(String, unique=True)

app.py

from database import session
from models import Base, Contact

Base.metadata.create_all()

def add_contact(name, phone, email):
    contact = Contact(name=name, phone=phone, email=email)
    session.add(contact)
    session.commit()

def find_contact(name):
    return session.query(Contact).filter(Contact.name.ilike(f"%{name}%")).all()

def update_contact(contact_id, phone=None, email=None):
    contact = session.query(Contact).filter_by(id=contact_id).first()
    if phone:
        contact.phone = phone
    if email:
        contact.email = email
    session.commit()

def delete_contact(contact_id):
    contact = session.query(Contact).filter_by(id=contact_id).first()
    if contact:
        session.delete(contact)
        session.commit()

Project 3: Notes API (Flask + MongoDB)

Project Structure

notes_api/
├── app.py
├── requirements.txt

API Endpoints

• POST /notes: Create a new note
• GET /notes: List all notes
• GET /notes/<id>: Get a specific note
• PUT /notes/<id>: Update a note
• DELETE /notes/<id>: Delete a note

MongoDB Collections

Collection: notes

Each document:

{
  "_id": "ObjectId",
  "title": "Meeting Notes",
  "content": "Discuss project roadmap",
  "timestamp": "ISO8601 DateTime"
}

Sample Code

app.py

from flask import Flask, request, jsonify
from pymongo import MongoClient
from bson.objectid import ObjectId
from datetime import datetime

app = Flask(__name__)
client = MongoClient('mongodb://localhost:27017/')
db = client['notesdb']
notes_collection = db['notes']

@app.route('/notes', methods=['POST'])
def create_note():
    data = request.json
    data['timestamp'] = datetime.utcnow()
    result = notes_collection.insert_one(data)
    return jsonify({"id": str(result.inserted_id)}), 201

@app.route('/notes', methods=['GET'])
def get_notes():
    notes = list(notes_collection.find())
    for note in notes:
        note['_id'] = str(note['_id'])
    return jsonify(notes)

@app.route('/notes/<id>', methods=['GET'])
def get_note(id):
    note = notes_collection.find_one({'_id': ObjectId(id)})
    if note:
        note['_id'] = str(note['_id'])
        return jsonify(note)
    return jsonify({'error': 'Note not found'}), 404

@app.route('/notes/<id>', methods=['PUT'])
def update_note(id):
    data = request.json
    result = notes_collection.update_one({'_id': ObjectId(id)}, {'$set': data})
    if result.matched_count:
        return jsonify({'success': True})
    return jsonify({'error': 'Note not found'}), 404

@app.route('/notes/<id>', methods=['DELETE'])
def delete_note(id):
    result = notes_collection.delete_one({'_id': ObjectId(id)})
    if result.deleted_count:
        return jsonify({'success': True})
    return jsonify({'error': 'Note not found'}), 404

if __name__ == '__main__':
    app.run(debug=True)

Best Practices for Database-Driven Applications

• Always validate and sanitize user input.
• Implement proper error handling for database failures.
• Close database connections properly.
• Use environment variables for sensitive data (like database credentials).
• Create indexes on frequently queried fields to improve performance.
• Use migrations tools like Alembic for schema changes.
• Always backup databases in production environments.

Conclusion

Building mini-applications using databases is one of the most effective ways to master backend development concepts in Python.
By creating To-Do lists, contact managers, and simple APIs, you not only strengthen your Python skills but also become comfortable with real-world database interactions.