Issues connecting to PythonAnywhere SQL Server

Question

I'm trying to create a table in mySQL server running on pythonAnywhere from my local machine. I followed the getting started guide, https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere, but I'm running into a OperationalError: (2013, 'Lost connection to MySQL server during query').

Here is my code:

import MySQLdb
import sshtunnel

sshtunnel.SSH_TIMEOUT = 10
sshtunnel.TUNNEL_TIMEOUT = 10
with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='MyUSERNAME', ssh_password='***',
    remote_bind_address=('MyUSERNAME.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    connection = MySQLdb.connect(
        user='MyUSERNAME',
        passwd='***',
        host='127.0.0.1', port=tunnel.local_bind_port,
        db='MyUSERNAME$liveSports',
    )



cur = connection.cursor()
with connection:
    cur.execute("CREATE TABLE table_one (date TEXT, start_time TEXT)")

I'm not sure why I'm getting this error, or how to resolve it. Similar errors, Lost connection to MySQL server during query , suggest that either I'm sending an incorrect query to my server, but as far as I know this is a valid query, or that my packet is too large, which I don't believe an empty table would be.

I'm new to SQL, but I can't seem to find an answer to this question.

Is that literally what your code looks like, including the exact indentation? If so, that's the problem. As soon as the "with" statement exits, the SSH tunnel will close and you will lose your connection. You need to keep the tunnel open the entire time you are using the connection. `with` might not be the right choice here. — Tim Roberts, Oct 18 '21 at 06:06
that is what my code looks like. I'm slightly more familiar with sqlite and using with connection allows me to execute commands on the server with the cursor. How should I resolve the error? This code is taken straight from the pythonanywhere guide minus the cursor execution. — Redratz, Oct 18 '21 at 06:09
Did you read my reply? I TOLD you how to fix it. You either put ALL of your code inside the `with` statement, or just do `tunnel = SSHTunnelForwarder(...)` and leave it as a global, without using `with`. — Tim Roberts, Oct 18 '21 at 06:16

score 1 · Accepted Answer · answered Oct 18 '21 at 06:19

You must leave the tunnel open. This is the easy way:

import MySQLdb
import sshtunnel

sshtunnel.SSH_TIMEOUT = 10
sshtunnel.TUNNEL_TIMEOUT = 10
tunnel = sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='MyUSERNAME', ssh_password='***',
    remote_bind_address=('MyUSERNAME.mysql.pythonanywhere-services.com', 3306)
)
connection = MySQLdb.connect(
    user='MyUSERNAME',
    passwd='***',
    host='127.0.0.1', port=tunnel.local_bind_port,
    db='MyUSERNAME$liveSports',
)

cur = connection.cursor()
cur.execute("CREATE TABLE table_one (date TEXT, start_time TEXT)")

You could put all of your database stuff in a function and use

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='MyUSERNAME', ssh_password='***',
    remote_bind_address=('MyUSERNAME.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    do_all_processing()

def do_all_processing():
    connection = MySQLdb.connect(
        user='MyUSERNAME',
        passwd='***',
        host='127.0.0.1', port=tunnel.local_bind_port,
        db='MyUSERNAME$liveSports',
    )
    ...etc...

Hey, thank you for the response, I didn't fully understand your reply. I see now that leaving out ```with``` will allow me to execute commands with the db connection open. I'm using a class so I need to leave the db connection open to allow my data scraper to interact with the connection and pass information to the db. Thank you for the answer. — Redratz, Oct 18 '21 at 06:21

Issues connecting to PythonAnywhere SQL Server

1 Answers1