SQLAlchemy Basic Setup
The project setup should have the following directories and files. I will go through the contents of the files shortly however for now lets just get the basic structure in place.
[project Folder]
|--app
| |--__init__.py
| |--models.py
|--config.py
|--main.pyOnce the folder are created and the empty files are in place lets create the venv folder within the project and enter into the virtual environment. If you can’t remember how to do this there is a post about it here: python-venv-virtual-environments
Now lets install the python packages that we are going to be using:
(venv) $ pip install flask
(venv) $ pip install Flask-SQLAlchemy
(venv) $ pip install Flask-Migrate
(venv) $ pip install pyodbcWith everything being in place we can begin filling out the code required in each of the python files.
To keep things nicely organized, I’m going to create the configuration class in a separate Python module. Below you can see the new configuration class for this application, stored in a config.py module in the top-level directory.
/config.py
import os
basedir = os.path.abspath(os.path.dirname(file))
class Config:
SECRET_KEY = 'you-will-never-guess'
# This is the URI for SQLite
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///' + os.path.join(basedir, 'app.db')
# This is the URI for MS SQL
SQLALCHEMY_DATABASE_URI = 'mssql+pyodbc://username:password@Server_Name:1433/DB_Name? driver=ODBC+Driver+17+for+SQL+Server'
# This is the URI for MS SQL
SQLALCHEMY_DATABASE_URI = 'mysql://username:password@Server_Name/DB_Name'The configuration settings are defined as class variables inside the Config class. Flask and some of its extensions use the value of the secret key as a cryptographic key, useful to generate signatures or tokens. The Flask-WTF extension uses it to protect web forms against Cross-Site Request Forgery or CSRF.
The next three lines starting with SQLALCHEMY_DATABASE_URI specifies the applications database connection. You will only need to use one of these connection they specify the connection strings for SQLite, MySQL and MS SQL.
app/init.py
from flask import Flask
from config import Config # Importing the Config class from the config.py file
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
app = Flask(name)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)
from app import modelsWe first create the application object as an instance of class Flask imported from the flask package. We then import the config class from the config.py file, under which we import the SQLAlchemy and Migrate modules
The name variable passed to the Flask class is a Python predefined variable, which is set to the name of the module in which it is used. Flask uses the location of the module passed here as a starting point when it needs to load associated resources such as template files
We then pass in the Config class to the app created above.
We add a db object that represents the database. Then add migrate passing it the app and db objects, to represent the database migration engine.
Finally I importing a new module called models at the bottom. This module will define the structure of the database. The models module is imported at the bottom and not at the top of the script as it is always done. The bottom import is a workaround that avoids circular imports, a common problem with Flask applications.
/app/models.py
from datetime import datetime, timezone
from typing import Optional
import sqlalchemy as sa # General purpose database functions
import sqlalchemy.orm as so # Provides the support for using models
from app import db
class User(db.Model):
id: so.Mapped[int] = so.mapped_column(primary_key=True)
username: so.Mapped[str] = so.mapped_column(sa.String(64), index=True, unique=True)
email: so.Mapped[str] = so.mapped_column(sa.String(120), index=True, unique=True)
password_hash: so.Mapped[Optional[str]] = so.mapped_column(sa.String(256))
#posts: so.WriteOnlyMapped['Post'] = so.relationship(back_populates='author')
def __repr__(self):
return '<User {}>'.format(self.username)We start by importing the datetime module which isn’t actually used in the class but your going to be righting stuff for a database so your going to be setting some timestamps which will probably be used as below so just import it:
timestamp: so.Mapped[datetime] = so.mapped_column(
index=True, default=lambda: datetime.now(timezone.utc))sqlalchemy and sqlalchemy.orm modules from the SQLAlchemy package, which provide most of the elements that are needed to work with a database. The sqlalchemy module includes general purpose database functions and classes such as types and query building helpers, while sqlalchemy.orm provides the support for using models. Given that these two module names are long and will need to be referenced often, the sa and so aliases are defined directly in the import statements. The db instance from Flask-SQLAlchemy and the Optional typing hint from Python are imported as well.
The User class created above will represent users stored in the database. The class inherits from db.Model, a base class for all models from Flask-SQLAlchemy. The User model defines several fields as class variables. These are the columns that will be created in the corresponding database table.
Fields are assigned a type using Python type hints, wrapped with SQLAlchemy’s so.Mapped generic type. A type declaration such as so.Mapped[int] or so.Mapped[str] define the type of the column, and also make values required, or non-nullable in database terms. To define a column that is allowed to be null, the Optional helper from Python is also added, as password_hash demonstrates.
In most cases defining a table column requires more than the column type. SQLAlchemy uses a so.mapped_column() function call assigned to each column to provide this additional configuration. In the case of id above, the column is configured as the primary key. For string columns many databases require a length to be given, so this is also included. I have included other optional arguments that allow us to indicate which fields are unique and indexed, which is important so that database is consistent and searches are efficient.
/main.py
import sqlalchemy as sa
import sqlalchemy.orm as so
from app import app, db
from app.models import User
@app.shell_context_processor
def make_shell_context():
return {'sa': sa, 'so': so, 'db': db, 'user': User}First we import the SQLAlchemy and SQLAlchemy.ORM modules as aliases in the same we we did in models. Flask application instance is called app and is a member of the app package. The from app import app statement imports the app variable that is a member of the app package as well as the db variable which is the database.
The app.shell_context_processor decorator registers the function as a shell context function. When the flask shell command runs, it will invoke this function and register the items returned by it in the shell session. The reason the function returns a dictionary and not a list is that for each item you have to also provide a name under which it will be referenced in the shell, which is given by the dictionary keys.
After you add the shell context processor function you can work with database entities without having to import them:
(venv) $ flask shell
>>>db
<SQLAlchemy mssql+pyodbc://Username:***@SQL_Server:1433/Test_Database?driver=ODBC+Driver+17+for+SQL+Server>
>>>
>>>User
<class 'app.models.User'>Flask-Migrate
Flask-Migrate exposes its commands through the flask command. You have already seen flask run, which is a sub-command that is native to Flask. The flask db sub-command is added by Flask-Migrate to manage everything related to database migrations. So let’s create the migration repository for microblog by running flask db init:
(venv) $ flask db init
(venv) $ flask db migrate -m "users table"
(venv) $ flask db upgradeCLI Database Interaction
To interact with the database from the python command line first run python
Import the application, database instance, models and SQLAlchemy entry point:
from app import app, db
from app.models import User
import sqlalchemy as saAn application context must be created and pushed.
app.app_context().push()Now we can create a new user:
u = User(username='Fin', email='fin@example.com')
db.session.add(u)
db.session.commit()Changes to a database are done in the context of a database session, which can be accessed as db.session. Multiple changes can be accumulated in a session and once all the changes have been registered you can issue a single db.session.commit(), which writes all the changes atomically.
Let’s add another couple of user:
u = User(username='Eira', email='eira@example.com')
db.session.add(u)
u = User(username='Vix', email='vix@example.com')
db.session.add(u)
db.session.commit()See how both the users where added to the database context and written to the database with the commit.
The database can answer a query that returns all the users.
query = sa.select(User)
users = db.session.scalars(query).all()
usersThe query variable in this example is assigned a basic query that selects all the users. This is achieved by passing the model class to the SQLAlchemy sa.select() query helper function. You will find that most database queries start from a sa.select() call.
The database session, which above was used to define and commit changes, is also used to execute queries. The db.session.scalars() method executes the database query and returns a results iterator. Calling the all() method of the results object converts the results to a plain list.
In many situations it is most efficient to use the results iterator in a for-loop instead of converting it to a list:
users = db.session.scalars(query)
for u in users:
… print(u.id, u.username)
…
