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

Something Else

Setup

Something else

What does this evaluate to?

Some other thing

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