Effective PyCharm (2021 edition) Transcripts
Chapter: Databases
Lecture: Data application introduction

Login or purchase this course to watch this video and the rest of the course contents.
0:00 In this chapter. It's going to be useful to have some realistic looking database structure
0:05 and some realistic data. So let me introduce you to the application that we're going
0:09 to use to explore the database capabilities of PyCharm in this chapter,
0:15 this is actually one of the applications we built over in our data driven web apps
0:19 with Flask. Let me just run it and then we'll talk through it notice that
0:23 we're connecting to the database with SQLite here.
0:27 So we're gonna be exploring the SQLite database.
0:30 Now the reason I show SQLite is not because it's necessarily the best option here
0:35 but it's incredibly simple. It requires no server set up and the connection is
0:40 just a file path. So what we're gonna see around the database tooling,
0:44 it's going to work with things like Postgres Microsoft SQLserver,
0:48 MySQL, all those kinds of things.
0:49 But we're using SQLite because it keeps the set up for you all extremely simple
0:53 Alright, here we go,
0:56 how about that? Does that look familiar PyPI
0:58 So over in that course we build a clone of PyPI.
1:01 If you scroll down here you can see it doesn't have all of the packages,
1:05 it only has 96 of the more popular ones and we come down here,
1:09 you can see things like 'gevent' or AWS CLI or whatever.
1:12 Let's just go to 'gevent' and here's the page.
1:15 Right? So we can go and actually look at the package name here and then
1:19 maybe do a query back and we get things like what releases are related to that
1:24 gevent package, what details are there around the package and so on.
1:29 Do you don't really need to even run this app but it is kind of cool
1:33 to have PyPI here as a proper little app that we can play with.
1:37 Right again, primarily we're going to be just looking at the 'pypi.sqllite'
1:42 database and exploring that. But just so you see how things work over here
1:47 in our app startup to run the app,
1:50 you just run app.py can see it does a few things when it starts
1:53 it registers the URL's
1:56 and then it registers the database by connecting over through this db_session
2:01 thing. And that's incredibly simple.
2:03 It uses SQLAlchemy and what it's gonna do is build up the connection string,
2:08 pretty little message and then just do all the SQLAlchemy Magic.
2:12 So it's going to come over here,
2:13 create an engine with a factory,
2:15 make sure the database gets generated with the schema and then off it goes as far
2:21 as the queries go, These happen in the services over here.
2:24 So for example, if we want to get a package by ID,
2:29 we come down here and normalize the name.
2:32 create a session, do this query,
2:35 we're doing a pre joined load or a joined load here,
2:38 which is a joint to make sure that we get the releases with the packages because
2:43 we're going to be accessing them that'll make things a little bit quicker.
2:46 Close up the session and return the object.
2:49 The object that we're doing the query on is one of these packages notice that
2:53 there's a bunch of columns specified here and they have things like primary keys.
2:58 They have nullable set, they have indexes set and we even have relationships over
3:04 here like this, so we can traverse the releases property to go get all the
3:09 releases given a particular package and we even order it newest to oldest automatically.
3:16 Pretty cool. So this is the app that we're going to be working with for
3:20 this chapter. Again, you don't really have to do too much with the code
3:23 but I do want you to be able to run it and play with it
3:25 if you wish, were mostly going to just focus on checking out the data that comes along with this app.