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. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9e043b09-1dfa-4f47-9de2-14086a80a074%40googlegroups.com.