Foreign key not set, fails for NOT NULL

[Steven Riggs]

Hello,

I have been all over the web, Stack Overflow and Youtube, and cannot find any answers.  I have a self-referential many-to-many class Color, which should contain an attribute recipe that gives a list of other colors used to make it, along with the quantities of eacj color used.  The simplified version of my code is:

from flask import Flask

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config.from_mapping({

    'SQLALCHEMY_DATABASE_URI': 'sqlite:///colors.sqlite',

    'SQLALCHEMY_ECHO': True,

    'SQLALCHEMY_TRACK_MODIFICATIONS': False,

    })

db = SQLAlchemy(app)

class Color(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    medium = db.Column(db.String(2), nullable=False)

    name = db.Column(db.String, nullable=False, unique=True)

    pure = db.Column(db.Boolean, nullable=False, default=True)

    recipe = db.relationship('Recipe',

            primaryjoin='Color.id==Recipe.base_id',

            uselist=True,

            join_depth=1,

            lazy='joined'

            )

    def __init__(self, medium, name, *, pure=True, recipe=[]):

        self.medium = medium.upper()

        self.name = name

        self.pure = False if len(recipe) > 1 else True

        if self.pure:

            recipe = [(self, self, 1)]

        for entry in recipe:

            self.recipe.append(Recipe(entry))

    def __repr__(self):

        return f'{self.name}'

class Recipe(db.Model):

    base_id = db.Column(db.Integer, primary_key=True, autoincrement=False)

    ingredient_id = db.Column(db.Integer, primary_key=True, autoincrement=False)

    ingredient_name = db.Column(db.String, db.ForeignKey('color.name'))

    quantity = db.Column(db.Integer, nullable=False, default=1)

    __table_args__ = (

            db.ForeignKeyConstraint(

                ['base_id', 'ingredient_id'],

                ['color.id', 'color.id'],

                onupdate = 'CASCADE',

                ondelete = 'CASCADE'

                ),

            )

    def __init__(self, ingredient_tuple):

        super().__init__()

        self.base_id = ingredient_tuple[0].id

        self.ingredient_id = ingredient_tuple[1].id

        self.ingredient_name = ingredient_tuple[1].name

        self.quantity = ingredient_tuple[2]

    def __repr__(self):

        return f'{self.ingredient_name}(x{self.quantity})'

if __name__ == '__main__':

    db.create_all()

No matter how many times I have tweaked it with various settings, I always get the following error when I try to commit:

>>> from colors import Color, db, Recipe

>>> blurg = Color('oa', 'blurg')

ingredient_tuple=(blurg, blurg, 1)

self.base_id=None

self.ingredient_id=None

>>> db.session.add(blurg)

>>> db.session.commit()

2019-11-18 05:02:05,053 INFO sqlalchemy.engine.base.Engine SELECT CAST('test pla

in returns' AS VARCHAR(60)) AS anon_1

2019-11-18 05:02:05,054 INFO sqlalchemy.engine.base.Engine ()

2019-11-18 05:02:05,054 INFO sqlalchemy.engine.base.Engine SELECT CAST('test uni

code returns' AS VARCHAR(60)) AS anon_1

2019-11-18 05:02:05,054 INFO sqlalchemy.engine.base.Engine ()

2019-11-18 05:02:05,055 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2019-11-18 05:02:05,056 INFO sqlalchemy.engine.base.Engine INSERT INTO color (me

dium, name, pure) VALUES (?, ?, ?)

2019-11-18 05:02:05,056 INFO sqlalchemy.engine.base.Engine ('OA', 'blurg', 1)

C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-packages\s

qlalchemy\sql\crud.py:799: SAWarning: Column 'recipe.ingredient_id' is marked as

 a member of the primary key for table 'recipe', but has no Python-side or serve

r-side default generator indicated, nor does it indicate 'autoincrement=True' or

 'nullable=True', and no explicit value is passed.  Primary key columns typicall

y may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must

be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_IN

CREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the prima

ry key. CREATE TABLE statements are impacted by this change as well on most back

ends.

  util.warn(msg)

2019-11-18 05:02:05,059 INFO sqlalchemy.engine.base.Engine INSERT INTO recipe (b

ase_id, ingredient_name, quantity) VALUES (?, ?, ?)

2019-11-18 05:02:05,059 INFO sqlalchemy.engine.base.Engine (1, 'blurg', 1)

2019-11-18 05:02:05,060 INFO sqlalchemy.engine.base.Engine ROLLBACK

Traceback (most recent call last):

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\base.py", line 1245, in _execute_context

    self.dialect.do_execute(

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\default.py", line 581, in do_execute

    cursor.execute(statement, parameters)

sqlite3.IntegrityError: NOT NULL constraint failed: recipe.ingredient_id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\scoping.py", line 162, in do

    return getattr(self.registry(), name)(*args, **kwargs)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\session.py", line 1027, in commit

    self.transaction.commit()

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\session.py", line 494, in commit

    self._prepare_impl()

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\session.py", line 473, in _prepare_impl

    self.session.flush()

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\session.py", line 2470, in flush

    self._flush(objects)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\session.py", line 2608, in _flush

    transaction.rollback(_capture_exception=True)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\util\langhelpers.py", line 68, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\util\compat.py", line 153, in reraise

    raise value

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\session.py", line 2568, in _flush

    flush_context.execute()

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\unitofwork.py", line 422, in execute

    rec.execute(self)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\unitofwork.py", line 586, in execute

    persistence.save_obj(

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\persistence.py", line 239, in save_obj

    _emit_insert_statements(

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\orm\persistence.py", line 1136, in _emit_insert_statements

    result = cached_connections[connection].execute(

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\base.py", line 982, in execute

    return meth(self, multiparams, params)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\base.py", line 1095, in _execute_clauseelement

    ret = self._execute_context(

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\base.py", line 1249, in _execute_context

    self._handle_dbapi_exception(

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception

    util.raise_from_cause(sqlalchemy_exception, exc_info)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\util\compat.py", line 398, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\util\compat.py", line 152, in reraise

    raise value.with_traceback(tb)

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\base.py", line 1245, in _execute_context

    self.dialect.do_execute(

  File "C:\Users\riggss2\Dropbox\programming\Playground\colors\wkenv\lib\site-pa

ckages\sqlalchemy\engine\default.py", line 581, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint fail

ed: recipe.ingredient_id

[SQL: INSERT INTO recipe (base_id, ingredient_name, quantity) VALUES (?, ?, ?)]

[parameters: (1, 'blurg', 1)]

(Background on this error at: http://sqlalche.me/e/gkpj)

I do not want the Recipe.ingredient_id to autoincrement, and even if I set it explicitly, it is always input as NULL.  I am sure it's a simple setup error, but I am lost as to where.  Any feedback is greatly appreciated.  Thank you.

[Simon King]

Out of interest, does it make any difference if you flush the Color
instance before creating the Recipe instances? The potential problem I
see is that, until the Color is flushed, its id will be None, and in
the Recipe constructor you set "self.ingredient_id =
ingredient_tuple[1].id", so that will therefore be set to None.

If you are using the "relationship" construct to link objects, you
should probably avoid assigning directly to foreign key columns, and
instead let SQLAlchemy handle those for you.

Conceptually, Recipe is related to 2 colors, the "base" and the
"ingredient". You've got a relationship (Color.recipe) which handles
the Recipe.base_id foreign key, but you haven't got one for
Recipe.ingredient_id. Personally, I would add 2 relationships to the
Recipe class, "base" (which is the other end of Color.recipe, and
probably ought to use "back_populates"), and "ingredient". Then rather
than assigning to the foreign key columns directly, you would assign
to the relationship properties.

Hope that helps,

Simon

[Steven Riggs]

Thank you very much Simon. I will try this and let you know.

[Steven Riggs]

Simon, you are a GENIUS!

Thank you SO much! Flushing was the answer. I simply added this to my original:

class Color(db.Model):

    ...

        def __init__(self, medium, name, *, pure=True, recipe=[]):

        ...

        self.pure = False if len(recipe) > 1 else True

        db.session.add(self)

        db.session.flush([self])

        if self.pure:

        ...

and it now works beautifully, as it is intended to. Thank you again.

On Monday, November 18, 2019 at 3:02:23 AM UTC-7, Simon King wrote: