Parsing XML Documents with Python

create_catalog.py contains a skeleton of what we need for this objective. Run it to see that it returns an error:

python create_catalog.py

The example code showing how to make this work is shown below:

import sqlite3
import xml.etree.ElementTree as ET
from xml.etree.ElementTree import Element, ElementTree

DB_NAME = "author_contracts.db"

def get_db_data():
    """
    write a code to execute the sql script and return the results
    """

    sql_query = """SELECT author, title, genre FROM authors"""
    
    con = sqlite3.connect(DB_NAME)
    cur = con.cursor()

    cur.execute(sql_query)
    results = cur.fetchall()

    cur.close()
    con.close()

    return results

def create_book_entry(author, title, genre):
    """
    create the book entry as defined and return book
    """

    book = Element('book')

    author_tag = ET.SubElement(book, 'author')
    author_tag.text = author

    title_tag = ET.SubElement(book, 'title')
    title_tag.text = title

    genre_tag = ET.SubElement(book, 'genre')
    genre_tag.text = genre

    ET.SubElement(book, 'isbn')

    return book


# using the information from get_db_data()
# write code to create a root and then
# add each book to it, finally write
# data to "catalog.xml"

root = Element("catalog")

book_info = get_db_data()

for author, title, genre in book_info:
    book = create_book_entry(author, title, genre)
    root.append(book)

tree = ElementTree(element=root)
tree.write("catalog.xml", encoding="UTF-8", xml_declaration=True)

# test code
expected_catalog = b"<?xml version='1.0' encoding='UTF-8'?>\n<catalog><book><author>Thompson, Keith</author><title>Oh Python! My Python!</title><genre>biography</genre><isbn /></book><book><author>Fritts, Larry</author><title>Fun with Django</title><genre>satire</genre><isbn /></book><book><author>Applegate, John</author><title>When Bees Attack! The Horror!</title><genre>horror</genre><isbn /></book><book><author>Brown, James</author><title>Martin Buber's Philosophies</title><genre>guide</genre><isbn /></book><book><author>Smith, Jackson</author><title>The Sun Also Orbits</title><genre>mystery</genre><isbn /></book></catalog>"

try:
    with open("catalog.xml", "rb") as f:
        catalog = f.read()
except FileNotFoundError:
    catalog = ""

assert catalog == expected_catalog

Run python create_catalog.py.

Congratulations! The data is in a format the ISBN company can use.

First install defusedxml:

pip3 install defusedxml

Then we can work on parse_catalog.py. It contains a skeleton of what we need for this next objective. Run python parse_catalog.py before editing, to see that it returns an error.

The example code showing how to accomplish this second objective is shown below:

import sqlite3
import sys
import defusedxml.ElementTree as ET

DB_NAME = "author_contracts.db"

def update_db(isbn_data_list):
    """ 
    add code to execute each sql_stmt in the order given
    results from sql_query_3 should be assigned to results
    """
    
    sql_query_1 = ''' ALTER TABLE authors ADD COLUMN isbn CHAR(20); '''

    sql_query_2 = "UPDATE authors SET isbn = ? WHERE title = ?;"

    sql_query_3 = ''' SELECT isbn FROM authors;'''

    con = sqlite3.connect("DB_NAME")
    cur = con.cursor()

    con.execute(sql_query_1)
    con.commit()

    con.executemany(sql_query_2, isbn_data_list)
    con.commit()

    cur.execute(sql_query_3)
    results = cur.fetchall()

    cur.close()
    con.close()  


    # test code
    expected_results = [('000-1-000000-00-1',), ('000-2-000000-00-2',), ('000-3-000000-00-3',), ('000-4-000000-00-4',), ('000-5-000000-00-5',)]

    assert results == expected_results

# using 'isbn.xml'
# loop through "book" in file and append isbn and title as a list object to isbn_data_list
# send isbn_data_list to function update_db

file_name = "isbn.xml"

try:
    tree = ET.parse(file_name)
except:
    print("File not found")
    sys.exit(1)

isbn_data_list = []
for book in tree.findall('book'):
    title = book.findtext('title')
    isbn = book.findtext('isbn')
    isbn_data_list.append([isbn, title])

update_db(isbn_data_list)

Now run python parse_catalog.py.

Awesome! You have mastered XML file parsing.