Ruan Bekker's Blog

From a Curious mind to Posts on Github

Using Python to Read Data From a MySQL Database

Wanted to use Python to read some data from MySQL and stumbled upon a couple of great resources, which I noted some of my output below:

Install Dependencies:

1
2
3

$ apt install python-dev libmysqlclient-dev python-setuptools gcc
$ easy_install pip
$ pip install MySQL-python

Download Some Sample Data:

Download the world dataset for MySQL:

1
2

$ wget http://downloads.mysql.com/docs/world.sql.zip
$ unzip world.sql.zip

Create Database:

Create the Database in MySQL for the dataset that we downloaded:

1

$ mysql -u root -p -e'CREATE DATABASE world;'

Import Data:

Import the data into the world database:

1

$ mysql -u root -p world < world.sql

Create the MySQL Credentials File:

Create a config.py file and populate the credentials in a dictionary:

1
2
3
4
5
6
7
8

credentials = {
  'mysql': {
      'host': 'localhost',
      'username': 'root',
      'password': 'password',
      'database': 'world'
  }
}

Run Queries from Python:

Enter the Python interpreter and run some queries:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

>>> import MySQLdb as pdb
>>> from config import credentials as secrets

# assignments 
>>> db_host = secrets['mysql']['host']
>>> db_username = secrets['mysql']['username']
>>> db_password = secrets['mysql']['password']
>>> db_name = secrets['mysql']['database']

# create a connection to the database
>>> conn = pdb.connect(host=db_host, user=db_username, passwd=db_password, db=db_name)

# create a object for the queries we will be using
>>> cursor = conn.cursor()

# execute the query
>>> cursor.execute('select continent, name from country where continent = "Africa" limit 5')
5L

# fetch the results by assigning it to the results object:
>>> results = cursor.fetchall()

# loop and print results:
>>> for x in results:
...     print(x)
...
('Africa', 'Angola')
('Africa', 'Burundi')
('Africa', 'Benin')
('Africa', 'Burkina Faso')
('Africa', 'Botswana')

# close the connection
>>> conn.close()

Graphing Results to Plotly:

A great blogpost that shows how to use this data to graph the results to plotly

Resources: