Working with Databases Using SQLite in Python

Introduction

Databases are essential for storing, organizing, and retrieving data efficiently. Whether it’s a simple desktop app or a data-driven web application, managing data effectively is key.

If you’re starting your journey with databases or need a lightweight option for your project, SQLite is a great choice. It’s fast, reliable, and comes built into Python; no installation or configuration is required. In this post, we’ll explore how to use SQLite with Python to create, read, update, and delete data with just a few lines of code.

---

What is SQLite?

SQLite is a self-contained, serverless database engine. Unlike MySQL or PostgreSQL, it doesn’t require a separate server to run your entire database lives in a single .db file.

Key advantages:

•  Lightweight and fast

•  No setup required

•  Ideal for small apps, data analysis, or prototypes

•  Built directly into Python’s standard library

Step-by-Step Guide: Using SQLite in Python

You can start using it instantly by importing Python’s built-in module:

import sqlite3

1) Create a Connection

To start, connect to a database. If it doesn’t exist, SQLite will create it automatically.

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

2) Create a Table

Now, let’s create a simple table to store student information.

cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    grade TEXT
)
''')
conn.commit()

3) Insert Data

You can easily insert records into the table.

c.execute("insert into customer values ('John','Stathom','C')")

You can easily insert multiple records into the table.

many_students = [

                    ('Mark',16,'A'),

                    ('Steve',13,'D'),

                    ('Mark',15,'B')

                 ]

cursor.execute("INSERT INTO students VALUES (?, ?, ?)",  many_students)

conn.commit()

4) Retrieve Data

Fetching records is simple with SELECT.

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
    print(row)

5)Update or Delete Records

Updating or deleting data uses standard SQL commands.

cursor.execute("UPDATE students SET grade = 'A+' WHERE name = 'Alice'")
cursor.execute("DELETE FROM students WHERE age < 18")
conn.commit()

6) Close the Connection

Always close your connection once you’re done.

conn.close()