wikinotes

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
{{tag>dev python web flask sqlalchemy flas-sqlachemy}}


====== Flask-SQLAlchemy : Forcer la vérification des contraintes sur une base SQLite3 ======


Notes concernant l'activation des contraintes sur clé étrangère (FK) sur les bases de type SQLite3 utilisées en backend via Flask-SQLAlchemy.



===== Problématique =====

Une contrainte de type **FK**((**F**oreign **K**ey)) est définie dans notre modèle mais ne semble pas s'appliquer. Ci-dessous la définition ayant présenté la problématique :

<code python [enable_line_numbers="true", highlight_lines_extra="16"] >
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship
from sqlalchemy import Integer, String, Text, ForeignKey

class Base(DeclarativeBase):
  pass

db = SQLAlchemy(model_class=Base)

class Category(db.Model):
    __tablename__ = "categories"

    id = mapped_column(Integer, primary_key=True,)
    label = mapped_column(String(50), nullable=False)
    description = mapped_column(Text, nullable=True)
    parent_id = mapped_column(Integer, ForeignKey("categories.id"), nullable=True,)

    children = relationship("Category", back_populates="parent",
                            cascade="all, delete-orphan", uselist=True)
    parent = relationship("Category", back_populates="children", remote_side='Category.id')

    def __init__(self, label):
        """Constructeur """
        self.label = label
</code>

La classe ''Category'' utilise un modèle(pattern) bien connu dit en "liste adjacente" ([[https://docs.sqlalchemy.org/en/20/orm/self_referential.html|adjacency list pattern]]) qui se présente comme une table se référençant elle même (ici via la création d'une Foreign Key sur le champ ''parent_id'' ligne 16).

Ce modèle permet de représenter des structures hiérarchiques dans des tables et dans ce cas de créer des sous catégories dans des catégories. Pour une sous catégorie, le champ ''parent_id'' contient l'identifiant de la catégorie parente et une catégorie racine aura une valeur nulle dans son champ ''parent_id''.


Lorsqu'on teste cet objet via la commande **flask shell** on constate que la contrainte définie dans la classe ne s'applique pas car il est possible de créer un sous-catégorie avec un ''parent_id'' aléatoire ne correspondant à aucun identifiant existant dans la table(relation) **categories**.

<code python>
>>> # Depuis flask shell
>>> db.create_all()

>>> # Ce comportement est correct
>>> rc = Category("root")
>>> rc.children.append( Category( "r1-A") )
>>> rc.children.append( Category( "r1-B") )
>>> 
>>> db.session.add(rc)
>>> db.session.commit()
>>>
>>> del rc
>>>
>>> # On récupère la catégorie racine depuis la base de données
>>> root_cat = Category.query.filter(Category.label == 'root').first()
>>> # root_cat est bien un objet de type Category
>>> type(root_cat)
<class 'app.Category'>
>>>
>>> # la catégorie racine contient bien les deux sous-catégories
>>> for c in root_cat.children :
...   print(c.label)
... 
r1-A
r1-B
>>> # Les sous-catégories ont pour parent_id l'identifiant de la catégorie root
>>> >>> root_cat.id
1
>>> root_cat.children[0].parent_id
1
>>> root_cat.children[1].parent_id
1
>>>
>>> # Le comportement suivant est anormal : 
>>> # On crée une catégorie avec un parent_id non existant
>>> cat_test = Category("une catégorie invalide")
>>> cat_test.parent_id = 666
>>> db.session.add(cat_test)
>>> db.session.commit()
>>> # Ici le commit a fonctionné sans lever d'exception
>>> quit()
</code>

via le client **sqlite3** en ligne de commande, on peut afficher le contenu de notre fichier base de données. On constate ainsi que la table existe bien avec la contrainte et que les enregistrements ont été créés :

<code bash>
sqlite3 instance/project.db
</code>

<code>
sqlite> -- Affiche la structure de la table
sqlite> .schema categories
CREATE TABLE categories (
        id INTEGER NOT NULL, 
        label VARCHAR(50) NOT NULL, 
        description TEXT, 
        parent_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(parent_id) REFERENCES categories (id)
);
sqlite>
sqlite> -- affiche les valeurs nulles et les entêtes
sqlite> .nullvalue NULL
sqlite> .headers on
sqlite>
sqlite> -- Affiche le contenu de la table categories
sqlite> SELECT * FROM categories;
id|label|description|parent_id
1|root|NULL|NULL
2|r1-A|NULL|1
3|r1-B|NULL|1
4|une catégorie invalide|NULL|666
</code>

Ici on voit bien que la catégorie d'identifiant 4 a pour ''parent_id'' une valeur 666 alors que la contrainte est bien présente dans la définition de la table.

La requête d'insertion suivante est également accepté :
<code sql>
-- la requête suivante passée depuis sqlite3 ne retourne pas d'erreur
INSERT INTO categories (label,parent_id) VALUES ("un autre test", 777 );
</code>

On affiche la table categories, on peut voir que le nouvel enregistrement a été créée :
<code>
sqlite> SELECT * FROM categories;
id|label|description|parent_id
1|root|NULL|NULL
2|r1-A|NULL|1
3|r1-B|NULL|1
4|une catégorie invalide|NULL|666
5|un autre test|NULL|777
</code>

En affichant la configuration du fichier de base de données on peut voir une option **enable_fkey** positionnée à ''off'' par défaut :

<code txt [highlight_lines_extra="5"]>
sqlite> .dbconfig
          defensive on
            dqs_ddl on
            dqs_dml on
        enable_fkey off
        enable_qpsg off
     enable_trigger on
        enable_view on
     fts3_tokenizer on
 legacy_alter_table off
 legacy_file_format off
     load_extension on
   no_ckpt_on_close off
     reset_database off
  reverse_scanorder off
    stmt_scanstatus off
        trigger_eqp off
     trusted_schema off
    writable_schema off
</code>

<note>
Si on passe cette variable de configuration a ''on'' et qu'on exécute une nouvelle requête, on obtient le comportement attendu !
</note>

<file txt [highlight_lines_extra="4"]>
sqlite> .dbconfig enable_fkey on
        enable_fkey on
sqlite> INSERT INTO categories (label,parent_id) VALUES ("encore un test", 888 );
Runtime error: FOREIGN KEY constraint failed (19)
</file>

C'est bien une variable de configuration pour notre base de données SQLite3 qui permet d'activer la prise en compte des contraintes FK existantes.

Le code ci-dessous proposé sur la [[https://gist.github.com/asyd/a7aadcf07a66035ac15d284aef10d458|plateforme github]] permet d'obtenir le comportement attendu sous Flask-SQLAlchemy en activant de la même manière la prise en compte des **contraintes de type FK** lors de l'ouverture de la base de données.


<code python [enable_line_numbers="true", highlight_lines_extra="15,16,17,18,19,20,21"]>
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

def create_app(config: str=None):
    app = Flask(__name__, instance_relative_config=True)
    if config is None:
        app.config.from_pyfile('dev.py')
    else:
        logger.debug('Using %s as configuration', config)
        app.config.from_pyfile(config)

    db.init_app(app)

    # Ensure FOREIGN KEY for sqlite3
    if 'sqlite' in app.config['SQLALCHEMY_DATABASE_URI']:
        def _fk_pragma_on_connect(dbapi_con, con_record):  # noqa
            dbapi_con.execute('pragma foreign_keys=ON')

        with app.app_context():
            from sqlalchemy import event
            event.listen(db.engine, 'connect', _fk_pragma_on_connect)
</code>


Après modification du code d'initialisation de l'application Flask, on obtient bien le résultat attendu dans le shell flask :

<code python [highlight_lines_extra="10"]>
>>> cat_must_fail = Category("categorie non valide")
>>> cat_must_fail.parent_id = 1789
>>> db.session.add(cat_must_fail)
>>> db.session.commit()
Traceback (most recent call last):
  File "/home/yoann/dev/poc/categories/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/yoann/dev/poc/categories/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 951, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed
</code>

===== Références =====

  * [[https://gist.github.com/asyd/a7aadcf07a66035ac15d284aef10d458|Imposer les contraintes FK pour les bases SQLite avec Flask-SQLAlchemy]]
  * [[https://stackoverflow.com/questions/2638217/sqlalchemy-mapping-self-referential-relationship-as-one-to-many-declarative-f|Usage des contraintes avec auto-référence dans SQLAlchemy (stackoverflow.com)]]
wikinotes

Outils pour utilisateurs

Outils du site

Outils de la page
