I'm trying to connect my Jupyter Notebook, I use Google Colab, with my local MySQL database. When I run this script in PyCharm, my preferred IDE, it works no problem. Only when I run it in Google Colab or Project Jupyter, both get the same error, does this happen.
import pymysql
import pandas as pd
mySQLuser = 'username'
mySQLpasswd = 'password'
mySQLhost = '127.0.0.1'
mySQLport = '3306'
mySQLdatabase = 'databasename'
connection = pymysql.connect(user = mySQLuser, passwd = mySQLpasswd, host = mySQLhost, port = mySQLport, db = mySQLdatabase)
I've also tried the same with sqlalchemy(create_engine) and mysql.connector, but get the same error.
OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 111] Connection refused)")
I've also tried granting all privileges to the user and that didn't change anything. Another suggestion I've seen online is to null or change the bind-address in the configuration file (my.ini), but that didn't affect anything either.
Are you sure your colab/jupyter instances are run locally?
If that's not the case, you will not be able to access the SQL database on localhost (127.0.0.1) until you make your it accessible remotely (which implies several steps: making it accessible, changing the mySQLhost address in your code, authorizing connections from the host server in your SQL settings).
Got it to work. The service provider was rejecting the requests despite me configuring the network to allow it, but I sorted it out.