Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed SQLAlchemy Basics!
You have completed SQLAlchemy Basics!
Preview
Import the CSV of books into your application.
Code Snippet
months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']
Challenges
- What if the CSV file’s date and/or price are not in the correct format?
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
We've got the beginning of our app setup.
0:00
But there's one more
thing we need to tackle,
0:02
adding our CSV of books to our database.
0:07
We're gonna create a new
function called add CSV,
0:10
and add it before our app function here
0:15
Okay, let me make sure I have two spaces,
perfect.
0:22
def add_csv
0:25
Inside of this function, we're gonna
grab all of the books in our CSV.
0:31
We're gonna clean the data, and
then add that information to our database.
0:35
At the top of the file,
we need to import CSV and date time,
0:40
because we're gonna need
CSV to work with the file.
0:44
And we're gonna need datetime to
work with turning our published date
0:48
into an actual date object.
0:53
Import datetime, import C,
0:55
oops, csv, awesome.
0:59
Let's jump back down, and
1:03
we can also delete that because we did it.
1:07
All right, back in our function, we need
to open the CSV file to see what we got.
1:12
So let's do with open, and
1:17
we're gonna pass in suggested_books.csv.
1:21
And then outside of it,
1:28
we're gonna call it as csvfile, and
1:31
then inside of here, we're gonna get our
1:36
data with csv.reader
passing in our csv file.
1:41
And okay, cool.
1:47
Now to see what our data looks like, let's
loop through each row in our data and
1:50
print it out.
1:54
So for row in data, print row,
1:55
and let's save that out.
2:00
And pop down to the bottom, and
we're gonna just comment out this app
2:04
call right now that's command,
and the forward slash key.
2:09
And then let's do add_csv, and hit Save.
2:14
All right, now let's run the file and
see what we've got.
2:19
Okay, so we've got all of our books and
each book is its own list.
2:25
And each data point inside
of the list is a string,
2:30
and this works great for
our title and author,
2:36
but not so great for our date and price.
2:41
They will need to be cleaned, and
what I mean by that is we need to change
2:45
the data type of these two from strings
to a datetime object and to an integer.
2:50
Because we told our database
that is the type of information
2:56
we would be inputting
into those two columns.
3:01
Since each of these changes are a task
on their own, I'm gonna split them
3:06
into two functions, one to clean the date,
and one to clean the price.
3:11
So let's create a function
called clean date.
3:16
And we'll do that above our add_csv.
3:19
And we did this too,
actually I'm gonna remove that.
3:25
Where do you clean_date, and this is
going to take a string of information.
3:31
We're gonna pass in each of
the date strings from our CSV, so
3:37
it's gonna receive a date string.
3:42
So let's call it date_stir for string,
3:45
just to make it super clear
what we're giving it.
3:48
Inside, we'll need to match the months
of the year to a number for that month.
3:52
This is because if I do datetime.date,
I do this,
3:57
you can see,
it gives me this little helpful hint.
4:01
So we're gonna need the year,
the month, and the day as integers.
4:05
And so right now we know
the whole thing is a string and
4:10
October is definitely not a number.
4:13
So we're gonna have to convert
those months into numbers.
4:16
So in the teacher's notes below is
a list of months that you can copy and
4:21
paste like I'm going to just make
things a little bit faster, and
4:26
they're all months of the year.
4:31
Now the next thing I'm going to
do is I'm going to use split,
4:36
split is going to allow me to break
this string up into three pieces.
4:41
So I'm gonna do split_date, equals,
4:46
and we're going to do date_str.split.
4:52
And we're gonna do it on the space, so
4:58
I'm gonna put a space inside
of those quotation marks.
5:00
So we'll cut it here, and
it'll cut it here, so
5:03
it'll get October 25, and
then 2017 as three separate pieces.
5:07
So let's print.
5:12
Split_date, and then I'm gonna
jump down to the bottom here,
5:17
so we can test this out.
5:23
And I'm gonna comment this out for
now, I'm gonna add clean_date.
5:25
And let's just pass in
this one right here, or
5:29
any of the ones from our data, so
that we can use that as an example.
5:33
Okay, scroll back and
now let me run the file.
5:39
Okay, so you can see I got October 25, and
5:44
2017, so I got three pieces.
5:49
Now I need to take that October and
5:53
I need to turn it into a number
that represents October.
5:55
One thing we know about lists
is that they are zero indexed.
5:59
So for instance, let's say we had a date
that had January instead of October,
6:03
if we grab the index for that month in
our list, it would return with zero.
6:07
But zero isn't really a month number.
6:12
January is usually represented
by the number 1, so
6:16
what we're going to need to do is
grab the index for the month and
6:19
then add one to it, so
we have the correct number for that month.
6:23
So I'm actually going to leave that
print statement there for now.
6:28
So we're gonna create
a variable called month, and
6:31
it's going to be equal to our split_date.
6:36
Zero because that is this part right here.
6:39
And we need to use that
in our months index.
6:43
So we need to do months.index
of that split date.
6:47
So we're gonna pass in October,
and we're gonna say, hey,
6:54
can you check inside of this list here,
and tell me what its index is.
6:58
So like I said before,
if this was January, it would give us 0.
7:02
And because it's October,
it's gonna give us 0, 1, 2, 3, 4,
7:05
5, 6, 7, 8, 9, which we know
when we type dates of the year.
7:11
9 usually represents September,
so we need to add plus 1.
7:16
And then if we remember, I'll type it out
again just so we can see, datetime.date.
7:21
These needs to be integers,
so I need to convert all of
7:28
this into an integer,
which I can do by wrapping it,
7:33
in int, which will convert
that number to an integer.
7:38
So for a quick recap, we're taking
the first part of our split date,
7:41
which is the name of the month.
7:46
And we're finding it inside of this list,
which gives us the index, but
7:48
that will give us a number one less than
we need, because lists are 0 indexed.
7:54
So we're gonna add 1 to it, and
8:01
then turn that into a regular number,
and month is done.
8:03
Now we need to do day.
8:09
An issue we have is we have a 25 comma,
and we definitely don't want that comma,
8:12
because when we tried to
turn it into an integer,
8:18
it's gonna be a little confused
by that comma being there.
8:21
So we're going to split our split, so
8:26
we know this is 0 and this is 1, so
8:31
this will be split (1).
8:35
Sorry, split_date (1), and
8:39
we're gonna split it again, split,
8:43
and we wanna split where that comma is.
8:47
And let's print that out and
see what we got.
8:52
Okay, so we're gonna get 25 and
then we're gonna get just an empty string,
9:03
which we definitely don't need.
9:08
So we can just say we want
the first part which is 0.
9:10
So we're just going to grab this part.
9:18
So we took our date here with 25, and
9:20
we split it on the comma,
which gave us 25 and nothing.
9:24
And we're saying we just
want this 25 right there.
9:30
So I'm gonna save,
9:33
I'll run this again just to show that
we've grabbed just this first part.
9:34
And awesome, we've grabbed 25,
now we need to turn
9:40
this into an integer, so
wrap it in int, and tada!
9:45
Now, the last part we need is the year,
so let's do here.
9:50
And if we look, the year is kind
of just ready to go for us,
9:54
all we need to do is turn it into
an integer instead of a string.
9:58
And it's our split_date, number 2,
remember 0, 1, 2 so split date number two.
10:02
Now we need to return
a datetime.date object and
10:09
you can see it wants year, months,
10:15
days, we can do year month day.
10:20
And Tedda, we did it.
10:24
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up