SQLite is a relational database management system based on the SQL language but optimized for use in small environments such as mobile phones or small applications. It is self-contained, serverless, zero-configuration and transactional.

Creating Database

import sqlite3

db = sqlite3.connect('employees.db') # Open a database File
print('Database opened')
db.close()
print('Database Closed')

Create Table

import sqlite3

db = sqlite3.connect('employees.db') # Open a database File
query = '''
CREATE TABLE IF NOT EXISTS employee(
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    DIVISION TEXT NOT NULL,
    STARS INT NOT NULL) 
'''
db.execute(query)
print('Database opened')
db.close()
print('Database Closed')

Insert Records

import sqlite3
print('Database opened')
db = sqlite3.connect('employees.db') # Open a database File
query = '''
INSERT INTO employee_records(ID,NAME,DIVISION,STARS) VALUES(1,'Ravi','Architect',4)
'''
db.execute(query)
db.commit()
print('REcord inserted')
db.close()
print('Database Closed')

Using variables to insert data

import sqlite3
print('Database opened')
db = sqlite3.connect('employees.db') # Open a database File

def insert_record(id, name, division, stars):
  query = ''' INSERT INTO employee_records(?,?,?,?)  VALUES(id, name, division,stars)
'''
  db.execute(query )
  db.commit()
  print('Record inserted')

insert_record(1,'Ravi','Architect',4)
insert_record(2,'Rahul','Architect',4)

db.close()
print('Database Closed')

Read Data

import sqlite3
print('Database opened')
db = sqlite3.connect('employees.db') # Open a database File

def read_records(id, name, division, stars):
  query = 'select * from employee'
  records = db.execute(query )
  for record in records:
    print(record)
  print('Record inserted')

read_records()
db.close()
print('Database Closed')

Update Records

import sqlite3
print('Database opened')
db = sqlite3.connect('employees.db') # Open a database File

def update_record(id,updated_stars):
  query = f"UPDATE employee set STARS={updated_stars} where id={id}"
  records = db.execute(query )
  db.commit()

update_record(1,5)
db.close()
print('Database Closed')

Delete Record

import sqlite3
print('Database opened')
db = sqlite3.connect('employees.db') # Open a database File

def delete_record(id):
  query = f"DELETE FROM employee WHERE id={id}"
  records = db.execute(query )
  db.commit()

delete_record(2)
db.close()
print('Database Closed')