As you've seen, writing SQL commands are complicated and error-prone. It would be much better if we could just write Python code and get the compiler to help us spot typos and errors in our code. That's why SQLAlchemy was created.

SQLAlchemy is defined as an ORM (Object Relational Mapping) library. This means that it's able to map the relationships in the database into Objects. Fields become Object properties. Tables can be defined as separate Classes and each row of data is a new Object. This will make more sense after we write some code and see how we can create a Database/Table/Row of data using SQLAlchemy.

Let's continue writing some more code in our separate project before we come back to the library of our favourite books.

1. Comment out all the existing code where we create an SQLite database directly using the sqlite3 module (or just close the separate project)

2. Install the required packages flask, SQLAlchemy, and flask_sqlalchemy from the requirements.txt. You can see the packages and their versions in the requirements.txt. PyCharm should have prompted you to install the packages in the requirements.txt file when you first opened the starting project:

That way you can be sure to grab the same version as in the tutorial.

If you don't see the packages, you can also always run the following command:

pip3 install -r requirements.txt

3. Import the Flask and SQLAlchemy classes from each.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import Integer, String, Float

4. CHALLENGE: Use the SQLAlchemy documentation to figure out how initialise the db object, define your model, and create the table.

Check out the recommended reading in the Resources.

Requirements:

• Create an SQLite database called new-books-collection.db. Remember to initialise the app.

• Create a table in this database called books.

• The books table should contain 4 fields: id, title, author and rating. The fields should have the same limitations as before e.g. INTEGER/FLOAT/VARCHAR/UNIQUE/NOT NULL etc.

• Provide the Flask "app context" and create the schema in the database.

• Again, with the flask app context, create a new entry in the books table that consists of the following data:

id: 1

title: "Harry Potter"

author: "J. K. Rowling"

review: 9.3

HINT 1: The URL for your database should be "sqlite:///new-books-collection.db"

HINT 2: You can always check the database using DB Browser.

SOLUTION