Hello again! This time, we will resume our data analysis and visualization training by creating databases through code.

While working with DB Browser for SQLite can be fun, it’s not the practical way to do things when we are handling tens of thousands of data records – or more. And since practicability is one of the core values of UniSci 24, here’s one of the most concise and yet fully functional examples of Python code for creating a database and adding data records to it:

import sqlite3

conn = sqlite3.connect('students.sqlite')

cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Students')

cur.execute('CREATE TABLE Students (name TEXT, grade

INTEGER)')

while 1:

    name = input("Input student name or Enter to stop: ")

    if len(name) < 1:

        break

    else:

        grade = input("Input student grade: ")

cur.execute('INSERT INTO Students (name, grade) VALUES (?, ?)', (name, grade))

conn.commit()

First, we need to import the sqlite3 module; otherwise, we wouldn’t be able to use SQL instructions in our code. Then, we create a connection to the students.sqlite database. If the database doesn’t exist yet, it is created. The next line of code creates a cursor, a connection to our database.

It’s time to check if the ‘Students’ database table exists. If it does exist, we will delete it, because we want to create a fresh table anytime we run the application. This way, if we made a mistake in a previous version of the code, we won’t have to suffer its consequences anymore.

Let's create the ‘Students’ table. It will have two columns: ‘name’ and ‘grade’, the first one storing strings (text) and the

second one numbers (integers).

The ‘while’ loop is a bit strange, isn’t it? In programming terms, ‘while 1’ is similar to ‘while 1 = 1’, which is always true. So, our

loop would run forever unless we do something to stop it. Fortunately, we thought of that, and you’ll see how we did it soon.

The first line of code inside the loop asks the user to input student names, or press the Enter key to stop the process. The next line verifies if the length of the input string is smaller than one. If this is the case, the ‘break’ instruction will make the interpreter exit the loop. It is assumed that we’ve input all the needed student data at this point.

The ‘else’ branch runs for as long as we continue to input student names, prompting us to input student grades as well. Then, we insert the name/grade pairs into the ‘Students’ table.

The last line of code saves the database to the disk drive. It’s a slow process, so it’s best to use it less often. Scientific programmers who work with millions of data records save the data to the disk every 100 records or so. Basically, you need to figure out how much data you are willing to lose in case something goes wrong and save the records to the disk accordingly.

Run the code, and then input as much student data as you want. Here’s an example: