Flask-SQLAlchemy CRUD examples

Examples of CRUD operations because I always forget the syntax!

Reference: 100 Days of Code - The Python Pro Bootcamp by Dr. Angela Yu.

Create new database

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///<name of database>.db"
db = SQLAlchemy(app)

Create new table

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(250), unique=True, nullable=False)
    author = db.Column(db.String(250), nullable=False)
    rating = db.Column(db.Float, nullable=False
 
db.create_all()

Create new record

new_book = Book(id=1, title="Harry Potter", author="J. K. Rowling", rating=9.3)
    # id field is optional and can be autogenerated therefore it can removed
db.session.add(new_book)
db.session.commit()

Read records

all_books = session.query(Book).all()
book = Book.query.filter_by(title="Harry Potter").first()

Update a particular record

# by query
book_to_update = Book.query.filter_by(title="Harry Potter").first()
book_to_update.title = "Harry Potter and the Chamber of Secrets"
db.session.commit()  

#by id
book_id = 1
book_to_update = Book.query.get(book_id)
book_to_update.title = "Harry Potter and the Goblet of Fire"
db.session.commit()  

Delete a particular record by primary key

book_id = 1
book_to_delete = Book.query.get(book_id)
db.session.delete(book_to_delete)
db.session.commit()