🗄️ Flask Database Fundamentals: Your Data’s Perfect Home

Imagine you’re building a magical library. Books need shelves. Shelves need a room. And someone needs to keep track of where everything goes. That’s exactly what databases do for your Flask app!

🌟 The Big Picture: What’s This All About?

Think of your Flask app as a bakery. You bake delicious treats (handle web requests), but where do you store recipes, customer orders, and inventory? You need a storage system — that’s your database!

SQLAlchemy is like hiring a super-smart librarian who:

• Speaks both “Python” and “Database language”
• Organizes everything perfectly
• Finds things instantly when you ask

📚 Chapter 1: SQLAlchemy Introduction

What is SQLAlchemy?

SQLAlchemy is a translator. Your Flask app speaks Python. Your database speaks SQL. SQLAlchemy helps them talk to each other!

graph TD
    A[🐍 Your Python Code] --> B[🔄 SQLAlchemy]
    B --> C[🗄️ Database]
    C --> B
    B --> A

Why Use It?

Without SQLAlchemy:

# Scary SQL strings everywhere!
cursor.execute(
  "SELECT * FROM users WHERE id=?"
  , (user_id,))

With SQLAlchemy:

# Clean Python code!
user = User.query.get(user_id)

It’s like ordering food by pointing at pictures instead of speaking a foreign language!

🔧 Chapter 2: Flask-SQLAlchemy Setup

Setting up is like preparing your kitchen before cooking.

Step 1: Install the Package

pip install flask-sqlalchemy

Step 2: Connect to Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

That’s it! You now have a database assistant (db) ready to help.

What Just Happened?

🔗 Chapter 3: Database URI Configuration

The Database URI is like your database’s home address. It tells Flask exactly where to store data.

The Address Format

database_type://username:password@host:port/database_name

Common Examples

SQLite (Perfect for Learning):

app.config['SQLALCHEMY_DATABASE_URI'] = \
    'sqlite:///myapp.db'

Creates a file called myapp.db in your project!

PostgreSQL (For Real Apps):

app.config['SQLALCHEMY_DATABASE_URI'] = \
    'postgresql://user:pass@localhost/mydb'

Quick Reference

graph TD
    A[Database URI] --> B[sqlite:///file.db]
    A --> C[postgresql://...]
    A --> D[mysql://...]
    B --> E[📁 Local File]
    C --> F[🌐 Server]
    D --> F

Pro Tip: Silence the Warning

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

This stops an annoying warning message!

🏗️ Chapter 4: Creating Database Models

A model is like a blueprint. Before building a house, you draw plans. Before storing data, you create models!

Your First Model

class User(db.Model):
    id = db.Column(db.Integer,
        primary_key=True)
    name = db.Column(db.String(100))
    email = db.Column(db.String(120))

What This Creates

Think of It Like This

📦 User Model = Filing Cabinet Drawer
├── 📄 id = Drawer Number (unique!)
├── 📄 name = Name Label
└── 📄 email = Contact Info

🧱 Chapter 5: SQLAlchemy Column Types

Columns are like labeled jars in your kitchen. Each jar holds a specific type of ingredient!

The Essential Types

Examples in Action

class Product(db.Model):
    id = db.Column(db.Integer,
        primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    price = db.Column(db.Float)
    in_stock = db.Column(db.Boolean)
    created_at = db.Column(db.DateTime)

Matching Types to Data

graph TD
    A[Your Data] --> B{What type?}
    B -->|Numbers only| C[Integer]
    B -->|Has decimals| D[Float]
    B -->|Words| E[String/Text]
    B -->|Yes or No| F[Boolean]
    B -->|When something happened| G[DateTime]

🔑 Chapter 6: Primary Keys

Every item needs a unique label. In a library, it’s the book ID. In a database, it’s the primary key!

What Makes It Special?

• Unique: No two rows can have the same value
• Never empty: Always has a value
• Identifies: Points to exactly one record

The Standard Pattern

class Book(db.Model):
    id = db.Column(db.Integer,
        primary_key=True)
    title = db.Column(db.String(200))

SQLAlchemy automatically numbers each new book: 1, 2, 3…

Why Primary Keys Matter

graph TD
    A[🔑 Primary Key] --> B[Unique ID]
    A --> C[Auto-Increments]
    A --> D[Never Null]
    B --> E[Find Exact Record]

🚧 Chapter 7: Column Constraints

Constraints are rules for your data. Like a bouncer at a club — only the right data gets in!

Common Constraints

Putting It Together

class User(db.Model):
    id = db.Column(db.Integer,
        primary_key=True)

    username = db.Column(db.String(80),
        unique=True,
        nullable=False)

    email = db.Column(db.String(120),
        unique=True,
        nullable=False)

    active = db.Column(db.Boolean,
        default=True)

What These Rules Do

username:
  ✅ Must be filled in (nullable=False)
  ✅ Can't match another user (unique=True)

email:
  ✅ Must be filled in
  ✅ Can't match another user

active:
  ✅ Automatically True for new users

🎬 Chapter 8: Creating Database Tables

You’ve drawn the blueprints (models). Now let’s build the house (create tables)!

The Magic Command

with app.app_context():
    db.create_all()

This reads all your models and creates matching tables!

Complete Working Example

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# Setup
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = \
    'sqlite:///shop.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] \
    = False

db = SQLAlchemy(app)

# Model
class Product(db.Model):
    id = db.Column(db.Integer,
        primary_key=True)
    name = db.Column(db.String(100),
        nullable=False)
    price = db.Column(db.Float,
        default=0.0)

# Create tables!
with app.app_context():
    db.create_all()

print("Database ready! 🎉")

What Happens When You Run This?

graph TD
    A[Run Python Script] --> B[Flask App Created]
    B --> C[SQLAlchemy Connected]
    C --> D[Models Read]
    D --> E[Tables Created]
    E --> F[🎉 shop.db File Exists!]

🎯 Quick Summary

🚀 You Did It!

You now understand how Flask talks to databases. It’s like learning a superpower — you can store, organize, and retrieve data like a pro!

Remember:

1. 📦 Models = Your data’s blueprint
2. 🔑 Primary Keys = Unique IDs
3. 🚧 Constraints = Data rules
4. ✨ create_all() = Make it real!

Next up: Adding, reading, updating, and deleting data. But that’s another adventure!