<style>
.present {
text-align: left;
}
</style>

# Using SQLAlchemy with Flask
## Week 18 Day 3

---

## Lecture Videos 1 (18min)
Watch:
- More About Classes (15:55)

---

### Instance variables
- Specific to each instance of the class—changing on one instance won't affect another
- Defined in the constructor and attached to `self`
- You can access on an instance, but not on the class as a whole

class Widget:
    # constructor
    def __init__(self, color):
        # instance variables
        self.color = color


my_widget = Widget("blue")
print(my_widget.color)  # "blue"
print(Widget.color)     # error 

---

### Class variables
- Shared across instances and on the class itself
- Each instance points to the same class variables, so updating the value on the class will change for all instances (unless their value was already changed)
- Updating on one instance will not change other instances

class Widget:
    price = "$5"
    def __init__(self, color):
        # instance variables
        self.color = color


my_widget = Widget("blue")
second_widget = Widget("chartreuse")
print(my_widget.price)  # "$5"
print(Widget.price)     # "$5"
my_widget.price = "$100"
print(second_widget.price) # "$5"
print(Widget.price)        # "$5"
Widget.price = "$50"
print(second_widget.price) # "$50"
print(my_widget.price)     # "$100"

---

### Instance methods
So far, we've written instance methods on classes—that is the default type of method on a class.
- `self` is passed in implicitly as the first parameter—this refers to the instance the method was invoked on

# inside class
def greet_widget(self):
    return f"hello, {self.color} widget"

my_widget = Widget("blue")
print(my_widget.greet_widget())  # "hello blue widget"

---

### Class methods
We can use the `@classmethod` decorator to write class methods.

The first argument will refer to the class itself (conventionally called `cls`), rather than an individual instance.

# inside class
@classmethod
def widget_factory(cls, colors):
    widgets = [cls(color) for color in colors]
    print([widget.greet_widget() for widget in widgets])
    return widgets


print(Widget.widget_factory(["red", 
"yellow", "beige"]))

---

### Static methods
Static methods don't take implicit arguments—they can't access the class or any instance of it.

@staticmethod
def something_about_widgets():
    return "widgets are neat"

---

## Lecture videos 2 (8 min)
Watch:
- FlaskSQLAlchemy Set-Up Best Practices (6:15)

---

### Steps for setting up Flask-SQLAlchemy

1. Create your database & user in psql.

create user widget_user with password 'password';
create database widget_database with owner 'widget_user';

2. Add a DATABASE_URL to your `.env`

DATABASE_URL=postgresql://widget_user:password@localhost/widget_database

3. Install psycopg2, sqlalchemy, and flask-sqlalchemy

pipenv install psycopg2-binary sqlalchemy flask-sqlalchemy

---

### Steps for setting up Flask-SQLAlchemy

4. Modify your config class.
a. Add a class variable called `SQLALCHEMY_DATABASE_URI`, and set the value to the `DATABASE_URL` from your environment
b. Add a class variable called `SQLALCHEMY_TRACK_MODIFICATIONS` and set the value

SQLALCHEMY_DATABASE_URI = os.environ.get("DATABASE_URL")
SQLALCHEMY_TRACK_MODIFICATIONS = False

---

### Steps for setting up Flask-SQLAlchemy

5. Create a models.py file, and instantiate a `SQLAlchemy` object from `flask_sqlalchemy`.

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

---

### Steps for setting up Flask-SQLAlchemy

6. Import the `db` instance into the file where you are defining your app.

from app.models import db
# much additional code...
db.init_app(app)

---

## Lecture Videos 3 (32 min)
Watch:
- FlaskSQLAlchemy: Creating Mapping Files (15:00)
- FlaskSQLAlchemy: Interacting with Data (13:10)

---

### Creating Models

Models inherit from the `db.Model` class, which comes from our `SQLAlchemy` instance.

If you want to specify a tablename, use the `__tablename__` property.

db = SQLALchemy()

class Widget(db.Model):
    __tablename__ = "widgets"

---

### Columns on models

Columns are added to the model as class variables—instances of `db.Column` from our `SQLAlchemy` instance.

Column types (`db.String`, `db.Integer`, etc.) also come from our SQLAlchemy instance.

class Widget(db.Model):
    __tablename__ = "widgets"
    id = db.Column(db.Integer, primary_key=True)
    color = db.Column(db.String(50), nullable=False)

---

### One-To-Many Relationships

Let's make a relationship between widgets and stores. Each widget belongs to one store, stores can have many widgets.

class Store(db.Model):
    __tablename__ = "stores"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)

---

### One-To-Many Relationships
First we need to add a foreign key to the widgets model that points to the store model. To make a column into a foreign key we use `db.ForeignKey(tablename.columnname)`.

class Widget(db.Model):
    __tablename__ = "widgets"
    id = db.Column(db.Integer, primary_key=True)
    color = db.Column(db.String(50), nullable=False)
    store_id = db.Column(db.Integer, db.ForeignKey("stores.id"))

---

### Adding the relationship property
Add a `db.relationship` to both of the models. Use the `back_populates` property to connect the two relationship attributes.

attribute_name = db.relationship("OtherModelName", back_populates="name_of_attribute_from_opposite_model")

# on the Widget model
store = db.relationship("Store", back_populates="widgets")

# on the Store model
widgets = db.relationship("Widget", back_populates="store")

---

### Many-to-Many Relationships

Many-to-many relationships are a lot like one-to-many relationships in terms of how they use the `db.relationship` property.

However, since neither of the two associated tables contains the foreign keys, we would need to build a separate association table to connect the models.

We would use a `db.Table` to do make an association table.

---

### Many-to-Many Relationships

The first argument to `db.Table` will be the table name. Each additional argument is a column. A typical association table only needs two columns—one for each of the two foreign keys.

It does not need its own id column (this is different from Sequelize).

widget_stores = db.Table(
    "widget_stores",
    db.Column(
        "widget_id", db.Integer, db.ForeignKey("widgets.id"), primary_key=True
    ),
    db.Column(
        "store_id", db.Integer, db.ForeignKey("stores.id"), primary_key=True
    )
)

---

### Many-to-Many Relationships

The only change we have to make the `db.relationship` attributes is to add a `secondary` argument. The value here is the table.

attribute_name = db.relationship(
    "OtherModelName",
    secondary=association_table_name,
    back_populates="name_of_attribute_from_opposite_model"
)

---

### Creating records

To make a new row in your database, first create a new instance of one of your model classes.

After you create your instance, add it to your session and commit it to the database.

new_widget = Widget(color="red")
db.session.add(new_widget)
db.session.commit()

---

### Querying with SQLAlchemy

To query the database, we use the `query` attribute on our `db.Model` classes.

Get all records:

widgets = Widget.query.all()

Get one record by primary key:

one_widget = Widget.query.get(id)

---

### Filtering on queries

To apply a filter, you can use the `.filter` method, passing in an operator on a column. Use `.all()` to get a list of all records that match the filter, or use `.first()` to get the first matching record.

# all widgets where color is blue
Widget.query.filter(Widget.color == "blue").all()

# all widgets where the color starts with "r"
Widget.query.filter(Widget.color.like("r%")).first()

[more available operators](https://docs.sqlalchemy.org/en/14/core/operators.html)

---

### Updating records
Once you have queried or created an instance of your model, you can update it by changing a value on that instance, and adding/commiting to the db.

widget = Widget.query.get(1)
widget.store_id = 1
db.session.add(widget)
db.session.commit()

---

### Deleting records

Deleting records uses the `delete` method on the `db.session` object.

widget = Widget.query.get(1)
db.session.delete(widget)
db.session.commit()