[Python] flask and sqlalchemy db upgrade creates mysql database tables on local host but...

I am at my wits end and realise the problem I have is not very defined. I am happy to provide anything else necessary for trouble shooting.

I am trying to deploy a web app that I have build. It is running fine on my local computer running a python 3.10 venv on pycharm and a local mysql database. I have cloned the git repository on the Linux VPS running ubuntu 22.04. I have installed all requirements and a mysql server with a user 'gnome' and a database 'gnome_hat'.

on sudo mysql -u root I can use gnome_hat, but SHOW TABLES; indicates it is empty (this is expected since I want them to be created when I first initialise my db.models through flask

my .env is set to DATABASE_URL=mysql+pymysql://gnomelocalhost:3306/gnome_hat I know this is working because I used mysql root on the local machine and that (obviously) didn't work and I was told I couldnt use that user, changing the .env worked.

However when I use flask db upgrade in the venv I am given a number of problems in the code described as sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'gnome_hat.stakeholder' doesn't exist") and (Background on this error at: https://sqlalche.me/e/20/f405). However, I expect the database to be empty and the table stakeholders to not exist yet, since this initial flask db upgrade is supposed to create the tables.

(venv) root@ubuntu:~/gnome_hat# flask db upgrade
Traceback (most recent call last):
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 558, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 822, in _read_query_result
result.read()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 1200, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 772, in _read_packet
packet.raise_for_error()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, "Table 'gnome_hat.stakeholder' doesn't exist")

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

Traceback (most recent call last):
File "/root/gnome_hat/venv/bin/flask", line 8, in <module>
sys.exit(main())
^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 1063, in main
cli.main()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/click/core.py", line 1682, in invoke
cmd_name, cmd, args = self.resolve_command(ctx, args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/click/core.py", line 1729, in resolve_command
cmd = self.get_command(ctx, cmd_name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 578, in get_command
app = info.load_app()
^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 312, in load_app
app = locate_app(import_name, None, raise_if_not_found=False)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 218, in locate_app
__import__(module_name)
File "/root/gnome_hat/app/__init__.py", line 23, in <module>
from app import routes, models, errors
File "/root/gnome_hat/app/routes.py", line 4, in <module>
import main
File "/root/gnome_hat/main.py", line 11, in <module>
for x in names:
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2832, in __iter__
result = self._iter()
^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2246, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2141, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
result = conn.execute(
^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
return meth(
^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
self._handle_dbapi_exception(
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 558, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 822, in _read_query_result
result.read()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 1200, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 772, in _read_packet
packet.raise_for_error()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'gnome_hat.stakeholder' doesn't exist")
[SQL: SELECT stakeholder.id AS stakeholder_id, stakeholder.first_name AS stakeholder_first_name, stakeholder.last_name AS stakeholder_last_name, stakeholder.`function` AS stakeholder_function, stakeholder.organisation_id AS stakeholder_organisation_id, stakeholder.national_party_id AS stakeholder_national_party_id, stakeholder.eu_party_id AS stakeholder_eu_party_id, stakeholder.email AS stakeholder_email, stakeholder.website AS stakeholder_website, stakeholder.facebook AS stakeholder_facebook, stakeholder.twitter AS stakeholder_twitter
FROM stakeholder]
(Background on this error at: https://sqlalche.me/e/20/f405)


I have made sure the MySQL server has the relevant database and the user+password are correct

Continue reading...