OverIQ.com

  1. Home
  2. SQLAlchemy Tutorial
  3. Defining Schema in SQLAlchemy ORM

Defining Schema in SQLAlchemy ORM

Last updated on July 27, 2020

SQLAlchemy ORM #

SQLAlchemy ORM (Object Relational Mapper) is a way to define tables and relationship between them using Python classes. It also provides a system to query and manipulate the database using object-oriented code instead of writing SQL. Unlike SQLAlchemy Core, which is focused around tables, rows and columns; the ORM is focused around objects and models.

ORM is built upon the SQLAlchemy Core, so everything you have learned so far still applies.

As you will see, an ORM allows you to be more productive but it also adds additional overhead to your queries. However, for most applications, its benefits far outweighs the performance penalty.

Note: Before moving on to the next section delete all the tables from the sqlalchemy-tuts database using the following command:

metadata.drop_all(engine)

Defining Models #

A Model is a Python class which corresponds to the database table and its attributes represent the columns.

For the class to be a valid model, it must do the following:

  1. Inherit from a declarative base class created by calling declarative_base() function.
  2. define table name via __tablename__ attribute.
  3. define at-least one column which must be a part of the primary key.

The last two points are self-explanatory but the first one deserves a bit of explanation.

The base class maintains a catalog of classes and tables. In other words, the declarative base class wraps the mapper and the MetaData. The mapper maps the subclass to the table and MetaData holds all the information about the database and the tables it contains. Just as in Core, in ORM we use create_all() and drop_all() methods of the MetaData object to create and drop tables.

The following listing defines a Post model which can be used to store blog posts.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
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 step through the code line by line.

  1. In line 1-4, we import necessary classes and functions.
  2. In line 6, we create a base declarative class by calling the declarative_base() function.
  3. In line 10-16, we define columns as class attributes.

Notice that we are using the same Column class to define the columns as we did while using SQLAlchemy Core. The only difference is that now the first argument is type instead of the column name. Also, notice that the keyword arguments passed to the Column() works exactly the same in ORM and Core.

Since ORM is built upon Core, SQLAlchemy will use the model definition to create a Table object and associate it with the model by calling the mapper() function. This completes the process of mapping the Post model with its corresponding Table instance. We can now use Post model to query and manipulate the database.

We can peek at the Table instance associated with the model using the __table__ attribute.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>>
>>> Post.__table__
Table('posts', MetaData(bind=None), 
Column('id', Integer(), table=<posts>, primary_key=True, nullable=False), 
Column('title', String(length=100), table=<posts>, nullable=False), 
Column('slug', String(length=100), table=<posts>, nullable=False), 
Column('content', String(length=50), table=<posts>, nullable=False), 
Column('published', String(length=200), table=<posts>, nullable=False), Column('created_on', DateTime(), table=<posts>, default=ColumnDefault(<function datetime.now at 0x7f226c152bf8>)), 
Column('updated_on', DateTime(), table=<posts>, onupdate=ColumnDefault(<function datetime.now at 0x7f226c162268>), default=ColumnDefault(<function datetime.now at 0x7f226c1621e0>)), 
schema=None)
>>>

Classical Mappings #

After reading the above section you might get an impression that to use SQLAlchemy's ORM, you will have to rewrite your existing Table instances as Models. However, this is simply not true.

It turns out that you can map any plain Python class to a Table instance using the mapper() function. Here is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
from sqlalchemy import MetaData, Table, 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)

The mapper() class takes two arguments: class to be mapped and the Table object.

Just by doing this the Post class will have attributes that corresponds to the columns of the table. Thus, the Post class now has following attributes:

  • post.id
  • post.title
  • post.slug
  • post.content
  • post.published
  • post.created_on
  • post.updated_on

The code in the above listing is functionally equivalent to the Post model we defined earlier.

Now you should have a good idea of what declarative_base() is doing for us behind the scenes.

Adding Keys and Constraints #

When using ORM, we add keys and constraints to model using the __table_args__ attribute.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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   
    )

Defining Relationships #

One to Many #

We create a one-to-many relationship by placing a foreign key on the child class. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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__ = 'authors'
    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 model.

The relationship() function adds attributes on the models to access the related data. At its minimum, it accepts the name of the class representing the target of the relationship.

The line books = relationship("Book") adds a books attribute to the Author class.

Given an Author object a, we can now access books written by him using a.books.

What if we want to access book's author from a Book object?

One way to achieve this is to define separate relationship() on Author model like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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__ = 'authors'
    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")

Given a Book object b, we can now access its author as b.author.

Alternatively, we can use the backref parameters to specify the attribute name to be added on the other side of the relationship.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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")

class Book(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    title = Column(string(100), nullable=False)
    copyright = Column(SmallInteger, nullable=False)
    author_id = Column(Integer, ForeignKey('authors.id'))

Further, you can define relationship() on any side of the relationship. Thus, the preceding code can also be written as:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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__ = 'authors'
    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 #

Establishing a one-to-one relationship in SQLAlchemy is almost the same as one-to-many relationship, the only difference is that we pass an additional argument uselist=False to the relationship() function. Here is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
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'))  # Foreign key

Given a Person object p, p.dl would return a DriverLicense object. If we hadn't passed uselist=False to the relationship() function then the relationship between Person and DriverLicense would be one-to-many and p.dl would return a list of DriverLicense objects rather than a single object. The uselist=False argument doesn't have any effect on the persons attribute of the DriverLicense object. As usual, it will return a Person object.

Many to Many #

Creating a many-to-many relationship requires an extra table called an association table or an intermediary table. We define this table as an instance of the Table class and then connect it to the model using the secondary argument of the relationship() function.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
Base = declarative_base()

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

An author can write one or more books. Similarly, a book can be written by multiple authors. Thus, there is a many-to-many relationship between author and book.

To represent many-to-many relationship we have created an association table called author_book.

Given an Author object a, we can access all books written by him as a.books. Similarly, given a Book object b, b.authors will return a list of Author objects.

In this case, we have defined relationship() on the Book model, but we could have just as easily defined it in the Author model.

There will be times when you want to store additional data in the association table. To achieve this we have to define association table as a model class.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
Base = declarative_base()

class Author_Book(Base):
    __tablename__ = 'author_book'
    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 #

As in SQLAlchemy Core, use use create_all() method of the MetaData instance to create the table.

Base.metadata.create_all(engine)

To drop the tables call drop_all method.

Base.metadata.drop_all(engine)

We will now re-define the tables using models and persists them to the database by calling the create_all() method. Here is the complete code to do so:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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("postgres+psycopg2://postgres:pass@localhost/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)
#     orders = relationship("Order", backref='customer')
    

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", secondary="order_lines", 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)

In the next chapter, we will learn how to work with the database using SQLAlchemy ORM.

SQLAlchemy Tutorial

Recent Posts