Databases with Flask

Key Concepts

• SQLite
• PostgreSQL
• MySQL
• MongoDB
• Redis

SQLite

SQLite is a lightweight, serverless database that is ideal for small to medium-sized applications. It stores the entire database in a single file, making it easy to manage and deploy. Flask integrates seamlessly with SQLite using the SQLAlchemy ORM.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

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

PostgreSQL

PostgreSQL is a powerful, open-source relational database system. It supports complex queries, transactions, and has a strong reputation for reliability and data integrity. Flask can connect to PostgreSQL using the psycopg2 adapter.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://username:password@localhost/dbname'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

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

MySQL

MySQL is another popular open-source relational database management system. It is widely used in web applications due to its speed, reliability, and ease of use. Flask can connect to MySQL using the pymysql adapter.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/dbname'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

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

MongoDB

MongoDB is a NoSQL database that stores data in JSON-like documents. It is highly scalable and flexible, making it suitable for applications that require high performance and dynamic schemas. Flask can connect to MongoDB using the Flask-PyMongo extension.

from flask import Flask
from flask_pymongo import PyMongo

app = Flask(__name__)
app.config['MONGO_URI'] = 'mongodb://localhost:27017/dbname'
mongo = PyMongo(app)

@app.route('/add')
def add():
    user = mongo.db.users.insert_one({'username': 'john_doe'})
    return 'User added!'

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

Redis

Redis is an in-memory data structure store, used as a database, cache, and message broker. It supports various data types such as strings, hashes, lists, sets, and sorted sets. Flask can connect to Redis using the Flask-Redis extension.

from flask import Flask
from flask_redis import FlaskRedis

app = Flask(__name__)
app.config['REDIS_URL'] = 'redis://localhost:6379/0'
redis_client = FlaskRedis(app)

@app.route('/set')
def set():
    redis_client.set('username', 'john_doe')
    return 'Username set!'

@app.route('/get')
def get():
    username = redis_client.get('username')
    return f'Username: {username.decode("utf-8")}'

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