Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Clean the data in order to find entries in the database by id. Create functions to support your work.
Challenges
- What if you have 1000 books? What would be a better way of sharing the id options with the user when searching by id?
- What are other ways you could let the user search for a book?
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
Next on our list is to tackle
finding a specific book by ID.
0:00
First, let's help our user out by
letting them know the range for
0:06
IDs in our database.
0:11
We can do this by creating
0:12
an id_options as a list and
0:17
then do for
book in session.query(Book) and
0:22
then do id_options.append and
0:31
let's do our book.id, okay?
0:37
Awesome, you may be asking why not just
grab the length of the database and
0:43
use that as a range?
0:48
Well, when an entry is deleted that
ID is no longer in our database.
0:50
For instance, if we had a range of
books that was one through five.
0:55
So 1, 2, 3, 4, 5.
1:01
And we deleted the book with the ID of 2.
1:04
Well, now our range is 1 and
then 3 through 5.
1:07
And so just to be more clear
about what the options are,
1:12
I think it's best if we
just show them to our user.
1:16
So let's do print, and actually let's
1:20
do this as an input and 1, 2, 3, 4,
1:25
5, 6, just so we can split it out on
1:30
a couple lines and let's say Id Options.
1:35
Let's put an f here, so we make this a,
1:40
we can use string formatting and
let's put in our id_options.
1:44
And then let's also ask the user for
the ID.
1:52
Okay, let's save and
let's run this in the console.
2:01
All right, so we want 3 cuz now
we're on searching for a book.
2:04
We see all of our options and
let's see what happens if I put in 12.
2:11
It just keeps going.
2:15
So we're going to need to check for
errors with our ID.
2:17
And we've done a similar thing with
our clean price and clean date.
2:20
So we're gonna do a very similar
thing here to clean the ID.
2:25
Let's save this as their id_choice.
2:30
And then above it,
2:36
I need to do id_error equals true.
2:39
And then while id_error and let's tab
2:44
all of this over, so
we're gonna ask for that.
2:49
And then do id_choice equals clean_id,
2:55
which doesn't exist yet, but
3:00
we are gonna make it in just a second.
3:04
And then we're going to check if
3:08
the type(id_choice) equals an integer
3:12
because our IDs are an integer
in the database,
3:17
and then we can set our
id_error equal to False.
3:23
Cool, all right,
now let's create our function.
3:29
So let's scroll up.
3:32
Lots of scrolling.
3:34
And let's do it after our clean price, so
3:36
that all of our cleans
are next to each other.
3:38
Clean_id and
we know we're gonna take an ID string and
3:43
inside of our function we
need to do a try lock.
3:49
And we need to take that, ID string and
3:54
we need to turn it into an integer.
3:59
So I'm gonna say, book_id equals Int,
4:03
oops lowercase i of id_string, and
4:10
then we're gonna except a ValueError.
4:15
And we're gonna give them an error and
just like we did before,
4:21
I'm just gonna copy one of these.
4:24
Paste here.
4:29
This ID ERROR and the,
ID should be a number.
4:31
And then I'm just gonna say,
press Enter to try again.
4:44
And then we need to put that return here,
so that it returns None.
4:48
So our while loop will kick in.
4:54
And then inside of here we're going
4:56
to then return our book_id and
5:02
let's, it already has been called.
5:07
Let me just check to make sure.
5:13
Let's go back down into three and
5:15
we are calling it, yeah, okay?
5:19
Just wanted to make sure.
5:23
All right, so we wanna do three and okay,
5:24
if I type in tree I get the error,
it needs to be number, okay?
5:28
I hit Enter and let me do 12,
and well it took 12.
5:34
And we know 12 is not an ID in our
database cuz it's not in our ID options.
5:41
So we also need to check and make sure
that our options are being checked.
5:46
So let's pass those in.
5:51
Id_options and hit Save and
5:53
then we need to accept
them up in our function.
5:58
I'm just gonna call them options up here,
awesome.
6:10
Then if the ID can be turned
into an integer inside here,
6:13
we can then check if
book_id is in options.
6:20
Then we can return it and if it's not,
6:26
then we need to do the return.
6:32
That we make sure we get a book with
an ID that is in our options list, okay?
6:36
Let's test this out in the console.
6:41
Cancel and scroll this up, app.py.
6:45
Wanna do 3, here are our options.
6:52
If I put in 12 now,
I get our Id Options again.
6:56
So I no longer get an error, but
I do get the option to try again.
7:04
So let's give them an error,
that way they know what they did wrong.
7:09
So inside of here, I'm actually,
7:13
I'm gonna copy this one and
paste it in here.
7:16
And then I'm just gonna say, Options and
I'm gonna turn this into an f string.
7:26
And let's just show them their options.
7:32
Save and let's try this again.
7:36
Clear python3 app.py.
7:37
Let me scroll this up.
7:44
Let's do 3.
7:47
Let me try 12.
7:49
And I get ID ERROR, and
it shows me the options and
7:52
it says press Enter to try again.
7:55
And I get to try again.
7:57
Cool, I feel like that works
a lot better for the user.
7:58
With all that complete, we can now find
the correct book in our database and
8:03
share it with the user.
8:07
So let me pop back down into our app.
8:09
And now that we actually have
a working ID, my call is the_book.
8:14
It's the_book.
8:20
We have found it.
8:21
We have found the_book and
8:22
it will be session.query(Book).filter,
8:27
filter, where Book.id
equals the id_choice.
8:35
And we wanna add dot first, just in case.
8:43
It should just return one, but sometimes
SQLAlchemy is a little weird.
8:47
So we'll add dot first there,
just so we don't get an error.
8:51
And let's see,
print our book to the console.
8:55
And let's do the_book.title
9:07
By the book.author.
9:13
And let's do the Published and
9:20
let's choose the book.publish_date and
then Price.
9:25
And I'm going to add
an a currency symbol here.
9:35
And let's do the book.price and
I'm actually going to divide this by 100,
9:40
so that we get back to an actual price and
not the price and cents.
9:48
That way it'll show us
10.99 instead of 1,099.
9:54
So let's save and run the file.
10:00
Give us some space here.
10:08
And three and let's say a book number two.
10:11
And there it is, Mindset by Dr.
Carol Dweck, perfect.
10:18
Now this is another great opportunity
to add a input or a time sleep.
10:25
Whatever you wanna kinda pause in
the console, so that the user can
10:32
actually look at the book before the menu
gets automatically printed again.
10:37
I'm going to choose doing an input,
just so
10:41
that the user has the control over
the amount of time they wanna spend here.
10:44
So I'm just gonna do Press enter
to return to the main menu.
10:49
Save and then just to show you all that
it's working, let's do that again.
10:57
Three, book two, and there.
11:04
Now, it kinda pauses us here, so
11:07
we can actually take in the book
before returning to the main menu.
11:09
All right,
you all look at all of this code.
11:18
It's still going.
11:26
[LAUGH] Wow, that is amazing.
11:27
And we finished another task,
so you know what to do.
11:32
It's time to add, commit and
push these changes up to your repo.
11:35
Nice work, you're amazing.
12:01
Keep it up.
12:04
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