Connecting to mySQL from Python
Connecting to database is mostly next step after playing with text/csv files. Here, i am trying to connect to mySQL which i have installed in my laptop using Jupyter Notebook and it seems i have not installed mysql package.
Installed the following,
mysql-connector-python-8.0.19-windows-x86-64bit.msiwhich i had got from dev.mysql.com
Installing with pip as well,
Note : Above step would fail, if there were any issues.
Next, i tried to run the below code from Jupyter Notebook,
This failed with below message,
It seems from from MySQL 8.0,
caching_sha2_password is the default authentication plugin rather than
mysql_native_password and thats the issue.
To resolve this, i created a new user with
mysql_native_password, below are the steps,
Login into mysql as admin user .
mysql -u root -p
Create a new user
Alter user to be identified with
Grant privileges to new user. Since this is test, i am giving everything.
Check the grants
Retry the python connection code in Jupyter again, it should work.
Python SELECT example,