00:0.04 In this chapter. It's going to be useful to have some realistic looking database structure
00:5.05 and some realistic data. So let me introduce you to the application that we're going
00:9.91 to use to explore the database capabilities of PyCharm in this chapter,
00:15.34 this is actually one of the applications we built over in our data driven web apps
00:19.87 with Flask. Let me just run it and then we'll talk through it notice that
00:23.57 we're connecting to the database with SQLite here.
00:27.24 So we're gonna be exploring the SQLite database.
00:30.75 Now the reason I show SQLite is not because it's necessarily the best option here
00:35.49 but it's incredibly simple. It requires no server set up and the connection is
00:40.14 just a file path. So what we're gonna see around the database tooling,
00:44.96 it's going to work with things like Postgres Microsoft SQLserver,
00:48.13 MySQL, all those kinds of things.
00:49.86 But we're using SQLite because it keeps the set up for you all extremely simple
00:53.82 Alright, here we go,
00:56.54 how about that? Does that look familiar PyPI
00:58.77  So over in that course we build a clone of PyPI.
01:1.96 If you scroll down here you can see it doesn't have all of the packages,
01:5.47 it only has 96 of the more popular ones and we come down here,
01:9.15 you can see things like 'gevent' or AWS CLI or whatever.
01:12.66 Let's just go to 'gevent' and here's the page.
01:15.91 Right? So we can go and actually look at the package name here and then
01:19.66 maybe do a query back and we get things like what releases are related to that
01:24.86 gevent package, what details are there around the package and so on.
01:29.54 Do you don't really need to even run this app but it is kind of cool
01:33.08 to have PyPI here as a proper little app that we can play with.
01:37.15 Right again, primarily we're going to be just looking at the 'pypi.sqllite'
01:42.72  database and exploring that. But just so you see how things work over here
01:47.98 in our app startup to run the app,
01:50.35 you just run app.py can see it does a few things when it starts
01:53.95 it registers the URL's
01:56.38 and then it registers the database by connecting over through this db_session
02:1.34 thing. And that's incredibly simple.
02:3.73 It uses SQLAlchemy and what it's gonna do is build up the connection string,
02:8.54 pretty little message and then just do all the SQLAlchemy Magic.
02:12.87 So it's going to come over here,
02:13.78 create an engine with a factory,
02:15.84 make sure the database gets generated with the schema and then off it goes as far
02:21.02 as the queries go, These happen in the services over here.
02:24.84 So for example, if we want to get a package by ID,
02:29.04 we come down here and normalize the name.
02:32.34 create a session, do this query,
02:35.04 we're doing a pre joined load or a joined load here,
02:38.67 which is a joint to make sure that we get the releases with the packages because
02:43.6 we're going to be accessing them that'll make things a little bit quicker.
02:46.38 Close up the session and return the object.
02:49.44 The object that we're doing the query on is one of these packages notice that
02:53.16 there's a bunch of columns specified here and they have things like primary keys.
02:58.04 They have nullable set, they have indexes set and we even have relationships over
03:4.86 here like this, so we can traverse the releases property to go get all the
03:9.81 releases given a particular package and we even order it newest to oldest automatically.
03:16.94 Pretty cool. So this is the app that we're going to be working with for
03:20.51 this chapter. Again, you don't really have to do too much with the code
03:23.37 but I do want you to be able to run it and play with it
03:25.9 if you wish, were mostly going to just focus on checking out the data that comes along with this app.
