Working with Databases in Flask

# CHAPTER 8

Working with Databases in Flask #

1. Introduction #

2. Learning Objectives #

• Understand how databases integrate with Flask.

• Differentiate between raw SQL queries and an ORM.

• Identify the role of SQLite for development.

• Configure a basic raw database connection in Python.

3. Beginner-Friendly Explanation #

• The Application (Flask): The Librarian at the front desk.

• The Database: The massive warehouse in the back containing thousands of books (Data).

4. The Database Choice #

1. 1. Raw SQL: You write raw SQL strings (e.g., SELECT * FROM users) inside your Python code. Fast, but dangerous (SQL Injection) and hard to maintain.

1. 2. An ORM (Object-Relational Mapper): You define Python Classes, and a third-party library automatically translates your Python code into perfect SQL. This is the industry standard.

The undisputed king of Python ORMs is SQLAlchemy. We will use the Flask-SQLAlchemy extension to bind it to our application.

5. Development vs. Production Databases #

• SQLite (Development): A lightweight database that stores everything inside a single file (e.g., app.db) on your hard drive. It requires zero installation. It is perfect for learning and building prototypes on your laptop.

• MySQL / PostgreSQL (Production): Heavy-duty database servers that run independently. They handle thousands of simultaneous connections. When you deploy your app to the internet, you MUST switch to one of these.

*The beauty of an ORM like SQLAlchemy is that you can develop using SQLite, and switch to MySQL later by changing exactly ONE line of code. The ORM handles the SQL translation differences automatically!*

6. Step 1: Installing the Extension #

pip install Flask-SQLAlchemy

7. Step 2: Configuring the Connection #

In app.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

# Get the absolute path of the directory containing app.py
basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)

# Configure the SQLite database location
# This creates a file named 'app.db' in the same folder as app.py
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'app.db')

# Disable a feature that takes up unnecessary memory
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Initialize the Database Assistant (SQLAlchemy) and bind it to our App
db = SQLAlchemy(app)

@app.route('/')
def home():
    return "Database Configured!"

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

8. Backend Workflow: Connection Strings (URIs) #

• SQLite Example: 'sqlite:///app.db' (Talks to a local file).

• MySQL Example: 'mysql+pymysql://username:password@localhost/dbname'

• PostgreSQL Example: 'postgresql://username:password@localhost/dbname'

When you are ready to launch your startup, you simply change this string from SQLite to your live cloud MySQL server, and the application continues working identically.

9. Best Practices #

• Security: Notice the MySQL connection string contains a password. NEVER type your live database password directly into app.py and upload it to GitHub. You must use Environment Variables (which we will cover in Chapter 19) to inject the connection string securely from a hidden file.

10. Common Mistakes #

• ModuleNotFoundError: If you run the code and Python crashes complaining about flasksqlalchemy, it means you forgot to install it, OR you installed it globally instead of inside your active (venv). Always ensure your virtual environment is activated before running pip install.

17. Next Chapter Recommendation #