Perform Database CRUD Operation in Python

Perform Database CRUD Operation in Python

Introduction:
CRUD (Create, Read, Update, Delete) operations are essential when working with databases. In Python, we can interact with databases using various libraries, with the most common being SQLite and MySQL. Below, we will demonstrate how to perform CRUD operations in Python using SQLite as an example.

1. Setting up SQLite Database:
First, make sure you have SQLite installed. You can check by running sqlite3 --version in your terminal.

2. Connecting to the Database:
To connect to an SQLite database in Python, use the sqlite3 module. Here's a sample code to establish a connection:

python
import sqlite3

# Connect to the database (creates a new one if it doesn't exist)
conn = sqlite3.connect('example.db')

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

3. Creating a Table:
Let's create a table named users to store user information:

python
# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    age INTEGER)''')

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

4. Inserting Data:
To insert data into the users table:

python
# Insert a new user
user_data = ('John Doe', 30)
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', user_data)

# Commit changes
conn.commit()

5. Reading Data:
To retrieve data from the users table:

python
# Fetch all users
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()
for user in all_users:
    print(f"ID: {user[0]}, Name: {user[1]}, Age: {user[2]}")

6. Updating Data:
To update existing data:

python
# Update user's age
new_age = 32
user_id = 1
cursor.execute('UPDATE users SET age = ? WHERE id = ?', (new_age, user_id))
conn.commit()

7. Deleting Data:
To delete data from the table:

python
# Delete a user by ID
user_id = 2
cursor.execute('DELETE FROM users WHERE id = ?', (user_id,))
conn.commit()

8. Closing the Connection:
Remember to close the connection after performing the CRUD operations:

python
# Close the connection
conn.close()

Conclusion:
With the above code examples, you can perform CRUD operations in Python using an SQLite database. Similar approaches can be used with other database systems as well, adjusting the connection and queries accordingly.