Creating tables (db.create_all) in Flask - Try It Yourself

Creating Tables with Flask and SQLAlchemy

📖 Scenario: You are building a simple web app to store information about books in a library. You want to create a database table to hold the book details.

🎯 Goal: Create a Flask app with SQLAlchemy that defines a Book model and creates the corresponding table in the database using db.create_all().

📋 What You'll Learn

Create a Flask app instance named app

Create a SQLAlchemy database instance named db linked to app

Define a Book model with columns id (integer primary key), title (string), and author (string)

Use db.create_all() to create the tables in the database

💡 Why This Matters

🌍 Real World

Creating tables is the first step in building web apps that store data persistently, like user info, products, or posts.

💼 Career

Understanding how to define models and create tables is essential for backend web development roles using Flask and SQLAlchemy.

1

Set up Flask app and SQLAlchemy

Create a Flask app instance called app and configure it with SQLALCHEMY_DATABASE_URI set to 'sqlite:///library.db'. Then create a SQLAlchemy instance called db linked to app.

Flask

# Create Flask app instance named app
# Configure app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///library.db'
# Create SQLAlchemy instance named db linked to app
# Your code here

Hint

Use Flask(__name__) to create the app. Set app.config['SQLALCHEMY_DATABASE_URI'] to the SQLite file path. Then create db = SQLAlchemy(app).

2

Define the Book model

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

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

# Define Book model with id, title, author columns
# Your code here

Hint

Define a class Book inheriting from db.Model. Use db.Column to define each column with the right type and primary key for id.

3

Create the tables in the database

Call db.create_all() to create the tables in the database based on the models defined.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=true)
    title = db.Column(db.String(100))
    author = db.Column(db.String(50))

# Create all tables in the database
# Your code here

Hint

Use db.create_all() to create tables for all models in the database.

4

Run the Flask app

Add the code to run the Flask app by calling app.run() inside the if __name__ == '__main__': block.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=true)
    title = db.Column(db.String(100))
    author = db.Column(db.String(50))

db.create_all()

# Add code to run the Flask app
# Your code here

Hint

Use the standard Python block if __name__ == '__main__': and call app.run() inside it to start the Flask server.

Practice

1. What does the db.create_all() function do in a Flask application?

easy

A. It updates existing tables to match model changes automatically.

B. It deletes all tables from the database.

C. It creates all tables defined by your models in the database if they don't exist.

D. It runs all database queries defined in your app.

Solution

1. Step 1: Understand the purpose of db.create_all()

   This function creates tables in the database based on your model definitions if those tables do not already exist.

2. Step 2: Recognize what it does not do

   It does not delete tables, update existing tables, or run queries; it only creates missing tables.

3. Final Answer:

   It creates all tables defined by your models in the database if they don't exist. -> Option C

4. Quick Check:

   db.create_all() creates missing tables [OK]

Hint: Remember: create_all only adds missing tables, no updates [OK]

Common Mistakes:

• Thinking it updates existing tables automatically
• Confusing it with deleting tables
• Assuming it runs queries or migrations

2. Which of the following is the correct way to use db.create_all() in a Flask app?

easy

A. Call db.create_all() inside the Flask app context.

B. Call db.create_all() after closing the app.

C. Call db.create_all() before defining your models.

D. Call db.create_all() outside the Flask app context.

Solution

1. Step 1: Understand Flask app context requirement

   Database operations like db.create_all() must run inside the Flask app context to access configuration and database connections properly.

2. Step 2: Identify correct usage

   Calling db.create_all() inside the app context ensures tables are created without errors.

3. Final Answer:

   Call db.create_all() inside the Flask app context. -> Option A

4. Quick Check:

   db.create_all() requires app context [OK]

Hint: Always run db.create_all() inside app context [OK]

Common Mistakes:

• Calling create_all outside app context causing errors
• Calling create_all before models are defined
• Calling create_all after app is closed

3. Given this Flask code snippet, what will happen when db.create_all() is called?

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
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()

medium

A. An error will occur because the app context is missing.

B. A new table named 'user' will be created in the database if it doesn't exist.

C. The existing 'user' table will be dropped and recreated.

D. Nothing happens because create_all does not create tables.

Solution

1. Step 1: Check app context usage

   The code calls db.create_all() inside app.app_context(), so the context is correct.

2. Step 2: Understand create_all behavior

   It creates missing tables based on models. Since 'User' model exists, a 'user' table will be created if not present.

3. Final Answer:

   A new table named 'user' will be created in the database if it doesn't exist. -> Option B

4. Quick Check:

   create_all creates missing tables inside app context [OK]

Hint: create_all adds missing tables, does not drop existing [OK]

Common Mistakes:

• Thinking create_all drops tables
• Forgetting app context causes errors
• Assuming create_all does nothing

4. You run db.create_all() but your new model's table is not created. What is the most likely cause?

medium

A. All of the above.

B. You defined the model after calling db.create_all().

C. The database URI is incorrect or missing.

D. You forgot to run db.create_all() inside the Flask app context.

Solution

1. Step 1: Check app context usage

   Running db.create_all() outside app context causes tables not to be created.

2. Step 2: Check model definition timing and database URI

   If models are defined after calling db.create_all(), their tables won't be created. Also, an incorrect or missing database URI prevents connection.

3. Final Answer:

   All of the above. -> Option A

4. Quick Check:

   App context, model timing, and URI all affect create_all success [OK]

Hint: Check app context, model order, and DB URI if tables missing [OK]

Common Mistakes:

• Ignoring app context errors
• Calling create_all before defining models
• Not setting database URI correctly

5. You want to add a new column to an existing table after calling db.create_all(). What will happen if you call db.create_all() again without migrations?

hard

A. An error will occur because the table schema changed.

B. The new column will be added automatically to the existing table.

C. The existing table will be dropped and recreated with the new column.

D. Nothing changes; db.create_all() does not modify existing tables.

Solution

1. Step 1: Understand create_all limitations

   db.create_all() only creates missing tables; it does not alter existing tables or add columns.

2. Step 2: Recognize need for migrations

   To change existing tables (like adding columns), you must use migration tools such as Flask-Migrate.

3. Final Answer:

   Nothing changes; db.create_all() does not modify existing tables. -> Option D

4. Quick Check:

   create_all creates tables but does not alter existing ones [OK]

Hint: Use migrations to change tables; create_all only adds missing tables [OK]

Common Mistakes:

• Expecting create_all to update tables
• Not using migration tools for schema changes
• Assuming create_all drops and recreates tables