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

Talk Python's Mastodon Michael Kennedy's Mastodon