Install pymysql

Either use pip to install pymysql or enter the following code into a new notebook cell and execute it:

!pip3 install pymysql

The result should be similar to the following, possibly with different version numbers:

Collecting pymysql
  Downloading PyMySQL-1.0.2-py3-none-any.whl (43 kB)
Installing collected packages: pymysql
Successfully installed pymysql-1.0.2

Create the Database

MariaDB has a good online knowledge base, so it's easy to find explanations of the SQL commands that it supports. For example, if you search for MariaDB create user, you can find a page that explains the CREATE USER command. I'm going to be lazy and use the root user for this example, but in general it's better to create a separate database user for each flesh-and-blood user so that they can log in separately and so that you can minimize their privileges. It's generally safer if normal users cannot create and destroy databases, tables, and other users at will.

To create the database, enter the following code in a new notebook cell and execute it:

# Create the AnimalData database.
import pymysql
 
# Connect to the database server.
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="TheSecretPassword")
 
# Create a cursor.
cur = conn.cursor()
 
# Drop the database if it exists.
cur.execute("DROP DATABASE IF EXISTS AnimalData")
 
# Create the database.
cur.execute("CREATE DATABASE AnimalData") 
 
# List the available databases.
cur.execute("SHOW DATABASES")
databases = cur.fetchall()
for database in databases:
    print(database)
 
# Close the connection.
conn.close()

This code first imports the pymysql module. It then creates a connection object attached to the database server. Replace the password shown here with the one you used when you installed MariaDB. Notice that this code does not specify a database because we haven't created one yet.

Next, the code creates a cursor. Databases use cursors to execute commands and return results. This code uses the cursor's execute method to execute the following SQL statement:

DROP DATABASE IF EXISTS AnimalData

SQL is reasonably English-like, so you can probably guess that this drops the database named AnimalData (for example, if you used HeidiSQL to create it). The IF EXISTS clause tells the cursor to not try to drop the database if it doesn't exist. Trying to drop a database that doesn't exist (or more generally, to work with any object that doesn't exist) will make the program throw a temper tantrum.

Next, the code executes the following SQL statement to create the database:

CREATE DATABASE AnimalData

The code then uses the cursor to execute the SHOW DATABASES query command to list the available databases. It uses the cursor's fetchall method to retrieve the query's results and then loops through the results and prints them.

The code finishes by closing the database connection to release the connection's resources.

The following text shows the output from the previous code:

('animaldata',)
('information_schema',)
('mysql',)
('performance:schema',)
('sys',)

Each of these values is a tuple (a sort of unchangeable list) containing a single item that holds the name of a database.

The first returned item is the database that we just created. The others represent system databases and schemas that MariaDB needs to manage its data. If you compare this output to Figure 16.6, you'll see the same list of databases.

Define Tables

Enter the following code into a new notebook cell to create the database's tables:

# Create the tables.
import pymysql
 
# Connect to the database server.
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="TheSecretPassword",
    database="AnimalData")
 
# Create a cursor.
cur = conn.cursor()
 
# Drop any existing tables.
cur.execute("DROP TABLE IF EXISTS AnimalPlanets")
cur.execute("DROP TABLE IF EXISTS Animals")
cur.execute("DROP TABLE IF EXISTS Planets")
 
# Create the Animals table.
cmd = """CREATE TABLE Animals
(
    Size TEXT NOT NULL,
    Animal TEXT NOT NULL,
    AnimalId INT PRIMARY KEY
)"""
cur.execute(cmd)
 
# Create the Planets table.
cmd = """CREATE TABLE Planets
(
    PlanetId INT PRIMARY KEY,
    HomePlanet TEXT NOT NULL,
    PlanetaryMass FLOAT NOT NULL
)"""
cur.execute(cmd)
 
# Create the AnimalPlanets table.
# Foreign keys:
#    AnimalPlanets.AnimalId = Animals.AnimalId
#    AnimalPlanets.PlanetId = Planets.PlanetId
cmd = """CREATE TABLE AnimalPlanets
(
    AnimalId INT,
    PlanetId INT,
    CONSTRAINT fk_animals
        FOREIGN KEY (AnimalId) REFERENCES Animals (AnimalId)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    CONSTRAINT fk_planets
        FOREIGN KEY (PlanetId) REFERENCES Planets (PlanetId)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    PRIMARY KEY (AnimalId, PlanetId)
)"""
cur.execute(cmd)
 
# Close the connection.
conn.close()

The code first creates a database connection much as before. This time, however, it includes the database name AnimalData so it will work with that database.

Next, the code executes three DROP TABLE statements to delete the AnimalPlanets, Animals, and Planets tables if they already exist. Note, you must drop the tables in a valid order. In this database, the AnimalPlanets table has foreign key constraints matching fields in the Animals and Planets tables. If the AnimalPlanets table contains data and you drop either of the other tables first, then the AnimalPlanets records will violate their constraints and the program will crash.

Dropping tables (if they exist) and then re-creating them is a common technique for building test databases. It lets you remove any experimental changes to the tables so that you can start with a known state. (This is the main reason why I didn't use HeidiSQL to build the database. If you build the database with a database management tool and then later decide to modify the table structure, you need to use the tool to rebuild the database's structure. It's often faster and easier to modify a program or script and rerun it to rebuild the database from scratch.)

Next, the code executes three CREATE TABLE statements to build the tables. The CREATE TABLE statement can be pretty complicated, but you can look at the code to get an idea of how it works. The first two statements just list the tables’ fields, giving them their data types and some special properties such as NOT NULL to indicate that a field is required and PRIMARY KEY to indicate that a field is the table's primary key.

The third CREATE TABLE statement is a bit more complicated. It defines the table's fields, and then defines two foreign key constraints. The field name after the FOREIGN KEY keywords indicates the field in this table. The table and field names after the REFERENCES keyword tell what table and field the first field must match. In this example, the fk_animals constraint requires that each AnimalPlanets.AnimalId value must match some Animals.AnimalId value.

Similarly, the fk_planets constraint requires that AnimalPlanets.PlanetId values must match some Planets.PlanetId value.

The ON UPDATE RESTRICT and ON DELETE RESTRICT clauses mean that the database will prevent (restrict) any updates or deletions that violate the constraints. Other choices can make a change cascade to related records or null out related fields.

This CREATE TABLE statement finishes with a PRIMARY KEY statement that makes the pair of fields AnimalId and PlanetId the table's primary key.

Create Data

To add some data to the tables, create a new notebook cell and enter the following code:

# Create some data.
import pymysql
 
# Connect to the database server.
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="TheSecretPassword",
    database="AnimalData")
 
# Create a cursor.
cur = conn.cursor()
 
# Delete any previous records.
cur.execute("DELETE FROM AnimalPlanets")
cur.execute("DELETE FROM Animals")
cur.execute("DELETE FROM Planets")
 
# Add records to the Animals table.
cmd = """INSERT INTO Animals
    (Size, Animal, AnimalId) VALUES
    ('Medium', 'Hermaflamingo', 1)"""
cur.execute(cmd)
 
cmd = """INSERT INTO Animals
    (Size, Animal, AnimalId) VALUES
    ('Large', 'Skunopotamus', 2)"""
cur.execute(cmd)
 
cmd = """INSERT INTO Animals
    (Size, Animal, AnimalId) VALUES
    ('Medium', 'Mothalope', 3)"""
cur.execute(cmd)
 
cmd = """INSERT INTO Animals
    (Size, Animal, AnimalId) VALUES
    ('Small', 'Platypus', 4)"""
cur.execute(cmd)
 
# Add records to the Planets table.
cur.executemany("""
    INSERT INTO Planets (PlanetId, HomePlanet, PlanetaryMass)
    VALUES (%s, %s, %s)""",
    [
        (101, "Virgon 4", 1.21),
        (102, "Dilbertopia", 0.88),
        (103, "Xanth", 0.01),
        (104, "Australia", 1.0)
    ])
 
# Add records to the AnimalPlanets table.
cur.executemany("""
    INSERT INTO AnimalPlanets (AnimalId, PlanetId)
    VALUES (%s, %s)""",
    [
        (1, 101),
        (2, 101),
        (2, 102),
        (3, 103),
        (4, 104)
    ])
 
# Commit the changes.
conn.commit()
 
# Close the connection.
conn.close()

This code creates a database connection and defines a cursor as usual. It then deletes all the records from the database's three tables. Emptying a table before adding test records is another common testing technique.

Next, the code creates four INSERT INTO statements and executes them one at a time to add records to the Animals table. That works but is somewhat verbose.

The code then uses a more concise method to add several records at once. It calls the cursor's executemany method, passing it an INSERT statement that contains the value %s as a placeholder for field values. It also passes the method an array of tuples, giving the values for each record. This approach lets the cursor insert many records all at once, which makes the code shorter. It also allows the database to optimize the insertion operation, so it is likely to be faster than inserting the records one at a time, although you won't notice the difference for such a small example.

After it has added the data, the code calls the connection object's commit method. This is important! If you don't commit the changes, then the newly added values are removed and you're left scratching your head while staring at code that you know should work. (Not that I have firsthand experience of that or anything.)

Fetch Data

To verify that the preceding code actually adds data to the database, create a new cell and add the following code:

# Fetch data.
import pymysql
 
# Connect to the database server.
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="TheSecretPassword",
    database="AnimalData")
 
# Create a cursor.
cur = conn.cursor()
 
# Select each table's records.
print("*** Animals ***")
cmd = "SELECT * FROM Animals"
cur.execute(cmd)
rows = cur.fetchall()
for row in rows:
    print(row)
 
print("
*** Planets ***")
cmd = "SELECT * FROM Planets"
cur.execute(cmd)
rows = cur.fetchall()
for row in rows:
    print(row)
 
print("
*** AnimalPlanets ***")
cmd = "SELECT * FROM AnimalPlanets"
cur.execute(cmd)
rows = cur.fetchall()
for row in rows:
    print(row)
 
# Select matching records.
cmd = """SELECT Size, Animal, HomePlanet, PlanetaryMass
FROM Animals, Planets, AnimalPlanets
WHERE
    Animals.AnimalId = AnimalPlanets.AnimalId AND
    Planets.PlanetId = AnimalPlanets.PlanetId
ORDER BY Animal"""
 
print("
*** Results ***")
cur.execute(cmd)
rows = cur.fetchall()
for row in results:
    print(row)
 
# Close the connection.
conn.close()

This code connects to the database as usual. It then creates three queries that fetch the data from the tables. It passes the queries into the cursor's fetchall method and loops through the returned tuples of results and displays each row's data.

The final query selects values from the tables using the ID fields to link corresponding records.

The following text shows the results:

*** Animals ***
('Medium', 'Hermaflamingo', 1)
('Large', 'Skunopotamus', 2)
('Medium', 'Mothalope', 3)
('Small', 'Platypus', 4)
 
*** Planets ***
(101, 'Virgon 4', 1.21)
(102, 'Dilbertopia', 0.88)
(103, 'Xanth', 0.01)
(104, 'Australia', 1.0)
 
*** AnimalPlanets ***
(1, 101)
(2, 101)
(2, 102)
(3, 103)
(4, 104)
 
*** Results ***
('Medium', 'Hermaflamingo', 'Virgon 4', 1.21)
('Medium', 'Mothalope', 'Xanth', 0.01)
('Small', 'Platypus', 'Australia', 1.0)
('Large', 'Skunopotamus', 'Virgon 4', 1.21)
('Large', 'Skunopotamus', 'Dilbertopia', 0.88)