How to Load an SQLite Database

ACCESS the FULL COURSE here: https://academy.zenva.com/product/data-science-mini-degree/?zva_src=youtube-datascience-md

TRANSCRIPT

In this video we're going to get started with SQLite. So the first thing that we have to do is download the main project source code, because inside of it is gonna contain a database file that we're gonna be working with. I have it already put up here called chinook.db, and it is a sample database that's quite popularly used for learning about SQL and SQLite, actually. That's the SQLite database and it already comes pre-populated with a ton of different rules, and it helps exemplify all the different features that SQLite has to offer. So the first thing you'll need to do is download the source code and unzip it. So on Mac OS I can just double click to unzip. On other operating systems you might have to do a right-click and then extract all. And then go into here and you'll have some source code, but the thing we're looking for is chinook.db. So I wanna copy that over into your working directory, whichever directory you want to be doing code in and housing our little database here. So I've already created a folder here called SQLite, and I'll be using that to host my database in, then any source code, any SQL code that I write, it will also go into this folder. So now that we have chinook.db copied over, we can load it up in -- load it up in SQLite, and then run it and just kinda mess around with SQLite a little bit so that we can kinda get comfortable in using it before we get on to doing queries. So after we have this copied over, you'll want to open up Anaconda Navigator and go to Environments, and then make sure that you've imported the SQLite environment because we'll be needing that. And in order to run So SQLite is a command line tool that we can use and so that's already pre-installed with dependencies so we just have to run it from the command line and then it'll open up a separate terminal where we can run queries, and see the results.

So I click on the screen arrow in my environment, make sure its the correct environment should be SQLite, I can click on this green arrow and go to open terminal. That will pop up this terminal and before this bash-3.2 dollar sign thing, there should be .. you know you're in the correct environment because environment name will be in parentheses. Okay, so now we have to navigate to the directories where we saved our stuff in. And so just a quick tutorial on Bash, you can type in ls and hit enter. That will show me where I currently .. it will show me the directories that are in the files that are in my current working directory. And so, it will usually default to your home folder and so my SQLite is in developer/SQLite. So if I want to go into a directory, I type cd which stands for change directory then I can just type in Developer and then hit enter. And now I'm in the developer directory. And once again, I can type in cd SQLite and hit enter. And now I'm in the appropriate directory. And I can just double check by hitting ls and then there's my chinook.db. So I'm indeed in the correct folder. Okay so now to get into the SQLite prompt, all I have to do to load this database. So type in sqlite3, space, the name of the database so chinook.db and then I can just hit enter. And then you'll see this is the SQLite command prompt. So SQLite and then a right carrot and we'll have a prompt here and then it'll show the SQLite version and then some other things. Okay, so from here, here's kind of the home point where we can start running all of our queries. That it will all be run from this SQLite command prompt. And so we have this chinook database but what's actually in it, we need to get some idea of what kind of tables it has and what kind of information is in those tables. So there is a number of SQLite meta commands. They're called meta commands because they're not exactly SQ, they're just commands that we can use to help navigate our database. So one of them that's very useful, especially if you have a new database that you don't quite know what is inside of it yet, you can just type dot tables and inside, when you hit enter it will give you a list of all the tables that .. it is inside of this database.