Performing CRUD (Create, Read, Update, Delete) operations

Performing CRUD (Create, Read, Update, Delete) operations is a fundamental aspect of working with databases. In this example, I’ll demonstrate how to perform CRUD operations using Python with SQLite as the database system. SQLite is a serverless, self-contained database engine that is easy to set up and use.

Connecting to SQLite Database:

import sqlite3

# Connect to SQLite database (creates one if not present)
connection = sqlite3.connect('example.db')

# Create a cursor to execute SQL commands
cursor = connection.cursor()

# Execute SQL commands (create a table)
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')

# Commit the changes and close the connection
connection.commit()
connection.close()

Create Operation (Inserting Data):

import sqlite3

# Connect to SQLite database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Execute SQL command (insert data)
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))

# Commit the changes and close the connection
connection.commit()
connection.close()

Read Operation (Querying Data):

import sqlite3

# Connect to SQLite database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Execute SQL command (query data)
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()

# Print the results
for user in users:
    print(user)

# Close the connection
connection.close()

Update Operation (Modifying Data):

import sqlite3

# Connect to SQLite database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Execute SQL command (update data)
cursor.execute('UPDATE users SET age=? WHERE name=?', (25, 'Alice'))

# Commit the changes and close the connection
connection.commit()
connection.close()

Delete Operation (Removing Data):

import sqlite3

# Connect to SQLite database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Execute SQL command (delete data)
cursor.execute('DELETE FROM users WHERE name=?', ('Alice',))

# Commit the changes and close the connection
connection.commit()
connection.close()

These examples illustrate how to perform CRUD operations with SQLite in Python. In a real-world application, you would typically encapsulate these operations in functions or methods, handle exceptions, and use a higher-level abstraction like an ORM (Object-Relational Mapping) library such as SQLAlchemy for more complex scenarios. Additionally, when working with other databases, you may need to adapt the queries and connection setup accordingly.