Q: How can you create a simple Python script to manage a SQLite database for storing and retrieving user information, including adding new users, displaying all users, and searching by name? Provide a step-by-step example with basic error handling.

A:

import sqlite3

# Connect to database (creates if not exists)
conn = sqlite3.connect('users.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL
)
''')
conn.commit()

# Add user function
def add_user(name, email):
    try:
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
        conn.commit()
        print(f"User {name} added.")
    except sqlite3.Error as e:
        print(f"Error: {e}")

# Display all users
def show_users():
    cursor.execute("SELECT * FROM users")
    users = cursor.fetchall()
    if users:
        for user in users:
            print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}")
    else:
        print("No users found.")

# Search user by name
def search_user(name):
    cursor.execute("SELECT * FROM users WHERE name LIKE ?", ('%' + name + '%',))
    results = cursor.fetchall()
    if results:
        for user in results:
            print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}")
    else:
        print("No user found.")

# Example usage
add_user("Alice", "[email protected]")
add_user("Bob", "[email protected]")

print("\nAll users:")
show_users()

print("\nSearching for 'Alice':")
search_user("Alice")

# Close connection
conn.close()

#Python #SQLite #Databases #Beginner #Programming #DatabaseManagement #SimpleCode #DataStorage #LearningPython

By: @DataScienceQ 🚀

1. What is a database?
2. Why do we use databases in Python?
3. Name a popular database library for Python.
4. How do you connect to a SQLite database in Python?
5. What is the purpose of cursor() in database operations?
6. How do you execute a query in Python using SQLite?

---

Explanation with Code Example (Beginner Level):

import sqlite3

# 1. Create a connection to a database (or create it if not exists)
conn = sqlite3.connect('example.db')

# 2. Create a cursor object to interact with the database
cursor = conn.cursor()

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

# 4. Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 30)")

# 5. Commit changes
conn.commit()

# 6. Query the data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close connection
conn.close()

This example shows how to:
- Connect to a SQLite database.
- Create a table.
- Insert and retrieve data.

Answer:
1. A database is an organized collection of data.
2. We use databases to store, manage, and retrieve data efficiently.
3. sqlite3 is a popular library.
4. Use sqlite3.connect() to connect.
5. cursor() allows executing SQL commands.
6. Use cursor.execute() to run queries.

#Python #Databases #SQLite #Beginner #Programming #Coding #LearnToCode

By: @DataScienceQ 🚀