Creating Models with SQLAlchemy

Learn about how to use SQLAlchemy to create models in this guest post by Daniel Gaspar, a programmer and DevOps engineer with over 20 years of experience.

Models are a means of abstracting and providing a common interface to access data. In most web applications, data is stored and retrieved from a relational database management system (RDBMS), which is a database that holds data in a tabular format with rows and columns and is able to implement a relational model with data across tables. Some examples include MySQL, Postgres, Oracle, and MSSQL.

In order to create models on top of our database, we will use a Python package named SQLAlchemy. SQLAlchemy is a database API at its lowest level, and performs object relational mapping at its highest level. An ORM (object relational mapper) is a tool that allows developers to store and retrieve data using object-oriented approaches and solve object-relational mismatches—a set of conceptual and technical difficulties that are often encountered when a relational database management system is being used by a program that is written in an object-oriented programming language.

Relational and object-oriented models are so different that additional code and functionalities are required to make them work together efficiently. This creates a virtual object database and converts data between the large number of types in databases into the mix of types and objects in Python.

Also, a programming language such as Python allows you to have different objects that hold references to each other, and to get and set their attributes. An ORM like SQLAlchemy helps translate these when inserting them into a traditional database.

In order to tie SQLAlchemy into our application context, we will use Flask SQLAlchemy. Flask SQLAlchemy is a convenience layer on top of SQLAlchemy that provides useful defaults and Flask-specific functions.

Setting up SQLAlchemy

If you have never installed a database, or you do not have a preference, then SQLite is the best option for beginners, or if you want to quickly bootstrap a proof of concept.

SQLite is an SQL-embedded database engine that is fast, works without a server, and is entirely contained in one file. SQLite is also natively supported in Python, so if you choose to go with SQLite, a SQLite database will be automatically created for you during the exercise later in this article.

Python packages

Flask SQLAlchemy can be used with multiple database engines, such as ORACLE, MSSQL, MySQL, PostgreSQL, SQLite, and Sybase, but we need to install additional specific packages for these engines. Now it is time to bootstrap our project by creating a new virtual environment for all our application's dependencies. This virtual environment will be used for a blogging application. Enter the following code:

$ virtualenv env

Then, in requirements.txt, add the following code to install the package:

flask-sqlalchemy

You will also need to install specific packages for your chosen database that will act as the connector for SQLAlchemy, so add the specific packages for your engine in requirements.txt, as shown in the following code. SQLite users can skip this step:

# MySQLPyMySQL# Postgrespsycopg2# MSSQLpyodbc# Oraclecx_Oracle

Finally, activate and install the dependencies using the following code:

$ source env/bin/activate

$ pip install -r requirements.txt

Flask SQLAlchemy

Before we can abstract our data, we need to set up Flask SQLAlchemy. SQLAlchemy creates its database connection through a special database URI. This is a string that looks like a URL that contains all the information that SQLAlchemy needs to connect. It takes the general form of the following code:

databasetype+driver://user:password@host:port/db_name

For each driver that you installed previously, the URI would be as follows:

# SQLite connection string/uri is a path to the database file - relative or absolute.sqlite:///database.db 

# MySQL 

mysql+pymysql://user:password@ip:port/db_name 

# Postgres 

postgresql+psycopg2://user:password@ip:port/db_name 

# MSSQL 

mssql+pyodbc://user:password@dsn_name 

# Oracle 

oracle+cx_oracle://user:password@ip:port/db_name

In our config.py file, add the URI to the DevConfig file with the following:

class DevConfig(Config):   debug = True   SQLALCHEMY_DATABASE_URI = "YOUR URI"

Setting up the first model

You may have noticed that we did not actually create any tables in our database to abstract from. This is because SQLAlchemy allows us to create either models from tables or tables from our models.

In our main.py file, SQLAlchemy must first be initialized with our app as follows:

from flask import Flask

from flask_sqlalchemy import SQLAlchemy

from config import DevConfig 

app = Flask(__name__)

app.config.from_object(DevConfig)

db = SQLAlchemy(app)

SQLAlchemy will read our app's configuration and automatically connect to our database. Let's create a User model to interact with a user table in the main.py file, as follows:

class User(db.Model):

   id = db.Column(db.Integer(), primary_key=True)

   username = db.Column(db.String(255))

   password = db.Column(db.String(255))

    def __init__(self, username):

     self.username = username

    def __repr__(self):

     return "<User '{}'>".format(self.username)

What have we accomplished? We now have a model that is based on a user table with three columns. When we inherit from db.Model, the entire connection and communication with the database will already be handled for us.

Each class variable that is the db.Column instance represents a column in the database. There is an optional first argument in the db.Column instance that allows us to specify the name of the column in the database. Without it, SQLAlchemy will assume that the name of the variable is the same as the name of the column. Using this, optional variable would look like the following:

username = db.Column('user_name', db.String(255))

The second argument to db.Column tells SQLAlchemy what type the column should be treated as. The main types that we will work with in this book are as follows:

• db.String
• db.Text
• db.Integer
• db.Float
• db.Boolean
• db.Date
• db.DateTime
• db.Time

What each type represents is rather simple, as shown in the following list:

• The String and Text types take Python strings and translate them to the varchar and text type columns, respectively.
• The Integer and Float types take any Python number and translates it into the correct type before inserting it into the database.
• Boolean takes Python True or False statements and, if the database has a boolean type, inserts a Boolean into the database. If there is no boolean type in the database, SQLAlchemy automatically translates between Python Booleans and a 0 or a 1 in the database.
• The Date, DateTime, and Time types use the Python types of the same names from the datetimenative library and translates them into the database.

The String, Integer, and Float types take an extra argument that tells SQLAlchemy the length limit of our column.

Note

If you wish to truly understand how SQLAlchemy translates your code into SQL queries, add the following to the DevConfig file: SQLALCHEMY_ECHO = True. This will print out the created queries to the Terminal.

The primary_key argument tells SQLAlchemy that this column has the primary key index on it. Each SQLAlchemy model requires a primary key to function. All object-relationally mapped objects are linked to their database rows within the session via an identity map, a pattern central to the unit of work mechanism implemented in SQLAlchemy. That's why we need primary keys to be declared in the model.

SQLAlchemy will assume that the name of your table is the lowercase version of your model class name. However, what if we want our table to be called something other than user? To tell SQLAlchemy what name to use, add the __tablename__ class variable. 

This is also how you connect to tables that already exist in your database. Just place the name of the table in the following string:

class User(db.Model):

   __tablename__ = 'user_table_name'

    id = db.Column(db.Integer(), primary_key=True)

   username = db.Column(db.String(255))

   password = db.Column(db.String(255))

We don't have to include the __init__ or __repr__ functions. If we don't, then SQLAlchemy will automatically create an __init__ function that accepts the names and values of your columns as keyword arguments.

Note

Naming a table user using an ORM may lead to problems, since in MySQL, user is a reserved word. One of the advantages of using an ORM is that you can easily migrate your engine from SQLite to MySQL and then to ORACLE, for example. One very easy fix would be to prefix your schema and use. 

Creating the user table

Using SQLAlchemy to do the heavy lifting, we will now create the user table in our database. Update manage.py to the following:

from main import app, db, User

@app.shell_context_processor

def make_shell_context():

  return dict(app=app, db=db, User=User)

This will allow us to work with our models in the Flask shell, because we are injecting. Run the shell now and use db.create_all() to create all of the tables, as shown in the following code:

# Tell Flask where to load our shell context

$ export FLASK_APP=manage.py

    $ flask shell>>> db.create_all()

In your database, you should now see a table called users with the columns specified. Also, if you are using SQLite, you should now see a file named database.db in your file structure, as shown in the following code:

$ sqlite3 database.db .tables

user

If you found this article helpful, you can explore Mastering Flask Web Development – Second Edition to build enterprise-grade and scalable Python web applications. Flask is a popular Python framework known for its lightweight and modular design, so it’s easy to transform it to the web framework that you need with a few extensions without weighing it down. Mastering Flask Web Development – Second Edition will take you on a complete tour of the Flask environment, so you can build production-ready applications.