Delete Operations

Delete operations in SQLite are used to remove records or data from a database table. These operations allow you to delete specific data that is no longer needed.

The primary SQL statement for deleting data is the DELETE statement.


99
1
2
3
4
5
6
7
8
9
10
11
12
13
import sqlite3

conn = sqlite3.conn('my_database.db')
cursor = connection.cursor()

# SQL query to delete a specific record from the `articles` table
delete_query = "DELETE FROM articles WHERE id = 1"

# Execute the SQL query to perform the deletion
cursor.execute(delete_query)

conn.commit()
conn.close()

In this code snippet, the first line defines an SQL query that deletes a specific record from the articles table, targeting the record where id is equal to 1. The second line, cursor.execute(delete_query), sends this query to the database for execution.

Deleting Multiple Records

To delete multiple records that match certain criteria, you can use a WHERE clause in the DELETE query:


DELETE FROM articles WHERE author = "John Doe"

This query will delete all records where the author matches "John Doe".

Deleting All Records

You can delete all records from a table by omitting the WHERE clause:


DELETE FROM articles

This query will delete all records in the "articles" table.

These are the basic concepts for performing delete operations in SQLite using Python. You can customize your DELETE queries to remove specific data from your database as needed.

Task

Swipe to start coding

Delete the record from the users table where the id field equals 1.

Solution

import sqlite3

conn = sqlite3.connect('my_database.db')

cursor = conn.cursor()

# Create the `users` table with fields

cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER, user_name TEXT, email TEXT, password TEXT)")

# Insert a records into the `users` table

cursor.execute("INSERT INTO users (id, user_name, email, password) VALUES (?, ?, ?, ?)", (1, "Alex", "AlexMain@gmail.com", "ZXCV2000"))

cursor.execute("INSERT INTO users (id, user_name, email, password) VALUES (?, ?, ?, ?)", (2, "Jamie", "Jamie123@gmail.com", "ASDF3000"))

# Delete the record

query = "DELETE FROM users WHERE id = 1"

cursor.execute(query)

# Execute select query

select_query = "SELECT * FROM users"

cursor.execute(select_query)

# Output

data = cursor.fetchall()

print(data)

# Save changes and close the connection

conn.commit()

conn.close()

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 5