Performing CRUD Operations in Python
CRUD stands for Create, Read, Update, and Delete. These are the four basic operations you perform when interacting with databases or other data structures. In this tutorial, we'll focus on performing these CRUD operations using Python, specifically for interacting with SQLite and MySQL databases.
Let’s break down each operation and how to perform it in Python.
1. Create: Adding Data to the Database
Creating data involves inserting new records into a database. This is done with the INSERT INTO SQL statement.
1.1 SQLite: Insert Data
import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect("example.db")
cursor = connection.cursor()
# Create the Users table (if it doesn't exist)
cursor.execute('''CREATE TABLE IF NOT EXISTS Users
(ID INTEGER PRIMARY KEY, Name TEXT, Age INTEGER)''')
# Insert a new record into the Users table
cursor.execute("INSERT INTO Users (Name, Age) VALUES (?, ?)", ("Alice", 30))
# Commit the changes
connection.commit()
# Close the connection
cursor.close()
connection.close()
1.2 MySQL: Insert Data
import mysql.connector
# Connect to the MySQL database
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="test_db"
)
cursor = connection.cursor()
# Insert a new record into the Users table
cursor.execute("INSERT INTO Users (Name, Age) VALUES (%s, %s)", ("Bob", 25))
# Commit the changes
connection.commit()
# Close the connection
cursor.close()
connection.close()
2. Read: Retrieving Data from the Database
Reading data involves selecting records from a database. The SELECT SQL statement is used to query data.
2.1 SQLite: Read Data
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()
# Retrieve all records from the Users table
cursor.execute("SELECT * FROM Users")
# Fetch all results
rows = cursor.fetchall()
# Print each record
for row in rows:
print(row)
# Close the connection
cursor.close()
connection.close()
2.2 MySQL: Read Data
import mysql.connector
# Connect to the MySQL database
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="test_db"
)
cursor = connection.cursor()
# Retrieve all records from the Users table
cursor.execute("SELECT * FROM Users")
# Fetch all results
rows = cursor.fetchall()
# Print each record
for row in rows:
print(row)
# Close the connection
cursor.close()
connection.close()
3. Update: Modifying Existing Data
Updating data involves modifying existing records in the database. The UPDATE SQL statement is used to change one or more values in an existing record.
3.1 SQLite: Update Data
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()
# Update a user's age
cursor.execute("UPDATE Users SET Age = ? WHERE Name = ?", (35, "Alice"))
# Commit the changes
connection.commit()
# Close the connection
cursor.close()
connection.close()
3.2 MySQL: Update Data
import mysql.connector
# Connect to the MySQL database
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="test_db"
)
cursor = connection.cursor()
# Update a user's age
cursor.execute("UPDATE Users SET Age = %s WHERE Name = %s", (30, "Bob"))
# Commit the changes
connection.commit()
# Close the connection
cursor.close()
connection.close()
4. Delete: Removing Data from the Database
Deleting data involves removing records from a database. The DELETE SQL statement is used to remove one or more records from a table.
4.1 SQLite: Delete Data
import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()
# Delete a user by name
cursor.execute("DELETE FROM Users WHERE Name = ?", ("Alice",))
# Commit the changes
connection.commit()
# Close the connection
cursor.close()
connection.close()
4.2 MySQL: Delete Data
import mysql.connector
# Connect to the MySQL database
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="test_db"
)
cursor = connection.cursor()
# Delete a user by name
cursor.execute("DELETE FROM Users WHERE Name = %s", ("Bob",))
# Commit the changes
connection.commit()
# Close the connection
cursor.close()
connection.close()
5. Using Try-Except Blocks for Error Handling
Whenever you're performing CRUD operations, it is a good practice to use error handling to catch potential exceptions and ensure that the program doesn’t crash unexpectedly.
Example for SQLite:
import sqlite3
try:
# Connect to SQLite database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()
# Perform CRUD operations
cursor.execute("INSERT INTO Users (Name, Age) VALUES (?, ?)", ("Charlie", 40))
connection.commit()
# Retrieve and display data
cursor.execute("SELECT * FROM Users")
rows = cursor.fetchall()
for row in rows:
print(row)
except sqlite3.Error as error:
print(f"Error occurred: {error}")
finally:
# Ensure that the connection is closed
if connection:
cursor.close()
connection.close()
Example for MySQL:
import mysql.connector
try:
# Connect to MySQL database
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="test_db"
)
cursor = connection.cursor()
# Perform CRUD operations
cursor.execute("INSERT INTO Users (Name, Age) VALUES (%s, %s)", ("David", 32))
connection.commit()
# Retrieve and display data
cursor.execute("SELECT * FROM Users")
rows = cursor.fetchall()
for row in rows:
print(row)
except mysql.connector.Error as error:
print(f"Error occurred: {error}")
finally:
# Ensure that the connection is closed
if connection:
cursor.close()
connection.close()
6. Best Practices
- Parameterized Queries: Always use parameterized queries (e.g., using
?for SQLite or%sfor MySQL) to prevent SQL injection attacks. - Transactions: Use transactions (
commit()) to ensure that changes are saved and can be rolled back in case of errors. - Error Handling: Always handle errors using
try-exceptto ensure smooth execution. - Closing Connections: Always close the database connection after performing operations to release resources.
7. Conclusion
In this tutorial, we learned how to perform the four basic CRUD operations (Create, Read, Update, Delete) using Python with both SQLite and MySQL databases.
Key points:
- Create: Insert data into the database.
- Read: Query and fetch data from the database.
- Update: Modify existing records in the database.
- Delete: Remove records from the database.
These operations are fundamental for any application that interacts with a database, and Python provides powerful tools and libraries like sqlite3 and mysql-connector-python to simplify this process.
