How do I connect my python code to a mysql database hosted on the internet?

Question

I have made a free account on https://www.freemysqlhosting.net which provides a free mysql database hosting facilities. I have created a database there, but I don't know how to connect to it from my python code on VSCode.
As of now, I am using a database that is on my computer.
This is the code in a config.py file that establishes connection to the MySQL server.

import mysql.connector as mysql

mysqlobj = mysql.connect(host='localhost', user='root',password='Mypass', database='timetable')

cursorobj = mysqlobj.cursor()

cursorobj.execute('USE timetable')

So how do I connect to a remote database on the said website with a piece of code that can be executed, because that way the connection can be established from any computer if they have the code to do so, without installing extensions(unless it's necessary).
If you require more details, please do ask in the comment section.

Answer 1

This looks like a dup of Connecting to remote database located on web (freemysqlhosting.net‏) via visual studio c#. I've never used that service(looks pretty sketchy). They should provide you a connection string or at least the connection parts which consists of:

1. IP
2. port
3. username
4. password
5. database

NOTE: it's generally a bad idea to have a mysql server bound to ports exposed to the public internet, even with a username password.

Answer 2

You should use remote address of server on which MySQL server is hosted and provide the IP address or Domain name exposed as connection properties to you in host,

import mysql.connector

config = {
  'user': 'test',
  'password': 'password',
  'host': 'Remote IP or Domain Name',
  'database': 'employees',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)

cnx.close()

Answer 3

I think you should be using pyodbc instead of mysql connector. That's what I remember using.

To install pyodbc it is a simple pip install pyodbc command You may find more useful information here

Answer 4

If you're unable to connect to your hostedDB, try to whitelist your local IP address in your hosting server.

This might work.

Answer 5

Run this command in your terminal to install mysql connector:

pip install mysql-connector-python

And run this in your python editor to connect to MySQL: import mysql.connector

mydb = mysql.connector.connect(
      host="sql12.freemysqlhosting.net",
      user="sql12602575drf",       # just an example
      passwd="BdfSPcdrfz5dJ",      # just an example
      database="sql1260257drf5"    # just an example
)

You are connected! If you face any errors, that is probably you are entering your host/user/password/database wrongly!

Now, do whatever you want:

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")    
mycursor.execute("SHOW TABLES")

mycursor.execute("INSERT INTO customers (name, address) VALUES ('John', 'Highway 21')")    
mydb.commit() # Use this command after insert, update, delete commands