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