Skip to content
bobby_dreamer
GithubLinkedIn

Connecting to mySQL from Python

— python, pandas, mysql, e&s — 1 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, 

    1pip install mysql-connector-python

  3. Verifying installation,

    1C:\Users\Sushanth>python
    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
2

3cnx = mysql.connector.connect(user='test', password='test01',
4                              host='localhost',
5                              database='TEST')
6print(cnx)
7cnx.close()

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';
    2+-----------------------------------------------------------------+
    3| Grants for snake_charmer@localhost                              |
    4+-----------------------------------------------------------------+
    5| GRANT USAGE ON *.* TO `snake_charmer`@`localhost`               |
    6| GRANT ALL PRIVILEGES ON `test`.* TO `snake_charmer`@`localhost` |
    7+-----------------------------------------------------------------+
    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()
3

4query = ("SELECT actor_id, first_name, last_name, last_update FROM actor "
5         "WHERE last_update BETWEEN %s AND %s")
6

7hire_start = dt.date(2016, 12, 10)
8hire_end = dt.date(2017, 12, 31)
9

10cursor.execute(query, (hire_start, hire_end))
11

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))
15

16cursor.close()
17cnx.close()

Output: 

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