SQLite CRUD in Python Tutorial 📝

Welcome to our SQLite CRUD (Create, Read, Update, Delete) tutorial! In this lesson, we'll learn how to work with databases using Python. No prior experience is necessary, and we'll cover everything from the basics to advanced examples. Let's get started!

What is SQLite? 💡

SQLite is a lightweight, file-based database that doesn't require a separate server to run. It's an excellent choice for small applications and prototyping due to its simplicity and ease of use.

Creating a Database 🎯

To create a SQLite database in Python, we'll use the sqlite3 module that comes pre-installed with Python.

python

import sqlite3

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

Creating Tables 📝

Tables are where we store our data. To create a table, we'll use the cursor object's execute() method.

python

cursor = conn.cursor()

# Create a table called 'users' with columns 'id', 'name', and 'email'
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);
''')

Inserting Data (Create) 🎯

Now that we have our table, let's insert some data!

python

# Insert a new user with name 'John Doe' and email 'john.doe@example.com'
cursor.execute('''
INSERT INTO users (name, email)
VALUES ('John Doe', 'john.doe@example.com');
''')

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

Reading Data (Read) 📝

To read data from the database, we'll use the execute() method again, this time with a SELECT statement.

python

# Reconnect to the database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Fetch all users
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the connection
conn.close()

Updating Data (Update) 🎯

Updating data involves modifying existing records. Here's how to update John Doe's email.

python

# Reconnect to the database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Update John Doe's email to 'john.doe_updated@example.com'
cursor.execute('''
UPDATE users
SET email = 'john.doe_updated@example.com'
WHERE name = 'John Doe';
''')

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

Deleting Data (Delete) 📝

Deleting data is straightforward. To delete John Doe, we'll use the DELETE statement.

python

# Reconnect to the database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Delete John Doe
cursor.execute('''
DELETE FROM users
WHERE name = 'John Doe';
''')

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

Quiz 🎯

Wrapping Up 📝

Congratulations on learning the basics of SQLite CRUD in Python! We've covered creating databases, tables, inserting, reading, updating, and deleting data. Now you're ready to take on your own projects and upskill even further.

Remember, practice is key. Keep experimenting with SQLite and Python to become proficient. Happy coding! 🎉