Effective PyCharm Transcripts
Chapter: Databases
Lecture: Concepts: Database features
Login or
purchase this course
to watch this video and the rest of the course contents.
0:01
Let's do a quick review of the various database features that we've seen in PyCharm,
0:06
which, really is data grip, right for the most part, except for that last one. We started out by saying we're going to add a data source,
0:12
we went to the database little tab tool window thing and we said hit the plus, add data source, pick SQLite serial, once we do that, it opens up this,
0:25
you've got to make sure that there is no warning or statement that you need to download the drivers,
0:32
because nothing will work at all from here on if you don't have those drivers. But, once you have done that to verify that the drivers are all good,
0:40
then you can just drag that SQLite file over to the database section and it will already know and understand it.
0:47
Once you've done that, you can expand it out and see that the database info should all be present. One of the first things you might want to do is
0:56
visualize the relationship between your tables in your database, you can go to the top and right click and say
1:04
go diagrams show visualization, show visualization pop up, whatever it is, whichever style it is that you want,
1:10
and then we also saw that you can select individual tables and do the same thing and it will show just that subset of the tables,
1:17
so let's say we picked show pop up, we'd see something like this, pop up next to our code, here's a slightly more interesting diagram,
1:26
from the same database but evolved further down into the application that we built in that course,
1:32
you can see there's an account that has a password reset and album purchase which relates back to the album and the account and all sorts of stuff;
1:40
so, really, really nice that we can visualize this really clearly. Next, we want to do query data, once we know it's there
1:47
let's go and ask some questions like what kind of data is there, here we're saying select just the album id, id and name from the tracks
1:55
and notice that we get intellisense or autocomplete all along here, in the select part, especially once we say from track
2:03
things get really good and we get the entire schema for the track table and the other tables as well.
2:11
We run this and it opens down at the bottom to this main.track thing and you can see here's all the tracks, their ids, their album ids
2:18
and these came back based on the three columns that we selected, that's really cool, there's a bunch of stuff that we can do down here
2:26
for example, if we make changes, we need to push the db button here to save those changes back, right now it's grayed out because there's no changes
2:36
but if you made changes then it would turn like green and blue or something like that. Also over here, we saw that we can export data
2:43
we can export data modifying scripts let's say so like insert statements, we can create DDL which will give us the operations
2:52
to generate or recreate the table, things like that so really, really nice, lots of features. Sticking to that for a second, so if we focus here
3:00
and we hit sql inserts and we click on it it depends on what was last selected, we might have tab separated values or comma separated values
3:08
or sql inserts or whatever it was that you last selected so these are all the different export types,
3:15
so csv is pretty common, notice there are some Javascript specific ones or groovy, closure, I don't really do anything with those
3:23
but if that's useful to you, you can definitely work with those as well. Now under DDL, we can say show me the data description language
3:33
to actually generate this table, so this is the kind of stuff that I always forget when I'm working with relational databases, this DDL stuff
3:41
and here including the schema, the types, the nullability as well as all the indexes are generated right there, so that's quite nice.
3:50
We also saw that we can modify the existing scheme, right click on a table and say modify and then we can add columns, keys, indexes,
3:59
change them, delete them, all kinds of stuff. So the final trick that we saw was PyCharm brings us together in a really wonderful way,
4:09
so it's very common to write SQL statements embedded in strings that you then send off through something like db api or something like that.
4:20
So here we're going to write our SQL, then we're going to execute the command and then loop over that.
4:25
So this is not SQLAlchemy or other ORMs, this is just raw SQL, and PyCharm has some really nice help so when you write it this far, it looks like
4:35
oh there's no help, it's just like, this is going to be a string like strings would normally be. If you type a little bit more though,
4:42
once it's really sure you have a SQL statement there it starts to give you all sorts of cool stuff so we have main.track, we have album_id=7,
4:50
you see there's like join and where and from these are all known standard SQL keywords, so that's already cool,
4:56
but it takes it one step further, where it actually, once you say the table, it actually understands the schema of that table
5:04
and gives you autocomplete list there as well. This is super valuable if you find yourself writing raw SQL statements inside Python.
5:13
This also works in the select field, you kind of have got to write enough out select * from main track and then go back and change * to something else,
5:22
sometimes select * is not a great idea because you're getting a bunch of stuff you don't need so if you just want like name and audio url
5:29
then you could easily do that here, select name,audio url and it will actually give you all that autocomplete stuff right there, which is super nice.