Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.
Вивчайте Deleting from DB | SQLAlchemy
Databases in Python

book
Deleting from DB

In this chapter, we’ll explore how to delete records from a database using SQLAlchemy. Deleting records is crucial when managing data, especially when removing outdated or unnecessary entries. SQLAlchemy offers simple yet powerful tools for handling deletions in both single and bulk operations.

Deleting a Single Record by ID

The most common way to delete a record is by identifying it through a specific criterion, such as its ID. Let’s see how to delete a product using its ID.

python
product = session.query(Product).filter(Product.id == 1).first()

# Check if the product exists
if product:
# Delete the product
session.delete(product)
session.commit()

The code fetches a product with ID 1, verifies its existence, marks it for deletion with session.delete(product), and applies the change using session.commit().

Deleting Multiple Records

Sometimes, you need to delete multiple records at once based on specific conditions. This is useful for tasks like removing out-of-stock items or products below a price threshold.

python
session.query(Product).filter(Product.is_in_stock == False).delete(synchronize_session="fetch")

# Commit the changes
session.commit()

The query filters out products that are out of stock, deletes them using .delete(synchronize_session="fetch"), and ensures that the session stays synchronized with the database.

Bulk Deletions

For large datasets, bulk deletions are efficient. This method directly modifies database records without loading them into memory, saving time and resources.

python
session.query(Product).filter(Product.price < 100).delete(synchronize_session="fetch")

# Commit the changes
session.commit()

This example filters products priced below $100 and removes them using the .delete() method, providing a fast and resource-efficient solution for large tables.

Deleting All Records in a Table

In cases where you need to clear a table completely, SQLAlchemy makes it easy with a single query.

python
session.query(Product).delete(synchronize_session="fetch")

# Commit the changes
session.commit()

The .delete(synchronize_session="fetch") method removes all records from the Product table, which is useful for resetting data or clearing test environments.

Завдання

Swipe to start coding

Your task is to complete the code by writing the exact lines needed to delete the product named "Headphones" from the database.

Рішення

from sqlalchemy import create_engine, Column, Integer, String, Boolean
from sqlalchemy.orm import declarative_base, sessionmaker

# Initialize the database
engine = create_engine("sqlite:///example.db")
Base = declarative_base()

# Define the Product model
class Product(Base):
__tablename__ = 'products'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
description = Column(String)
price = Column(Integer, nullable=False)
is_in_stock = Column(Boolean, default=True)

# Create tables
Base.metadata.create_all(engine)

# Set up the session
Session = sessionmaker(bind=engine)
session = Session()

# Add multiple products (one at a time)
products_to_add = [
{"name": "Laptop", "description": "High-end gaming laptop", "price": 1500},
{"name": "Smartphone", "description": "Latest model smartphone", "price": 800},
{"name": "Headphones", "description": "Noise-cancelling headphones", "price": 200, "is_in_stock": False}
]

# Iterate through the list and add each product one by one
for product in products_to_add:
new_product = Product(**product) # Unpack the dictionary as keyword arguments
session.add(new_product)
session.commit() # Commit after adding each product

Все було зрозуміло?

Як ми можемо покращити це?

Дякуємо за ваш відгук!

Секція 4. Розділ 8
from sqlalchemy import create_engine, Column, Integer, String, Boolean
from sqlalchemy.orm import declarative_base, sessionmaker

# Initialize the database
engine = create_engine("sqlite:///example.db")
Base = declarative_base()

# Define the Product model
class Product(Base):
__tablename__ = 'products'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
description = Column(String)
price = Column(Integer, nullable=False)
is_in_stock = Column(Boolean, default=True)

# Create tables
Base.metadata.create_all(engine)

# Set up the session
Session = sessionmaker(bind=engine)
session = Session()

# Add multiple products (one at a time)
products_to_add = [
{"name": "Laptop", "description": "High-end gaming laptop", "price": 1500},
{"name": "Smartphone", "description": "Latest model smartphone", "price": 800},
{"name": "Headphones", "description": "Noise-cancelling headphones", "price": 200, "is_in_stock": False}
]

# Iterate through the list and add each product one by one
for product in products_to_add:
new_product = Product(**product) # Unpack the dictionary as keyword arguments
session.add(new_product)
session.commit() # Commit after adding each product

# Delete the product 'Headphones'
headphones_to_delete = session.query(Product).filter(Product.name == "Headphones").first()

if headphones_to_delete:
session.___(___) # Delete the product
session.___() # Commit the deletion
print(f"Deleted product: {headphones_to_delete.name}")

# Retrieve and display all products after deletion
retrieved_products = session.query(Product).all()
print("\nAll products in the database after deletion:")
for product in retrieved_products:
print(product.name)
toggle bottom row
some-alt