How to access an external MySQL database from within a PythonAnywhere Flask Webapp (via Pandas&sqlal-CodePudding

I am attempting to convert a Flask webapp to run on PythonAnywhere instead of my Raspberry Pi on which it is currently hosted and functions perfectly.

One of the core features of the webapp is using Pandas to query an externally hosted MySQL database (NOT hosted on PythonAnywhere).

Previously I have done this using the following method, with no troubles:

import pandas as pd
URI=f"mysql://{user}@{host}:{port}/{schema}"
my_dataframe=pd.read_sql_query(sql="select * from users",con=URI)

Attempting this with the webapp hosted on PythonAnywhere results in a 502-backend error, with an error log of:

File "./webapp.py", line 240, in ages
    message_, success=scripts.ages.main()
  File "./scripts/ages.py", line 22, in main
    my_dataframe=pd.read_sql_query(sql="select * from users",con=URI)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/pandas/io/sql.py", line 383, in read_sql_query
    chunksize=chunksize,
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/pandas/io/sql.py", line 1295, in read_query
    result = self.execute(*args)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/pandas/io/sql.py", line 1162, in execute
    *args, **kwargs
  File "<string>", line 2, in execute
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 401, in warned
    return fn(*args, **kwargs)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3145, in execute
    connection = self.connect(close_with_result=True)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3204, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3283, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3254, in _wrap_pool_connect
    e, dialect, self
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2101, in _handle_dbapi_exception_noconnection
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 3250, in _wrap_pool_connect
    return fn()
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/MySQLdb/__init__.py", line 130, in Connect
    return Connection(*args, **kwargs)
  File "/home/mywebapp/.virtualenvs/my-virtualenv/lib/python3.6/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2003, "Can't connect to MySQL server on '34.105.08.12:8080' (111)")
(Background on this error at: https://sqlalche.me/e/14/e3q8)

(I've changed the IP)

I've take a look at the linked site https://sqlalche.me/e/14/e3q8, but didn't see anything useful on there.

I've also tried googling how to use sqlalchemy within PythonAnywhere. This mostly resulted in questions about connecting to databases hosted by PythonAnywhere, which doesn't help me.

I came across this link:

https://help.pythonanywhere.com/pages/UsingSQLAlchemywithMySQL/

I don't actually know if it's referring to using SQLAlchemy to connect to a PythonAnywhere hosted MySQL database, or an external one, but I tried implementing this anyway. I doubt it makes a difference, but the URI is in a separate file to the Pandas sql query.

#credentials.py
from sqlalchemy import create_engine
URI_string=f"mysql://{user}@{host}:{port}/{schema}"
URI = create_engine(URI_string, pool_recycle=280)

import pandas as pd
import credentials
my_dataframe=pd.read_sql_query(sql="select * from users",con=credentials.URI)

The webapp is still functional when testing on the Pi with this change, however it made no difference in the outcome when testing on PyAnywhere and the same error log output is received.

If anyone has any ideas please let me know!

CodePudding user response：

If you're using a free account, you will only be able to connect out of PythonAnywhere using http(s) to a list of approved sites. So external database connections will not work from a free account.