SQLite Basics

After learning what databases are, the best place to start is SQLite.

SQLite is simple, lightweight, and already included with Python.

That makes it perfect for beginners.

What Is SQLite?

SQLite is a relational database management system (RDBMS).

Unlike larger database systems, SQLite stores the entire database in a single file.

Example:

school.db

That file contains your tables, rows, and data.

Why SQLite Is Great for Beginners

SQLite is popular because it is:

• easy to start
• no server required
• fast for small to medium apps
• portable
• built into Python through sqlite3
• widely used in real software

Where SQLite Is Used

SQLite is used in:

• mobile apps
• desktop apps
• browser storage
• prototypes
• local tools
• small business systems

Many real products use SQLite behind the scenes.

SQLite vs Other Databases

SQLite

• single file
• no setup server
• local projects
• simple deployment

PostgreSQL / MySQL

• separate server software
• multi-user systems
• larger production apps
• advanced scaling tools

SQLite is the perfect first step.

Using SQLite in Python

Python includes the built-in module:

import sqlite3

This lets Python connect to SQLite databases.

Create or Connect to a Database

import sqlite3

conn = sqlite3.connect("school.db")

If the file does not exist, SQLite creates it.

If it exists, Python connects to it.

What Is a Connection?

The connection object represents the link between Python and the database.

Example:

conn = sqlite3.connect("school.db")

You use it to send SQL commands.

What Is a Cursor?

A cursor is used to execute SQL queries.

Create one like this:

cursor = conn.cursor()

Think of the cursor as your SQL command tool.

First SQL Command: Create Table

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")

conn.commit()
conn.close()

Explanation

CREATE TABLE

Creates a new table.

INTEGER PRIMARY KEY

Unique id for each row.

TEXT

Stores text.

commit()

Saves changes permanently.

close()

Closes the connection.

Insert Data

cursor.execute("""
INSERT INTO students (name, age)
VALUES ('Maya', 22)
""")

conn.commit()

This adds one row.

Read Data

cursor.execute("SELECT * FROM students")

rows = cursor.fetchall()

print(rows)

Example output:

[(1, 'Maya', 22)]

fetchall() vs fetchone()

fetchall()

Returns all rows.

fetchone()

Returns one row only.

Example:

row = cursor.fetchone()

Full Example

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")

cursor.execute("""
INSERT INTO students (name, age)
VALUES ('Tom', 25)
""")

conn.commit()

cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

conn.close()

Expected output:

[(1, 'Tom', 25)]

Common Beginner Errors

Table Already Exists

Running CREATE TABLE again causes an error.

Use:

CREATE TABLE IF NOT EXISTS students (...)

Forgetting commit()

Data changes may not save.

Forgetting close()

Always close connections.

SQL Typing Mistakes

Check commas, brackets, quotes, and keywords.

Code Along

Create database:

library.db

Create table:

books(id, title, year)

Insert one book and print all rows.

Mini Challenge

Build:

shop.db

Tasks:

1. Create table products

   • id
   • name
   • price

2. Insert:

• Pen, 2
• Book, 5

3. Print all rows

Expected output:

[(1, 'Pen', 2), (2, 'Book', 5)]

Real World Use Case

SQLite is often used for local business tools, note apps, desktop software, prototypes, and offline systems.

Quiz

1. What is SQLite?
2. Why is SQLite beginner-friendly?
3. What does cursor.execute() do?
4. Why is commit() important?
5. What does fetchall() return?

Assignment

Create your own database file with one table and two inserted rows.

Summary

You learned how SQLite works, how Python connects using sqlite3, and how to create tables, insert data, and read records.