Access MySQL from Python Notebook using MySQLdb

MySQL is one of the most popular open source relational databases. In this article we will explore how to connect to a MySQL database from a Python notebook and perform data access operations.

We will use the MySQLdb, which is a thread-compatible interface to MySQL that provides the Python database API. This package is pre-installed in your Workbench. Open the Jupyter notebooks on your Data Scientist Workbench and choose Python in the top right corner. Create a new cell, enter the following cell and execute it (Ctrl+Enter).
import MySQLdb
Next, enter your database credentials.
#Enter the values for you database connection
dsn_database = "Enter Database name"  # e.g. "MySQLdbtest"
dsn_hostname = "<Enter Hostname" # e.g.: "mydbinstance.xyz.us-east-1.rds.amazonaws.com"
dsn_port = 3306               # e.g. 3306
dsn_uid = "<Enter UserID>"       # e.g. "user1"
dsn_pwd = "<Enter Password"     # e.g. "Password123"
Now establish the database connection.
conn = MySQLdb.connect(host=dsn_hostname, port=dsn_port, user=dsn_uid, passwd=dsn_pwd, db=dsn_database)
Let's create a sample table and insert some data into it.
conn.query("""DROP TABLE IF EXISTS Cars""")
conn.query("""CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)""")
conn.query("""INSERT INTO Cars VALUES(1,'Audi',52642)""")
conn.query("""INSERT INTO Cars VALUES(2,'Mercedes',57127)""")
conn.query("""INSERT INTO Cars VALUES(3,'Skoda',9000)""")
conn.query("""INSERT INTO Cars VALUES(4,'Volvo',29000)""")
conn.query("""INSERT INTO Cars VALUES(5,'Bentley',350000)""")
conn.query("""INSERT INTO Cars VALUES(6,'Citroen',21000)""")
conn.query("""INSERT INTO Cars VALUES(7,'Hummer',41400)""")
conn.query("""INSERT INTO Cars VALUES(8,'Volkswagen',21600)""")
conn.cursor will return a cursor object, you can use this cursor to perform queries.
cursor=conn.cursor()
cursor.execute("""SELECT * FROM Cars""")
cursor.fetchone()
Now you can print out the result set using pretty print:
print "\nShow me the records:\n"
rows = cursor.fetchall()
import pprint
pprint.pprint(rows)
You should see the following results:
Show me the records:

((2L, 'Mercedes', 57127L),
 (3L, 'Skoda', 9000L),
 (4L, 'Volvo', 29000L),
 (5L, 'Bentley', 350000L),
 (6L, 'Citroen', 21000L),
 (7L, 'Hummer', 41400L),
 (8L, 'Volkswagen', 21600L))
It is a good practice to close the connection when you are done.
conn.close()
Good luck!

Feedback and Knowledge Base