Course contentsShow
← Python
Lesson 829 of 1,06013. Data Integration and Web APIsFree lesson

Performing CRUD Operations with SQL

Learners will implement Create, Read, Update, and Delete operations to manage data lifecycle within a relational database table.

In this lesson, you will learn how to bridge your Python applications with relational databases to manage the complete lifecycle of data using SQL CRUD operations.

Managing Data Lifecycles with SQL

CRUD stands for Create, Read, Update, and Delete. These four operations form the backbone of any data-driven application. By using Python’s sqlite3 module, you can execute these commands directly against a database file to persist information beyond the runtime of your script.

To ensure your operations are secure, always use parameterized queries (the ? placeholder) to prevent SQL injection vulnerabilities.

Implementing CRUD Logic

Here is how you perform these operations using a cursor object:

import sqlite3

connection = sqlite3.connect("app_data.db")
cursor = connection.cursor()

# 1. CREATE: Insert a new record
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))

# 2. READ: Fetch records
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
user = cursor.fetchone()

# 3. UPDATE: Modify existing records
cursor.execute("UPDATE users SET email = ? WHERE name = ?", ("new_alice@example.com", "Alice"))

# 4. DELETE: Remove a record
cursor.execute("DELETE FROM users WHERE name = ?", ("Alice",))

# Commit changes to save the transaction
connection.commit()
connection.close()

Essential Best Practices

  • Transactions: Remember that INSERT, UPDATE, and DELETE operations modify the database state. You must call connection.commit() to finalize these changes, or they will be discarded when the connection closes.
  • The Cursor: The cursor acts as a pointer to the database, allowing you to traverse records returned by SELECT queries. Always use fetchone() or fetchall() to retrieve your data after execution.
  • Parameterization: Never use f-strings to inject user input into SQL strings. Always pass variables as a tuple in the second argument of cursor.execute() to keep your data operations secure.

Key Takeaway: CRUD operations represent the essential lifecycle of persistent data, and by combining parameterized queries with connection commits, you ensure your database remains both secure and synchronized with your application state.