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: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.