Pre-requisites

If you haven't installed PyMySQL, head back to the supplementary material to configure your system properly.

PyMySQL Examples

We will run some examples on the database we built before, the fellowship_of_the_ring

Our first example will return all the information of all the heroes that use swords. We will save it under db_swords.py.

import pymysql

# Open connection to the database
db = pymysql.connect("localhost","root","mypassword","fellowship_of_the_ring" )

# Start a cursor object using cursor() method
cursor = db.cursor()

# Execute a SQL query using execute() method. This should return all the columns of heroes that use swords.
cursor.execute("SELECT * FROM heroes WHERE weapon='Sword';")

# Fetch all the rows using fetchall() method.
data = cursor.fetchall()
print (data)

# disconnect from server
db.close()

Now we simply run:

$ python3 db_swords.py
((1, 'Frodo', 'Sword', 'Hobbit'), (2, 'Sam', 'Sword', 'Hobbit'))

For our second example, we will get just the names of all the heroes that are of the Hobbit race. We will save it under db_hobbits.py.

import pymysql

# Open connection to the database
db = pymysql.connect("localhost","root","mypassword","fellowship_of_the_ring" )

# Start a cursor object using cursor() method
cursor = db.cursor()

# Execute a SQL query using execute() method. This should return all the columns of heroes that use swords.
cursor.execute("SELECT names FROM heroes WHERE race='Hobbit';")

# Fetch all the rows using fetchall() method.
data = cursor.fetchall()
print (data)

# disconnect from server
db.close()

Now we simply run:

$ python3 db_hobbits.py
(('Frodo',), ('Sam',))

For our next example we will add a hero to the Fellowship. We will name the file db_boromir.py

import pymysql

# Open database connection. Notice the autocommit. What is it doing there?
db = pymysql.connect("localhost","root","mypassword","fellowship_of_the_ring", autocommit=True)

# Start a cursor object using cursor() method
cursor = db.cursor()

# Create entry
sql = """INSERT INTO heroes (name, weapon, race) VALUES
('Boromir','Sword','Man')"""

cursor.execute(sql)

# Create query to check if all our heroes are in the table
sql_ret = """SELECT * FROM heroes"""
cursor.execute(sql_ret)
heroes = cursor.fetchall()

print (heroes)

# disconnect from server
db.close()

Now we simply run:

$ python3 db_boromir.py
((1, 'Frodo', 'Sword', 'Hobbit'), (2, 'Sam', 'Sword', 'Hobbit'), (3, 'Gandalf', 'Staff', 'Istari'), 
(4, 'Gimli', 'Axe', 'Dwarf'), (5, 'Legolas', 'Bow', 'Elf'), (7, 'Boromir', 'Sword', 'Man'))