What is SQLAlchemy?

Written by Sümeyye Sever (notes I took while learning Python)

SQLAlchemy is a powerful and popular Python SQL toolkit and Object Relational Mapper (ORM). It allows developers to interact with databases in Pythonic ways, abstracting raw SQL queries into high-level Python objects.

Why Use SQLAlchemy?

Abstraction: Write less SQL, work more with Python objects.
Flexibility: Works with multiple databases (SQLite, PostgreSQL, MySQL, etc.).
Productivity: ORM simplifies database operations.

How to Use SQLAlchemy

Installation
```
pip install -U Flask-SQLAlchemy
```
Initialize the Extension

First create the db object using the SQLAlchemy constructor.
```
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
  pass

db = SQLAlchemy(model_class=Base)
```
Once constructed, the db object gives you access to the db.Model class to define models, and the db.session to execute queries.

The SQLAlchemy object also takes additional arguments to customize the objects it manages.
Configure the Extension

The next step is to connect the extension to your Flask app. The only required Flask app config is the SQLALCHEMY_DATABASE_URI key. That is a connection string that tells SQLAlchemy what database to connect to.

Create your Flask application object, load any config, and then initialize the SQLAlchemy extension class with the application by calling db.init_app. This example connects to a SQLite database, which is stored in the app’s instance folder.
```
# create the app
app = Flask(__name__)
# configure the SQLite database, relative to the app instance folder
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///new-books-collection.db"
# initialize the app with the extension
db.init_app(app)
```

Define Models

Subclass db.Model to define a model class. The model will generate a table name by converting the CamelCase class name to snake_case.

from sqlalchemy import Integer, String
from sqlalchemy.orm import Mapped, mapped_column

class Book(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(nullable=False)
    author: Mapped[str] = mapped_column(nullable=False)
    rating: Mapped[str] = mapped_column(nullable=False)

Create the Tables

After all models and tables are defined, call SQLAlchemy.create_all() to create the table schema in the database. This requires an application context. Since you’re not in a request at this point, create one manually.
```
with app.app_context():
    db.create_all()
```

Query the Data

#add new book to the database
with app.app_context():
    new_book = Book(
		    id=1,
        title="Small Things Like These",
        author="Claire Keegan",
        rating="⭐⭐⭐⭐⭐"
    )
    db.session.add(new_book)
    db.session.commit()

CRUD Operations with SQLAlchemy

Create A New Record

with app.app_context():
    new_book = Book(
		    id=1,
        title="Small Things Like These",
        author="Claire Keegan",
        rating="⭐⭐⭐⭐⭐"
    )
    db.session.add(new_book)
    db.session.commit()

Read All Records

with app.app_context():
    result = db.session.execute(db.select(Book).order_by(Book.title))
    all_books = result.scalars()

To read all the records we first need to create a "query" to select things from the database. When we execute a query during a database session we get back the rows in the database (a Result object). We then use scalars() to get the individual elements rather than entire rows.

Read A Particular Record By Query

with app.app_context():
    book = db.session.execute(db.select(Book).where(Book.title == "Small Things Like These")).scalar()

To get a single element we can use scalar() instead of scalars().