Flask-SQLAlchemy setup - Mini Project: Build & Apply

Flask-SQLAlchemy setup

📖 Scenario: You are building a simple web app to store book information. You want to save book titles and authors in a database using Flask and SQLAlchemy.

🎯 Goal: Create a Flask app with SQLAlchemy configured, define a Book model, and set up the database connection.

📋 What You'll Learn

Create a Flask app instance named app

Configure the app with SQLALCHEMY_DATABASE_URI set to sqlite:///books.db

Create a SQLAlchemy object named db linked to the app

Define a Book model with id, title, and author fields

Ensure the id is the primary key and an integer

Use db.Column and appropriate types for fields

💡 Why This Matters

🌍 Real World

Flask with SQLAlchemy is commonly used to build web apps that need to store and manage data in databases.

💼 Career

Understanding how to set up Flask with SQLAlchemy is essential for backend web development roles using Python.

1

Create Flask app instance

Write code to import Flask from flask and create a Flask app instance called app.

Flask

# Import Flask and create app instance
# Your code here

Hint

Use Flask(__name__) to create the app.

2

Configure database URI

Add a configuration line to app.config setting SQLALCHEMY_DATABASE_URI to 'sqlite:///books.db'.

Flask

from flask import Flask
app = Flask(__name__)
# Configure SQLALCHEMY_DATABASE_URI
# Your code here

Hint

Use app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'.

3

Create SQLAlchemy object

Import SQLAlchemy from flask_sqlalchemy and create a db object by passing app to SQLAlchemy.

Flask

from flask import Flask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'
# Import SQLAlchemy and create db object
# Your code here

Hint

Use db = SQLAlchemy(app) after importing.

4

Define Book model

Define a class Book that inherits from db.Model. Add three fields: id as an integer primary key, title as a string of max length 100, and author as a string of max length 50. Use db.Column and appropriate types.

Flask

from flask import Flask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)

# Define Book model
# Your code here

Hint

Define Book class with fields using db.Column and types.

Practice

1. What is the main purpose of Flask-SQLAlchemy in a Flask application?

easy

A. To create HTML templates for the Flask app

B. To connect the Flask app to a database and manage data easily

C. To handle user authentication and sessions

D. To serve static files like images and CSS

Solution

1. Step 1: Understand Flask-SQLAlchemy role

   Flask-SQLAlchemy is a tool that helps Flask apps talk to databases easily.

2. Step 2: Compare with other options

   Options B, C, and D relate to other Flask features, not database connection.

3. Final Answer:

   To connect the Flask app to a database and manage data easily -> Option B

4. Quick Check:

   Flask-SQLAlchemy = database connection [OK]

Hint: Flask-SQLAlchemy is about databases, not templates or files [OK]

Common Mistakes:

• Confusing Flask-SQLAlchemy with template engines
• Thinking it manages user sessions
• Assuming it serves static files

2. Which of the following is the correct way to initialize Flask-SQLAlchemy in your Flask app?

easy

A. db = SQLAlchemy(app)

B. db = SQLAlchemy()
db.init_app(appp)

C. db = SQLAlchemy(appp)

D. db = SQLAlchemy(app=Flask())

Solution

1. Step 1: Recall initialization pattern

   The simplest and most common way is to pass the app directly to SQLAlchemy constructor: db = SQLAlchemy(app).

2. Step 2: Check other options

   db = SQLAlchemy() db.init_app(appp) uses undefined 'appp'. db = SQLAlchemy(appp) uses undefined 'appp'. db = SQLAlchemy(app=Flask()) creates a new Flask app, not the existing one.

3. Final Answer:

   db = SQLAlchemy(app) -> Option A

4. Quick Check:

   Direct app in constructor = db = SQLAlchemy(app) [OK]

Hint: Pass your Flask app directly to SQLAlchemy() for quick setup [OK]

Common Mistakes:

• Trying to pass app as a keyword argument incorrectly
• Creating a new Flask app inside SQLAlchemy
• Forgetting to link SQLAlchemy to the app

3. Given this Flask-SQLAlchemy model and code, what will print(user.name) output?

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db = SQLAlchemy(app)

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

with app.app_context():
    db.create_all()
    user = User(name='Alice')
    db.session.add(user)
    db.session.commit()

print(user.name)

medium

A. Empty string

B. None

C. Alice

D. Error: No such attribute

Solution

1. Step 1: Understand model and data insertion

   The User model has a name field. We create a user with name 'Alice' and add it to the database.

2. Step 2: Check what user.name holds after commit

   After committing, the user object still has the name 'Alice' stored in its attribute.

3. Final Answer:

   Alice -> Option C

4. Quick Check:

   User.name after commit = 'Alice' [OK]

Hint: Committed model instances keep their attribute values accessible [OK]

Common Mistakes:

• Assuming user.name becomes None after commit
• Thinking print causes a database query error
• Confusing model attribute with database column name

4. What is wrong with this Flask-SQLAlchemy setup code?

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy()
db.init_app(app)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))

product = Product(name='Book')
db.session.add(product)
db.session.commit()

medium

A. Missing app.app_context() when adding and committing

B. SQLALCHEMY_DATABASE_URI is incorrect

C. Product model lacks a __tablename__ attribute

D. db = SQLAlchemy() should be db = SQLAlchemy(app)

Solution

1. Step 1: Check app context usage

   When using db.init_app(app), database operations must be inside app.app_context() to access app config.

2. Step 2: Verify other parts

   The URI is valid, __tablename__ is optional, and db = SQLAlchemy() with init_app is valid. The missing app context causes runtime error.

3. Final Answer:

   Missing app.app_context() when adding and committing -> Option A

4. Quick Check:

   Database ops need app context [OK]

Hint: Wrap DB actions in app.app_context() if using init_app() [OK]

Common Mistakes:

• Ignoring app context requirement with init_app()
• Thinking __tablename__ is always required
• Confusing init_app() with passing app in constructor

5. You want to create a Flask app with Flask-SQLAlchemy that uses PostgreSQL. Which steps correctly set up the database URI and initialize the app and database? Choose the correct sequence.

hard

A. Set app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/dbname'; then db = SQLAlchemy(appp)

B. Set app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'; then db = SQLAlchemy(app)

C. Set app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/dbname'; then db = SQLAlchemy(); no init_app call

D. Set app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/dbname'; then db = SQLAlchemy(); db.init_app(app)

Solution

1. Step 1: Set correct PostgreSQL URI

   The URI must start with 'postgresql://' and include user, password, host, and database name.

2. Step 2: Initialize SQLAlchemy properly

   Using db = SQLAlchemy() then db.init_app(app) is a flexible pattern, especially for larger apps.

3. Step 3: Check other options

   Set app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/dbname'; then db = SQLAlchemy(appp) uses undefined 'appp'; Set app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'; then db = SQLAlchemy(app) uses SQLite, not PostgreSQL; Set app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/dbname'; then db = SQLAlchemy(); no init_app call misses init_app call, so db won't link to app.

4. Final Answer:

   Set app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/dbname'; then db = SQLAlchemy(); db.init_app(app) -> Option D

5. Quick Check:

   PostgreSQL URI + init_app() = Set app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/dbname'; then db = SQLAlchemy(); db.init_app(app) [OK]

Hint: Use init_app() with PostgreSQL URI for flexible setup [OK]

Common Mistakes:

• Using SQLite URI when PostgreSQL is needed
• Forgetting to call init_app() after creating db without app
• Passing app to SQLAlchemy but using wrong URI format