SQLAlchemy ORM (Object Relational Mapping) is a way of creating tables and the relationships between them using classes in Python. It also provides a system for creating queries and managing the database using object-oriented code instead of pure SQL. Unlike SQLAlchemy Core, which focuses on tables, rows, and columns, ORM focuses on objects and models. ORM is built on top of SQLAlchemy Core, so the knowledge you have should come in handy. ORM allows you to be more productive, but it also adds additional complexity to queries. However, for most applications, the benefits outweigh the performance loss. Before moving on, remove all tables from sqlalchemy-tuts
with the following command: metadata.drop_all(engine)
. Creating models A model is a Python class that corresponds to a table in the database, and its properties are columns. For a class to be a valid model, you must meet the following requirements:
- Inherit from a declarative base class by calling
declarative_base()
. - Declare table name with
__tablename__
attribute. - Declare at least one column to be part of the primary key.
The last two items speak for themselves, but the first requires details. A base class manages a catalog of classes and tables. In other words, a declarative base class is a shell over the mapper and MetaData
. The mapper relates a subclass to a table, and MetaData
stores all the information about the database and its tables. Similar to Core in ORM, the create_all()
and drop_all()
methods of MetaData
object are used to create and delete tables. The following code shows how to create a Post
model that is used to save blog posts.
from sqlalchemy import create_engine, MetaData, Table, Integer, String,
Column, DateTime, ForeignKey, Numeric
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
Base = declarative_base()
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
slug = Column(String(100), nullable=False)
content = Column(String(50), nullable=False)
published = Column(String(200), nullable=False, unique=True)
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
Let’s analyze it line by line:
- Lines 1-4 import the necessary classes and functions.
- On line 6, a base class is created by calling
declarative_base()
. - On lines 10-16 the columns are declared as class attributes.
It’s worth noting that the same Column
class is used to create columns as the one for SQLAlchemy Core. The only difference is that the first argument is the type, not the column name. The keyword arguments, in turn passed to Column()
, work the same in ORM and Core. Because ORM is built on Core, SQLAlchemy uses the model definition to create a Table
object and link it to the model using mapper()
. This completes the process of mapping the Post model to the corresponding Table
instance. The Post
model can now be used to manage and query the database.
Table of Contents
Classic Mapping
After the last section, you might get the impression that to use the SQLAlchemy ORM you need to rewrite all instances of Table
as models. But this is not the case. You can easily map any Python classes to Table
instances using the mapper()
function. For example:
from sqlalchemy import MetaData, Table, Integer, String, Column, Text, DateTime, Boolean
from sqlalchemy.orm import mapper
from datetime import datetime
metadata = MetaData()
post = Table('post', metadata,
Column('id', Integer(), primary_key=True),
Column('title', String(200), nullable=False),
Column('slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('published', Boolean(), default=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)
class Post(object):
pass
mapper(post, post)
This class takes two arguments: a class to mapp and a Table
object. After that, the Post
class will have attributes corresponding to the columns of the table. So Post
now has the following attributes:
post.id
post.title
post.slug
post.content
post.published
post.created_on
post.updated_on
The code in the list above is equivalent to the Post
model that was declared above. You should now have a better understanding of what declarative_base()
does.
Adding keys and constraints
When using an ORM, keys and constraints are added using the __table_args__
attribute.
from sqlalchemy import Table, Index, Integer, String, Column, Text,
DateTime, Boolean, PrimaryKeyConstraint,
UniqueConstraint, ForeignKeyConstraint
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer)
username = Column(String(100), nullable=False)
email = Column(String(100), nullable=False)
password = Column(String(200), nullable=False)
__table_args__ = (
PrimaryKeyConstraint('id', name='user_pk'),
UniqueConstraint('username'),
UniqueConstraint('email'),
)
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
slug = Column(String(100), nullable=False)
content = Column(String(50), nullable=False)
published = Column(String(200), nullable=False, default=False)
user_id = Column(Integer(), nullable=False)
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
__table_args__ = (
ForeignKeyConstraint(['user_id'], ['users.id']),
Index('title_content_index' 'title', 'content'), # composite index on title and content
)
Relationships
One-to-many
A one-to-many relationship is created by passing a foreign key to a child class. For example:
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Book")
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
The line author_id = Column(Integer, ForeignKey('authors.id'))
establishes a one-to-many relationship between the Author
and Book
models. The relationship()
function adds attributes to the models to access related data. At a minimum, the name of the class responsible for one side of the relationship. The line books = relationship('Book')
adds an attribute books
to the class Author
. If you have an object a
of the Author
class, you can access its books through a.books
. But what if you want to get the author of a book through a Book
object? You can define a separate relationship()
in the Author
model for that:
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Book")
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
author = relationship("Author")
Now you can get the author b.author
through an object b
of the Book
class. Alternatively, you can use backref
parameters to define the name of the attribute to be specified on the other side of the relationship.
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Book", backref="book")
Relationship can be specified on either side of the relationship. Therefore, the previous code can be written like this:
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
author = relationship("Author", backref="books")
One-to-One
Setting up a one-to-one relationship in SQLAlchemy is almost the same as one-to-many. The only difference is that you need to pass an additional argument uselist=False
to relationship()
. For example:
class Person(Base):
__tablename__ = 'persons'
id = Column(Integer(), primary_key=True)
name = Column(String(255), nullable=False)
designation = Column(String(255), nullable=False)
doj = Column(Date(), nullable=False)
dl = relationship('DriverLicense', backref='person', uselist=False)
class DriverLicense(Base):
__tablename__ = 'driverlicense'
id = Column(Integer(), primary_key=True)
license_number = Column(String(255), nullable=False)
renewed_on = Column(Date(), nullable=False)
expiry_date = Column(Date(), nullable=False)
person_id = Column(Integer(), ForeignKey('persons.id'))
Having a p
object of class Person
, p.dl
will return a DriverLicense
object. If you do not pass uselist=False
to the function, a one-to-many relationship is established between Person
and DriverLicense
, and p.dl
will return a list of DriverLicense
objects instead of one. In this case, uselist=False
has no effect on the persons
attribute of the DriverLicense
object. It will return the Person
object as usual.
Many-to-many
You need a separate table for the many-to-many relationship. It is created as an instance of the Table
class and then connected to the model using the secondary
argument of relationship()
.
author_book = Table('author_book', Base.metadata,
Column('author_id', Integer(), ForeignKey('authors.id')),
Column('book_id', Integer(), ForeignKey('books.id')
)
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
author = relationship("Author", secondary=author_book, backref="books")
One author can write one or more books. Likewise, a book can be written by one or more authors. Therefore, a many-to-many relationship is required here. The author_book
table was created to represent this relationship. Through object a
of class Author
you can get all books of the author with a.books
. By analogy, through b
of class Book
you can return the list of authors b.authors
. In this case relationship()
was declared in the Book
model, but it could also be done from the other side. It may be necessary to store additional information in an intermediate table. To do this, you need to define this table as a model class.
class Author_Book(Base):
__tablename__ = 'author_book'
id = Column(Integer, primary_key=True)
author_id = Column(Integer(), ForeignKey("authors.id"))
book_id = Column(Integer(), ForeignKey("books.id"))
extra_data = Column(String(100))
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Author_Book", backref='author')
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
authors = relationship("Author_Book", backref="book")
Creating tables
Similar to SQLAlchemy Core, ORM has a create_all()
method of MetaData
instance that is responsible for creating a table.
Base.metadata.create_all(engine)
To delete all tables there is drop_all
.
Base.metadata.drop_all(engine)
Recreate tables using models and save them in the database by calling create_all()
. Here is all the code for this operation:
from sqlalchemy import create_engine, MetaData, Table, Integer, String,
Column, DateTime, ForeignKey, Numeric, SmallInteger
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime
engine = create_engine("postgresql+psycopg2://postgres:[email protected]/sqlalchemy_tuts")
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customers'
id = Column(Integer(), primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
username = Column(String(50), nullable=False)
email = Column(String(200), nullable=False)
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
orders = relationship("Order", backref='customer')
class Item(Base):
__tablename__ = 'items'
id = Column(Integer(), primary_key=True)
name = Column(String(200), nullable=False)
cost_price = Column(Numeric(10, 2), nullable=False)
selling_price = Column(Numeric(10, 2), nullable=False)
quantity = Column(Integer())
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer(), primary_key=True)
customer_id = Column(Integer(), ForeignKey('customers.id'))
date_placed = Column(DateTime(), default=datetime.now)
line_items = relationship("OrderLine", backref='order')
class OrderLine(Base):
__tablename__ = 'order_lines'
id = Column(Integer(), primary_key=True)
order_id = Column(Integer(), ForeignKey('orders.id'))
item_id = Column(Integer(), ForeignKey('items.id'))
quantity = Column(SmallInteger())
item = relationship("Item")
Base.metadata.create_all(engine)