OperationalError: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: note

[DJRyLau]

This issue is on the current main branch.

Unsure if this is something on my end, or is a problem that arised from the recent file reorganisation. I have reinstalled requirements.txt but that still does not solve the issue.

The full error is as shown below:

Traceback (most recent call last):
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: note

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

Traceback (most recent call last):
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: note

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

Traceback (most recent call last):
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1498, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1476, in wsgi_app
    response = self.handle_exception(e)
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1473, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 882, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 880, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\flask_login\utils.py", line 290, in decorated_view
    return current_app.ensure_sync(func)(*args, **kwargs)
  File "c:\Users\RyLau\Documents\GitHub\CITS3403-Project\app\routes.py", line 62, in notes
    notes = Note.query.filter_by(user_id=current_user.id).all()
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\query.py", line 2673, in all
    return self._iter().all()  # type: ignore
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\query.py", line 2827, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 2306, in execute
    return self._execute_internal(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 2191, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\context.py", line 293, in orm_execute_statement
    result = conn.execute(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1422, in execute
    return meth(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\sql\elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1850, in _execute_context
    return self._exec_single_context(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 2357, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\RyLau\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: note
[SQL: SELECT note.id AS note_id, note.content AS note_content, note.created_at AS note_created_at, note.user_id AS note_user_id, note.color AS note_color, note.position_x AS note_position_x, note.position_y AS note_position_y, note.width AS note_width, note.height AS note_height 
FROM note 
WHERE note.user_id = ?]
[parameters: (1,)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[DJRyLau]

Could it be that I do not have an exisitng notes table? Surely if it is absent, it would automatically generate any empty one.

[HenryHewgill]

Yep I'm getting a similar issue, though not for the absence of the 'note' table, but for the column 'note.position_x'. I haven't done any database migrations and @Ciaranengelbrecht did mention that potentially being necessary so will investigate.

Here's the log: Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context self.dialect.do_execute( File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute cursor.execute(statement, parameters) sqlite3.OperationalError: no such column: note.position_x

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

Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/flask/app.py", line 1473, in wsgi_app response = self.full_dispatch_request() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/flask/app.py", line 882, in full_dispatch_request rv = self.handle_user_exception(e) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/flask/app.py", line 880, in full_dispatch_request rv = self.dispatch_request() ^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/flask/app.py", line 865, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(*view_args) # type: ignore[no-any-return] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/flask_login/utils.py", line 290, in decorated_view return current_app.ensure_sync(func)(args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Users/hhewgill/WEB/cits3403/CITS3403-Project/app/routes.py", line 62, in notes notes = Note.query.filter_by(user_id=current_user.id).all() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2673, in all return self._iter().all() # type: ignore ^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter result: Union[ScalarResult[_T], Result[_T]] = self.session.execute( ^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2306, in execute return self._execute_internal( ^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal result: Result[Any] = compile_state_cls.orm_execute_statement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement result = conn.execute( ^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1422, in execute return meth( ^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection return connection._execute_clauseelement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context self._handle_dbapi_exception( File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception raise sqlalchemy_exception.with_traceback(exc_info[2]) from e File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context self.dialect.do_execute( File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: note.position_x [SQL: SELECT note.id AS note_id, note.content AS note_content, note.created_at AS note_created_at, note.user_id AS note_user_id, note.color AS note_color, note.position_x AS note_position_x, note.position_y AS note_position_y, note.width AS note_width, note.height AS note_height FROM note WHERE note.user_id = ?] [parameters: (1,)] (Background on this error at: https://sqlalche.me/e/20/e3q8)

[HenryHewgill]

Ok I found the issue, in 'init.py' we needed to be importing 'Note' as well as 'User' from our models. I made a tweak to the code that will recreate 'database.db' if you delete it when you use 'flask run', so if you don't care about losing dev data, you can bypass migrating by just deleting & letting flask recreate it. If it already exists it will be left as is, it only creates new tables or the file if either are missing. You will need to migrate to add new columns though.

[DJRyLau]

Resolved thanks to Henry's comment.