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.

I'm not sure I underood your question. Do you just want to know, how to connect to a remote MySql DB? If so, you can basically use you're code and replace `host` with the IP/URL of you database access and `user` and `password` need to be adjusted too. See here for more details: https://www.thepythoncode.com/article/connect-to-a-remote-mysql-server-in-python — Marc, Sep 15 '21 at 05:22
@Marc thanks for the link. It seems to work, but actually I have a problem in creating an user because the mysql hosting website doesn't give the privilege for create or grant options. So i'll need to figure that out. Do you have any recommendations for free remote mysql server hosts? — Veeraja Veeraesh, Sep 15 '21 at 07:44
The site allows acces through phpMyAdmin, so at leas a root user should be present. (User: root, Passwd: root) — Marc, Sep 15 '21 at 07:48
@Marc Well, you were right! But when I login through the phpMyadmin page with the username as root and password as root along with the host name(which i won't divulge), It gives me an error that access is denied. So if you have any other free mysql hosting service providers that are better, let me know. — Veeraja Veeraesh, Sep 15 '21 at 10:30

score 1 · Answer 1 · answered Sep 15 '21 at 05:26

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:

IP
port
username
password
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.

score 1 · Answer 2 · answered Sep 15 '21 at 05:27

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()

score 0 · Answer 3 · answered Sep 15 '21 at 05:27

0

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

answered Sep 15 '21 at 05:27

score 0 · Answer 4 · answered Sep 15 '21 at 05:36

0

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

This might work.

answered Sep 15 '21 at 05:36

aristotle11

54
1
10

score 0 · Answer 5 · answered Mar 05 '23 at 02:47

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

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

5 Answers5

Linked