Creating a schema in SQLAlchemy ORM

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:

1. Inherit from a declarative base class by calling declarative_base().
2. Declare table name with __tablename__ attribute.
3. 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:

1. Lines 1-4 import the necessary classes and functions.
2. On line 6, a base class is created by calling declarative_base().
3. 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.

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)