SQLite Database “CRUD Operations” using Python.

The purpose of writing this is for beginners who are curious about backend development or front-end developers who want to learn database technology with server-side programming language.

As we know server-side languages like python, java, and many others are not sufficient for backed developers and even a backend developer needs to be more knowledgeable in database technologies.

So let’s start with the basics of SQLite Database with Python.

I chose the SQLite database as just an option, one can apply the same knowledge with any other database also like MySQL and Oracle or any other. the best part of database technology is all the databases are very similar to SQL concepts except fora few new Databases.

We are not using any Python package to make our job easy for learning these four basic operations.

CRUD

• C: Create
• R: Read
• U: Update
• D: Delete

CREATE:

Inserting or creating a new record within the table. so let’s create an example table within the SQLite Database.

# Creating table into database!!!

import sqlite3

# Connect to sqlite database
conn = sqlite3.connect('students.db')
# cursor object
cursor = conn.cursor()
# drop query
cursor.execute("DROP TABLE IF EXISTS STUDENT")

# create query
query = """CREATE TABLE STUDENT(
        ID INT PRIMARY KEY NOT NULL,
        NAME CHAR(20) NOT NULL, 
        ROLL CHAR(20), 
        ADDRESS CHAR(50), 
        CLASS CHAR(20) )"""
cursor.execute(query)

# commit and close
conn.commit()
conn.close()

The conn = sqlite3.connect(‘students.db’) is the connection method and it is pretty simple with SQLite DB but it will differ with different databases.

The cursor.execute() method executes SQLite queries.

"""
CREATE TABLE table_name (
   column name datatype properity,
   ...
   ...
);
"""

The above syntax can be mapped with the query, there are three main attributes of a create query “column name datatype property”.

After every database operation, we should add a commit and close the DB operation.

INSERT:

import sqlite3

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

conn.execute("INSERT INTO STUDENT (ID,NAME,ROLL,ADDRESS,CLASS) "
             "VALUES (1, 'John', '001', 'Bangalore', '10th')")

conn.execute("INSERT INTO STUDENT (ID,NAME,ROLL,ADDRESS,CLASS) "
             "VALUES (2, 'Naren', '002', 'Hyd', '12th')")

conn.commit()
conn.close()

The above query syntax is hardcode data insertion but when we have data from an external input then we can modify the syntax this way.

query = ('INSERT INTO STUDENT (ID,NAME,ROLL,ADDRESS,CLASS) '
         'VALUES (:ID, :NAME, :ROLL, :ADDRESS, :CLASS);')

params = {
        'ID': 3,
        'NAME': 'Jax',
        'ROLL': '003',
        'ADDRESS': 'Delhi',
        'CLASS': '9th'
    }

conn.execute(query, params)

https://sqliteonline.com/ is a nice online platform that can be used to perform DB operations without installing any additional software.

READ:

This is an important operation because this belongs to the Select query and has a lot more verity in fetching the records from the database. sometimes this operation will be very tricky with multiple table databases, Here are a few examples of select operations.

import sqlite3

conn = sqlite3.connect('students.db')
cursor = conn.execute("SELECT * from STUDENT")
print(cursor.fetchall())

conn.close()

The simplest way fetching all the data “SELECT * from TABLENAME”

SELECT column1, column2, columnN FROM table_name;

we can mention only those column names are required, It is always good practice to mention names of the column if all the data is not required to fetch.

SELECT column1, column2, columnN FROM table_name WHERE column_name = value;

where clause returns specific rows which record will match with the value.

UPDATE:

The update is changing the existing record, the simple rule of the update is to use the best approach to reach the record and change it.

import sqlite3

conn = sqlite3.connect('students.db')
conn.execute("UPDATE STUDENT set ROLL = 005 where ID = 1")
conn.commit()

cursor = conn.execute("SELECT * from STUDENT")
print(cursor.fetchall())

conn.close()

Note: Why I say about using the best approaches because not only in cases of update where but in all database operations we should avoid reading or fetching unnecessary records it will save the query time and will increase the whole system performance of any application.

DELETE:

Removing any records from the table is a DELETE operation and the code below shows the delete query example.

import sqlite3

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

conn.execute("DELETE from STUDENT where ID = 2;")
conn.commit()

cursor = conn.execute("SELECT * from STUDENT")
print(cursor.fetchall())

conn.close()

These are the basic CRUD operations on the SQLite database using Python.

The source code is available on this GitHub click here.

It is always good to directly jump into developing an application we should learn the basics of backbone technologies required for those applications.

If one is clear with basic knowledge of database operation with CRUD operation then it is ok to use any of the Python SQL libraries for easy implementation.

Thank you!!! Happy Coding.

Published Here: https://medium.com/analytics-vidhya/sqlite-database-crud-operations-using-python-3774929eb799