Effective PyCharm Transcripts
Chapter: Databases
Lecture: Querying data in the SQL console
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: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.