Analytics Vidhya
Published in

Analytics Vidhya

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 programing language.

As we know that server-side language 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 is 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 for SQL concepts accept few new Databases.

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 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 execute 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 DB operation.

INSERT:

import sqlite3conn = 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 operation without installing any additional software.

READ:

This is an important operation because this belongs to 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 database, Here are a few examples of select operations.

import sqlite3conn = 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 sqlite3conn = 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()

DELETE:

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

import sqlite3conn = 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.

Thank you!!! Happy Coding.

--

--

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store