SQLAlchemy

An ORM for the EPIC WINNING

Objectives

  1. Describe what an ORM is and how it is used
  2. Use SQLAlchemy syntax to build tables
  3. Model all types of  SQL relationships with SQLAlchemy (1-to-1, 1-to-many, many-to-many)
  4. Use SQLAlchemy to CRUD our Flask App

1. What is an ORM?

  • ORM - "Object Relational Mapping"
  • Used to manage database relationships
  • Simplifies SQL statements through abstraction (Models)

What is an ORM?

A conceptual perspective:

SQL         Object Oriented Programming (OOP)

Table  <->  Class
Column <->  Attribute
Row    <->  Instance

ORM's allow us to use SQL using OOP concepts

Your Turn!

Spend 5 minutes and

write an OOP model  that creates a book class with:

  • A title
  • An author
  • A publication year

and create a book "Harry Potter" by "J.K. Rowling" from 1997

Your Turn!

Now, turn to a neighbor and discuss for 2 minutes:

Why might it be useful to use an ORM?

What might some of the costs to using an ORM be?

2. Use SQLAlchemy to build Tables

 

Setup in the terminal in your chosen dir:

# create a new database
createdb sqlalchemy_app

# install needed dependencies
pip install flask flask_sqlalchemy psycopg2

# create an env folder and setup/source it 
virtualenv env
source env/bin/activate

#create the needed files and structure
mkdir views
touch app.py models.py

SQLAlchemy Setup

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =
 'postgres://localhost/sqlalchemy_app'
db = SQLAlchemy(app)

In your app & models files in your root dir:

What's happening here?

SQLAlchemy Setup

class Book(db.Model):

    __tablename__ = 'books'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.Text())
    author = db.Column(db.Text())

    def __init__(self, title, author):
        self.title = title
        self.author = author

    def __repr__(self):
        return 'title {} - author {}'.format(self.title, self.author)

In your models.py file in your root dir:

SQLAlchemy Setup

from models import *

db.drop_all()

db.create_all() # this creates the database table(s)

cats_cradle = Book('cats cradle', 'kurt')
harry_potter = Book('harry potter', 'jk')

db.session.add(cats_cradle)
db.session.add(harry_potter)

db.session.commit()

In your app.py file in your root dir:

Model a 1-to-1 relationship with SQLAlchemy

Objectives

  1. Something
  2. Something Else
  3. Some other thing

This is revisiting Post It, time for a CFU

Discuss:

What is Something?

Where would we use Something?

3 ways to refactor your last project into Something-style

You can use the technique Cold Calling

You can also use Turn and Talk, Batch Process, or Think Pair Share

Something Else

Setup

Something else

What does this evaluate to?

Objectives

  1. Something
  2. Something Else
  3. Some other thing

Revisiting Post It!

Time for a CFU!

Reflect

Show how to use Something Else on your whiteboards

How do we use Something Else with Something?

This technique is called Everybody Writes

Take Note:

Something Else:

  1. Do Something
  2. Do Another Thing
  3. One More Thing
  4. These Are The Steps
  5. I have Named The Steps

This technique is called Name The Steps

Anytime you Name The Steps, make them write it with BOARD = PAPER

Some other thing

Objectives

  1. Something
  2. Something Else
  3. Some other thing

Check Your Understanding

  1. Pull up Something
  2. Use Something Else
  3. Try Some Other Thing
  4. Check your work and refactor

This technique is called Name The Steps

They should write it down, the BOARD = PAPER techique

Exercise:

10 Minutes: try to do Something

10 Minutes: Do Something Else, Recursively

25 Minutes: Use Some Other Thing to fix the problem with Something Else

This technique is called Brighten Lines

Copy of SQL Alchemy and Flask

By Matthew Williams

Copy of SQL Alchemy and Flask

  • 703