Skip to content
Search

Flask-SQLAlchemy

lastmod 2025-02-14created 2024-07-318 edits

Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It simplifies using SQLAlchemy with Flask by setting up common objects and patterns for using those objects, such as a session tied to each web request, models, and engines.

Flask-SQLAlchemy does not change how SQLAlchemy works or is used. See the SQLAlchemy documentation to learn how to work with the ORM in depth. The documentation here will only cover setting up the extension, not how to use SQLAlchemy.

Categories

  • Flask-SQLAlchemy:Examples:SeahQRCodePOC

Simple Example 

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///example.sqlite"

class Base(DeclarativeBase):
  pass

db = SQLAlchemy(app, model_class=Base)

class User(db.Model):
    id: Mapped[int] = mapped_column(db.Integer, primary_key=True)
    username: Mapped[str] = mapped_column(db.String, unique=True, nullable=False)

with app.app_context():
    db.create_all()

    db.session.add(User(username="example"))
    db.session.commit()

    users = db.session.execute(db.select(User)).scalars()

Simple usage

A customization of SqlAlchemy, the ORM used in python, to make it convenient to use in flask.

It does the same things as SqlAlchemy, but lets you handle the DB more simply.

Creating a connection

With plain SqlAlchemy, you have to

  1. declare declarative_base to create a connection,
  2. create an engine and connect it to the dsn,
  3. write the model and schema to use,
  4. and then bind the created engine to the session

before the DB connection is complete and usable. With Flask-SqlSlchemy, however, you can use sqlachemy with far simpler steps, at least within flask.

Creating and binding the connection 

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask()
app.config["SQLALCHEMY_DATABASE_URI"] = 'postgresql://usename:password@localhost:5432/postgres'

db = SQLAlchemy()
db.init_app(app)

Creating the model

Declaring a model works the same as in plain SqlAlchemy; the difference is that it inherits from the Model of SQLAlchemy() instead of Base(declaratibe_base). 

class User(db.Model):
    # 테이블의 이름
    __tablename__ = "users"

    id = Column("id", Integer, primary_key=True)
    name = Column("name", String, nullable=False)
    email = Column("email", String, nullable=False, unique=True)
    password = Column("password", Unicode, nullable=False)

If you want to define a relationship between tables, write it in the model to be linked, as follows. 

user_id = db.Column(db.Integer, db.ForeignKey(User.id))
# backref는 반대 관계 모델에서 표시되길 원하는 이름
user = db.relationship('User', backref="connections")

Using it

Once connected, you can perform database operations without creating a session separately. Apart from not creating a session separately, use it the same way as plain SqlAlchemy.

If you used it like this in plain SqlAlchemy, 

session.add(User(id, name, email, password))
session.commit()

in Flask-SqlAlchemy the slight difference is that you call the session of the SQLAlchemy instance, as follows. 

db.session.add(User(id, name, email, password))
db.session.commit()

MariaDB configuration

SQLALCHEMY_TRACK_MODIFICATIONS is an option that automatically tracks changes and applies them to the database. Setting it to False has the drawback that you must call commit manually, but it can improve speed. 

class Config:
    # 코드 생략
    db_pt = os.environ["MARIADB_PORT"]
    db_id = os.environ["MARIADB_USER"]
    db_pw = os.environ["MARIADB_PASSWORD"]
    db_nm = os.environ["MARIADB_DATABASE"]
    SQLALCHEMY_DATABASE_URI = f"mysql+pymysql://{db_id}:{db_pw}@db:{db_pt}/{db_nm}"
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Create the database object, connect to the database, and create tables using the defined class objects. 

db = SQLAlchemy()
# ...
def create_app():
    app.config.from_object(obj=config["development"])

    with app.app_context():
        # ...
        db.init_app(app=app)
        db.create_all()

Creating models

Defined a user class that will be created as a database table.

__tablename__ defines the table name in the database, and each class variable is defined as a table column.

Table columns can be defined using Column.

A relationship is not defined as a table column, but acts like a JOIN to make it easy to reference data between closely related classes. 

class User(db.Model, UserMixin):
    __tablename__ = "users"

    id = Column(INTEGER(display_width=11, unsigned=True), primary_key=True, autoincrement="auto", comment="사용자 식별 값")
    username = Column(VARCHAR(length=50), unique=True, nullable=False, comment="사용자 아이디")
    password = Column(VARCHAR(length=255), nullable=False, comment="사용자 비밀번호")
    roles = relationship(argument="Role", secondary="rel_user_roles", backref=backref(name="users", lazy=True), uselist=True)
    comments = relationship(argument="Comment", secondary="rel_user_comments", backref=backref(name="users"), uselist=True)

    def __init__(self, username:str, password:str, roles:list):
        self.username = username
        self.password = generate_password_hash(password=password)
        self.roles = roles

    # ...

Queries

Create (INSERT)

No need to add autoincrement attributes. 

user = User(username=form.username.data, password=form.password.data, roles=[role])

db.session.add(instance=user)
try:
    db.session.commit()

Use commit at the point you want to apply changes to the database.

Read (SELECT)

Query the database using the object's query, and add conditions with filter_by.

Call first() to get the first result, or all() to get all results. 

user = User.query.filter_by(username=form.username.data).first()

You can also use filter instead of filter_by. Note that the usage differs from filter_by, as shown below. 

user = User.query.filter(User.username==form.username.data).first()

Update (UPDATE)

Stores the data passed from the form into the title and content fields of a model called post. 

post.title = form.title.data
post.content = form.content.data

## 이후, 데이터베이스에 적용하기 위해 commit을 사용합니다.
db.session.commit()

Delete (DELETE)

Pass the post model to the delete function and commit to apply it, and the data is deleted from the database. 

db.session.delete(instance=post)
db.session.commit()

Troubelshooting

NOT NULL constraint failed: {TABLE}.id

When adding a column with a sqlite3 DB connection, the following error occurs. 

...
  File "/home/your/.pyenv/versions/opy-your-3.11.9-default/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    dialect.do_execute(
  File "/home/your/.pyenv/versions/opy-your-3.11.9-default/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: history.id
[SQL: INSERT INTO history (data, status, created_at) VALUES (?, ?, ?) RETURNING id]
[parameters: ('According.', 'out', '2024-06-26 05:21:18.801133')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

If you get this error... 

class BaseModel(db.Model):
    id = db.Column(db.BigInteger, primary_key=True)

Check whether the primary_key column is BigInteger. It needs to be changed to Integer. 

class BaseModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)

See also

Favorite site

author Zer0 sitemap