This chapter's example uses Python to build a NoSQL document database in the cloud. It uses the MongoDB Atlas database to save and query the following data about assignments for East Los Angeles Space Academy graduates, as shown in Table 22.1.

The next few sections talk a bit about this data, the way document databases store information, and the MongoDB Atlas database that we'll use for this example. The rest of the chapter describes the example program.

NOT NORMAL BUT NOT ABNORMAL

If you remember the relational database normalization rules, then you know that the data in the preceding table is not normalized because some non-key fields do not depend on all the key fields. Here, the key fields are FirstName, LastName, and Position, because we need all three to identify a record. However, the fields Rank and Ship depend on FirstName and LastName but not on Position.

You can also see the problem by looking for anomalies. For example, if you change one of Sally Barker's Ship values, then her ship assignments will be inconsistent (an update anomaly). In fact, Al Farnsworth's Rank assignments already don't match.

For another example, if you delete the last record to remove the information about Bud Pickover, then you also lose the fact that the ship Athena Ascendant exists (a deletion anomaly).

This data won't fit in a relational database without some revisions, but it's no problem for a document database. That kind of database stores each document separately, so it doesn't matter if the documents don't hold exactly the same types of data. That does mean the data may be subject to some anomalies, but that's up to the way you (and your program) manage the data.

XML, JSON, AND BSON

Document databases do not have table definitions, so they need some other way to keep track of what “fields” are in a document. They do that by using formats that include both the field names and their values.

For example, the following shows how we might store the first record in an Extensible Markup Language (XML) format document:

<posting>
    <FirstName>Joshua</FirstName>
    <LastName>Ash</LastName>
    <Position>Fuse Tender</Position>
    <Rank>6th Class</Rank>
    <Ship>Frieda's Glory</Ship>
</posting>

Each of the values inside the open and close tokens is an element of whatever object contains it. For example, Joshua is the value of this posting's FirstName element.

XML also supports attributes that you can use to save some space. Here's the same data in attribute form:

<posting
    FirstName = "Joshua"
    LastName = "Ash"
    Position = "Fuse Tender"
    Rank = "6th Class"
    Ship = "Frieda's Glory"/>

This works, but the first version allows you to create repeated elements and this version doesn't. For example, Sally Barker has two positions. You can include those as separate <Position> elements in the first version, but you cannot give the posting element two Position properties in the second version.

JavaScript Object Notation (JSON) is another format for storing documents that's currently more popular with document databases.

The following text shows a JSON version of Sally Barker's data:

{
    "FirstName": "Sally",
    "LastName": "Barker",
    "Position": "Pilot",
    "Position": "Arms Master",
    "Ship": "Scrat"
}

This version is slightly more compact than the first XML version because it doesn't require closing elements such as </FirstName>. Notice that it contains multiple Position elements. (Just to be clear, the first XML version can also contain repeated elements.)

Notice also that Sally's data doesn't include a Rank because it wasn't included in the original data. That's a feature of document databases: each document sets its own rules.

Some databases let you define a database schema that defines rules about what a document must contain. For example, you could require that posting documents include a Rank value, but you aren't required to do that.

Behind the scenes MongoDB stores data in a binary version of JSON called Binary JavaScript Object Notation (BSON) to save space, to better encode some data types such as dates and binary values, and because it's a bit faster for some operations.

MongoDB stores documents in BSON format, but your database adapter will probably convert it to JSON when you display it. However, it may not observe niceties such as adding line breaks, indentation, and other formatting. For example, the following code shows how the Python statement print(record) displays Joshua Ash's data:

{'_id': ObjectId('62e2d70c359cf3cfda8deb21'), 'FirstName': 'Joshua', 
 'LastName': 'Ash', 'Position': 'Fuse Tender', 'Rank': '6th Class', 
 'Ship': "Frieda's Glory"}
 

This is pretty hard to read, so you may want to write some code to pull out the record's fields and display them in a more reasonable format.

INSTALL MongoDB ATLAS

MongoDB Atlas is a cloud database. To get started, go to www.mongodb.com/cloud/atlas/register and create a free user account. When you create the account, you will receive an email containing a button that you must click to verify your email address (possibly so bots can't create hordes of free Atlas accounts, or possibly just so MongoDB can send you advertising later).

After you successfully log in, select the Database category on the left and click the big Build a Database button shown in Figure 22.1.

The next page gives you three options for the kind of service that you want:

• Serverless—With this option, you pay per transaction, currently starting at $0.10 per million reads. This is a good choice if your workload varies throughout the day.
• Dedicated—This option gives you more advanced configuration options and a set cost, currently $0.08 per hour regardless of the number of transactions. It's designed for large production systems.
• Shared—This is the free “try it out” version. It has a limit on the number of connections that you can use, uses shared memory, has limited storage, and is generally perfect for this example.

After you click the Shared option's Create button, you'll see a page where you can pick a cloud provider to host the database and a region. I'm using Amazon Web Services (AWS) as the provider for this example, so click the big AWS button.

Below the provider selection buttons is a list of regions that you can select to locate your server. It may be tempting to set your host location to an exotic place like Bahrain or Tokyo, but the server won't send you postcards or anything, so just pick the one that's physically closest to you. In a real application, you may be required to pick a particular location to satisfy data sovereignty rules (mentioned way back in Chapter 1, “Database Design Goals”), such as storing personal customer data within your country's borders.

If you scroll to the bottom, you can expand the Cluster Name area and enter a name for your cluster. If you don't, you'll get the accurate but uninspired name Cluster0 and you can't change the name later. I'm going to use the name PostingsDB.

After you pick your provider and optionally set the cluster name, click Create Cluster.

Next, you decide how to connect with the database. For this example, select the Username and Password options. Enter a username and password in the indicated boxes. You can use the Autogenerate Secure Password button to make the system pick a good password for you. If you do that, click Copy to copy the password to the clipboard and paste it somewhere safe. After you've set the username and password, click Create User.

Scroll down to the next section, where you indicate how you would like to access the database. The choices are My Local Environment (run the database on your computer) and Cloud Environment (run the database in the cloud). Pick Cloud Environment.

When you're finished defining the database, click Finish and Close. In the congratulatory dialog box, click Go to Databases.

FIND THE CONNECTION CODE

Connecting to most databases isn't hard, but it can be annoyingly difficult to find out exactly what syntax and connection data to use. For example, how do you specify the database's location, a username, and the password? I'm glad you asked!

MongoDB's database deployments page gives you a handy tool for figuring out how to do that. If you look at Figure 22.2, you'll see a row of buttons for the cluster named PostingsDB. Click Connect to display the dialog box shown in Figure 22.3. Now, click the second choice, Connect Your Application, to see the page shown in Figure 22.4.

For this example, select Python from the left drop-down list. (For Chapter 23, “MongoDB Atlas in C#,” select C#/.NET.) Then from the right drop-down list, select the appropriate PyMongo driver version. If you haven't installed PyMongo yet (it would be surprising if you have), then you'll probably want the most recent version.

If you're not sure which version to use, install the database adapter (I'll show you instructions shortly), and then run the following code in a Jupyter Notebook cell:

# Get the pymongo version.
import pymongo
pymongo.version

When I ran this code, Jupyter Notebook told me that I had PyMongo version 4.2.0 installed, so I selected the newest version from the drop-down list, which was version 3.12 or later (as you can see in Figure 22.4).

When you pick the driver version, the page shows a connect string that you can use to connect your program to the database. If you select Include Full Driver Code Example, as shown in Figure 22.4, then you'll see a complete Python code snippet that you can copy and paste into your program. There's even a button to the right that copies the code to the clipboard for you. Copy that value and paste it somewhere safe so that you can use it later.

Notice that the snippet includes the password as <password>. You need to replace that (including the brackets) with the actual password. For this example we'll be lazy and include the username and password in the program, but it's always best to not include real credentials in your program's code. Ask the user for a username and password and insert them into the connection string at runtime.

CREATE THE PROGRAM

Now that the database is waiting for you in the cloud, you need to install a database driver for it. Then you can start writing code.

To create a Python program to work with the Atlas ship assignment database, create a new Jupyter Notebook, and then add the code described in the following sections.

$ pip install pymongo[srv]

!pip install pymongo[srv]

# Prepare to use PyMongo.
import pymongo
from pymongo import MongoClient
from pymongo.server_api import ServerApi

# Return a string for a person's record.
Def person_string(record):
    # These should always be present.
    Name = record["FirstName"] + " " + record["LastName"]
    ship = record["Ship"]
 
    # This might be a single value or a list.
    Position = record["Position"]
    if isinstance(position, list):
        position = ", ".join(record["Position"])
    
    # Rank may be missing.
    If "Rank" in record:
        rank = record["Rank"]
    else:
        rank = "---"
 
    return f"{name:16}{ship:19}{rank:15}{position}"

Sally Barker    Scrat              ---            Pilot, Arms Master

# Connect to the database.
def connect_to_db(user, password, url):
    global client, db
 
    connect_string = 
        f"mongodb+srv://{user}:{password}@{url}/?retryWrites=true&w=majority"
    client = MongoClient(connect_string, server_api=ServerApi('1'))
    db = client.personnel

global client, db

# Delete any old data.
def delete_old_data(db):
    db.postings.delete_many({})
 
    print("Deleted old data
")

# Create sample data.
def create_data(db):
    josh = {
        "FirstName" : "Joshua",
        "LastName" : "Ash",
        "Position" : "Fuse Tender",
        "Rank" : "6th Class",
        "Ship" : "Frieda's Glory"
    }
    sally = {
        "FirstName" : "Sally",
        "LastName" : "Barker",
        "Position" : [ "Pilot", "Arms Master"],
        "Ship" : "Scrat"
    }
    bil = {
        "FirstName" : "Bil",
        "LastName" : "Cumin",
        "Position" : "Cook's Mate",
        "Ship" : "Scrat",
        "Rank" : "Hall Monitor"
    }
    al = {
        "FirstName" : "Al",
        "LastName" : "Farnsworth",
        "Position" : [ "Diplomat", "Interpreter" ],
        "Ship" : "Frieda's Glory"
    }
    major = {
        "FirstName" : "Major",
        "LastName" : "Major",
        "Position" : "Cook's Mate",
        "Rank" : "Major",
        "Ship" : "Athena Ascendant"
    }
    bud = {
        "FirstName" : "Bud",
        "LastName" : "Pickover",
        "Position" : "Captain",
        "Rank" : "Captain",
        "Ship" : "Athena Ascendant"
    }
    
    # Insert josh individually.
    result = db.postings.insert_one(josh)
 
    # Insert the others as a group.
    others = [sally, bil, al, major, bud]
    result = db.postings.insert_many(others)
 
    print("Created data
")

# Insert josh individually.
result = db.postings.insert_one(josh)

# Insert the others as a group.
others = [sally, bil, al, major, bud]
result = db.postings.insert_many(others)

# Query the data.
def query_data(db):
    print(f"{'Name':16}{'Ship':19}{'Rank':15}{'Position'}")
    print("-------------   ----------------   ------------   -----------")
 
    # List everyone.
    print("   *** Everyone ***")
    cursor = db.postings.find({})
    for doc in cursor:
        print(person_string(doc))

    # People posted to Scrat.
    print("
   *** Assigned to Scrat ***")
    cursor = db.postings.find({"Ship": "Scrat"})
    for doc in cursor:
        print(person_string(doc))

    # People with Rank values.
    print("
   *** Has a Rank ***")
    cursor = db.postings.find({"Rank": {"$exists": True}})
    for doc in cursor:
        print(person_string(doc))

    # People with no Rank.
    print("
   *** Has no Rank ***")
    cursor = db.postings.find({"Rank": {"$exists": False}})
    for doc in cursor:
        print(person_string(doc))

    # Cook's Mates or people on Frieda's Glory.
    print("
   *** Cook's Mates or on Frieda's Glory ***")
    cursor = db.postings.find(
        {"$or": [
            {"Position": "Cook's Mate"},
            {"Ship": "Frieda's Glory"}
        ]})
    for doc in cursor:
        print(person_string(doc))

    # Cook's Mates or on Frieda's Glory, sorted by Ship.
    print("
   *** Cook's Mates or on Frieda's Glory, sorted ***")
    cursor = db.postings.find(
        {"$or": [
            {"Position": "Cook's Mate"},
            {"Ship": "Frieda's Glory"}
        ]}).sort("Ship", pymongo.ASCENDING)
    for doc in cursor:
        print(person_string(doc))

    # Cook's Mates or on Frieda's Glory, sorted by Ship then FirstName.
    print("
   *** Cook's Mates or on Frieda's Glory, sorted ***")
    cursor = db.postings.find(
        {"$or": [
            {"Position": "Cook's Mate"},
            {"Ship": "Frieda's Glory"}
        ]}).sort([
            ("Ship", pymongo.ASCENDING),
            ("FirstName", pymongo.ASCENDING)
        ])
    for doc in cursor:
        print(person_string(doc))

# Query the data.
def query_data(db):
    print(f"{'Name':16}{'Ship':19}{'Rank':15}{'Position'}")
    print("-------------   ----------------   ------------   -----------")
 
    # List everyone.
    print("   *** Everyone ***")
    cursor = db.postings.find({})
    for doc in cursor:
        print(person_string(doc))
 
    # People posted to Scrat.
    print("
   *** Assigned to Scrat ***")
    cursor = db.postings.find({"Ship": "Scrat"})
    for doc in cursor:
        print(person_string(doc))
 
    # People with Rank values.
    print("
   *** Has a Rank ***")
    cursor = db.postings.find({"Rank": {"$exists": True}})
    for doc in cursor:
        print(person_string(doc))
 
    # People with no Rank.
    print("
   *** Has no Rank ***")
    cursor = db.postings.find({"Rank": {"$exists": False}})
    for doc in cursor:
        print(person_string(doc))
 
    # Cook's Mates or people on Frieda's Glory.
    print("
   *** Cook's Mates or on Frieda's Glory ***")
    cursor = db.postings.find(
        {"$or": [
            {"Position": "Cook's Mate"},
            {"Ship": "Frieda's Glory"}
        ]})
    for doc in cursor:
        print(person_string(doc))
 
    # Cook's Mates or on Frieda's Glory, sorted by Ship.
    print("
   *** Cook's Mates or on Frieda's Glory, sorted ***")
    cursor = db.postings.find(
        {"$or": [
            {"Position": "Cook's Mate"},
            {"Ship": "Frieda's Glory"}
        ]}).sort("Ship", pymongo.ASCENDING)
    for doc in cursor:
        print(person_string(doc))
 
    # Cook's Mates or on Frieda's Glory, sorted by Ship then FirstName.
    print("
   *** Cook's Mates or on Frieda's Glory, sorted ***")
    cursor = db.postings.find(
        {"$or": [
            {"Position": "Cook's Mate"},
            {"Ship": "Frieda's Glory"}
        ]}).sort([
            ("Ship", pymongo.ASCENDING),
            ("FirstName", pymongo.ASCENDING)
        ])
    for doc in cursor:
        print(person_string(doc))

Deleted old data
 
Created data
 
Name            Ship               Rank           Position
-------------   ----------------   ------------   -----------
   *** Everyone ***
Joshua Ash      Frieda's Glory     6th Class      Fuse Tender
Sally Barker    Scrat              ---            Pilot, Arms Master
Bil Cilantro    Scrat              Hall Monitor   Cook's Mate
Al Farnsworth   Frieda's Glory     ---            Diplomat, Interpreter
Major Major     Athena Ascendant   Major          Cook's Mate
Bud Pickover    Athena Ascendant   Captain        Captain
 
   *** Assigned to Scrat ***
Sally Barker    Scrat              ---            Pilot, Arms Master
Bil Cilantro    Scrat              Hall Monitor   Cook's Mate
 
   *** Has a Rank ***
Joshua Ash      Frieda's Glory     6th Class      Fuse Tender
Bil Cilantro    Scrat              Hall Monitor   Cook's Mate
Major Major     Athena Ascendant   Major          Cook's Mate
Bud Pickover    Athena Ascendant   Captain        Captain
 
   *** Has no Rank ***
Sally Barker    Scrat              ---            Pilot, Arms Master
Al Farnsworth   Frieda's Glory     ---            Diplomat, Interpreter
 
   *** Cook's Mates or on Frieda's Glory ***
Joshua Ash      Frieda's Glory     6th Class      Fuse Tender
Bil Cilantro    Scrat              Hall Monitor   Cook's Mate
Al Farnsworth   Frieda's Glory     ---            Diplomat, Interpreter
Major Major     Athena Ascendant   Major          Cook's Mate
 
   *** Cook's Mates or on Frieda's Glory, sorted ***
Major Major     Athena Ascendant   Major          Cook's Mate
Joshua Ash      Frieda's Glory     6th Class      Fuse Tender
Al Farnsworth   Frieda's Glory     ---            Diplomat, Interpreter
Bil Cilantro    Scrat              Hall Monitor   Cook's Mate
 
   *** Cook's Mates or on Frieda's Glory, sorted ***
Major Major     Athena Ascendant   Major          Cook's Mate
Al Farnsworth   Frieda's Glory     ---            Diplomat, Interpreter
Joshua Ash      Frieda's Glory     6th Class      Fuse Tender
Bil Cilantro    Scrat              Hall Monitor   Cook's Mate

# Main program.
 
# Connect to the database.
user = "Rod"
password = "KENDmaHmdhK23Kn"
url = "postingsdb.b1bprz5.mongodb.net"
connect_to_db(user, password, url)
 
# Delete old data.
delete_old_data(db)
 
# Create new data.
create_data(db)
 
# Query the data.
query_data(db)
 
# Close the database connection.
client.close()

SUMMARY

This chapter shows how you can use Python and a NoSQL document database to store and retrieve JBON documents.

As you work with this example, you might notice that operations are relatively slow, particularly if you have a slow network connection. This is generally true of cloud applications. Network communications tend to be slower than local calculations.

The PyMongo database driver uses dictionary objects to define data. You build a dictionary containing field names as their values. Then you can use the database collection's methods such as insert_one and insert_many to add documents to the database. Later, you can use the find method to find those documents.

The next chapter shows how to build a similar example program in C#. That example uses a very different method for finding documents.

Before you move on to that example, however, use the following exercises to test your understanding of the material covered in this chapter. You can find the solutions to these exercises in Appendix A.