Skip to content

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

  2. Installing with pip as well,

    1pip install mysql-connector-python
  3. Verifying installation,

    2Python 3.5.2 (v3.5.2:4def2a2901a5, Jun 25 2016, 22:01:18) [MSC v.1900 32 bit (Intel)] on win32
    3Type "help", "copyright", "credits" or "license" for more information.
    4>>> import mysql
    5>>> exit()

    Note : Above step would fail, if there were any issues.

Next, i tried to run the below code from Jupyter Notebook,

1import mysql.connector
3cnx = mysql.connector.connect(user='test', password='test01',
4 host='localhost',
5 database='TEST')

This failed with below message,

1NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

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

    1CREATE USER 'snake_charmer'@'localhost' IDENTIFIED BY 'pepe' PASSWORD EXPIRE NEVER;
  3. Alter user to be identified with mysql_native_password

    1ALTER USER 'snake_charmer'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pepe';
  4. Grant privileges to new user. Since this is test, i am giving everything.

    1GRANT ALL PRIVILEGES ON TEST.* To 'snake_charmer'@'localhost' ;
  5. Check the grants

    1mysql> show grants for 'snake_charmer'@'localhost';
    3| Grants for snake_charmer@localhost |
    5| GRANT USAGE ON *.* TO `snake_charmer`@`localhost` |
    6| GRANT ALL PRIVILEGES ON `test`.* TO `snake_charmer`@`localhost` |
    82 rows in set (0.10 sec)

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

Python SELECT example,

1cnx = mysql.connector.connect(**config)
2cursor = cnx.cursor()
4query = ("SELECT actor_id, first_name, last_name, last_update FROM actor "
5 "WHERE last_update BETWEEN %s AND %s")
7hire_start =, 12, 10)
8hire_end =, 12, 31)
10cursor.execute(query, (hire_start, hire_end))
12for (actor_id, first_name, last_name, last_update) in cursor:
13 print("actorID={}, first_name={}, last_name={}, last_update={:%d %b %Y}" \
14 .format(actor_id, first_name, last_name, last_update))


1actorID=4, first_name=Jenni, last_name=Lewis, last_update=07 Apr 2017
2actorID=1000, first_name=Cruz, last_name=bluto, last_update=03 Apr 2017
3actorID=1002, first_name=Hilary, last_name=SWAY, last_update=03 Apr 2017

More information