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): ...

To start making mapped classes, you must create a Base class, which all of your mapped classes will inherit from:Your mapped classes will inherit from the Base class, like so: 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.

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 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.

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.

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: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

All of the column types are properties of the db instance. Columns can be specified as Unique:All of the types mentioned previously are properties of the db Object: 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!

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 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 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"))

And we want to link Users to Posts. To do this, we have to use a Relationship and a Foreign Key: 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.

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.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.

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() To actually save your changes, however, you must call the commit() method:The session object also allows you to get rid of all the changes you've made:

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().

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().