Photo: Unsplash.com

SQLite3: Getting a Head Start

Introduction:

Are you an avid programmer but have always found SQLite3 to be oddly challenging? Here’s a quick tutorial to get you started.

SQLite3 is a complete functional relational database management system (RDSMS) that scales well. It’s the most used database in the world.

In this tutorial, we will start by looking at some important SQLite steps and operations such as: Connecting to table, Update, Select, Delete etc., and then playing with them on a working example.

SQLite comes bundled with Python and can be easily used in any Python application. I assume that you already have Python installed on your computer, and are here for a quick read.

So, let’s get started!

NOTE: We will be working on a database file named “school.db”, with a table we will create in it named “students”. To start working with SQLite, our first step should be to import Sqlite3 module.

1. Connecting to a Database

Conn = sqlite3.connect(‘school.db’)

This one line connects your Python file to the database named ‘school.db’. If the file does not exist, it would be created automatically in your working directory.

Note that to work with any SQLite operation on a database, you must connect to it first.

By saving the connection as an object in the variable “conn”, we could easily use it in later examples to interact with the database.

2. Creating a Table

In a SQL database, data is stored in tables. Tables define a set of columns, and contain 0 or more rows with data for each of the defined columns

conn.execute(‘’’CREATE TABLE STUDENTS (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), YEAR INT NOT NULL);’’’)

The above code creates the table STUDENTS in the connected database; ‘school.db’. The table has the following five columns; ID, NAME, AGE, ADDRESS and YEAR.

Notice in the above code, each column name e.g. NAME is followed by terms like; TEXT NOT NULL. These terms describe the column and the type of data that could be added to it. To learn more about these descriptive terms, read this.

3. Insert Operation

The INSERT operation is used to add new records to the table.

conn.execute(“INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS,YEAR) \ VALUES (1, ‘Najah’, 30, ‘California’, 2022 )”);

4. Select Operation

The SELECT operator is used to retrieve records. This operator is very useful, as retrieved records or data from a database could be used in numerous functions including; displaying all records, analyzing data, calculating average etc.

cursor = conn.execute(“SELECT id, name, address, year from STUDENTS”)

5. Update Operation

The update operator updates existing records in a database.

conn.execute(“UPDATE MYTABLE set NAME = “Hafsah” where ID=1")
conn.commit

The above lines, update the table “mytable” at the column SALARY at the record with ID=1.

Think of “conn.commit” as a save button. Note that conn.commit is used after modifications are made to a database, eg. Update and delete.

6. Delete Operation

conn.execute(“DELETE from COMPANY where ID=2;”)
conn.commit

As simple as that. The record is deleted. “conn.commit” updates and saves the changes made.

7. Closing Database

For the purpose of safe coding, you should always close database connections explicitly to prevent any other objects from reusing the same connection after you are done with it.

Don’t worry too much about the technicalities, just keep in mind that it’s strongly recommended to always close the connection at the end of your code.

conn.close()

Example:

In the example below, the program adds a new record from a user, updates it and then displays all the records in the database using a for loop.

import sqlite3
conn = sqlite3.connect(‘test.db’)
print “Opened database successfully”;
#Inserting new student record
conn.execute(“INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS,YEAR) \ VALUES (1, ‘Manal’, 30, ‘Silicon Valley’, 2020 )”);
conn.execute(“INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS,YEAR) \ VALUES (2, ‘Jerry’, 35, ‘Germany’, 2021 )”);
print(“Records added successfully”)
conn.commit
#Updating student record
conn.execute(“UPDATE STUDENTS set YEAR = 2022 where ID=1”)
conn.commit
print(“Record updated”)
#Selecting Records
cursor = conn.execute(“SELECT id, name, address, year from STUDENTS”)
#Printing Records
for row in cursor:
    print (“ID = “, row[0])
print (“NAME = “, row[1] )
print (“ADDRESS = “, row[2])
print (“YEAR = “, row[3], “\n”)
print(“****************************”)
print “Operation done successfully”;
conn.close()

The above code would output this:

Record added successfully
Record updated successfully
ID = 1
NAME = Manal
ADDRESS = Silicon Valley
YEAR = 2022
****************************
ID = 2
NAME = Jerry
ADDRESS = Silicon Valley
YEAR = 2021
****************************

Conclusion:

We just scratched the surface on some very useful SQLite3 operations. We learned how to use the sqlite3 module to connect to a SQLite database, adding data to that database, as well as updating records, deleting and reading/selecting data in that database.

Now it’s time to get started!

If you have understood this tutorial, I say you are set to get your hands dirty with SQLite3.

Check this out, to learn more operations. Good luck!