Effective PyCharm (2021 edition) Transcripts
Lecture: Concept: Database features
0:00 We start working with these databases by adding a data source.
0:02 So open up the database tab window here like the plus data source and then you
0:09 saw there's a huge bunch of options and even under other there's some that are partially
0:14 or have limited support or something to that effect.
0:17 So what we did is we added our SQLite data source.
0:21 The most important thing about doing this is that we have the right drivers downloaded.
0:26 So down here you may need to click download drivers and set this up to work
0:30 with the right one after that.
0:32 You can drag and drop over the SQLite database or you can actually fill out the
0:37 file name here using the plus browse over to it.
0:40 You can test the connection and then add it everything's working.
0:43 If everything works right, you should now have your database schema and all the details
0:47 about it available here in the window.
0:51 First thing you might want to do is look at the structure of the schema.
0:54 What are the tables? How do they fit together?
0:56 We can go to the diagrams and show the visualization gives us something like that.
1:01 Remember you can manually delete things like the management admin section that you don't care about
1:06 You can also delete other tables.
1:09 You can even select certain tables to focus in on a certain scenario and just see
1:14 the diagram for that. When you're working with the data,
1:16 you're usually writing SQL queries but we can go and say jump to the console and
1:22 then write things like select star from packages where and we get all sorts of Nice
1:26 Auto complete. That helps us write our queries much faster and actually know that they're
1:32 right. Right. Is it author name or just name?
1:35 Well PyCharm will tell you once we run it,
1:36 we get the outputs down here at the bottom and we get this excel grid type
1:40 of thing where we can see the data,
1:43 sort the results and even make changes to them.
1:47 If we make a change, it doesn't actually go back into the database.
1:50 It queues up here and is then eventually ready to go.
1:53 We can either preview or push those changes back to the database.
1:57 That button when it turns green.
1:59 We can also use this section to do all sorts of stuff like create a script
2:04 of inserts, SQL inserts here and copy it to a file or import a series
2:09 of inserts and then run them against this database,
2:12 create CSV files, all kinds of cool stuff.
2:16 So if we expand this out we can see,
2:18 we have the SQL inserts or the JSON are all kinds of things.
2:21 If we want to actually create the structure of the database,
2:24 we can click the DDL
2:25 Button and it will open up this data definition language script that's going to
2:30 actually generate the table. So if we wanted to say take this packages table and
2:34 create it somewhere else on a new database.
2:37 We do this, we want to modify the existing scheme out.
2:41 We can right click on a column on a table,
2:44 whatever and say modify schema. We get this visual editor that lets us make changes
2:50 like adding a column or something and then we actually get the script that's going to
2:54 run. And that is fantastic because you'll be able to see exactly what changes are
2:58 really going to happen to your database.
3:00 You can even save them to a script,
3:01 share them with your DBA.
3:03 Let them verify. Yes, this is actually what I want to happen.
3:06 You're not going to break anything.
3:08 In this case we're going to rename created underscore date to created.