5.2 Relational Databases With SQLAlchemy (42m) Flashcards

Question 1

Q

What is Relations Database? Explain with examples.

Answer

A

A “relations database” appears to be a term that is closely related to a “relational database.” However, it is important to note that the more common and widely used term is “relational database.”

A relational database, as mentioned earlier, is a type of database management system (DBMS) that organizes and stores data in a tabular format, with data organized in tables consisting of rows and columns. It is based on the relational model, which defines relationships between tables using keys.

To explain with an example, let’s consider a scenario where we have a company that sells products to customers. We can create a relational database to manage this information.

Entities:
1. Customers
2. Products
3. Orders

Attributes:
For Customers:
- Customer ID (primary key)
- Name
- Email
- Address

For Products:
- Product ID (primary key)
- Name
- Price

For Orders:
- Order ID (primary key)
- Customer ID (foreign key)
- Product ID (foreign key)
- Quantity

Based on these entities and attributes, we can create three tables in our relational database: “Customers,” “Products,” and “Orders.”

Table: Customers
|-------------|----------|------------------|-------------------|
| 1           | John     | john@email.com   | 123 Main Street   |
| 2           | Alice    | alice@email.com  | 456 Elm Street    |
| 3           | Bob      | bob@email.com    | 789 Oak Street    |

Table: Products
| Product ID | Name        | Price |
|------------|-------------|-------|
| 1          | Laptop      | $999  |
| 2          | Smartphone  | $699  |
| 3          | Headphones  | $99   |

Table: Orders
| Order ID | Customer ID | Product ID | Quantity |
|----------|-------------|------------|----------|
| 1        | 1           | 1          | 2        |
| 2        | 2           | 3          | 1        |
| 3        | 3           | 2          | 3        |

In the “Customers” table, we have three records representing different customers. Each customer has a Customer ID, Name, Email, and Address.

In the “Products” table, we have three records representing different products. Each product has a Product ID, Name, and Price.

In the “Orders” table, we have three records representing different orders. Each order has an Order ID, Customer ID, Product ID, and Quantity. The Customer ID and Product ID act as foreign keys, referencing the respective primary keys in the “Customers” and “Products” tables.

These tables demonstrate the relationships between customers, products, and orders in a relational database. By using primary keys and foreign keys, we can establish connections between related data and retrieve information using SQL queries.

For example, if we want to find out which products were ordered by a customer named “Alice,” we can execute the following SQL query:

SELECT Products.Name
FROM Products
JOIN Orders ON Products.ProductID = Orders.ProductID
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Name = 'Alice';

This query joins the “Products,” “Orders,” and “Customers” tables based on the relationships defined by the foreign keys and retrieves the product names for orders placed by the customer named “Alice.”

Relational databases provide a powerful and flexible way to store, manage, and query structured data, making them widely used in various applications and industries.

TreeHouse Notes:
Relational Database - A database structured to recognize relationships between items.

Customer ID | Name | Email | Address |

Question 2

Q

Animals

Explain the code line by line like you would explain a beginner?

# Animals
# ID / Name / Habitat

Zookeeper Log
# ID / Animal ID (Foreign Key) / Notes

from sqlalchemy import (create_engine, Column, Integer, 
                        String, ForeignKey)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("sqlite:///zoo.db", echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Animal(Base):
    \_\_tablename\_\_ = 'animals'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    habitat = Column(String)
    logs = relationship("Logbook", back_populates="animal") 

    def \_\_repr\_\_(self):
        return f"""
        \nAnimal {self.id}\r
        Name = {self.name}\r
        Habitat = {self.habitat}
        """

class Logbook(Base):
    \_\_tablename\_\_ = 'logbook'

    id = Column(Integer, primary_key=True)
    animal_id = Column(Integer, ForeignKey("animals.id"))
    notes = Column(String)
    animal = relationship("Animal", back_populates="logs")

    def \_\_repr\_\_(self):
        return f"""
        \nLogbook {self.id}\r
        Animal ID = {self.animal_id}\r
        Notes = {self.notes}
        """
    
    def \_\_repr\_\_(self):
        return f"""
        \nLogbook {self.id}\r
        Animal ID = {self.animal_id}\r
        Notes = {self.notes}
        """
if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

Answer

A

Animals

Sure, I’d be happy to explain this code to you line by line!

```python
# Animals
# ID / Name / Habitat

Zookeeper Log
# ID / Animal ID (Foreign Key) / Notes
~~~
These lines are comments, providing a brief description of what the code is going to do. It seems like the code will involve creating a database to manage information about animals and their logbook entries.

```python
from sqlalchemy import (create_engine, Column, Integer,
String, ForeignKey)
~~~
Here, the code is importing various components from the SQLAlchemy library, which is a popular toolkit for working with databases in Python. create_engine, Column, Integer, String, and ForeignKey are elements that will be used to define the structure of the database tables and relationships.

```python
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
~~~
These lines import additional parts of the SQLAlchemy library. declarative_base is a base class for declarative class definitions, which makes it easier to define database models using class syntax. sessionmaker is used to create sessions to interact with the database, and relationship is used to define relationships between tables.

```python
engine = create_engine(“sqlite:///zoo.db”, echo=False)
~~~
Here, an SQLite database engine is created. The "sqlite:///zoo.db" specifies that the database will be stored in a file named “zoo.db”. The echo=False parameter means that SQLAlchemy won’t output all SQL commands it executes.

```python
Session = sessionmaker(bind=engine)
session = Session()
~~~
A session is created using the Session class, which is bound to the database engine. A session represents a transactional scope of work with the database.

```python
Base = declarative_base()
~~~
A base class is created using declarative_base(). This base class will be used to define the structure of the database tables.

```python
class Animal(Base):
__tablename__ = ‘animals’

id = Column(Integer, primary_key=True)
name = Column(String)
habitat = Column(String)
logs = relationship("Logbook", back_populates="animal") 

def \_\_repr\_\_(self):
    return f"""
    \nAnimal {self.id}\r
    Name = {self.name}\r
    Habitat = {self.habitat}
    """ ~~~ A Python class named `Animal` is defined, inheriting from the `Base` class. This class represents the "animals" table in the database. It has attributes like `id`, `name`, and `habitat`, which correspond to the columns in the table. The `logs` attribute creates a relationship with the `Logbook` class. The `\_\_repr\_\_` method defines a string representation of an `Animal` object.

```python
class Logbook(Base):
__tablename__ = ‘logbook’

id = Column(Integer, primary_key=True)
animal_id = Column(Integer, ForeignKey("animals.id"))
notes = Column(String)
animal = relationship("Animal", back_populates="logs")

def \_\_repr\_\_(self):
    return f"""
    \nLogbook {self.id}\r
    Animal ID = {self.animal_id}\r
    Notes = {self.notes}
    """ ~~~ Similarly, a class named `Logbook` is defined to represent the "logbook" table in the database. It has attributes like `id`, `animal_id`, and `notes`, which correspond to the columns in the table. The `animal` attribute creates a relationship with the `Animal` class. The `\_\_repr\_\_` method defines a string representation of a `Logbook` object.

```python
if __name__ == “__main__”:
Base.metadata.create_all(engine)
~~~
Finally, this block of code checks if the script is being run as the main program (not imported as a module). If it is, it creates the tables defined by the classes Animal and Logbook using the create_all method provided by the Base.metadata object.

In summary, this code sets up a SQLite database to manage information about animals and their logbook entries using SQLAlchemy, a powerful database toolkit in Python. It defines two classes, Animal and Logbook, to represent database tables and their relationships. When the script is run, it creates the necessary database tables if they don’t already exist.answer

Question 3

Q

With the following code called models.py:

# Animals
# ID / Name / Habitat

Zookeeper Log
# ID / Animal ID (Foreign Key) / Notes

from sqlalchemy import (create_engine, Column, Integer, 
                        String, ForeignKey)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("sqlite:///zoo.db", echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Animal(Base):
    \_\_tablename\_\_ = 'animals'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    habitat = Column(String)
    logs = relationship("Logbook", back_populates="animal") 

    def \_\_repr\_\_(self):
        return f"""
        \nAnimal {self.id}\r
        Name = {self.name}\r
        Habitat = {self.habitat}
        """

class Logbook(Base):
    \_\_tablename\_\_ = 'logbook'

    id = Column(Integer, primary_key=True)
    animal_id = Column(Integer, ForeignKey("animals.id"))
    notes = Column(String)
    animal = relationship("Animal", back_populates="logs")

    def \_\_repr\_\_(self):
        return f"""
        \nLogbook {self.id}\r
        Animal ID = {self.animal_id}\r
        Notes = {self.notes}
        """
    
    def \_\_repr\_\_(self):
        return f"""
        \nLogbook {self.id}\r
        Animal ID = {self.animal_id}\r
        Notes = {self.notes}
        """
if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

The following code is run in terminal, explain what each of this code will do:
~~~
import models

lion = models.Animal(name=”lion”, habitat=”savannah”)

lion.name
lion.habitat
lion.id
models.session.add(lion)
models.session.commit()
lion.id
lion_log = models.Logbook(animal_id=”1”, notes=”great pouncer”)

models.session.add(lion_log)
models.session.commit()
lion_log.animal
lion.logs
lion_log2 = models.Logbook(animal_id=”1”, notes=”really likes meat”)

models.session.add(lion_log2)
models.session.commit()
lion.logs
exit()

sqlite3 zoo.db
.tables
select * from animals;
select * from logbook
.exit

python3
import models
seal_log = models.Logbook(animal_id=”2”, notes=”likes to wave”)

models.session.add(seal_log)
models.session.commit()
seal_log.id
seal_log.animal
print(seal_log.animal)
wombat = models.Animal(name=”wombat”, habitat=”forest”)

models.session.add(wombat)
models.session.commit()
seal_log.animal
~~~

Answer

A

Certainly! I’ll walk you through the execution of each part of the code and explain what’s happening at each step.

First, let’s assume that you have already created the models.py file with the provided code and have it in the same directory as your terminal session.

```python
import models
~~~
You’re importing the models module, which means you’re importing the classes and functions defined in the models.py file.

```python
lion = models.Animal(name=”lion”, habitat=”savannah”)
~~~
Here, you’re creating a new Animal object named lion. You’re specifying its name as “lion” and its habitat as “savannah”.

```python
lion.name
lion.habitat
lion.id
~~~
These lines are accessing the attributes of the lion object. You’re retrieving the values of the name, habitat, and id attributes.

```python
models.session.add(lion)
models.session.commit()
lion.id
~~~
You’re adding the lion object to the session using models.session.add(lion), and then you’re committing the changes to the database using models.session.commit(). After the commit, the lion object will have an id assigned by the database.

```python
lion_log = models.Logbook(animal_id=”1”, notes=”great pouncer”)
models.session.add(lion_log)
models.session.commit()
~~~
Here, you’re creating a new Logbook object named lion_log. You’re specifying its animal_id as “1” (which is an incorrect way to reference the ID; it should be the actual ID assigned by the database) and providing some notes. You add this log to the session and commit it to the database.

```python
lion_log.animal
lion.logs
~~~
You’re accessing the animal attribute of the lion_log object, which will retrieve the related Animal object. Then, you’re accessing the logs attribute of the lion object, which will retrieve a list of related Logbook objects.

```python
lion_log2 = models.Logbook(animal_id=”1”, notes=”really likes meat”)
models.session.add(lion_log2)
models.session.commit()
lion.logs
~~~
You’re creating another Logbook object named lion_log2 with the same incorrect reference to animal_id and different notes. You add it to the session and commit the changes. Then, you check the logs attribute of the lion object, which should now include both lion_log and lion_log2.

```python
exit()
~~~
You’re exiting the Python interpreter.

```bash
sqlite3 zoo.db
.tables
select * from animals;
select * from logbook
.exit
~~~
Here, you’re entering the SQLite shell to directly interact with the database. You’re listing the tables using .tables, then querying and displaying all rows from the animals and logbook tables using SQL SELECT statements. Finally, you’re exiting the SQLite shell.

```python
python3
import models
seal_log = models.Logbook(animal_id=”2”, notes=”likes to wave”)
models.session.add(seal_log)
models.session.commit()
~~~
You’re running another Python interpreter session and importing the models module. Then, you’re creating a new Logbook object named seal_log, this time using the correct reference to animal_id. You add it to the session and commit the changes.

```python
seal_log.id
seal_log.animal
print(seal_log.animal)
~~~
You’re accessing the id attribute of the seal_log object, which should now have an assigned ID from the database. Then, you’re accessing the related Animal object using the animal attribute of seal_log, and finally, you’re printing the animal object, which should provide a string representation based on the \_\_repr\_\_ method.

```python
wombat = models.Animal(name=”wombat”, habitat=”forest”)
models.session.add(wombat)
models.session.commit()
seal_log.animal
~~~
You’re creating a new Animal object named wombat with the name “wombat” and habitat “forest”. You add it to the session and commit the changes. Then, you access the animal attribute of seal_log again, which is unrelated to the new wombat object.

This sequence of code demonstrates how to use the defined classes and interact with the database using SQLAlchemy. It covers creating and manipulating Animal and Logbook objects, establishing relationships, and working with a SQLite database.

Question 4

Q

With the following file models.py containing the following code:
~~~
# Animals
# ID / Name / Habitat

Zookeeper Log
# ID / Animal ID (Foreign Key) / Notes

from sqlalchemy import (create_engine, Column, Integer,
String, ForeignKey)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine(“sqlite:///zoo.db”, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Animal(Base):
__tablename__ = ‘animals’

id = Column(Integer, primary_key=True)
name = Column(String)
habitat = Column(String)
logs = relationship("Logbook", back_populates="animal") 

def \_\_repr\_\_(self):
    return f"""
    \nAnimal {self.id}\r
    Name = {self.name}\r
    Habitat = {self.habitat}
    """

class Logbook(Base):
__tablename__ = ‘logbook’

id = Column(Integer, primary_key=True)
animal_id = Column(Integer, ForeignKey("animals.id"))
notes = Column(String)
animal = relationship("Animal", back_populates="logs")

def \_\_repr\_\_(self):
    return f"""
    \nLogbook {self.id}\r
    Animal ID = {self.animal_id}\r
    Notes = {self.notes}
    """

def \_\_repr\_\_(self):
    return f"""
    \nLogbook {self.id}\r
    Animal ID = {self.animal_id}\r
    Notes = {self.notes}
    """ if \_\_name\_\_ == "\_\_main\_\_":
Base.metadata.create_all(engine) ~~~

The following is run in the terminal, explain each line in beginner friendly way and what result it will yield:

source ./env/bin/activate
python3 models.py
import models
lion = models.session.query(models.Animal).filter(models.Animal.name=="lion").first()
lion.name
wombat = models.session.query(models.Animal).filter(models.Animal.name=="wombat").first()
wombat.name
lion_log = models.session.query(models.Logbook).filter(models.Logbook.animal_id==1).first()
lion_log.notes
seal_log = models.session.query(models.Logbook).filter(models.Logbook.animal_id==2).first()
seal_log.notes
lion.habitat = "grasslands"
lion.habitat
models.session.dirty
models.session.commit()
lion_log.animal
seal = wombat
seal.name
seal.name = "seal"
seal.habitat = "ocean"
seal_log.animal
models.session.commit()
seal_log.animal_id = 1
seal_log.animal
lion.logs
seal_log.animal_id = 2
seal.logs
models.session.commit()
models.session.delete(seal)
models.session.commit()
print(seal_log)
models.session.delete(lion_log)
models.session.commit()
lions.log

Answer

A

Sure, I’d be happy to explain each line for you!

```bash
source ./env/bin/activate
~~~
This line activates a virtual environment named “env.” A virtual environment is a way to isolate Python projects and their dependencies from each other. It ensures that the packages and libraries you install for one project don’t interfere with another project.

```python
python3 models.py
~~~
This line runs the Python script “models.py” using the Python 3 interpreter. This script defines your database models using SQLAlchemy, a popular Python library for working with databases.

```python
import models
~~~
This line imports the definitions (classes and objects) from the “models” module, which is the script you just ran. This allows you to access and interact with the classes Animal and Logbook that were defined in that script.

```python
lion = models.session.query(models.Animal).filter(models.Animal.name==”lion”).first()
~~~
This line queries the database for an instance of the Animal class where the name is “lion”. It retrieves the first matching result. The result is assigned to the variable lion.

```python
lion.name
~~~
This line accesses the name attribute of the lion instance, which represents the name of the animal. It prints the name of the lion.

```python
wombat = models.session.query(models.Animal).filter(models.Animal.name==”wombat”).first()
~~~
This line is similar to the previous query, but it looks for an instance of the Animal class with the name “wombat”. The result is assigned to the variable wombat.

```python
wombat.name
~~~
This line accesses the name attribute of the wombat instance and prints its name.

```python
lion_log = models.session.query(models.Logbook).filter(models.Logbook.animal_id==1).first()
~~~
This line queries the database for an instance of the Logbook class where the animal_id is 1 (which corresponds to the lion). It retrieves the first matching logbook entry and assigns it to the variable lion_log.

```python
lion_log.notes
~~~
This line accesses the notes attribute of the lion_log instance and prints the notes associated with the lion’s logbook entry.

```python
seal_log = models.session.query(models.Logbook).filter(models.Logbook.animal_id==2).first()
~~~
This line is similar to the previous query, but it looks for a logbook entry where the animal_id is 2 (which corresponds to the hypothetical “seal”). It retrieves the first matching logbook entry and assigns it to the variable seal_log.

```python
seal_log.notes
~~~
This line accesses the notes attribute of the seal_log instance and prints the notes associated with the seal’s logbook entry.

```python
lion.habitat = “grasslands”
~~~
This line updates the habitat attribute of the lion instance to “grasslands”, effectively changing the habitat of the lion.

```python
lion.habitat
~~~
This line prints the updated habitat attribute of the lion instance, which is now “grasslands”.

```python
models.session.dirty
~~~
This line checks if there are any pending changes to the objects within the session that have not been committed to the database. In this case, it will indicate that the lion instance has been modified.

```python
models.session.commit()
~~~
This line commits the changes made to the objects within the session to the database. It effectively saves the changes.

```python
lion_log.animal
~~~
This line accesses the animal attribute of the lion_log instance, which is an instance of the Animal class. It retrieves the associated animal (lion) for this logbook entry.

```python
seal = wombat
~~~
This line assigns the wombat instance to the variable seal. Now both wombat and seal point to the same instance in memory.

```python
seal.name
~~~
This line accesses the name attribute of the seal instance, which still represents the name of the wombat.

```python
seal.name = “seal”
~~~
This line changes the name attribute of the seal instance to “seal”, effectively renaming the instance.

```python
seal.habitat = “ocean”
~~~
This line updates the habitat attribute of the seal instance to “ocean”, changing the habitat of the seal.

```python
seal_log.animal
~~~
This line accesses the animal attribute of the seal_log instance, retrieving the associated animal (seal) for this logbook entry.

```python
models.session.commit()
~~~
This line commits the changes made to the seal instance and its associated logbook entry to the database.

```python
seal_log.animal_id = 1
~~~
This line updates the animal_id attribute of the seal_log instance to 1, effectively associating this logbook entry with the lion.

```python
seal_log.animal
~~~
This line retrieves the associated animal for the seal_log entry, which is now the lion due to the change made above.

```python
lion.logs
~~~
This line accesses the logs attribute of the lion instance, which represents the logbook entries associated with the lion.

```python
seal_log.animal_id = 2
~~~
This line changes the animal_id attribute of the seal_log instance back to 2, associating it with the seal again.

```python
seal.logs
~~~
This line accesses the logs attribute of the seal instance, which represents the logbook entries associated with the seal.

```python
models.session.commit()
~~~
This line commits the changes to the seal_log instance and its association with the seal to the database.

```python
models.session.delete(seal)
~~~
This line deletes the seal instance from the session, effectively removing it from the database.

```python
models.session.commit()
~~~
This line commits the deletion of the seal instance to the database.

```python
print(seal_log)
~~~
This line prints the representation of the seal_log instance, showing its attributes.

```python
models.session.delete(lion_log)
~~~
This line deletes the lion_log instance (lion’s logbook entry) from the session and database.

```python
models.session.commit()
~~~
This line commits the deletion of the lion_log instance to the database.

```python
lions.log
~~~
This line seems to have a typo. It should likely be lion.logs to access the logbook entries associated with the lion.

The provided code demonstrates interactions with a database using SQLAlchemy and the defined models for animals and logbook entries. It showcases querying, updating, and deleting data, as well as relationships between different tables.

Question 5

Q

With the following file models.py containing the following code, I want you to explain the following code line by line, mainly the cascading part and what cascading is like you would explain a beginner:

# Animals
# ID / Name / Habitat

Zookeeper Log
# ID / Animal ID (Foreign Key) / Notes

from sqlalchemy import (create_engine, Column, Integer, 
                        String, ForeignKey)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("sqlite:///zoo.db", echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Animal(Base):
    \_\_tablename\_\_ = 'animals'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    habitat = Column(String)
    logs = relationship("Logbook", back_populates="animal",
                        cascade="all, delete, delete-orphan") 

    def \_\_repr\_\_(self):
        return f"""
        \nAnimal {self.id}\r
        Name = {self.name}\r
        Habitat = {self.habitat}
        """

class Logbook(Base):
    \_\_tablename\_\_ = 'logbook'

    id = Column(Integer, primary_key=True)
    animal_id = Column(Integer, ForeignKey("animals.id"))
    notes = Column(String)
    animal = relationship("Animal", back_populates="logs")

    def \_\_repr\_\_(self):
        return f"""
        \nLogbook {self.id}\r
        Animal ID = {self.animal_id}\r
        Notes = {self.notes}
        """
    
    def \_\_repr\_\_(self):
        return f"""
        \nLogbook {self.id}\r
        Animal ID = {self.animal_id}\r
        Notes = {self.notes}
        """
if \_\_name\_\_ == "\_\_main\_\_":
    Base.metadata.create_all(engine)

The following code is run in Terminal, I want you to explain each line of the code like you would explain a beginner:
~~~
python3
import models
lion = models.Animal(name=”lion”, habitat=”savannah”)

models.session.add(lion)
models.session.commit()

lion_log = models.Logbook(animal_id=”1”, notes=”great pouncer”)

models.session.add(lion_log)
models.session.commit()

seal = models.Animal(name=”seal”, habitat=”seas”)

models.session.add(seal)
models.session.commit()

seal_log = models.Logbook(animal_id=”2”, notes=”likes to wave”)

models.session.add(seal_log)
models.session.commit()

models.session.delete(seal)
models.session.commit()

for logs in models.session.query(models.Logbook):
print(logs)

for animal in models.session.query(models.Animal):
print(animal)

del lion.logs[0]

for logs in models.session.query(models.Logbook):
print(logs)
~~~

Answer

A

Certainly! Let’s break this down step by step.

models.py Explanation:

Imports

```python
from sqlalchemy import (create_engine, Column, Integer,
String, ForeignKey)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
~~~

These lines import the necessary components from the SQLAlchemy library, which provides a toolkit for working with relational databases in Python.

create_engine: Creates a new engine instance.
Column, Integer, String, ForeignKey: Used to define table columns.
declarative_base: A base class for declarative classes.
sessionmaker: A factory for creating new Session objects.
relationship: Defines a relationship between two tables.

Database Initialization

```python
engine = create_engine(“sqlite:///zoo.db”, echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
~~~

engine: This line initializes a connection to a SQLite database named zoo.db.
Session: This line defines a factory for creating new session objects. Sessions are the “handles” to the database.
session: This line creates an instance of the Session.
Base: This line defines a base class for all the declarative base classes.

Animal Class

```python
class Animal(Base):
__tablename__ = ‘animals’

id = Column(Integer, primary_key=True)
name = Column(String)
habitat = Column(String)
logs = relationship("Logbook", back_populates="animal",
                    cascade="all, delete, delete-orphan") ~~~

This class defines the Animal table.
\_\_tablename\_\_: This line names the table animals.
id, name, and habitat: These lines define columns in the animals table.
logs: This line establishes a relationship between the Animal and Logbook tables.
- back_populates: Synchronizes the two sides of the relationship.
- cascade: Determines how changes to the parent object (Animal) should cascade to the child object (Logbook). In this case:
  - "all": Corresponds to all cascade options being applied.
  - "delete": Cascade the deletion of parent items to the child.
  - "delete-orphan": Delete child items that are de-associated from the parent.

Logbook Class

```python
class Logbook(Base):
__tablename__ = ‘logbook’

id = Column(Integer, primary_key=True)
animal_id = Column(Integer, ForeignKey("animals.id"))
notes = Column(String)
animal = relationship("Animal", back_populates="logs") ~~~

This class defines the Logbook table.
id: A unique identifier for each log.
animal_id: A foreign key that links to the id of the Animal table.
notes: Contains additional notes about the animal.
animal: This line establishes a relationship between the Logbook and Animal tables.

Main Function

```python
if __name__ == “__main__”:
Base.metadata.create_all(engine)
~~~

If this script is run directly (not imported elsewhere), then the database tables are created using the create_all method.

Terminal Code Explanation:

python3: Start the Python 3 interpreter.
import models: Import the models.py file.
lion = models.Animal(name="lion", habitat="savannah"): Create a new Animal object named lion.
models.session.add(lion): Add the lion object to the session (but it’s not saved to the database yet).
models.session.commit(): Commit (save) the lion object to the database.
… (Repeat steps 3-5 for the lion_log, seal, and seal_log objects.)
models.session.delete(seal): Delete the seal object from the database. Due to cascading, any logbook entries for the seal will also be deleted.
Query and print all logbook entries and all animals.
del lion.logs[0]: Delete the first logbook entry associated with lion. This demonstrates the cascading delete in action.
Query and print all logbook entries to see the changes.

Hope this helps! Let me know if you have any further questions.

Brainscape's Knowledge GenomeTM

5.2 Relational Databases With SQLAlchemy (42m) Flashcards

Brainscape's Knowledge Genome^TM