SQLAlchemy and SQLite errors appearing on PA but fine on localhost : Forums : PythonAnywhere

SQLAlchemy and SQLite errors appearing on PA but 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!

movieelo | 6 posts | Jan. 30, 2021, 10:59 a.m. | permalink

Is there any more to that traceback? The error looks like it's saying that SQLAlchemy is sending invalid SQL to SQLite, which would be unusual to say the least.

giles | 11800 posts | PythonAnywhere staff | Jan. 30, 2021, 3:58 p.m. | permalink

Sure I can send a whole error, for reference I have since posting the original question managed to switch the database to MySQL but I am still getting errors that look mostly the same. Here is one in it's entirety:

2021-01-30 14:22:31,233: Exception on /m/550 [GET]
Traceback (most recent call last):
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/movieelo/cinelow/my_server/people_movies/routes.py", line 22, in moviePage
    pmf.add_movie(movie_id)
  File "/home/movieelo/cinelow/my_server/database/pers_movie_dbf.py", line 31, in add_movie
    cate.movies.append(a)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 282, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 710, in get
    value = self.callable_(state, passive)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 729, in _load_for_state
    return self._emit_lazyload(
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 848, in _emit_lazyload
    lazy_clause, params = self._generate_lazy_clause(state, passive)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 650, in _generate_lazy_clause
    value = mapper._get_state_attr_by_column(
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 2795, in _get_state_attr_by_column
    return state.manager[prop.key].impl.get(state, dict_, passive=passive)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 705, in get
    value = state._load_expired(state, passive)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/state.py", line 660, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 985, in load_scalar_attributes
    raise orm_exc.ObjectDeletedError(state)
sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Category at 0x7fea857543d0>' has been deleted, or its row is otherwise not present.
2021-01-30 14:27:16,434: 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)
  File "/usr/lib/python3.8/site-packages/mysql/connector/cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python3.8/site-packages/mysql/connector/connection.py", line 590, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/lib/python3.8/site-packages/mysql/connector/connection.py", line 478, in _handle_result
    raise errors.get_exception(packet)
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**

I agree that it seems like SQLAlchemy is generating invalid SQL, but as mentioned before it was working perfectly when running on my own computer, so I am a little bit dumbfounded. Could it have anything to do with the fact that I was running Flask-SQLAlchemy?

movieelo | 6 posts | Jan. 30, 2021, 5:56 p.m. | permalink

It could be. I'm wondering if perhaps there's something about your code that is tripping up SQLAlchemy, though I can't see why that might not trip up Flask-SQLAlchemy too! Whatever it is seems to be triggered by your line:

cate.movies.append(a)

It's a bit of a stab in the dark, but are there any fields in the objects involved in that transaction that have names that might clash with SQL keywords or something like that (say, a field called "select")? I'd certainly have thought that SQLAlchemy would be fine with that and would just make adjustments to the names of the database columns so that there weren't any clashes, but you never know.

giles | 11800 posts | PythonAnywhere staff | Jan. 30, 2021, 8:11 p.m. | permalink

No not really, however I think I have identified what the root of the problem is. It is this SQLAlchemy query:

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)

movieelo | 6 posts | Jan. 31, 2021, 10:39 a.m. | permalink

It looks like we've been talking over on Stack Overflow too, and Max Shenfield picked up on the likely cause -- it's that over clause, which is a window function and isn't supported by the versions of SQLite and MySQL that we have here. Is there another way to express the query that you're trying to do without the use of over?

giles | 11800 posts | PythonAnywhere staff | Jan. 31, 2021, 4:21 p.m. | permalink

No I actually don't think I can... There is no workaround to be able to use window functions in pythonanywhere?

movieelo | 6 posts | Jan. 31, 2021, 6:11 p.m. | permalink

You could perhaps use Postgres? That costs extra, though, as we have to spin up an entirely new container to run your server (unlike MySQL where multiple people can share one server instance).

giles | 11800 posts | PythonAnywhere staff | Jan. 31, 2021, 6:33 p.m. | permalink

I see, and my current code would work on postgres? Are there any other benifits of using postgres over MySQL?

movieelo | 6 posts | Jan. 31, 2021, 6:37 p.m. | permalink

Your code should work fine on Postgres; SQLAlchemy normally masks differences between different databases from you (except, of course, in cases like this one where the underlying database doesn't support a particular feature).

You'll probably get better performance on Postgres, because you'll be using your own separate server, and of course it has more features -- this is a pretty good comparison of the pros and cons.

giles | 11800 posts | PythonAnywhere staff | Feb. 1, 2021, 11:28 a.m. | permalink