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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/9e043b09-1dfa-4f47-9de2-14086a80a074%40googlegroups.com.
