SQLAlchemy

SQLAlchemy and Flask

A Tutorial by Example

A Talk by:

Thomas Hobohm

Platform

Python 2.6+

Python Libraries: Flask, SQLAlchemy, Flask-SQLAlchemy, and Flask-Migrate!

Operating Systems: Linux, Windows, and OS X.

Required Knowledge

Basic Knowledge of Flask

Talk Structure

What is SQLAlchemy?
Using SQLAlchemy and Flask
A Real-World Example

What is SQLAlchemy?

SQLAlchemy is a Python Database toolkit and Object Relational Mapper.

The Benefits of SQLAlchemy:

You can express SQL through Python.
You can map Python classes to tables.
You can access your database quickly through Python.
Integration with Flask is extremely easy.
Migrations are Handled for You

SQLAlchemy at a High Level

SQLAlchemy consists of two components, the Core and the ORM:

Core

A fully-featured SQL toolkit.
Provides a smooth layer of abstraction over most DB implementations.
Lets you use generative Python expressions to represent SQL.
Maps Python types to database types.

ORM

An optional part of SQLAlchemy.
Maps Python classes to database tables.
Automatically handles Primary and Foreign keys.
Lets you write Query's in raw SQL and get back results as Python Objects.

The ORM

The main feature of SQLAlchemy is the stellar Object Relational Mapper (or ORM). The ORM maps Python classes to Database Tables and Records. Classes mapped to tables are called mapped classes. Each instance of a mapped class would represent a row in the table the mapped class defines. The Declarative module in SQLAlchemy handles mapping classes:

from sqlalchemy.ext.declarative import declarative_base

To start making mapped classes, you must create a Base class, which all of your mapped classes will inherit from:

# Base is a class
Base = declarative_base()

Your mapped classes will inherit from the Base class, like so:

class MyMappedClass(Base):
    ...

Mapped Class Dual Roles

Mapped classes represent tables in a database when defined. Instances of a mapped class represents rows in that table. In this regard, Mapped classes have two roles: They represent the table and the records in the table. This is oftentimes confusing to newcomers, so I will model it with a spreadsheet:

Columns

Mapped classes represent tables, and information in tables is stored in column. You must declare the columns in your mapped classes:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column

Base = declarative_base()

class MyMappedClass(Base):
    username = Column(...)
    email = Column(...)

Columns are represented through Static Class Variables.

Column Types

Each column in your mapped has to have a Type (like variables in statically-typed programming languages). For example, a "username" column would have the "string" type. SQLAlchemy will ensure that you only put values of the proper type in your Columns. These are the types:

Integer

String (size)

Text

DateTime

Float

Boolean

PickleType

LargeBinary

Specifying Types

I've already showed you that Columns must have types. These types are included as the first argument when creating an instance of the Column class:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import String

Base = declarative_base()

class MyMappedClass(Base):
    username = Column(String)
    email = Column(String)

Primary Keys (IDs)

Your models should include a Primary Key column. These are used to uniquely identify each record in your database. Primary keys columns are normally called "id columns". They're of the type "Integer", can be created like so:

id = db.Column(db.Integer, primary_key=True)

Flask-SQLAlchemy automatically assigns unique Keys to records.

Using SQLAlchemy with Flask

Although SQLAlchemy is a great framework on its own, Flask-SQLAlchemy has been created to integrate SQLAlchemy with Flask.

Adding SQLAlchemy support to your Flask Application is this simple:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "/path/to/database/file.db"
db = SQLAlchemy(app)

The SQLALCHEMY_DATABASE_URI config variable is the location of your database file.

The DB Object

In the last slide, we created an object called "db" by creating an instance of the SQLAlchemy class with our Flask application passed in.

This DB object is very important in Flask-SQLAlchemy. It's how we add models to / access our database.

Models

In Flask-SQLAlchemy, mapped classes are called Models. They inherit from the db.Model class:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

All of the classes used to create Mapped Classes in SQLAlchemy are properties of the DB object in Flask-SQLAlchemy.

Primary Keys (IDs)

Primary keys in Flask-SQLAlchemy applications are Columns of the type Integer with the primary_key parameter set to true:

id = db.Column(db.Integer, primary_key=True)

Just like SQLAlchemy, Flask-SQLAlchemy will automatically handle the assignment of unique keys to each Record in your database. Some people will create a Base model with a Primary Key column so they don't have to keep writing out the above code:

class Base(db.Model):
    __abstract__ = True
    id = db.Column(db.Integer, primary_key=True)

Setting the static class variable __abstract__ to True tells SQLAlchemy not to create a database table or mapper for the class. This allows subclasses to extend from the class.

A Column Syntax Primer

In Flask-SQLAlchemy, columns are instances of the db.Column class:

username = db.Column(db.String(20), unique=True)

All of the column types are properties of the db instance. Columns can be specified as Unique:

# If unique is True, SQLAlchemy will make sure that all of the records have unique Usernames
username = db.Column(unique=True / False)

All of the types mentioned previously are properties of the db Object:

db.Integer
db.String
db.Text
db.DateTime
db.Float
db.Boolean
db.PickleType
db.LargeBinary

Instances of Models

SQLAlchemy allows you to manipulate records in your database using instances of your Mapped Classes. You could create instances of the "User" class featured in the last slide like so:

myuser = User()
user.username = "superman3275"
user.email = "superman3275@gmail.com

SQLAlchemy will automatically convert Python types to database types! This allows you to work with your database records in pure Python!

A Model Example

A User model, where Users had a unique (maximum of 12 characters) username, a password, and a short biography, would look like this:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(12), unique=True)
    password = db.Column(db.String(12))
    bio = db.Column(db.String(140))

Oftentimes, in real applications, we want to tie models to each other. For example, many websites tie Users to Posts. Flask-SQLAlchemy abstracts these connetions away to two types of relationships:

One to Many - One User has Many Posts
Many to Many - Many Users Follow Many Other Users

One to Many Relationships

Imagine we have a database constructed from these models:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))

And we want to link Users to Posts. To do this, we have to use a Relationship and a Foreign Key:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    posts = db.relationship("Post", backref="user", lazy="dynamic")

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))

One To Many Explanation

posts = db.relationship("Post", backref="user", lazy="dynamic")

This creates a relationship between the User class and the Post class. We use a string, "Post", to represent the post class because it is declared later in the file. Setting backref="user" creates an attribute called "user" in the post class, which links to the User which created a post. posts is a list of instances of the Post class.

user_id = db.Column(db.Integer, db.ForeignKey("post.id"))

This creaes a foreign key column in the Post table. A foreign key is a field in one table that uniquely identifies a row of another table. This enforces a link between two tables.

Many to Many Relationships

In many applications, people want to have many objects relating to many other objects. For example, in Twitter many people follow many other people. If you want to define Many-to-Many relationships, you must create a helper table. For example, if you wanted to link Pages to Tags (in a Blog, for example):

tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('page_id', db.Integer, db.ForeignKey('page.id'))
)

class Page(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags,
        backref=db.backref('pages', lazy='dynamic'))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)

I'm not going to go into much depth regarding Many to Many relationships, because they're very complex and specialized depending on your application.

Using the Session

To modify your database, you use the db.session object. If you wanted to add a new User to your database:

new_user = User()
new_user.username = "superman3275"
new_user.email = "superman3275@gmail.com"
db.session.add(new_user)

To actually save your changes, however, you must call the commit() method:

db.session.commit()

The session object also allows you to get rid of all the changes you've made:

db.session.rollback()

An Introduction to Querying

Mapped classes have a property called query. You can use this to query all the records in the table that the mapped class represents. The query object has various functions for querying your table. Here are a few of them:

query.get() - Get object using id.

 User.query.get(id) # Returns User object

2. query.filter_by() - Takes values of columns in table as arguments.

 User.query.filter_by(username="superman3275") # Gets users whose username is superman3275

3. It's important to note that query.filter_by() doesn't return a list, it returns a query object with it's own methods. Two of the most useful methods are first() and order_by().

Where to Go From Here

SQLAlchemy has far too many features for me to cover here. These are some of the more advanced features which I left out:

Database Migrations with Alembic (Made by SQLAlchemy creator)!
Advanced Query statements (using Joins, for example).
Using Your own, Optimized SQL Statements.

For Extra Questions, Feel Free to Contact Me (I'll Get Back to You!):

hobohm.business@gmail.com

@superman3275

https://superman3275.github.io

https://www.github.com/superman3275

/u/superman3275

https://www.linkedin.com/superman3275