CRUD (Create, Read, Update, Delete) operations are fundamental procedures used to interact with data in a database. Mastering CRUD operations is essential for any developer working with databases and APIs, as it allows you to create, retrieve, update, and delete records efficiently. In this lesson, we will learn about each operation, understand key terminology, and explore practical examples using Python.
The Create operation is used to insert new records into a database table. Here's an example of how to create a new record using the sqlite3
library in Python:
import sqlite3
conn = sqlite3.connect('example.db') # Connect to the database
c = conn.cursor() # Create a cursor object to execute queries
# Execute an SQL command to create a new table if it doesn't exist
c.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')
# Insert a new record into the users table
c.execute("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com')")
conn.commit() # Commit the changes to the database
conn.close() # Close the connection
The Read operation is used to retrieve data from a database table. Here's an example of how to read all records from the users
table:
import sqlite3
conn = sqlite3.connect('example.db') # Connect to the database
c = conn.cursor() # Create a cursor object to execute queries
# Execute an SQL command to select all records from the users table
c.execute("SELECT * FROM users")
rows = c.fetchall() # Fetch all rows as a list of tuples
for row in rows:
print(row)
conn.close() # Close the connection
The Update operation is used to modify existing records in a database table. Here's an example of how to update John Doe's email address:
import sqlite3
conn = sqlite3.connect('example.db') # Connect to the database
c = conn.cursor() # Create a cursor object to execute queries
# Execute an SQL command to update John Doe's email address
c.execute("UPDATE users SET email = 'john.doe_new@example.com' WHERE name = 'John Doe'")
conn.commit() # Commit the changes to the database
conn.close() # Close the connection
The Delete operation is used to remove records from a database table. Here's an example of how to delete John Doe's record:
import sqlite3
conn = sqlite3.connect('example.db') # Connect to the database
c = conn.cursor() # Create a cursor object to execute queries
# Execute an SQL command to delete John Doe's record
c.execute("DELETE FROM users WHERE name = 'John Doe'")
conn.commit() # Commit the changes to the database
conn.close() # Close the connection
In addition to the examples above, you can find more practical exercises and projects on CRUD operations in databases using Python online resources like Real Python and W3Schools.
What causes it: Misspelled variable or function names.
# Bad code example that triggers a NameError
print(db) # db hasn't been defined yet
Error message:
NameError: name 'db' is not defined
Solution: Define the variable before using it.
# Corrected code
conn = sqlite3.connect('example.db') # Connect to the database
db = conn # Assign the connection object to a variable for easier use later
print(db) # db is now defined and can be used
Why it happens: Variable or function names are not correctly spelled, causing Python to raise a NameError.
How to prevent it: Double-check that all variable and function names are spelled correctly before running the code.
What causes it: Incorrect data types when expecting another data type.
# Bad code example that triggers a TypeError
c.execute("INSERT INTO users (name, email) VALUES ('John Doe', 1234567890)")
Error message:
TypeError: Incorrect number of arguments for sqlite3.Cursor.execute() given
Solution: Provide the correct data types when executing an SQL command.
# Corrected code
c.execute("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com')")
Why it happens: Incorrect data type is passed to the function that expects another data type, causing a TypeError.
How to prevent it: Ensure that data types provided match the expected types in the SQL command.
What causes it: Accessing an undefined key in a dictionary.
# Bad code example that triggers a KeyError
print(user['email']) # user dictionary doesn't have an 'email' key
Error message:
KeyError: 'email'
Solution: Check if the key exists before accessing its value.
# Corrected code
if 'email' in user:
print(user['email'])
else:
print("No email found for this user.")
Why it happens: Trying to access a key that doesn't exist in the dictionary, causing a KeyError.
How to prevent it: Always check if the key exists before attempting to access its value.
sqlite3
library supports parameterized queries using question marks (?) in place of values.In this lesson, we learned about CRUD operations (Create, Read, Update, Delete) and their importance in working with databases using Python. We explored practical examples for each operation and discussed common errors and solutions related to CRUD operations. To continue learning, consider exploring Object Relational Mappers like SQLAlchemy or Django ORM, and working on more complex database projects. Happy coding!