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
Use queries to create a small analysis of your book database.
Challenges
- Try creating one menu function
- Add docstrings to each function
- Add type hinting
- Try creating pagination for the books so you only see 5 books at a time when viewing them
- Try giving the user options for searching for a book (besides searching by id)
- Try adding other columns to your database like topic area, difficulty level, number of pages, etc.
- Try creating a practice app using SQLAlchemy!
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
Welcome back Pythonistas.
0:00
We've got one final section to complete,
the analysis.
0:02
I've chosen a few small items, but
I encourage you to think of your own and
0:06
add them here as well.
0:10
The ones I came up with are the most
recently published book in the database,
0:12
the oldest book in the database and
the total number of books in the database.
0:17
Let's start with the newest
book in the database.
0:23
Let's create a variable
called newest_book.
0:25
And it should be equal
0:30
to session.query(Book).order
0:33
_by(Book.published_date), and
0:39
we wanna grab the first entry there.
0:46
And let's print it out to see what we get,
newest_book,
0:52
okay, now let's run this in the console.
0:58
Oops, clear, there we go.
1:07
All right, now we can finally hit 4.
1:12
And let's see what we get.
1:15
We got Title: Think Like a Programmer and
it was published 2012-08-12.
1:18
And I know there are newer books.
1:24
So, the order right now is
ordering by ascending so
1:28
it's going from small to big and
we need descending.
1:32
So this one that we found here is
actually the oldest book in the database.
1:36
We can actually switch that to oldest and
1:43
then we can do newest_book and
set it equal to the same query but
1:47
we need to make a small change,
let's paste that.
1:53
In here we need to add our
desc() to say descending.
1:58
And so I'm gonna do newest_book and
2:04
then let's print the oldest_book
cuz that should still give us that
2:07
first book we just saw which
was Think Like a Programmer.
2:12
Save.
2:17
Let's exit here, so we can run again.
2:19
Number 4 and we gotta scroll up.
2:23
Oops, too much, there we go.
2:29
So we have one 2019.
2:32
So it looks like November 12th 2019.
2:33
We have Automate the Boring Stuff
is the newest book,
2:36
the most recent book in our database, and
the oldest book in our database is this,
2:39
Think Like a Programmer, from 2012, great.
2:43
Next, let's tackle getting the total
number of entries in our database.
2:48
So let's create a new variable, and we're
gonna print these out in a nicer format
2:53
soon, so
I'm gonna get rid of them for now.
2:57
So let's do total_books.
3:00
Set it = session.query(Book).
3:04
And we're going to do count.
3:12
Now remember count will give us a number
that represents how many entries were
3:15
returned and we know this will return
all of the entries in our database, so
3:20
this will give us the total count.
3:24
So let's print(total_books), save.
3:29
And let's close out of that, I'm gonna
hit clear to so start up at the top.
3:37
Okay, so
I'm gonna run 2 first to view all books so
3:43
we can see how many books, so we have 10.
3:47
So our number here when we run 4
should give us the answer of 10.
3:50
Scroll up, yep.
3:58
10 books, perfect.
3:59
What are some other data
points that might be helpful?
4:04
What about the number of books
with Python in the title?
4:07
Let's do that.
4:11
Python_books=session.query(Book), and
4:14
let's do .filter.
4:23
And then inside of here
we need to look for
4:29
Book.title that is like where it has
4:34
Python somewhere in the title.
4:41
So these percent symbols just
say somewhere in the title
4:45
there's the word Python.
4:49
And then we want the number of books.
4:52
So again,
we're gonna use .count at the end
4:55
here to return the number of books
that have Python in the title,
5:00
then we can hit enter and
print our python_books.
5:08
Save, shut that down and then we
can run 4, and we have to scroll up,
5:14
and it looks like we have 5 books.
5:22
And we can check that by running number 2.
5:25
Let's see.
5:27
So we got 1, 2, 3, 4 and 5, awesome.
5:32
Now that we have our analysis,
let's print these out in a nice format.
5:44
So I'm gonna do a new line and
5:50
I'm gonna do a couple stars and
5:55
do BOOK ANALYSIS couple stars.
6:00
Actually I'm gonna turn this just so
we do it all at one into a triple string.
6:05
Okay, so then we can use return and
6:16
let's say Oldest Book,
oops, we need to make
6:20
this f string, there we go, oldest_book.
6:26
And then let's do our Newest Book.
6:32
And then let's do our Total Books.
6:44
And our Number of Python Books.
6:56
Save, and then I'm going to do an input
here that way you can take as much time
7:08
as you want to view the information
before moving on and do it on a new line.
7:12
Press enter to return to the main menu,
save.
7:18
Okay, now let's run it and check this out.
7:25
So number 4, if I scroll up a bit,
we have book analysis and
7:29
we have the oldest_book, the newest_book,
7:35
total_books and
Number of Python Books, awesome.
7:39
And if you wanna make this look even
neater, you can specify which portions
7:44
of each book you want to print out so
you could do like oldest_book.title to
7:49
just print the title if you wanna change
how this is looking in the console.
7:54
And then press enter to return to
the main menu, and amazing it works.
8:00
5 to exit, clear, clear our console.
8:05
Spend some time thinking of data
points you might be interested in,
8:11
like the number of books
published after 2015.
8:15
This is a great way to practice
your querying and analysis skills.
8:19
Don't forget git add,
8:23
git commit.
8:28
And git push.
8:35
Oops, got a typo in there.
8:37
Review what you've learned in this course.
8:50
Take some time to think through
the code we've created together,
8:53
then start a project of your own.
8:57
This is the best way to test yourself and
solidify your knowledge.
8:59
How about a practice app to track your
studying, you could input the minutes or
9:03
hours studied, date and
content covered as a practice project.
9:09
I also put, in the teacher's notes below,
9:14
some ways to improve on
the work we've done so far.
9:16
Keep practicing and
improving those skills Pythonistas.
9:20
Even a little coding every day
will keep the forgetfulness away.
9:23
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