Effective PyCharm Transcripts
Chapter: Databases
Lecture: Concept: Database features

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

Talk Python's Mastodon Michael Kennedy's Mastodon