Jonnie Grieve Digital Media: Blog

Blog: SQLAlchemy – Reading, Cleaning and Adding Data in a Command Line App (More Posts)

Recap:

This blog post continues on in my learning journal series about SQLAlchemy. I will be attempting to create a CLI Application based Megan Amendola’s excellent teaching on Treehouse but changed ever so slightly to expand the scope of the database to create records for all kinds of media, not just books. This is her wisdom I’m putting into text form to try and help me understand it better.  There are one or 2 adaptions to the original material.  I’m adding a Media Library table as an example rather than just a books example and I have added one or 2 of my own columns to the schema.

By the end of this post, I will have correctly formatted date string and float data so it can be used as compatible data to the schema defined in models.py. And when the file is run in the command line it will add data to the database.

When I started this series I previously wrote about how I’d managed to create a functioning loop that allows the user to select from a group of options or exit the app entirely.

Later on, I tackled data cleaning and using new data for the first time.  Which didn’t go as initially plannned.  There was constant runtime errors in the process; I felt a little overwhelmed and I felt a had to take a step back and come back to it again.  I took some time out and yesterday made a breakthrough thanks to plenty oftrial and error. But I didn’t really understand at that point how it worked as opposed to the “how” it worked so I was left feeling relieved but still lacking some understanding.

The following are my remarks based on what I looked at today. This code is in addition to the code I added in this post last month.

The first thing I wanted to look at was how to avoid exceptions such as ValueErrors and NameErrors; showing up when I try to run the file. Having looked again it seemed to occur using references to variables passed in an argument to an instance of the Media variable.

First of all, in models.py, variables are defined in the database schema as properties of the Media class. So each variable represents a column and data type of a database table.

class Media(Base):
    __tablename__ = 'media'

    id= Column(Integer, primary_key=True)
    media_title = Column('Media Title', String)
    ...

Then in app.py  (below) we import the data and assign the data to individual columns using variables of the same name.  You’ll notice as well the data cleaning methods called on the rows of data returned that convert the data types when needed.

# import
def import_csv():
with open('media_list.csv') as csvfile:
data = csv.reader(csvfile)

# display data in the console
for row in data:
print(row)

media_title = row[0]
media_type = row[1]
artist = row[2]
genre = row[3]
published_date = clean_date(row[4])
price = clean_price(row[5])

The next step is to store a new instance of the Media() class with the arguments that add new data. This is done by assigning the variable of the database schema property so that it is the same as the variable of the instance of the data being displayed. That is why in this example, one variable is being assigned to a variable of the same name.  e.g. media_title=media_title

    new_media = Media(
        media_title=media_title, 
        media_type=media_type, 
        artist=artit, genre=genre, 
        published_date=published_date, 
        price=price
        )
    session.add(new_media)
session.commit()

The other issue I wasn’t to look at was issues of formatting when it comes to CSV files.  Here’s one I used 5 rows of data with 6 columns. They are what the array indexes “row[0]” etc refer to and like all arrays they are zero-indexed.  So row[0] would refer to Media Example 1, Media Example 2 etc.

Media Example 1, dvd, Author 1, Action, "June 28, 2021", 29.99 
Media Example 2, book, Author 2,Action, "July 12, 2021", 29.99 
Media Example 3, cd, Author 3,Horror, "September 24, 2021",29.99 
Media Example 4, dvd, Author 4,"Sci Fi", "January 02, 2021", 29.99 
Media Example 5, dvd, Author 5,History, "March 12, 2021", 29.99

But here’s the problem. When Python imports the CSV files they are read and displayed to the console literally. So a space between a separating comma and the next dataset would appear in the command line.  This can cause problems with the display and can affect the database cleaning methods. Python read the spaces and seemed to interpret them as part of the split it needed to perform on the date string.  Leading to something like this appearing in the console. [“”, June, 28″]

Best to have no separating spaces between the data and to format the CSV files like this.

Media Example 1,dvd,Author 1,Action,"June 28, 2021",29.99
Media Example 2,book,Author 2,Action,"July 12, 2021",29.99
Media Example 3,cd,Author 3,Horror,"September 24, 2021",29.99
Media Example 4,dvd,Author 4,"Sci Fi","January 02, 2021",29.99
Media Example 5,dvd,Author 5,History,"March 12, 2021",29.99

So those were the major issues that prevented the database from being modified. This is just one way to add multiple rows of data from a data source so it can be processed by SQLAlchemy.

At this point, I now have a file that contains functions to navigate an applications’ command-line interface; imports data in CSV format as a data source; converts data to a compatible data source with a given schema, and adds new data to a binary database file. The next item on the list is to run the rest of the usual CRUD operations in Python using SQLAlchemy.