Published on

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:

$ 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:

$ wget
$ unzip

Create Database:

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

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

Import Data:

Import the data into the world database:

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

Create the MySQL Credentials File:

Create a file and populate the credentials in a dictionary:

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

Run Queries from Python:

Enter the Python interpreter and run some queries:

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

# 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


Thank You

Thanks for reading, feel free to check out my website, feel free to subscribe to my newsletter or follow me at @ruanbekker on Twitter.

Buy Me A Coffee