Using SQLite and DB Browser
.png)
Hi everyone! Welcome to the first tutorial in the "Data Analysis and Visualization" course. So far, we have learned to store data in text files and retrieve it later. Now, it’s time to move on to the next level and implement what the real pros use in their scientific applications: databases.
Databases are ideal for scientific programming; they can shuffle around millions of records without putting a strain on regular computers. We still want to move things fast in the computer memory using tuples and dictionaries, but once we’ve taken care of that, we will create and save a database file, and then work with it.
This course will also teach you the basics of SQL, the most popular database ‘programming’ language. Let’s get started!
The first step is to download and install the DB Browser for SQLite:
Start the application and you will see an image similar to the one below:
We are going to use this program to create and edit databases for now; later, we will learn how to do all these operations from within our Python applications. However, it is important to understand how things work in real life, rather than imagine them while we are writing code.
Click ‘File’, and then ‘New Database’ to create a new database:
Pick a name for your database and then choose the desired destination path. A new window will pop up:
Close that window; we don’t need to fill in those details for now. This is how the DB Browser window should look like after you close the “Edit table definition” window:
As you can see, we don’t have any tables in our database; nevertheless, we can easily create a table using this SQL instruction:
CREATE TABLE Students (
name VARCHAR (100),
grade VARCHAR (100)
)
Copy the code above to the clipboard, click the ‘Execute SQL’ tab at the top of the DB Browser application, and then paste the code inside ‘SQL 1’:
Click the ‘Execute SQL’ button. If you have copied the code correctly, the program will print out an ‘Execution finished without errors’ message.
Click the ‘Browse Data’ tab at the top of the window and you will see our newly created ‘Students’ table, which includes the ‘name’ and ‘grade’ fields:
We don’t have any student records yet, so let’s change that right away. Click the ‘Insert new record in the current table’ button, as shown in the picture below:
Add a student name and their grade. Feel free to add as many students as you like.
Save the database and then close the application. Next time we’re going to do the same thing by embedding the SQL instructions inside Python code.