
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.
Top comments(0)
For further actions, you may consider blocking this person and/orreporting abuse