Issues connecting to PythonAnywhere SQL Server-CodePudding

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.

CodePudding user response：

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...