Skip to main content

Connecting Flask to Databases

Connecting Flask to Databases

Connecting Flask to databases is essential for building data-driven web applications, such as dashboards, machine learning (ML) interfaces, or content management systems that handle persistent data. Flask supports various databases through libraries like SQLAlchemy (for relational databases) or PyMongo (for NoSQL databases like MongoDB). This guide covers connecting Flask to databases, focusing on secure and efficient database integration using SQLAlchemy for relational databases (e.g., SQLite, PostgreSQL) and PyMongo for MongoDB, with practical examples tailored to data-driven applications.

01. Overview of Database Integration in Flask

Flask is a lightweight framework that does not include a built-in database layer, allowing developers to choose their preferred database and integration method. SQLAlchemy is widely used for relational databases due to its ORM (Object-Relational Mapping) capabilities, while PyMongo is popular for MongoDB’s NoSQL flexibility. Proper configuration ensures secure, efficient, and scalable database interactions.

  • Purpose: Enable Flask applications to store, retrieve, and manipulate data persistently.
  • Key Components: SQLAlchemy (for SQL databases), PyMongo (for MongoDB), database URI, and connection management.
  • Use Cases: Storing user data, managing ML model inputs/outputs, or logging dashboard interactions.

1.1 Security and Performance Considerations

  • SQL Injection: Use parameterized queries or ORM to prevent injection attacks.
  • Connection Management: Properly manage database connections to avoid leaks.
  • Credentials Security: Store database credentials securely (e.g., environment variables).
  • Performance: Optimize queries and use connection pooling for scalability.

02. Connecting to Relational Databases with SQLAlchemy

2.1 Installation

Install Flask-SQLAlchemy and a database driver (e.g., psycopg2 for PostgreSQL or sqlite3 for SQLite, which is built into Python): 

pip install flask-sqlalchemy psycopg2-binary

2.2 Project Structure

project/
├── app.py
├── static/
│   ├── css/
│   │   └── style.css
├── templates/
│   ├── base.html
│   ├── users.html
│   └── add_user.html
└── instance/
    └── database.db  # SQLite database

2.3 Basic Configuration

Configure Flask-SQLAlchemy with a database URI and initialize the database connection.

Example: SQLite Database with Flask-SQLAlchemy

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///instance/database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Define User model
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

# Form for adding users
class UserForm(FlaskForm):
    name = StringField('Name', validators=[DataRequired()])
    email = StringField('Email', validators=[DataRequired()])
    submit = SubmitField('Add User')

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

@app.route('/')
def index():
    users = User.query.all()
    return render_template('users.html', users=users)

@app.route('/add', methods=['GET', 'POST'])
def add_user():
    form = UserForm()
    if form.validate_on_submit():
        user = User(name=form.name.data, email=form.email.data)
        db.session.add(user)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_user.html', form=form)

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

File: templates/base.html

<!DOCTYPE html>
<html>
<head>
    <title>{% block title %}Flask Database{% endblock %}</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}">
</head>
<body>
    <div class="container mt-3">
        {% block content %}{% endblock %}
    </div>
</body>
</html>

File: static/css/style.css

body {
    font-family: Arial, sans-serif;
}
.form-group {
    margin-bottom: 15px;
}
.error {
    color: red;
    font-size: 0.9em;
}

File: templates/users.html

{% extends 'base.html' %}

{% block title %}Users{% endblock %}

{% block content %}
    <h1>Users</h1>
    <a href="{{ url_for('add_user') }}" class="btn btn-primary mb-3">Add User</a>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
            </tr>
        </thead>
        <tbody>
            {% for user in users %}
                <tr>
                    <td>{{ user.id }}</td>
                    <td>{{ user.name | escape }}</td>
                    <td>{{ user.email | escape }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

File: templates/add_user.html

{% extends 'base.html' %}

{% block title %}Add User{% endblock %}

{% block content %}
    <h1>Add User</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.name.label }}
            {{ form.name(class="form-control") }}
            {% for error in form.name.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.email.label }}
            {{ form.email(class="form-control") }}
            {% for error in form.email.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

Output (/):

  • GET /: Displays a table of users from the SQLite database.
  • GET /add: Shows a form to add a user.
  • POST /add (valid data): Adds a user to the database and redirects to the user list.

Explanation:

  • SQLALCHEMY_DATABASE_URI: Specifies the database (SQLite in this case).
  • User model: Defines the database schema.
  • db.create_all(): Creates tables within the application context.
  • FlaskForm: Secures form submissions with CSRF protection.

2.4 Connecting to PostgreSQL

To use PostgreSQL, update the database URI and ensure the database is running. 

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://username:password@localhost:5432/dbname'

Ensure psycopg2 is installed and the PostgreSQL server is configured.

03. Connecting to NoSQL Databases with PyMongo

3.1 Installation

Install Flask-PyMongo for MongoDB integration: 

pip install flask-pymongo

3.2 Basic Configuration

Configure Flask-PyMongo with a MongoDB URI.

Example: MongoDB with Flask-PyMongo

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_pymongo import PyMongo
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['MONGO_URI'] = 'mongodb://localhost:27017/mydb'

mongo = PyMongo(app)

# Form for adding documents
class DocumentForm(FlaskForm):
    title = StringField('Title', validators=[DataRequired()])
    content = StringField('Content', validators=[DataRequired()])
    submit = SubmitField('Add Document')

@app.route('/')
def index():
    documents = mongo.db.documents.find()
    return render_template('documents.html', documents=documents)

@app.route('/add', methods=['GET', 'POST'])
def add_document():
    form = DocumentForm()
    if form.validate_on_submit():
        mongo.db.documents.insert_one({
            'title': form.title.data,
            'content': form.content.data
        })
        return redirect(url_for('index'))
    return render_template('add_document.html', form=form)

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

File: templates/documents.html

{% extends 'base.html' %}

{% block title %}Documents{% endblock %}

{% block content %}
    <h1>Documents</h1>
    <a href="{{ url_for('add_document') }}" class="btn btn-primary mb-3">Add Document</a>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>Title</th>
                <th>Content</th>
            </tr>
        </thead>
        <tbody>
            {% for doc in documents %}
                <tr>
                    <td>{{ doc.title | escape }}</td>
                    <td>{{ doc.content | escape }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

File: templates/add_document.html

{% extends 'base.html' %}

{% block title %}Add Document{% endblock %}

{% block content %}
    <h1>Add Document</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.title.label }}
            {{ form.title(class="form-control") }}
            {% for error in form.title.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.content.label }}
            {{ form.content(class="form-control") }}
            {% for error in form.content.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

Output (/):

  • GET /: Displays a table of documents from MongoDB.
  • GET /add: Shows a form to add a document.
  • POST /add (valid data): Adds a document to MongoDB and redirects to the document list.

Explanation:

  • MONGO_URI: Specifies the MongoDB connection.
  • mongo.db.documents: Interacts with the documents collection.
  • FlaskForm: Secures form submissions with CSRF protection.

04. Secure Database Configuration

4.1 Storing Credentials Securely

Use environment variables to store sensitive database credentials instead of hardcoding them.

Example: Using Environment Variables

File: app.py (SQLAlchemy)

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', 'your-secret-key')
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', 'sqlite:///instance/database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

Environment Setup:

export SECRET_KEY='your-secret-key'
export DATABASE_URL='postgresql://username:password@localhost:5432/dbname'

Explanation:

  • os.getenv: Retrieves environment variables securely.
  • Fallback: Provides a default for development (e.g., SQLite).

4.2 Connection Pooling

SQLAlchemy supports connection pooling to manage database connections efficiently. Configure pool settings for high-traffic applications: 

app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_size': 5,
    'max_overflow': 10,
    'pool_timeout': 30
}

4.3 Preventing SQL Injection

Use SQLAlchemy’s ORM or parameterized queries to prevent SQL injection. Avoid raw SQL with user input.

Example: Insecure Raw SQL

email = request.form['email']
db.execute(f"SELECT * FROM users WHERE email = '{email}'")  # Vulnerable

Correct:

email = request.form['email']
user = User.query.filter_by(email=email).first()  # Safe

05. Database Integration in Data-Driven Applications

Data-driven applications often require storing and querying complex datasets, such as ML model inputs or user analytics.

Example: Storing ML Predictions with SQLAlchemy

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import FloatField, SubmitField
from wtforms.validators import DataRequired, NumberRange
import os
import pandas as pd

app = Flask(__name__)
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', 'your-secret-key')
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', 'sqlite:///instance/database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Define Prediction model
class Prediction(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    feature1 = db.Column(db.Float, nullable=False)
    feature2 = db.Column(db.Float, nullable=False)
    result = db.Column(db.Float, nullable=False)

# Form for predictions
class PredictionForm(FlaskForm):
    feature1 = FloatField('Feature 1', validators=[DataRequired(), NumberRange(min=0, max=1)])
    feature2 = FloatField('Feature 2', validators=[DataRequired(), NumberRange(min=0, max=1)])
    submit = SubmitField('Predict')

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

@app.route('/', methods=['GET', 'POST'])
def index():
    form = PredictionForm()
    if form.validate_on_submit():
        # Mock ML prediction
        result = (form.feature1.data + form.feature2.data) / 2
        prediction = Prediction(
            feature1=form.feature1.data,
            feature2=form.feature2.data,
            result=result
        )
        db.session.add(prediction)
        db.session.commit()
        return redirect(url_for('index'))
    
    # Fetch predictions as DataFrame
    predictions = Prediction.query.all()
    df = pd.DataFrame([(p.feature1, p.feature2, p.result) for p in predictions],
                      columns=['Feature 1', 'Feature 2', 'Result'])
    return render_template('index.html', form=form, data=df.to_dict(orient='records'))

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

File: templates/index.html

{% extends 'base.html' %}

{% block title %}ML Predictions{% endblock %}

{% block content %}
    <h1>ML Prediction Dashboard</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.feature1.label }}
            {{ form.feature1(class="form-control") }}
            {% for error in form.feature1.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.feature2.label }}
            {{ form.feature2(class="form-control") }}
            {% for error in form.feature2.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    {% if data %}
        <h2>Prediction History</h2>
        <table class="table table-striped mt-3">
            <thead>
                <tr>
                    {% for key in data[0].keys() %}
                        <th>{{ key | title }}</th>
                    {% endfor %}
                </tr>
            </thead>
            <tbody>
                {% for row in data %}
                    <tr>
                        {% for value in row.values() %}
                            <td>{{ value | escape }}</td>
                        {% endfor %}
                    </tr>
                {% endfor %}
            </tbody>
        </table>
    {% endif %}
{% endblock %}

Output (/):

  • GET: Displays a form and a table of past predictions.
  • POST (valid data): Stores the prediction in the database and refreshes the table.

Explanation:

  • Prediction model: Stores ML prediction data.
  • Pandas: Converts query results to a DataFrame for display.
  • FlaskForm: Secures input with CSRF protection.

06. Best Practices for Database Integration

6.1 Recommended Practices

  • Use ORM: Leverage SQLAlchemy or PyMongo for safe queries.
  • Secure Credentials: Store database URIs in environment variables.
  • Enable Connection Pooling: Configure pool settings for scalability.
  • Validate Inputs: Use Flask-WTF forms to validate user input.
  • Optimize Queries: Use indexes and limit query scope for performance.
  • Handle Errors: Catch database exceptions and provide user-friendly feedback.

6.2 Security Considerations

  • Prevent SQL Injection: Avoid raw SQL with user input.
  • Encrypt Sensitive Data: Use encryption for sensitive fields (e.g., passwords).
  • Restrict Access: Use database roles and permissions to limit access.

Example: Insecure Database Connection

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://admin:password@localhost/dbname'  # Hardcoded credentials
db.execute(f"SELECT * FROM users WHERE name = '{request.form['name']}'")  # SQL injection risk

Correct:

app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL')
user = User.query.filter_by(name=request.form['name']).first()  # Safe

6.3 Practices to Avoid

  • Avoid Hardcoding Credentials: Use environment variables.
  • Avoid Raw SQL with User Input: Use ORM or parameterized queries.
  • Avoid Unmanaged Connections: Use connection pooling and close sessions.

07. Conclusion

Connecting Flask to databases enables robust data-driven applications. Key takeaways:

  • Use Flask-SQLAlchemy for relational databases like SQLite or PostgreSQL.
  • Use Flask-PyMongo for NoSQL databases like MongoDB.
  • Secure credentials with environment variables and prevent injection with ORM.
  • Optimize performance with connection pooling and query optimization.

By following these practices, you can build secure, scalable Flask applications that efficiently manage database interactions for data analysis, ML, and more!

Comments

Post a Comment