Data Management With Python, SQLite, and SQLAlchemy

Advantages of Flat Files

Working with data in flat files is manageable and straightforward to implement. Having the data in a human-readable format is helpful not only for creating the data file with a text editor but also for examining the data and looking for any inconsistencies or problems.

Many applications can export flat-file versions of the data generated by the file. For example, Excel can import or export a CSV file to and from a spreadsheet. Flat files also have the advantage of being self-contained and transferable if you want to share the data.

Almost every programming language has tools and libraries that make working with CSV files easier. Python has the built-in csv module and the powerful pandas module available, making working with CSV files a potent solution.

Disadvantages of Flat Files

The advantages of working with flat files start to diminish as the data becomes larger. Large files are still human-readable, but editing them to create data or look for problems becomes a more difficult task. If your application will change the data in the file, then one solution would be to read the entire file into memory, make the changes, and write the data out to another file.

Another problem with using flat files is that you’ll need to explicitly create and maintain any relationships between parts of your data and the application program within the file syntax. Additionally, you’ll need to generate code in your application to use those relationships.

A final complication is that people you want to share your data file with will also need to know about and act on the structures and relationships you’ve created in the data. To access the information, those users will need to understand not only the structure of the data but also the programming tools necessary for accessing it.

Flat File Example

The example program examples/example_1/main.py uses the author_book_publisher.csv file to get the data and relationships in it. This CSV file maintains a list of authors, the books they’ve published, and the publishers for each of the books.

The CSV file presented above is a pretty small data file containing only a few authors, books, and publishers. You should also notice some things about the data:

• The authors Stephen King and Tom Clancy appear more than once because multiple books they’ve published are represented in the data.

• The authors Stephen King and Pearl Buck have the same book published by more than one publisher.

These duplicated data fields create relationships between other parts of the data. One author can write many books, and one publisher can work with multiple authors. Authors and publishers share relationships with individual books.

The relationships in the author_book_publisher.csv file are represented by fields that appear multiple times in different rows of the data file. Because of this data redundancy, the data represents more than a single two-dimensional table. You’ll see more of this when you use the file to create an SQLite database file.

The example program examples/example_1/main.py uses the relationships embedded in the author_book_publisher.csv file to generate some data. It first presents a list of the authors and the number of books each has written. It then shows a list of publishers and the number of authors for which each has published books.

It also uses the treelib module to display a tree hierarchy of the authors, books , and publishers.

Lastly, it adds a new book to the data and redisplays the tree hierarchy with the new book in place. Here’s the main() entry-point function for this program:

 1def main():
 2    """The main entry point of the program"""
 3    # Get the resources for the program
 4    with resources.path(
 5        "project.data", "author_book_publisher.csv"
 6    ) as filepath:
 7        data = get_data(filepath)
 8
 9    # Get the number of books printed by each publisher
10    books_by_publisher = get_books_by_publisher(data, ascending=False)
11    for publisher, total_books in books_by_publisher.items():
12        print(f"Publisher: {publisher}, total books: {total_books}")
13    print()
14
15    # Get the number of authors each publisher publishes
16    authors_by_publisher = get_authors_by_publisher(data, ascending=False)
17    for publisher, total_authors in authors_by_publisher.items():
18        print(f"Publisher: {publisher}, total authors: {total_authors}")
19    print()
20
21    # Output hierarchical authors data
22    output_author_hierarchy(data)
23
24    # Add a new book to the data structure
25    data = add_new_book(
26        data,
27        author_name="Stephen King",
28        book_title="The Stand",
29        publisher_name="Random House",
30    )
31
32    # Output the updated hierarchical authors data
33    output_author_hierarchy(data)

The Python code above takes the following steps:

• Lines 4 to 7 read the author_book_publisher.csv file into a pandas DataFrame.
• Lines 10 to 13 print the number of books published by each publisher.
• Lines 16 to 19 print the number of authors associated with each publisher.
• Line 22 outputs the book data as a hierarchy sorted by authors.
• Lines 25 to 30 add a new book to the in-memory structure.
• Line 33 outputs the book data as a hierarchy sorted by authors, including the newly added book.

Running this program generates the following output:

$ python main.py
Publisher: Simon & Schuster, total books: 4
Publisher: Random House, total books: 4
Publisher: Penguin Random House, total books: 2
Publisher: Berkley, total books: 2

Publisher: Simon & Schuster, total authors: 4
Publisher: Random House, total authors: 3
Publisher: Berkley, total authors: 2
Publisher: Penguin Random House, total authors: 1

Authors
├── Alex Michaelides
│   └── The Silent Patient
│       └── Simon & Schuster
├── Carol Shaben
│   └── Into The Abyss
│       └── Simon & Schuster
├── Isaac Asimov
│   └── Foundation
│       └── Random House
├── John Le Carre
│   └── Tinker, Tailor, Soldier, Spy: A George Smiley Novel
│       └── Berkley
├── Pearl Buck
│   └── The Good Earth
│       ├── Random House
│       └── Simon & Schuster
├── Stephen King
│   ├── Dead Zone
│   │   └── Random House
│   ├── It
│   │   ├── Penguin Random House
│   │   └── Random House
│   └── The Shining
│       └── Penguin Random House
└── Tom Clancy
    ├── Patriot Games
    │   └── Simon & Schuster
    └── The Hunt For Red October
        └── Berkley

The author hierarchy above is presented twice in the output, with the addition of Stephen King’s The Stand, published by Random House. The actual output above has been edited and shows only the first hierarchy output to save space.

main() calls other functions to perform the bulk of the work. The first function it calls is get_data():

def get_data(filepath):
    """Get book data from the csv file"""
    return pd.read_csv(filepath)

This function takes in the file path to the CSV file and uses pandas to read it into a pandas DataFrame, which it then passes back to the caller. The return value of this function becomes the data structure passed to the other functions that make up the program.

get_books_by_publisher() calculates the number of books published by each publisher. The resulting pandas Series uses the pandas GroupBy functionality to group by publisher and then sort based on the ascending flag:

def get_books_by_publisher(data, ascending=True):
    """Return the number of books by each publisher as a pandas series"""
    return data.groupby("publisher").size().sort_values(ascending=ascending)

get_authors_by_publisher() does essentially the same thing as the previous function, but for authors:

def get_authors_by_publisher(data, ascending=True):
    """Returns the number of authors by each publisher as a pandas series"""
    return (
        data.assign(name=data.first_name.str.cat(data.last_name, sep=" "))
        .groupby("publisher")
        .nunique()
        .loc[:, "name"]
        .sort_values(ascending=ascending)
    )

add_new_book() creates a new book in the pandas DataFrame. The code checks to see if the author, book, or publisher already exists. If not, then it creates a new book and appends it to the pandas DataFrame:

def add_new_book(data, author_name, book_title, publisher_name):
    """Adds a new book to the system"""
    # Does the book exist?
    first_name, _, last_name =