108640 Views
83860 Views
59555 Views
48723 Views
48311 Views
47806 Views
C2Pi-O Laser cut Camera holder
Build a laser-cut robot
Robots and Lasers
Arduino Plug and Make Kit Review
Pi to Pico W Bluetooth Communication
Two-Way Bluetooth Communication Between Raspberry Pi Picos
Introduction to the Linux Command Line on Raspberry Pi OS
How to install MicroPython
Wall Drawing Robot Tutorial
BrachioGraph Tutorial
Intermediate level MicroPython
Introduction to FreeCAD for Beginners
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 >