Python > Web Development with Python > Flask > Database Integration (with SQLAlchemy or other ORMs)
Flask App with SQLAlchemy Integration
This snippet demonstrates how to integrate Flask with SQLAlchemy to create a simple web application that interacts with a database. We'll define a model, create a database table, and perform basic CRUD (Create, Read, Update, Delete) operations. This is a fundamental pattern for building data-driven web applications with Flask.
Project Setup and Dependencies
First, install Flask and Flask-SQLAlchemy. Flask-SQLAlchemy simplifies using SQLAlchemy within a Flask application. It provides helpful utilities and abstractions for managing database connections and sessions.
bash
pip install flask flask-sqlalchemy
App Configuration and Database Model
This code sets up the Flask application, configures the database connection (using SQLite for simplicity), and defines a `Task` model. The `SQLALCHEMY_DATABASE_URI` specifies the database to use. `SQLALCHEMY_TRACK_MODIFICATIONS` is set to `False` to suppress a warning. The `Task` model has `id`, `content`, and `completed` attributes. The `db.create_all()` statement creates the database tables within the application context.
python
from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tasks.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # Suppress warning
db = SQLAlchemy(app)
class Task(db.Model):
id = db.Column(db.Integer, primary_key=True)
content = db.Column(db.String(200), nullable=False)
completed = db.Column(db.Boolean, default=False)
def __repr__(self):
return f'<Task %r>' % self.id
with app.app_context():
db.create_all()
Routes and CRUD Operations
These routes handle the main application logic. The `/` route handles both displaying the list of tasks and adding new tasks. The `/delete/
python
@app.route('/', methods=['POST', 'GET'])
def index():
if request.method == 'POST':
task_content = request.form['content']
new_task = Task(content=task_content)
try:
db.session.add(new_task)
db.session.commit()
return redirect('/')
except:
return 'There was an issue adding your task'
else:
tasks = Task.query.order_by(Task.id).all()
return render_template('index.html', tasks=tasks)
@app.route('/delete/<int:id>')
def delete(id):
task_to_delete = Task.query.get_or_404(id)
try:
db.session.delete(task_to_delete)
db.session.commit()
return redirect('/')
except:
return 'There was a problem deleting that task'
@app.route('/update/<int:id>', methods=['GET', 'POST'])
def update(id):
task = Task.query.get_or_404(id)
if request.method == 'POST':
task.content = request.form['content']
try:
db.session.commit()
return redirect('/')
except:
return 'There was an issue updating your task'
else:
return render_template('update.html', task=task)
if __name__ == "__main__":
app.run(debug=True)
HTML Templates (index.html and update.html)
These are simple HTML templates that render the task list and the update form. Flask's Jinja2 templating engine is used to dynamically generate the HTML. The `index.html` template displays a list of tasks with delete and update links. The `update.html` template provides a form to update a task's content.
html
<!-- index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Task List</title>
</head>
<body>
<h1>Task List</h1>
<form action="/" method="post">
<input type="text" name="content" id="content">
<input type="submit" value="Add Task">
</form>
<ul>
{% for task in tasks %}
<li>{{ task.content }} - <a href="/delete/{{ task.id }}">Delete</a> - <a href="/update/{{ task.id }}">Update</a></li>
{% endfor %}
</ul>
</body>
</html>
<!-- update.html -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Update Task</title>
</head>
<body>
<h1>Update Task</h1>
<form action="/update/{{ task.id }}" method="post">
<input type="text" name="content" id="content" value="{{ task.content }}">
<input type="submit" value="Update Task">
</form>
</body>
</html>
Concepts Behind the Snippet
This snippet showcases the Model-View-Controller (MVC) architectural pattern. SQLAlchemy models represent the data (Model), Flask routes handle user requests and application logic (Controller), and HTML templates provide the user interface (View). Flask-SQLAlchemy simplifies database interactions within this framework.
Real-Life Use Case
This pattern is commonly used in web applications that require persistent data storage, such as to-do list apps, blog platforms, e-commerce sites, and content management systems. Any application that needs to store and retrieve data can benefit from this architecture.
Best Practices
Interview Tip
Be prepared to explain the benefits of using an ORM (Object-Relational Mapper) like SQLAlchemy, such as code reusability, database abstraction, and security. Also, understand the trade-offs between ORMs and raw SQL queries.
When to Use Them
Use Flask and SQLAlchemy when you need to build a web application that requires database interaction, especially when you want to abstract away the complexities of raw SQL and benefit from an ORM's features.
Memory Footprint
The memory footprint depends on the size of the database and the number of active users. SQLAlchemy caches query results and database connections, which can impact memory usage. Optimize queries and connection pooling to minimize memory overhead.
Alternatives
Pros
Cons
FAQ
-
What is SQLAlchemy?
SQLAlchemy is a Python SQL toolkit and Object-Relational Mapper that gives application developers the full power and flexibility of SQL. -
Why use SQLAlchemy with Flask?
SQLAlchemy provides a high-level interface for interacting with databases, while Flask provides a lightweight framework for building web applications. Combining them simplifies the development of data-driven web applications. -
How do I handle database migrations?
Use a migration tool like Alembic to track and apply database schema changes.