Effective PyCharm Transcripts
Lecture: Querying data in the SQL console
0:00 We saw the structure of the database with the diagrams but let's explore the data itself
0:05 Now. Normally when we explore data with relational databases we would have some kind of select statement or something like that.
0:13 The way we do that over. PyCharm is we have what's called a query console so we can right click
0:19 on some part of the database tools and say jump to query console we'll say a new one. Get it nice and fresh.
0:25 And here we can write things like select and notice we get auto complete for our SQL language. So select where order by and so on.
0:35 Which is pretty awesome. What do you want to select? Let's say select star from and check that out. Here's our schema. You can see we've got packages,
0:43 auditing, downloads, maintainers and so on. Let's do from packages. We could do a 'WHERE' clause.
0:50 Right? That's pretty common. Wait space though we get auto complete into that table So here we can do things like where the id is whatever it is.
1:00 The idea is actually the PyPI package name like requests or boto or something like
1:07 that. We could do ordering based on creation date or something like that. All right so let's go over here and actually save where
1:15 ID. Is like and then we'll put 8% boto percent. This is like a anything that has that sub string there. We can run it and check that out.
1:25 We have three packages in our database. That's pretty cool. Right over here we've got boto we've gotta boto three and we've
1:33 got boto core with this down here we can kind of explore you know all
1:38 the data it's obviously hard to see the super long description but what are you gonna
1:42 do with paragraphs of text? But we can actually come in here and edit this section. So I could come down here and make this boto like that.
1:53 For example if I wanted to you can also come over here and say that Michael created this package didn't but whatever notice something as I move around,
2:05 notice how this is changing, this is like a blue type of thing and let's
2:11 even change I'll leave the ideal because there's a relationship there but we can change other things as well and let's here this.
2:17 I also wrote this one by the way awesome. You can see that this is actually keeping track of all the changes that we've added
2:25 or made. And here you can even preview you can preview the pending changes.
2:30 So check this out. We're going to say update packages set author named to Michael
2:34 where the ideas boto update packages like those two changes where the name is, I'm sorry boto according the first one boto for the second one.
2:42 How cool is that? But if we rerun this notice or we even refresh this down here you can sort of do it either way,
2:50 notice the name is gone, the author is gone and so on. What happened. Well thing is not broken what we gotta do, we don't need a bigger editor.
2:59 What we gotta do is we actually have to push these changes. So this thing showed us what the changes would be but they didn't commit to the
3:05 database until we are ready. We push this. It will actually submit what you saw over in that dialogue out there and now if
3:12 we rerun our query notice all these things are sticking, how cool. Huh? So not only do you get the query ability up here
3:20 with nice auto complete and so on. But you also get this really cool sort of like sell like transactional editor thing.
3:29 Let's just get all the data for a minute here. There's all of it. What if I wanted to create this table but in a
3:37 different database. Well we need to create what's called the data definition language DDL
3:42 And over here it will actually show you what script you would have to run to create this packages table.
3:51 So we would take this and run it on whatever relational database run. So create the ID. It's VARCHAR. not null. Primary key created day to day time.
4:00 And then once the structure is made we can add the indexes to them. How cool is that? So we can use that with the DDL
4:08 bit right there. We also might want to get this not the structure but we might want to get the data down here actually exported.
4:15 Right? And if we want to get that data out, we can click here and there's all sorts of options.
4:22 So check this out. We can come along and we could generate uh CSV. File of this data. We could generate pipe separated or tab separated.
4:31 We could create a script that will be the SQL in search to insert this data
4:36 Let me do a different table here because the description makes us look really crazy Well here, we don't have such long data.
4:47 So actually we could come over here and click on CSV And then we have the ability to export or import this data Like we could import CSV'sinto here,
4:55 but let's go and hit this notice. Here's the CSV file that has all that data over here. We've got the primary key, we've got,
5:03 what is this? The number of downloads, actual file and version information over here and even sort of bounce around.
5:11 So here's the insert. So we would insert into releases with all these values. And then you can see just like there's your SQL insert statements,
5:19 what this is, JSON there, you have it in JSON. So if you want to import or export this data it's incredibly easy in a ton of different formats.
5:29 Right? Then you can of course copy to the clipboard, save it to a file or you don't do nothing with it. Here. You go, this query console is fantastic.
5:39 There's all sorts of neat things and we're just touching on the surface. Another thing that's worth pointing out here,
5:45 let's go and do a select from packages, notice as I hover over it. The auto complete shows me the definition.
5:53 Crazy. Ok. But one other thing to notice here is you can sort by these things like we'd sort by date last updated.
6:01 We could sort by summary. Doesn't really mean anything. Remember, as you look at the structure of this,
6:08 the blue means there's an index in the grey means there's no index. Right? You actually see that here.
6:13 So if I click this button are this column heading, it's going to order by this and it's going to do it quickly because there's an
6:21 index and maybe if I had a ton of data actually trying to do that sort right, there would be really slow because there's no index.
6:28 So you even get that kind of visibility into like if I'm sorted by these things you want to make sure that there's an index and so on.
6:35 All right. Make sure you take advantage of this. There's all sorts of cool stuff going on right here.