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.