Contents

• Overview
• What is SQLite?
• Installing SQLite & SQLite Studio
• Sample Database

Overview

For the purposes of learning to connect to and interact with a database from within Python, we will use SQLite because installation of SQLite and working with it is fairly easy. However, the concepts we cover here will work with all relational database systems with only minor changes to the code. So, on this page, we'll get SQLite and SQLite Studio installed and we'll also set up a sample database to work with over the next several sections.

SQLite is a file-based database tool. In this context, file-based means that databases are stored in a file (with a .db file extension). This fact makes SQLite databases very portable and easy to manage. We also should recognize that SQLite is not a "server," like SQL Server, MySQL, etc. This means that I can use SQLite on nearly any kind of computer and many "smart" devices such as phones, tablets, etc. This makes SQLite very flexible. On the other hand, SQLite does have limitations for large applications where multiple users need to connect to the database simultaneously, large quantities of data, etc. These limitations will not affect what we're studying here and as mentioned above, the concepts of connecting to a database, querying a database, updating data in a database, etc. are conceptually the same regardless of the database product in use.

Be sure you have SQLite and SQLite Studio installed on your computer before proceeding.

Also, I will use the following configuration on my computer where I have SQLite and SQLite Studio software installed, as well as where I will store additional files as explained below. You can set up your computer any way you wish, you'll need to know the location of these items on your computer.

Next, open SQLite Studio and click the Add a Database button as shown here:

The Add Database dialog will open:

Click the green + button and the File Explorer will open in which you'll navigate to the location where you want the new database file to be created. In my example, I will navigate to my C:\SQLite\Data folder where I want to store all of my database files. After I have navigated to that folder I enter the name of the database in the File name: field of the dialog. In this case we want to name our database grading_system.

When we click the Save button and then Ok, we see that the new database is now listed in SQLite Studio:

At this point though the grading_system database is empty. There is no structure to the database and no data in it, yet. There are several options to load create the database structure and to load data. For our purposes, to make it easier to get started on Python coding, we'll use a script I wrote to populate this database automatically.

The script is a program written in Structured Query Language (SQL) that resets the grading_system database, that is, removes everything from it and rebuilds it to an original state. You can run this script as often as you like to rebuild the database. This is useful as you experiment with the database, you can modify and delete data all you want to try different SQL features. Then you can re-run this script to reset the database back to its original state any time.

I have packaged the script file, along with another file that contains sample queries you can use with the grading_system database, in a ZIP file you can download. Download the ZIP file using the download icon ...

Download ZIP File

In my example, I will download the ZIP and then move the downloaded file from my Downloads folder to me my C:\SQLite\Scripts folder:

I then extract the ZIP file and now have two .sql files:

• BuildGradingSystemDB.sql: This is the script file we'll use to populate our new grading_system database (see the instructions below).
• DemoQueries.sql: This is a script file containing numerous example queries we can use with our database from inside of SQLite Studio and in our Python code.

Next, inside of SQLite Studio, open the BuildGradingSystemDB.sql file by clicking the Open SQL Editor button:

Then click the Load SQL from File button:

In the File Open dialog navigate to the location where you stored the script files, in my example that is the C:\SQLite\Scripts folder. Select the BuildGradingSystemDB.sql file and then click the Open button:

The script file will open in the editor and look like this:

To run the script, first double-click the grading_system database name to select it. Then, select all of the text (Windows & Linux: Ctrl + A, Macintosh: Cmd + A) in the editor window and then click the Run button:

After the script runs we now see that there are tables in the database (you can see these by clicking the > arrow next to Tables under the database name):

Next, let's try a query to confirm that we also have data in the database. To do this, we'll open the DemoQueries.sql script file. To do this, click the Load SQL from file button again and this time select the DemoQueries.sql file and click the Open button:

The script will open. If you look through the file there are numerous, separate, segments of SQL code, each starting with SELECT. Each of those are individual queries. To run one, highlight a query (which starts with SELECT and ends with a semi-colon ; character), and then click the Run button.

For example, in the following screen capture I highlighted the SELECT * FROM students; query and clicked the Run button:

The results of the query is called our result set. Getting a result set here indicates that our build script worked and we have data in the database.

For now, that completes the set up of SQLite and the sample database. We will continue to use the grading_system database in the sections that follow.