[FlaskFM] No such table

Hello,

I’m doing the FlaskFM project in the Build Python Web Apps with Flask.

I run into the following issue on step 14:

$ python3 add_data.py
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1228, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 587, in do_executemany
    cursor.executemany(statement, parameters)
sqlite3.OperationalError: no such table: playlist

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "add_data.py", line 32, in <module>
    db.session.commit()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 1036, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 503, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 482, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2496, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2637, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
    exc_value, with_traceback=exc_tb,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2597, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/persistence.py", line 1083, in _emit_insert_statements
    c = cached_connections[connection].execute(statement, multiparams)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 984, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1103, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1288, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1482, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1228, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 587, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: playlist
[SQL: INSERT INTO playlist (id) VALUES (?)]
[parameters: ((3456,), (2342,), (4576,), (8743,))]
(Background on this error at: http://sqlalche.me/e/e3q8)
$ python3 add_data.py
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1228, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 587, in do_executemany
    cursor.executemany(statement, parameters)
sqlite3.OperationalError: no such table: playlist

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "add_data.py", line 32, in <module>
    db.session.commit()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 1036, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 503, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 482, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2496, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2637, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
    exc_value, with_traceback=exc_tb,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2597, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/persistence.py", line 1083, in _emit_insert_statements
    c = cached_connections[connection].execute(statement, multiparams)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 984, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1103, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1288, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1482, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1228, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 587, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: playlist
[SQL: INSERT INTO playlist (id) VALUES (?)]
[parameters: ((3456,), (2342,), (4576,), (8743,))]
(Background on this error at: http://sqlalche.me/e/e3q8)

models.py

from app import db #the User model: each user has a username, and a playlist_id foreign key referring #to the user's Playlist class User(db.Model): id = db.Column(db.Integer, primary_key = True) username = db.Column(db.String(50), index = True, unique = True) playlist_id = db.Column(db.Integer, db.ForeignKey('playlist.id')) #representation method def __repr__(self): return "{}".format(self.username) #create the Song model here + add a nice representation method class Song(db.Model): id = db.Column(db.Integer, primary_key = True) artist = db.Column(db.String(50), index = True, unique = False) title = db.Column(db.String(75), index = True, unique = False) n = db.Column(db.Integer, index = False, unique = False) def __repr__(self): return "{} by {}".format(self.title, self.artist) #create the Item model here + add a nice representation method class Item(db.Model): id = db.Column(db.Integer, primary_key = True) song_id = db.Column(db.Integer, db.ForeignKey('song.id')) playlist_id = db.Column(db.Integer, db.ForeignKey('playlist.id')) def __repr__(self): return "{}".format(self.id) #create the Playlist model here + add a nice representation method class Playlist(db.Model): id = db.Column(db.Integer, primary_key = True) items = db.relationship('Item', backref = 'playlist', lazy = 'dynamic') def __repr__(self): return "{}".format(self.id)

The output from step 13, it appeared to run successfully:

$ python3
Python 3.6.9 (default, Mar 15 2022, 13:55:28) 
[GCC 8.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from app import db
>>> db.create_all()
>>> exit()

Why would step 13 not have created these tables?
I have deleted the .db file and recreated it many times and still run into the error above

Did you do db.session.commit()? Sqlite uses transactions like most relational databases, so transactions have to be completed for them to take effect. A good sanity check is if to see if it actually creates a db file in the directory (if not, it’s not being created). Another sanity check is that it’s checking in the correct directory.

db.session.commit() exists in add_data.py, it appears to error before getting to that step. Is the provided material incorrect and there needs to be many db.session.commit() commands in this module instead of just the one?

from app import db from models import Song, Playlist, Item, User p1 = Playlist(id = 3456) p2 = Playlist(id = 2342) p3 = Playlist(id = 4576) p4 = Playlist(id = 8743) u1 = User(id = 1, username = "mlky_way", playlist_id = p1.id) u2 = User(id = 2, username = "martian2", playlist_id = p2.id) u3 = User(id = 3, username = "andromeda_3", playlist_id = p3.id) u4 = User(id = 4, username = "calypso123", playlist_id = p4.id) s1 = Song(id = 1, artist = "Franks Sinatra", title = "Fly me to the Moon", n = 0) s2 = Song(id = 2, artist = "David Bowie", title = "Space Oddity", n = 0) s3 = Song(id = 3, artist = "Sting", title = "Walking on the Moon", n = 0) s4 = Song(id = 4, artist = "Nick Cave & The Bad Seeds", title = "Rings of Saturn", n = 0) s5 = Song(id = 5, artist = "Babylon Zoo", title = "Spaceman", n = 0) db.session.add(p1) db.session.add(p2) db.session.add(p3) db.session.add(p4) db.session.add(u1) db.session.add(u2) db.session.add(u3) db.session.add(u4) db.session.add(s1) db.session.add(s2) db.session.add(s3) db.session.add(s4) db.session.add(s5) db.session.commit()

One thing is adding data and the other thing is creating the tables. The tables have to be created before data is inserted. This is regardless of the transaction (commit) concept. Commits are a way to mark a transaction as done. Types of transactions can include: create table, alter table, update table, insert data, update data, delete table, delete row, etc. The idea is to try avoid race conditions from multiple writers altering the db data an having it be out of sync
see

I don’t know the material (don’t work for ca). But you always need to have a db table before being able to write to it. If you don’t have a table, there’s nothing to write to.

You can use a database GUI or CLI editor and add the table manually if need be. Or programmatically via SQLAlchemy.

Okay so when running db.create_all() it does not create tables without an additional step of db.session.commit()? I was under the impression that SQLAlchemy did a lot of that magic of creation when running create_all()

Hm it depends. How exactly did you create your table? I think if you used the MetaData create_all pattern it may auto-commit for you. If you use a session, you have to commit the session (even with SQLAlchemy).

The instructions said to create models and then run python interactively import the db and run db.create_all(). Exit interactive mode and run python3 add_data.py. That’s what I have been trying to do so it seemed like to me that db.create_all() was supposed to do that creation piece and then there would be tables to add to. It’s not doing that but I am not understanding if it is a I don’t have it coded correctly for creation to happen or if I am hoping for it to do something it is not programmed to do issue

If you did it interactively it’s hard to say when you created_all, what all were created at the time, even if you ran the command. A command running without error doesn’t imply it did the thing you wanted. (happens to me all the time)

The most important thing is the database itself. Try querying it (use SQL) and see what’s there. Or use a GUI https://sqlitebrowser.org/. That will tell you in the end whether your commands were successful when you try again. It will give you a better picture of what went wrong.

If you set your SQLAlchemy app to log errors, it will try to be more verbose when things go wrong. app.config['SQLALCHEMY_ECHO'] = True