Forums

I am trying to connect to a Python Anywhere mySQL database while doing development and running into SSH issues. I read this - https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/ - but can really use some code samples that use SQLAlchemy. Any guidance, please? Thanks.

What kind of errors do you get?

I did not get any code errors since I did not try and I am not quite sure how I would plug it in. Using SQLAlchemy, I only have a couple lines of code and all the DB functions magically work. I simply have:

SQLALCHEMY_DATABASE_URI = "mysql://joeg:password@joeg.mysql.pythonanywhere-services.com/joeg$database_name"

db = SQLAlchemy(app)

I am not sure how that all translates to using the SSH tunnel. Do I need to pass in all that code before each DB query etc?

If you use the "From Python code" section on that help page, you'd just put all of your SQLAlchemy code inside the "with" statement. You'd need to change the URI to reflect the different host and port number.

However, it does look like you're using Flask-SQLAlchemy, so perhaps you could give some more details on what you're trying to achieve -- are you trying to run your Flask site locally on your computer, and connect it to your MySQL instance on PythonAnywhere?

Giles - exactly. I am doing Flask development on my local system and want to use a mySQL Database on Python Anywhere. Do you have any code samples for that scenario?

For that scenario, your best bet would be to use the "Manual SSH tunnelling" from the help page and then just connect your Flask app to the local end of the tunnel.

watch this video - for me it solved 2 days browsing google and PA forum... https://www.youtube.com/watch?v=UXsb4IFkSfc&t=626s

Pretty Printed to the rescue again. He has an amazing talent for demystifying Flask and related.

Again, thanks for posting that!

Unfortunately, I am still stuck even after the mighty Pretty Printed example. :(

Here is my sample code:

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

tunnel = sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'), ssh_username='MYNAME', ssh_password='MYPASS',
    remote_bind_address=(MYNAME.mysql.pythonanywhere-services.com', 3306)
)

tunnel.start()

SQLALCHEMY_DATABASE_URI = 'mysql+mysqlconnector://MYNAME:MYDBPASS@127.0.0.1:{}/MYNAME$DBNAME'.format(tunnel.local_bind_port)

When I start the server and try to run db.create_all(), it hangs forever. I received an email from PA that my processes were in the tarpit. From what I can tell, it just keeps trying to make SSH connections and hangs.

I am not sure how to debug further. :( Thanks for any guidance.

It must be very busy doing something. Maybe add some logging to your code to see if it's just running db.create_all()

I am just running this from the Python console:

from application import db
db.create_all()

Any idea how I can add logging to that? It just hangs there with no output. If I do show tables on the DB, it never creates anything...

Do you see anything if you run show processlist in a MySQL console connected to the database?

I only see this if it means anything:

mysql> show processlist;
+-----------+------+------------------+-----------------+---------+------+----------+------------------+
| Id        | User | Host             | db              | Command | Time | State    | Info             |
+-----------+------+------------------+-----------------+---------+------+----------+------------------+
| 457629556 | USER | 10.0.0.249:51086 | USER$DB | Query   |    0 | starting | show processlist |
| 458269340 | USER | 10.0.0.197:33530 | USER$DB | Sleep   |   30 |          | NULL             |
+-----------+------+------------------+-----------------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

That looks like a database that is not doing much.

Based on the previous queries in this thread, it's not clear whether you're running the create_all code from your own machine or on PythonAnywhere. If it's on your own machine, it could just be that you do not have a working SSH tunnel or that the code is not being run in a way that would use the tunnel.

I am running the create_all code from my local machine and trying to use the database hosted on PA. I can connect to the database using Pycharm's database tools, which uses SSH, but I can't quite get it working through Python code.

If I modify the code to purposely give it invalid SSH or DB credentials, it gives the expected error messages. If I run it through a debugger, the proper variables appear to be set so I THINK it is connecting. But it is a black box after that. :(

Maybe try to open your tunnel in the python repl and check manually if you are able to open db connection there.

@fjl - any leads on how to test if I am able to open a DB connection using the repl?

Hi @joeg -- sorry, could you actually try the code we suggest on our help page: https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/ in the section "From Python code"?