101591 Views
79842 Views
45596 Views
44332 Views
40991 Views
33419 Views
Raspberry Pi Time machine
Now Ad-Free
Guiding Light
Sync Files on your Pis, with Syncthing
NextCloud
Buddy Jr.
Introduction to FreeCAD for Beginners
Building a Robot Arm with Raspberry Pi and PCA9685
Building User Authentication for Static Sites with FastAPI
Mastering Pydantic for Robust Data Validation
Mastering Markdown for Documentation with Jekyll
Introduction to Rust
KevsRobots Learning Platform
30% Percent Complete
By Kevin McAleer, 3 Minutes
Module 5 focuses on modifying data in SQLite tables. You will learn how to update existing records and delete data from tables using SQL statements.
To modify existing data in a SQLite table, we use the SQL UPDATE statement. This statement allows us to specify the table name, the columns to update, and the new values.
UPDATE
# Update a record in the table connection.execute("UPDATE books SET title = ? WHERE id = ?", ("New Title", 1))
In this example, we update the title of a book with an ID of 1 in the “books” table.
To remove data from a SQLite table, we use the SQL DELETE statement. This statement allows us to specify the table name and the conditions for deleting records.
DELETE
# Delete records from the table connection.execute("DELETE FROM books WHERE author = ?", ("F. Scott Fitzgerald",))
In this example, we delete records from the “books” table where the author is “F. Scott Fitzgerald.”
SQLite allows us to perform batch updates and deletes using the executemany() method. This method allows us to execute the same SQL statement multiple times with different parameter values.
executemany()
# Batch update data = [("New Title 1", 1), ("New Title 2", 2), ("New Title 3", 3)] connection.executemany("UPDATE books SET title = ? WHERE id = ?", data)
In this example, we perform a batch update on the “books” table, updating the titles of multiple books based on their IDs.
SQLite supports referential integrity, which means that you can define relationships between tables using foreign keys. When a record in a parent table is deleted, you can specify the desired behavior for associated records in child tables.
# Create tables with foreign key relationship connection.execute(''' CREATE TABLE artists ( id INTEGER PRIMARY KEY, name TEXT ) ''') connection.execute(''' CREATE TABLE albums ( id INTEGER PRIMARY KEY, title TEXT, artist_id INTEGER, FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE ) ''')
In this example, we create two tables: “artists” and “albums.” The “albums” table has a foreign key relationship with the “artists” table. The ON DELETE CASCADE clause ensures that when an artist is deleted, all associated albums are also deleted.
ON DELETE CASCADE
By understanding how to update and delete data, as well as handle cascading deletes and referential integrity, you will have the tools to effectively modify and manage data in SQLite tables.
< Previous Next >