Introduction

I’ve been using Goodreads for the past few years, primarily to track books that I’m reading, mark books that I want to read in the future, and try and hit a reading goal of completed books each year. I really love the base functionality of Goodreads for this purpose, but I’ve also always felt like Goodreads was decently clunky and could absolutely use a design refresh. But based on the fact that Amazon purchased Goodreads in 2013 and not much has changed since then… I’m not holding my breath.

So, as I’ve been learning Python and SQLAlchemy over the past few weeks, I decided to try and build out my own Goodreads book tracker in a CLI with full CRUD.

Getting Started: File Configuration & Setting Up Database

As a first step, I began by setting up my database and configuring my files. My goal was to create a Bookshelf which would contain instances of the three book types I wanted: “Currently Reading”, “Want to Read”, and “Completed Books”. For this project, I used SQLAlchemy, which is a SQL toolkit and ORM (object-relational mapping) system for Python, in order to handle and configure the database.

To start, I created 3 Python files:

models.py

This is where I created my main two classes, Bookshelf and Book.

The Bookshelf class represents the entire bookshelf itself, which contains the attributes/Columns “name” and “description”.

The Book class, then, is capable of generating a single book instance when called, containing attributes/Columns like “title”, and “author”, which are required fields for all books, along with other fields such as “description”, “page_count”, “pages_read”, “type”, “star_rating”, and “personal_review”.

I also create a relationship between the Bookshelf & Book class, by creating a book_connection & bookshelf_connection, respectively. That way, bookshelf_id in Book is tied to the “type” of book I’ve configured on the Bookshelf.

Here’s a sample of what the final classes looked like:

class Bookshelf(Base):
    __tablename__ = "bookshelf" 
    id = Column(Integer, primary_key = True)
    name = Column(String)
    description = Column(String)
    book_connection = relationship('Book', back_populates = "bookshelf_connection")

class Book(Base):
    __tablename__ = "book"
    id = Column(Integer, primary_key = True)
    title = Column(String, nullable = False)
    author = Column(String, nullable = False)
    description = Column(String)
    page_count = Column(Integer)
    pages_read = Column(Integer)
    type = Column(String)
    star_rating = Column(Integer)
    personal_review = Column(String)
    bookshelf_id = Column(ForeignKey('bookshelf.id'))
    bookshelf_connection = relationship('Bookshelf', back_populates="book_connection")

Within each class, I also added validation for all fields, but more on that later!

seeds.py

Seeding initial data into the database

Now that the models.py file was configured, I created a seed file to populate the databases with mock data for testing. This served to not only get the database up and running upon starting the CLI app, but also gave me a solid foundation for testing my program. I’d be able to manipulate this data in the app as I continued building and testing, and then simply re-run my seeds file to get everything back to base. In order to do this, I had to make sure that my Bookshelf and Book tables got dropped each time the file ran, before my mock data got re-created. Here’s what that looks like:

if __name__ == '__main__':
    Bookshelf.__table__.drop(engine)
    Book.__table__.drop(engine)
    Base.metadata.create_all(engine)

I then created three Bookshelf instances: Currently Reading, Want to Read, and Completed Books.

Next, I created mock book data for each of these instances, which would allow me perform full CRUD on these while testing.

Here’s a small sample of what this seed data looked like:

with Session(engine) as session:

#Create the three bookshelf instances
    cr_shelf = Bookshelf(
        name = "Currently Reading",
        description = "A list of books that I'm currently reading"
    )

    wtr_shelf = Bookshelf(
        name = "Want to Read",
        description = "A list of books that I eventually want to read."
    )

    completed_shelf = Bookshelf(
        name = "Completed Books",
        description = "Books I've finished."
    )

#Create a book instance that is of type "Currrently Reading" 
    b1 = Book(
        title = "Hyperion",
        author = "Dan Simmons",
        description = "A few voyagers travel to Hyperion to solve a great cosmic mystery",
        page_count = 483,
        pages_read = 340,
        type = "Currently Reading",
        star_rating = 5,
        personal_review = "Really enjoying it!",
        bookshelf_id = 1

app.py

Here’s where all the CLI magic happens. I began with importing inquirer, which is an incredible tool for easily getting user-generated input through the creation of questions, messages, choices, and responses. This is also where we’d get our database up and running each time the file is run.

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from models.models import *
import inquirer

engine = create_engine('sqlite:///bookshelf.db')
Base.metadata.create_all(engine)

Validation

Jumping back into models.py for a moment, I knew I needed to ensure that I had validation for all database inputs. I want to make sure that all the data that gets input into the respective Bookshelf & Book instances is sanitized and validated – after all, this app is meant to be run by users who might not know the exact requirements for an input they’re being required to provide. By using SQLAlchemy’s @validates decorator function, I made sure to check that a user was inputting data as required by the class/column, and if not, provide them with information on what went wrong via raising a ValueError.

One example is shown below - this is for the “star_rating” column in the book table, where a user can rate the book on a scale from 0 - 5. Star ratings should be whole numbers, not fractions, just to keep things simple. However, image that a user is adding a book to their “Want to Read” list — they’re not going to rate a book that they have’t read.

@validates("star_rating")
    def validate_star_rating(self, key, value):
        if type(value) is int and 0<=value<=5 or value == None:
            return value
        else:
            raise ValueError(f"Please enter a {value} that is between 0 and 5 with no decimal places.")

App.py with Inquirer

The final and most interactive part of the project was creating the app.py file with inquirer. By using the inquirer library to create interactive prompts for the user, I provided users with the opportunity to proactively engage with all of their bookshelf types - either by viewing every book across all types, or by adding a new book, editing a current book, removing a book, or moving a book from one list to another (e.g. moving a “Currently Reading” book to their “Completed Books” list. Each selection screen typically involves an initial question that asks the user what action hey want to take, followed by a series of choices they can select from in the CLI. I made sure to include what I called “escape routes” that ensure the app never closes/ends without direct action from the user. So, for example, if a user gets to the selection screen where they’ve added a new book to their “Want to Read” list, I don’t want to close the application on them - instead, I ask them if they want to return back to the main “Want to Read” menu, or to the “Main Menu” where they can exit the program from there.

One of my favorite sections in the app.py file is where a user updates the page count for a book on their Currently Reading list. In order to help automate things a bit better and provide a better user experience, the code below first checks to see if the updated pages read is equal to or larger than the total number of pages in the book itself. If it is, the user is prompted to either mark the book as complete, or acknowledge that they potential mis-typed their entry. If they do decide to mark the book is complete, the book is automatically removed from their Currently Reading list and subsequently moved to their Completed Books list. Since the Completed Books list also allows for a star rating and a personal written review, the user is prompted to enter those as well. Finally, if the current pages read is less than the total number of pages in the book, the code updates the Currently Reading book instance, and calculates the percentage toward completion of that book.

def update_cr():
            print(f"Here's a list of all the books you're currently reading: {session.query(Book).filter(Book.bookshelf_id == 1).all()}")
            title_to_update = input("Please type the name of the book that you want to update the current page for: ")
            book_edit = session.query(Book).filter(Book.title == title_to_update).first()
            book_edit.pages_read = input("What page of the book are you on now?: ")

            #check to see if the user has read all the pages in the book. If so, confirm and move to completed books.
            if book_edit.pages_read == book_edit.page_count or book_edit.pages_read > book_edit.page_count:
                questions = [
                    inquirer.List(
                        "move_to_complete",
                        message = f"Nice! Based on the page number you entered, it looks like you've finished the book, {title_to_update}! Do you want to move it to your Completed Books list?",
                        choices = [
                            f"Yes! I've finished {title_to_update} and I want to mark it as COMPLETE.",
                            "No, I've mistyped. Take me back to the main Currently Reading selection."
                        ]
                    )
                ]
                response = inquirer.prompt(questions)
                if response["move_to_complete"] == f"Yes! I've finished {title_to_update} and I want to mark it as COMPLETE.":
                    new_star_rating = input("Sweet! Now that you've finished, please rate the book betwee 0 - 5 stars: ")
                    new_personal_review = input("If desired, please also leave a written personal review of the book: ")
                    book_edit.type = "Completed"
                    book_edit.bookshelf_id = 3
                    book_edit.pages_read = book_edit.page_count
                    book_edit.star_rating = int(new_star_rating)
                    book_edit.personal_review = new_personal_review
                    session.add(book_edit)
                    session.commit()
                    print(f"It's lit! Congrats on finishing the book, {book_edit.title}! It's been moved from Currently Reading to Completed List.\n")
                    cr_return_prompt()
                else:
                    cr_main()
            else:
                session.add(book_edit)
                session.commit()
                percent_complete = int(((book_edit.pages_read) / (book_edit.page_count))*100)
                print(f"I've updated your progress on that book. Here's all the details of that book: \n {book_edit}")
                print(f"Sweet! You're now {percent_complete}% done with {book_edit.title}.\n")

Conclusion

I really enjoyed building this app, and it was really fun to try and re-create the general experience that I’ve had in using Goodreads - maybe not with the UI that I’m used to, but it did allow me to have a better understanding of how databases function and how I can validate user inputs.

In the future, I’d really love to add more to this app, such as connecting it to an external API like OpenLibrary to more easily pull in book data without having to make the user enter it themselves, as well as adding additional fields like book genre & year read to allow for even more data analysis.

If you want to see the project in its entirety and even try it out for yourself, be sure to check it out on my Github at the link here: https://github.com/barrettk8090/book-tracker