In this article, we’ll learn how to perform essential SQL operations—SELECT, UPDATE, and DELETE—using Python with SQLite. These operations will help us retrieve, modify, and remove records from our tables.

In the previous tutorial, we learned how to insert records into tables. Now, we’ll explore how to manage data with these SQL commands.

Selecting records from a Table (SELECT)

The SELECT command allows us to retrieve and display records stored in our tables. Here’s an example of selecting all records from the Book table:

book_list = []
with con:
    cur = con.cursor()
    cur.execute("SELECT * FROM Book")
    rows = cur.fetchall()
    
    for row in rows:
        book_list.append(row)

print(book_list)

Explanation:

  1. cur.execute("SELECT * FROM Book"): Retrieves all rows from the Book table.
  2. cur.fetchall(): Fetches all the rows and stores them in the rows variable.
  3. Looping Through Rows: Each row is added to the book_list for further use or display.

This process can be repeated for other tables by changing the table name in the query.

Updating records in a Table (UPDATE)

The UPDATE command allows us to modify records in a table. To update a specific record, we identify it by its id and execute the following query:

values = ["Juliet Didn't Love Romeo", 0]
with con:
    cur = con.cursor()
    query = "UPDATE Book SET title = ? WHERE id = ?"
    cur.execute(query, values)

Explanation:

  1. title = ?: Specifies the column to update and uses a placeholder for the new value.
  2. WHERE id = ?: Ensures only the record with the matching id is updated.
  3. cur.execute(query, values): Executes the query, replacing placeholders with values from the values list.

Verifying the Update:

To confirm the update, use the SELECT command to retrieve and display the modified record.

Deleting records from a Table (DELETE)

The DELETE command removes records from a table. To delete a specific record, use the following query:

value = [0]
with con:
    cur = con.cursor()
    query = "DELETE FROM Book WHERE id = ?"
    cur.execute(query, value)

Explanation:

  1. DELETE FROM Book: Specifies the table from which the record will be removed.
  2. WHERE id = ?: Ensures only the record with the matching id is deleted.
  3. cur.execute(query, value): Executes the query, replacing the placeholder with the id value.

This process can also be applied to other tables by modifying the table name in the query.

Best practices for managing data

  1. Backup Data: Always back up your database before performing DELETE or bulk UPDATE operations.
  2. Use Transactions: Group multiple operations in a transaction to ensure data integrity.
  3. Validate Inputs: Always sanitize or validate user inputs to prevent SQL injection.

Conclusion

By mastering these fundamental operations—SELECT, UPDATE, and DELETE—you can effectively manage data in your SQLite tables using Python. These skills are essential for building robust and dynamic database-driven applications.

In the next tutorial, we’ll dive into advanced queries and database optimization techniques. Stay tuned!

 

Copyright Statement: Unless stated otherwise, all articles are original to this site, please credit the source when sharing.

Article link:http://pybeginners.com/sql-tutorial/python-with-sqlite-how-to-select-update-and-delete-sql-data/

License Agreement:Attribution-NonCommercial 4.0 International License