Connecting to mysql database using python on Hostgator

Hi everyone, I am trying to connect to a mysql database on my Hostgator web server
I followed the instruction in this link:

However, i encounter the following errors:

Runtime error (OdbcException): ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

The following is my code:
import clr
import System
clr.AddReference(“System.Data”)
from System.Data import DataSet
from System.Data.Odbc import OdbcConnection, OdbcDataAdapter

connectString = (“DRIVER={SQL Server};”
“SERVER=xxx.xxx.xxx.xxx;”
“PORT=3306;DATABASE=mydb;”
“UID=root;”
“PWD=’’;”
“OPTION=3;”)

query = “SELECT * FROM users”

connection = OdbcConnection(connectString)

adaptor = OdbcDataAdapter(query, connection)
dataSet = DataSet()

connection.Open()
adaptor.Fill(dataSet)
connection.Close()

Make sure your database actually listens on the IP address and port you specified. Are you suer the driver string is correct? For MySQL in the cookbook you link a different string is used…

Also ensure you give the correct database username and its password. Are you sure the database config allows you to connect as root from anything else than localhost?

Thanks for the quick response dude.
I’m sure the credentials are correct. the UID and PWD are suggested by another website. I’ve tried different combination, but no luck is given.

Also, I already enabled the remote database connection of my current computer

btw, How do i know the database is listening the ip and port?

also, when i use the DRIVER suggested by the link
the following error is given instead

Runtime error (OdbcException): ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I guess this is the first thing to find out. To me it sounds that either root connections are not allowed, non-localhost connectios are not allowed, or credentials are incorrect. If you have CLI access to your database I suggest adding a user with a password, and ensure you grant the proper privileges etc.

1 Like

The root is just a fake data. I guess i will double check the credentials.
thanks anyway =)

Hey man I just solve the problem.
MySQL driver (used on Hostgator) should be set instead of SQL Server (from MS)
I install MySQL driver and get the driver name in the control panel. it requires the most updated Visual Studio too.
And it works so well now.
Thanks again for your suggestion =)