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
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