SQLAlchemy and SQLite errors appearing on PythonAnywhere but it was fine on localhost. So I just uploaded my Flask site to pythonanywhere, and when I tried to access it it can't access my database, but is getting a bunch of errors instead.
2021-01-30 10:42:05,740: Exception on /m/550 [GET]
Traceback (most recent call last):
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
self.dialect.do_execute(
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: near "(": syntax error
**NO MATCH**
I was not getting these exceptions on my local computer. A little googling and it seems like this is because PA uses an older version of SQLite? Which seems a bit stupid to begin with but it also seems like I can't update it?
So I'm wondering if there is some smart solution to this, or if it would perhaps be easier to switch to MySQL? Would there be other problems in doing that instead? Or do I just need to switch out the SQLALCHEMY_DATABASE_URI to fit MySQL instead? My Current one looks like this:
SQLALCHEMY_DATABASE_URI = 'sqlite:////home/movieelo/cinelow/my_server/database/database.db'
SQLALCHEMY_TRACK_MODIFICATIONS = False
All help and guidence will be greatly appreciated!
UPDATE: I managed to switch to MySQL but am still getting SQL syntax related errors, such as:
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '(PARTITION BY
movie_category_scores.category_id ORDER BY movie_category_scores.s' at line 2
**NO MATCH**
UPDATE 2
I think that I have now identiefied the single function that is causing the errors, and it looks like this:
def get_top_movies_by_category(category_id):
query = db.session.query(
MovieCategoryScores,
func.rank()\
.over(
order_by=MovieCategoryScores.score.desc(),
partition_by=MovieCategoryScores.category_id,
)\
.label('rank')
).filter(MovieCategoryScores.votes >= 10)
# now filter
query = query.filter(MovieCategoryScores.category_id == category_id)
query = query.order_by(MovieCategoryScores.category_id, 'rank')
movies = query.all()
return movies
Which generates this error:
Traceback (most recent call last):
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
self.dialect.do_execute(
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: near "(": syntax error
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/movieelo/cinelow/my_server/database/pers_movie_dbf.py", line 137, in get_top_movies_by_category
movies = query.all()
File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3186, in all
return list(self)
File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3342, in __iter__
return self._execute_and_instances(context)
File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3367, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File "/usr/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
ret = self._execute_context(
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1252, in _execute_context
self._handle_dbapi_exception(
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/usr/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
raise value.with_traceback(tb)
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
self.dialect.do_execute(
File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "(": syntax error
[SQL: SELECT movie_category_scores.votes >= ? AS anon_1, movie_category_scores.movie_id AS movie_category_scores_movie_id, movie_category_scores.category_id AS movie_category_scores_category_id, movie_category_scores.score AS movie_category_scores_score, movie_category_scores.votes AS movie_category_scores_votes, rank() OVER (PARTITION BY movie_category_scores.category_id ORDER BY movie_category_scores.score DESC) AS rank
FROM movie_category_scores
WHERE movie_category_scores.votes >= ? AND movie_category_scores.category_id = ? ORDER BY movie_category_scores.category_id, rank]
[parameters: (10, 10, 11)]
(Background on this error at: http://sqlalche.me/e/e3q8)
Giles Thomas (of PythonAnywhere) confirmed that the SQLite version available on PythonAnywhere doesn't support window functions.
Window functions were added to SQLite version 3.25.0. In the future, you can check whether the SQLite version included with your Python installation supports windows functions by running:
import sqlite3
if sqlite3.sqlite_version_info > (3, 25, 0):
print("Window functions are supported!")
else:
print("Window functions are not supported :(")