Hands-On Enterprise Application Development with Python

上QQ阅读APP看书，第一时间看更新

Building optimal database models

The first step to achieve any efficient access to your database is to build an optimal model for your database. If a model is not optimal, the rest of the techniques to speed up access to the database will make very little difference.

But before we dive into how we can build an optimal model for the database, let's first see how we can actually build any model for our database using SQLAlchemy.

For this example, let's imagine we want to build a model to represent a user in our BugZot application. In our BugZot application, a user will be required to provide the following fields:

First name and last name
Username
Email address
Password

Additionally, our BugZot application also needs to maintain some more information about the user, such as their membership level in the system, the privileges the user is entitled to, whether the user account is active or not, and the activation key that is sent to the user to activate their account.

Now, let's see what happens if we try to model our user table with these requirements using SQLAlchemy. The following code depicts how we build a user model in SQLAlchemy:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Boolean, Date, Integer, String, Column
from datetime import datetime

# Initialize the declarative base model
Base = declarative_base()

# Construct our User model
class User(Base):
 __tablename__ = 'users'

 id = Column(Integer, primary_key=True, autoincrement=True)
 first_name = Column(String, nullable=False)
 last_name = Column(String, nullable=False)
 username = Column(String(length=25), unique=True, nullable=False)
 email = Column(String(length=255), unique=True, nullable=False)
 password = Column(String(length=255), nullable=False)
 date_joined = Column(Date, default=datetime.now())
 user_role = Column(String, nullable=False)
 user_role_permissions = Column(Integer, nullable=False)
 account_active = Column(Boolean, default=False)
 activation_key = Column(String(length=32))

 def __repr__(self):
 return "<User {}>".format(self.username)

This example shows how we can build a model using SQLAlchemy. Now, let's take a look at what we did in the code sample.

In the starting part of the code sample, we first imported the declarative_base method that is responsible for providing the base class for our models.

The Base = declarative_base() line assigns the base model to our base variable.

The next thing we did was to include the different datatypes from SQLAlchemy that we will be using in our definition of the model.

The final import imports the Python datetime library that we will be using in our database model.

Now, without considering how our code will populate the different fields of the database model, let's take a look at how we designed our user model.

The first step of designing the model was to define a class user that acts as our model class. This class derives from the base model that we initialized earlier in our code.

The __tablename__ = 'users' line defines the name that should be given to the table when this database model is realized inside the database.

Following on from there, we start to define the columns our table will consist of. To define the column, we use a key=value type approach, where the key defines the name of the column, and the value defines the attributes of the column.

For example, to define the column id, which should be of integer type and should act as a primary key for table users, we define it like this:

id = Column(Integer, primary_key=True, autoincrement=True)

We can now see how simple it is. We did not have to write any SQL to define our column. Similarly, it is quite easy to enforce that a particular field should have a unique value and cannot have null as a value by just passing unique=True and nullable=False parameters to the column constructor, as can be taken as an example from the following line:

username = Column(String(length=25), unique=True, nullable=False)

After we have defined all the columns, we provide the definition for the __repr__ method. The __repr__ method is a magic method that is called by the internal repr() Python method to provide the representation of the object, such as when a user issues print(userobj).

This completes our definition of our user model using SQLAlchemy. It was simple, wasn't it? We did not have to write any SQL; we just quickly added the columns to a class and left everything else for SQLAlchemy to deal with. Now, while all of this was quite fun and easy to achieve, we made some mistakes, which doesn't seem to have caused any harm now, but will prove to be costly as our application scales up. Let's take a look at these mistakes.