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:
>
> 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/09ea0cf1-a294-4889-a9ed-2f1cc989d0a7%40googlegroups.com.
