Python SQLite Tutorial

UpdatedApril 17, 2025

I am a Tech Enthusiast having 13+ years of experience in 𝐈𝐓 as a 𝐂𝐨𝐧𝐬𝐮𝐥𝐭𝐚𝐧𝐭, 𝐂𝐨𝐫𝐩𝐨𝐫𝐚𝐭𝐞 𝐓𝐫𝐚𝐢𝐧𝐞𝐫, 𝐌𝐞𝐧𝐭𝐨𝐫, with 12+ years in training and mentoring in 𝐒𝐨𝐟𝐭𝐰𝐚𝐫𝐞 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐓𝐞𝐬𝐭 𝐀𝐮𝐭𝐨𝐦𝐚𝐭𝐢𝐨𝐧 𝐚𝐧𝐝 𝐃𝐚𝐭𝐚 𝐒𝐜𝐢𝐞𝐧𝐜𝐞. I have 𝒕𝒓𝒂𝒊𝒏𝒆𝒅 𝒎𝒐𝒓𝒆 𝒕𝒉𝒂𝒏 10,000+ 𝑰𝑻 𝑷𝒓𝒐𝒇𝒆𝒔𝒔𝒊𝒐𝒏𝒂𝒍𝒔 and 𝒄𝒐𝒏𝒅𝒖𝒄𝒕𝒆𝒅 𝒎𝒐𝒓𝒆 𝒕𝒉𝒂𝒏 500+ 𝒕𝒓𝒂𝒊𝒏𝒊𝒏𝒈 𝒔𝒆𝒔𝒔𝒊𝒐𝒏𝒔 in the areas of 𝐒𝐨𝐟𝐭𝐰𝐚𝐫𝐞 𝐃𝐞𝐯𝐞𝐥𝐨𝐩𝐦𝐞𝐧𝐭, 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐂𝐥𝐨𝐮𝐝, 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐢𝐬, 𝐃𝐚𝐭𝐚 𝐕𝐢𝐬𝐮𝐚𝐥𝐢𝐳𝐚𝐭𝐢𝐨𝐧𝐬, 𝐀𝐫𝐭𝐢𝐟𝐢𝐜𝐢𝐚𝐥 𝐈𝐧𝐭𝐞𝐥𝐥𝐢𝐠𝐞𝐧𝐜𝐞 𝐚𝐧𝐝 𝐌𝐚𝐜𝐡𝐢𝐧𝐞 𝐋𝐞𝐚𝐫𝐧𝐢𝐧𝐠. I am interested in 𝐰𝐫𝐢𝐭𝐢𝐧𝐠 𝐛𝐥𝐨𝐠𝐬, 𝐬𝐡𝐚𝐫𝐢𝐧𝐠 𝐭𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐤𝐧𝐨𝐰𝐥𝐞𝐝𝐠𝐞, 𝐬𝐨𝐥𝐯𝐢𝐧𝐠 𝐭𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐢𝐬𝐬𝐮𝐞𝐬, 𝐫𝐞𝐚𝐝𝐢𝐧𝐠 𝐚𝐧𝐝 𝐥𝐞𝐚𝐫𝐧𝐢𝐧𝐠 new subjects.

Comments

Join the discussion

No comments yet. Be the first to comment.

Programming

Part 1 of 1

More from this blog

ACID Properties

RDBMS works under 4 properties (ACID) Atomicity If any operation is performed on the data, either the entire transaction should be executed or should not be executed at all. Single unit of work

May 20, 20262 min read16

Key Problems Microsoft Fabric Solves

Data Silos Across Tools Problem Organizations use many separate tools for ETL (Data Factory), Warehousing (Synapse/Snowflake), Big Data (Databricks/Hadoop), Visualization (Power BI/Tableau), etc.

Mar 3, 20264 min read13

Unity Catalog vs Hive Metastore

What is Hive Metastore Legacy metadata store for tables and schemas Linked to single Databricks workspace Stores based info : table names, locations, schema Limitation No centralized security across workspaces No column level access control H...

Jul 17, 20251 min read53

Advanced Python Dependency Injection with Pydantic and FastAPI

Introduction Modern backend architectures demand modular, maintainable, and testable code. One of the cornerstones of achieving this is Dependency Injection (DI) — a software design pattern that helps decouple object creation from business logic, ma...

Jun 20, 20255 min read511

Advanced Python Dependency Injection with Pydantic and FastAPI

Building Reactive Python Apps with Async Generators and Streams

Introduction Modern applications increasingly rely on real-time data streams — from chat apps and stock tickers to IoT device feeds, real-time analytics dashboards, and webhooks. The challenge isn’t just speed, but also how to process continuous str...

Jun 20, 20255 min read177

Building Reactive Python Apps with Async Generators and Streams

Naveen P.N's Tech Blog

95 posts

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')

Command Palette