Thank you very much Simon. I will try this and let you know. On Monday, November 18, 2019 at 3:02:23 AM UTC-7, Simon King wrote: > > On Mon, Nov 18, 2019 at 5:32 AM Steven Riggs <[email protected] > <javascript:>> wrote: > > > > 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. > > 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 >
-- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a92199ad-fedf-40b4-9e2e-a63af5600c79%40googlegroups.com.
