Setting up SQL to be remotely accessed with python

Question

Ok so I'm a complete nood in SQL, and SQL connections. I have app which requires an SQL connection, up until now i have not had any SQL issues because i have been using xampp to connect to a local database, which is pretty straight forward to configure. However now i have a database on a cPanel account, with namecheap. When i search online every tutorial has a Remote Access option for their mySQL DB in the main interface of their cPanel, however i don't. I followed the namecheap tutorial for connecting to your data base remotely and i ran the following command on my machine.

ssh -f [email protected] -p21098 -L 3306:127.0.0.1:3306 -N

Entered my password and everything and then tried to run this python script

import pymysql

# Fields = the host, the user, the password, the database

try:
    db = pymysql.connect("host.com", "hostUser", "********" , "hostDB")

    # prepare a cursor object using 
    cursor = db.cursor()
     
    # Creating tables for our data base
    user_table = """CREATE TABLE user(  
                id INT AUTO_INCREMENT,
                First_Name VARCHAR(100),
                Last_Name VARCHAR(100),
                Email VARCHAR(100),
                Password VARCHAR(100),
                register_date DATETIME,
                PRIMARY KEY(id));"""

   # Execute databse insertion
    cursor.execute(user_table)

    print("Succesfully created the table")
except Exception as e:
    print("Failed to create table: ", str(e))

And I got this error

Failed to create table:  (2003, "Can't connect to MySQL server on 'host.com' (timed out)")

Not only that but now I cant even access my original locally hosted data base. I ran the netsat command in my cmd.

Input:

netstat -aon | findstr [3306]

Output:

 TCP    127.0.0.1:3306         0.0.0.0:0              LISTENING       15676

My question is how can i connect to the data base hosted on cPanel, and what actual parameters do i need to change in my physical machine to go back to running code on the locally hosted database. I just want to ability to be able to go from posting in the cPanel DB to posting on the locally hosted DB. What is the simplest way to do this

Answer 1

the host is wrong

The host must be '127.0.0.1' Port 3306

So every package that arrives at that address(your local computer) and port gets transported to the 'other' side via the ssh tunnel