Introduction to Flask SQLAlchemy and SQLite

This example demonstrates how to integrate Flask with SQLAlchemy, a powerful Object-Relational Mapper (ORM), and SQLite, a lightweight, file-based relational database. This combination is excellent for developing smaller to medium-sized Python web applications, prototypes, or when a full-fledged database server is not required.

Setting Up Your Flask Application with SQLAlchemy

To begin, you need to install Flask and Flask-SQLAlchemy. You can do this using pip:

pip install Flask Flask-SQLAlchemy
                

The provided Python code initializes a Flask application and configures it to use SQLite. The `SQLALCHEMY_DATABASE_URI` is set to point to a file named `db.sqlite3` in the application's directory. `SQLALCHEMY_TRACK_MODIFICATIONS` is set to `False` to suppress a warning and save resources.

Defining Database Models with SQLAlchemy

SQLAlchemy allows you to define your database tables as Python classes. In this example, the `Student` class inherits from `db.Model` and defines columns for `id`, `name`, `email`, and `date_registered`. The `__tablename__` attribute specifies the name of the table in the database. The `date_registered` column uses `dt.utcnow` as a default value, ensuring that the registration date is automatically recorded.

Seeding and Querying Database Records

The `@app.route('/seed', methods=['GET'])` function provides an endpoint to seed the database with initial data. It creates a new `Student` instance, adds it to the session, and commits the transaction. The `@app.route('/', methods=['GET'])` function queries all students from the database, formats the results into a list of dictionaries, and returns them as a JSON response. This is a common pattern for building APIs with Flask.

Running the Flask Application

The `if __name__ == '__main__':` block ensures that `db.create_all()` is called to create the database tables if they don't exist, and then the Flask development server is started. The application will be accessible at `http://127.0.0.1:5000/`.

For more advanced usage and detailed explanations, refer to the official Flask-SQLAlchemy documentation and the example linked in the code comments.

External Resources

• Flask-SQLAlchemy Documentation
• SQLite Documentation
• MDN Web Docs: Web Frameworks

# More in depth example:
# https://hackersandslackers.com/flask-sqlalchemy-database-models/

from flask import Flask, make_response, render_template, jsonify
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime as dt

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.sqlite3'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class Student(db.Model):
    __tablename__ = 'students'
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(50))
    email = db.Column(db.String(120))
    date_registered = db.Column(db.Date, default=dt.utcnow)

    def __repr__(self):
        return 'User: {user}'.format(user=self.email)

@app.route('/seed', methods=['GET'])
def student_seeds():
    new_student = Student(
        id=1,
        name="Ruan",
        email="ruan@example.com",
        date_registered=dt.now()
    )
    db.session.add(new_student)  
    db.session.commit()  
    return make_response(f"{new_student} successfully created!")

@app.route('/', methods=['GET'])
def student_records():
    students = Student.query.all()
    student_list = []
    for student in students:
        student_list.append({"name": student.name, "email": student.email, "date_registered": student.date_registered})
    return jsonify(student_list)

if __name__ == '__main__':
    db.create_all()
    app.run(host='0.0.0.0', port=5000, debug=True)