Posted onFeb 26, 2024 • Edited onFeb 28, 2024

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, thesqlite3 module provides a simple and efficient way to interact with SQLite databases. This chapter explores the capabilities ofsqlite3 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

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Connect to an in-memory SQLite database# conn = sqlite3.connect(database=":memory:")

Creating tables and defining table schema

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Define table schema and create tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (                    id INTEGER PRIMARY KEY,                    name TEXT NOT NULL,                    age INTEGER,                    department TEXT                 )""")# Commit changes and close the cursorconn.commit()cursor.close()

Inserting data into tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Insert data into the 'employees' tablecursor.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 cursorconn.commit()cursor.close()

Reading data from tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Read data from the 'employees' tablecursor.execute("SELECT name, age, department FROM employees")rows=cursor.fetchall()forrowinrows:print(row)# Close the cursorcursor.close()

Output:

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

Updating data in tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Update data in the 'employees' tablecursor.execute("UPDATE employees SET department ='IT' WHERE name ='John Doe'")# Commit changes and close the cursorconn.commit()cursor.close()

Removing data from tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Delete data from the 'employees' tablex=cursor.execute("DELETE FROM employees WHERE name ='Jane Smith'")# Commit changes and close the cursorconn.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 likeDBeaver Community offer comprehensive tools for interacting with SQLite database files in a graphical user interface, providing an alternative approach to database management.