SQLite with Python

Learn how to create and work with SQLite databases using Python.

What is SQLite?

SQLite is a lightweight relational database that stores data in a single database file.

Python provides the built-in sqlite3 module for working with SQLite databases.

SQLite Flow

Importing sqlite3

The sqlite3 module is used to work with SQLite databases.

import sqlite3

Creating a Database Connection

The connect() function creates a connection to an SQLite database.

import sqlite3

connection = sqlite3.connect("students.db")

print("Database connected")

Output:

Database connected

If the database file does not exist, SQLite creates it.

Creating a Cursor

A cursor is used to execute SQL statements on the database.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

The cursor allows Python to execute SQL commands.

Creating a Table

We can use the SQL CREATE TABLE statement to create a table.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students
(
    id INTEGER PRIMARY KEY,
    name TEXT,
    course TEXT
)
""")

connection.commit()

connection.close()

print("Table created")

Output:

Table created

Inserting Data

The SQL INSERT statement is used to add records to a table.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    ("Rahul", "Python")
)

connection.commit()

connection.close()

print("Student inserted")

Output:

Student inserted

Inserting Multiple Records

students = [
    ("Rahul", "Python"),
    ("Priya", ".NET"),
    ("Amit", "Java")
]

cursor.executemany(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    students
)

connection.commit()

The executemany() method can insert multiple records.

Reading Data

The SQL SELECT statement is used to retrieve records from a table.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM students")

rows = cursor.fetchall()

for row in rows:

    print(row)

connection.close()

Example Output:

(1, 'Rahul', 'Python')
(2, 'Priya', '.NET')
(3, 'Amit', 'Java')

Using fetchone()

The fetchone() method returns one record from the result.

cursor.execute("SELECT * FROM students")

row = cursor.fetchone()

print(row)

Example Output:

(1, 'Rahul', 'Python')

Using fetchall()

The fetchall() method returns all available records from the result.

cursor.execute("SELECT * FROM students")

rows = cursor.fetchall()

print(rows)

Updating Data

The SQL UPDATE statement is used to modify existing records.

cursor.execute(
    "UPDATE students SET course = ? WHERE id = ?",
    ("Python Full Stack", 1)
)

connection.commit()

print("Student updated")

Output:

Student updated

Deleting Data

The SQL DELETE statement is used to remove records from a table.

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (1,)
)

connection.commit()

print("Student deleted")

Output:

Student deleted

Parameterized Queries

Parameterized queries allow values to be passed safely to SQL statements.

name = "Rahul"
course = "Python"

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

connection.commit()

The question mark placeholders are replaced by the supplied values.

Closing the Connection

After database operations are complete, the connection should be closed.

connection.commit()

connection.close()

print("Connection closed")

Output:

Connection closed

Example 🤓👀

The following example creates a database, creates a table, inserts data and displays the records.

import sqlite3

connection = sqlite3.connect("college.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students
(
    id INTEGER PRIMARY KEY,
    name TEXT,
    course TEXT
)
""")

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    ("Amit", "Python")
)

connection.commit()

cursor.execute("SELECT * FROM students")

students = cursor.fetchall()

for student in students:

    print(student)

connection.close()

Example Output:

(1, 'Amit', 'Python')

Advantages of SQLite

Best Practices

Common Mistakes

• Forgetting to call commit() after changing data.
• Forgetting to close the database connection.
• Using incorrect SQL syntax.
• Not using parameterized queries for input values.

Practice Programs

1. Create an SQLite database named college.db.
2. Create a students table with id, name and course.
3. Insert five student records.
4. Display all student records.
5. Update a student's course.
6. Delete a student record.