On Mon, Nov 18, 2019 at 5:32 AM Steven Riggs <sfrigg...@gmail.com> 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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexesS16Z5%3DW1QV-UvOrZua0-gfs%2BwAud_WokkcsezXWSWw%40mail.gmail.com.
