Skip to content

bobby_dreamer

Connecting to mySQL from Python

python, pandas, mysql, e&s1 min read

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,

  1. Installed mysql-connector-python-8.0.19-windows-x86-64bit.msi which i had got from dev.mysql.com

  2. Installing with pip as well,

  3. Verifying installation,

    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,

  1. Login into mysql as admin user . mysql -u root -p

  2. Create a new user

  3. Alter user to be identified with mysql_native_password

  4. Grant privileges to new user. Since this is test, i am giving everything.

  5. Check the grants

Retry the python connection code in Jupyter again, it should work.

Python SELECT example,

Output:

More information