How to build your own Sqlite database in Python


Introduction

Being a programmer it is essential to learn to use databases in our applications to store, retrieve, manipulate, and delete data with ease. Python comes with the SQLite package preinstalled with it, using which we can create and manipulate SQLite databases.

SQLite databases are written in a single file and are hence are easier to use and access. You can easily manipulate the data within and hence is very easy for data analysis. It is very simple and easy to setup and use.

Getting Started

Now that you know, what SQLite is and why we use it, let us get started with how we can use its various features using Python.

Firstly, make sure you have Python installed on your computer. Once done, you are all set to get started. The SQLite package comes preinstalled with Python, so you do not have to install it separately.

To start using the SQLite library, just import it to your python script using,

import sqlite3

Creating a database

In order to create a database, you try to connect to it. If database does not exist, one is made during the time of execution

database = sqlite3.connect("Student_records.db")
print("Connection has been made successfuly!")
database.close()

You have now successfully created a database and connected to it. If you look into your working directory you can see Student_records.db file present in it.

Adding attributes

In RDBMS, attributes are nothing but columns of a table.

Note − One database can have multiple tables with various different columns inside each of them.

First, connect to the database and then add attributes to it.

database = sqlite3.connect("Student_records.db")
print("Connection has been made successfuly!")
database.execute("""CREATE TABLE Scores(ID INT PRIMARY KEY NOT NULL, NAMES TEXT NOT NULL, MARKS TEXT NOT NULL, GRADES NOT NULL)""")
print("Columns created in your Database")
database.close()

In the above lines of code, we are creating a table named Scores that contains the Student ID, Names, Marks and Grades.

Note: Not null means value must be entered and cannot be null and primary key means each value must be unique, that is no two student IDs should match.

Inserting records

Now that you’ve created a database and added attributes to it, it’s time to start adding in records or data into it.

Once again, you start with connecting to the database.

connection = sqlite3.connect("Student_records.db")
print("Connection has been made successfuly!")
cursor = connection.cursor()
cursor.execute("""INSERT INTO Scores(ID,NAMES,MARKS,GRADES)VALUES(?,?,?,?)""",(1,"Vijay",93,"A"))
connection.commit()
connection.close()

In the above lines of code,

cursor = connection.cusror() is used as a basic means to traverse through records in the database.

connection.commit() saves all the executed commands into the database, make sure you add it at the end of the program before closing your connection.

You can add more records by simply adding more cursor.execute command and following the above syntax with your own data.

Note − If you want to view your database using GUI, download DB Browser, it is an open source, free to use, lightweight software that allows you to view the contents of your database with ease.

Example

import sqlite3
connection = sqlite3.connect("Student_records.db")
print("Connection has been made successfuly!")
cursor = connection.cursor()
cursor.execute("""INSERT INTO Scores (ID,NAMES,MARKS,GRADES)VALUES(?,?,?,?)""",(1,"Vijay",93,"A"))
cursor.execute("""INSERT INTO Scores (ID,NAMES,MARKS,GRADES)VALUES(?,?,?,?)""",(2,"Tony",86,"B"))
cursor.execute("""INSERT INTO Scores (ID,NAMES,MARKS,GRADES)VALUES(?,?,?,?)""",(3,"Stark",100,"A"))
cursor.execute("""INSERT INTO Scores (ID,NAMES,MARKS,GRADES)VALUES(?,?,?,?)""",(4,"Steve",63,"C"))
print("Records have been added")
connection.commit()
connection.close()
input("Press Enter key to exit ")

Conclusion

You can now create your own database and add tables, attributes and records to it!

There are a lot more features within the Sqlite library that allows you to view, update, delete, sort and perform other data analysis with ease.

For more information, you can go through the documentation at https://docs.python.org/3/library/sqlite3.html.

Updated on: 11-Feb-2021

522 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements