Exploring sqlite3 in Python

Introduction

SQLite is a lightweight, embedded SQL database engine that is widely used for local data storage in various applications. In Python, the sqlite3 module provides a simple and efficient way to interact with SQLite databases. This chapter explores the capabilities of sqlite3 in Python, covering topics such as database creation, table manipulation, data insertion and retrieval, as well as data modification and deletion.

Topics

• Difference between in-memory and file-based SQLite databases
• Creating a database and establishing a connection
• Creating tables and defining table schema
• Inserting data into tables
• Reading data from tables (inner join, left join)
• Updating data in tables
• Removing data from tables

Difference between in-memory and file-based SQLite databases

• In-memory SQLite databases reside entirely in the system's RAM and are not persisted to disk. They offer high performance but are suitable only for temporary data storage.
• File-based SQLite databases are stored as files on the disk. They provide persistent data storage and are ideal for long-term data retention.

Creating a database and establishing a connection

import sqlite3

# Connect to a file-based SQLite database
conn = sqlite3.connect(database="example.db")

# Connect to an in-memory SQLite database
# conn = sqlite3.connect(database=":memory:")

Creating tables and defining table schema

import sqlite3

# Connect to a file-based SQLite database
conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Define table schema and create table
cursor.execute("""CREATE TABLE IF NOT EXISTS employees (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    age INTEGER,
                    department TEXT
                 )""")

# Commit changes and close the cursor
conn.commit()
cursor.close()

Inserting data into tables

import sqlite3

# Connect to a file-based SQLite database
conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Insert data into the 'employees' table
cursor.execute("INSERT INTO employees (name, age, department) VALUES (?, ?, ?)", ('John Doe', 30, 'HR'))
cursor.execute("INSERT INTO employees (name, age, department) VALUES (?, ?, ?)", ('Jane Smith', 35, 'Finance'))

# Commit changes and close the cursor
conn.commit()
cursor.close()

Reading data from tables

import sqlite3

# Connect to a file-based SQLite database
conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Read data from the 'employees' table
cursor.execute("SELECT name, age, department FROM employees")
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the cursor
cursor.close()

Output:

(1, 'John Doe', 30, 'HR')
(2, 'Jane Smith', 35, 'Finance')

Updating data in tables

import sqlite3

# Connect to a file-based SQLite database
conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Update data in the 'employees' table
cursor.execute("UPDATE employees SET department = 'IT' WHERE name = 'John Doe'")

# Commit changes and close the cursor
conn.commit()
cursor.close()

Removing data from tables

import sqlite3

# Connect to a file-based SQLite database
conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Delete data from the 'employees' table
x = cursor.execute("DELETE FROM employees WHERE name = 'Jane Smith'")

# Commit changes and close the cursor
conn.commit()
cursor.close()

Conclusion

The sqlite3 module in Python provides a convenient interface for working with SQLite databases, allowing developers to create, manipulate, and query databases seamlessly. By mastering the functionalities of sqlite3, developers can build robust and efficient data storage solutions for a wide range of applications, from small-scale projects to enterprise-level systems.

Additionally, for those who prefer visual management of SQLite databases, applications like DBeaver Community offer comprehensive tools for interacting with SQLite database files in a graphical user interface, providing an alternative approach to database management.