Effective PyCharm (2021 edition) Transcripts
Chapter: Databases
Lecture: Querying data in the SQL console

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