Introduction

In this tutorial, we are going to use SQLAchemy with SQLite3, but changing the database connection string will allow you to use another database engine. First, we are going to define some models and then we will see how to connect and make some queries. We are going to learn about the Unit of Work pattern, which is a very important concept on sqlalchemy.

Step 1: Installation

Open a terminal and install SQLAlchemy with pip:

pip install sqlalchemy

Step 2: Define the models

We are going to create a file called models.py, in this file we are going to define all the models.

from sqlalchemy import (
    Column, 
    Integer,
    String,
)
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = ‘user’
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)

In the code above we use mapped classes. We use declarative_base() to create the Base class from where all mapped class should inherit. We use tablename to specify the where the users are going to be stored. Then we define the id and name columns with their respective types.

Step 3: Connect to the database

from sqlalchemy import create_engine
from sqlalchemy.orm.session import sessionmaker
from models import User, Base

conn_string = ‘sqlite:///test.sqlite3’
engine = create_engine(conn_string)
Base.metadata.create_all(engine) # here we create all tables
Session = sessionmaker(bind=engine)
session = Session()

# Now we are ready to use the model

new_user = User(name=’test’)
session.add(new_user)
session.commit()

SQLAlchemy uses the session to track all changes on objects. Once you are ready you can commit or flush changes to the database. This is known as the Unit of Work pattern and is a very important concept to learn when using SQLAlchemy.

You can verify that the user was created by using the sqlite3 command:

 $ sqlite3 test.sqlite3
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .tables
user
sqlite> select * from user;
1|test