Access MySQL from Python Notebook using MySQLdb
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 MySQLdbNext, enter your database credentials.
#Enter the values for you database connectionNow establish the 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"
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.cursor will return a cursor object, you can use this cursor to perform queries.
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)""")
cursor=conn.cursor()Now you can print out the result set using pretty print:
cursor.execute("""SELECT * FROM Cars""")
cursor.fetchone()
print "\nShow me the records:\n"You should see the following results:
rows = cursor.fetchall()
import pprint
pprint.pprint(rows)
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!