Flask SQLAlchemy SQLite Example
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
# 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)