'sqlalchemy - Failed to open the referenced table
I am a newbie to sql and sqlalchemy, what am I doing wrong here? The error occurs at the time of upgrade.
ERROR:
Traceback (most recent call last):
File "/workspace/Star-Wars-Blog-API/.venv/bin/flask", line 8, in <module>
sys.exit(main())
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/flask/cli.py", line 967, in main
cli.main(args=sys.argv[1:], prog_name="python -m flask" if as_module else None)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/flask/cli.py", line 586, in main
return super(FlaskGroup, self).main(*args, **kwargs)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/click/decorators.py", line 21, in new_func
return f(get_current_context(), *args, **kwargs)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/flask/cli.py", line 426, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/flask_migrate/cli.py", line 134, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/flask_migrate/__init__.py", line 96, in wrapped
f(*args, **kwargs)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/flask_migrate/__init__.py", line 271, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/command.py", line 294, in upgrade
script.run_env()
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/script/base.py", line 490, in run_env
util.load_python_file(self.dir, "env.py")
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
module = load_module_py(module_id, path)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/util/compat.py", line 182, in load_module_py
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 843, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "migrations/env.py", line 96, in <module>
run_migrations_online()
File "migrations/env.py", line 90, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/runtime/environment.py", line 813, in run_migrations
self.get_context().run_migrations(**kw)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/runtime/migration.py", line 561, in run_migrations
step.migration_fn(**kw)
File "/workspace/Star-Wars-Blog-API/migrations/versions/4985d3e36676_.py", line 21, in upgrade
op.create_table('Favorites_List',
File "<string>", line 8, in create_table
File "<string>", line 3, in create_table
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/operations/ops.py", line 1072, in create_table
return operations.invoke(op)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/operations/base.py", line 354, in invoke
return fn(self, operation)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/operations/toimpl.py", line 101, in create_table
operations.impl.create_table(table)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/ddl/impl.py", line 277, in create_table
self._exec(schema.CreateTable(table))
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/alembic/ddl/impl.py", line 146, in _exec
return conn.execute(construct, multiparams)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1014, in execute
return meth(self, multiparams, params)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1071, in _execute_ddl
ret = self._execute_context(
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
self._handle_dbapi_exception(
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
util.raise_(
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
self.dialect.do_execute(
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 264, in execute
result = self._cnx.cmd_query(stmt, raw=self._raw,
File "/workspace/Star-Wars-Blog-API/.venv/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 491, in cmd_query
raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 1824 (HY000): Failed to open the referenced table 'Planet'
[SQL:
CREATE TABLE `Favorites_List` (
id INTEGER NOT NULL AUTO_INCREMENT,
`USER_ID` INTEGER,
`FAV_CHARACTER` INTEGER,
`FAV_PLANET` INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(`FAV_CHARACTER`) REFERENCES `Character` (id),
FOREIGN KEY(`FAV_PLANET`) REFERENCES `Planet` (id),
FOREIGN KEY(`USER_ID`) REFERENCES `Userr` (id)
)
]
Quick summary of the design; When user creates account, user info is post in the userr table. Character and Planet tables are going to have the necessary data from the very beginning, it's not something meant to be modified at any point, at least for this project.
Favorites_List table will hold the user id, a fav_char, and a fav_planet.
When a specific existing user, adds a character or planet to favorites a new entry in the Favorites_List table will be created, with the user_id, and the id of either the fav character or fav planet. Aka, one of the columns is meant to be empty for every entry. If I add a fav char, the fav planet field will be empty, and vice versa.
class Userr(db.Model):
__tablename__ = 'Userr'
id = db.Column(db.Integer, primary_key=True)
USER_FNAME = db.Column(db.String(250), nullable=False)
USER_LNAME = db.Column(db.String(250), nullable=False)
USER_EMAIL = db.Column(db.String(250), nullable=False)
USER_PASSWORD = db.Column(db.String(250), nullable=False)
USER_FAVORITE_LIST = db.Column(db.Integer, db.ForeignKey("Favorites_List.id"))
def __repr__(self):
return '<Userr %r>' % self.userrname
class Favorites_List(db.Model):
__tablename__ = 'Favorites_List'
id = db.Column(db.Integer, primary_key=True)
USER_ID = db.Column(db.Integer, db.ForeignKey('Userr.id'))
FAV_CHARACTER = db.Column(db.Integer, db.ForeignKey('Character.id'))
FAV_PLANET = db.Column(db.Integer, db.ForeignKey('Planet.id'))
user = db.relationship("Userr", foreign_keys=[USER_ID])
favoriteChar = db.relationship("Character")
favoritePlanet = db.relationship("Planet")
def __repr__(self):
return '<Favorites_List %r>' % self.id
def serialize(self):
return {
"id": self.id,
"favorite_character": self.FAV_CHARACTER,
"favorite_planet": self.FAV_PLANET,
# do not serialize the password, its a security breach
}
class Planet(db.Model):
__tablename__ = 'Planet'
id = db.Column(db.Integer, primary_key=True)
PLANET_NAME = db.Column(db.String(250))
PLANET_ROT_PERIOD = db.Column(db.Integer)
PLANET_TRANS_PERIOD = db.Column(db.Integer)
PLANET_DIAMETER = db.Column(db.Integer)
PLANET_CLIMATE = db.Column(db.String(250))
PLANET_GRAVITY = db.Column(db.String(250))
PLANET_TERRAIN = db.Column(db.String(250))
PLANET_SURFACE_WATER = db.Column(db.Integer)
PLANET_POP = db.Column(db.Integer)
def __repr__(self):
return '<Planet %r>' % self.PLANET_NAME
def serialize(self):
return {
"id": self.id,
"planet_name": self.PLANET_NAME,
"planet_pop": self.PLANET_POP,
"planet_terrain": self.PLANET_TERRAIN,
# do not serialize the password, its a security breach
}
class Character(db.Model):
__tablename__ = 'Character'
id = db.Column(db.Integer, primary_key=True)
CHAR_NAME = db.Column(db.String(250))
CHAR_SPECIES = db.Column(db.String(250))
CHAR_GENDER = db.Column(db.String(250))
CHAR_YOB = db.Column(db.String(250))
CHAR_HEIGHT = db.Column(db.Integer)
CHAR_WEIGHT = db.Column(db.Integer)
CHAR_EYE_COLOR = db.Column(db.String(250))
CHAR_HAIR_COLOR = db.Column(db.String(250))
CHAR_SKIN_COLOR = db.Column(db.String(250))
def __repr__(self):
return '<Character %r>' % self.CHAR_NAME
def serialize(self):
return {
"id": self.id,
"species": self.CHAR_SPECIES,
"name": self.CHAR_NAME,
"gender": self.CHAR_GENDER
# do not serialize the password, its a security breach
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|