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