SQLAlchemy
An ORM for the EPIC WINNING
Objectives
- Describe what an ORM is and how it is used
- Use SQLAlchemy syntax to build tables
- Model all types of SQL relationships with SQLAlchemy (1-to-1, 1-to-many, many-to-many)
- 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
- Something
- Something Else
- 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
- Something
- Something Else
- 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:
- Do Something
- Do Another Thing
- One More Thing
- These Are The Steps
- 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
- Something
- Something Else
- Some other thing
Check Your Understanding
- Pull up Something
- Use Something Else
- Try Some Other Thing
- 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
- 708