What are CRUD operations in Python databases?

Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). Use parameterized queries for security.

What is SQLAlchemy ORM and how to use it with SQLite?

SQLAlchemy is an ORM that maps Python classes to tables. Define models with declarative_base(), use sessions for CRUD, supports SQLite via sqlite:/// URL.

How to connect to SQLite in Python?

Use sqlite3.connect('database.db') for raw SQL or create_engine('sqlite:///database.db') for SQLAlchemy.

What are common mistakes in Python database handling?

Not using parameterized queries (SQL injection risk), forgetting commit(), not closing connections/sessions, hardcoding paths.

What are best practices for Python database handling?

Use parameterized queries, always commit changes, close resources, handle exceptions, use context managers or sessions properly.

Python Database Handling: SQLite with sqlite3 & SQLAlchemy ORM

Q: What is SQLite and how does the sqlite3 module work?

SQLite is a lightweight, file-based database. The built-in sqlite3 module provides connect(), cursor(), execute(), commit(), and close() for SQL operations.

1. What is SQLite, and how does the sqlite3 module work in Python?

Q: What is SQLite and sqlite3?

SQLite: A lightweight, serverless, file-based relational database engine included with Python via the sqlite3 module.

sqlite3 Module: Python's built-in module for interacting with SQLite databases.

Key Classes/Functions:

sqlite3.connect(database): Connects to a database (creates if not exists).
connection.cursor(): Creates a cursor for executing SQL queries.
cursor.execute(query, params): Runs an SQL query with optional parameters.
connection.commit(): Saves changes.
connection.rollback(): Reverts changes on error.
connection.close(): Closes the database connection.

Use Case: Small to medium-sized applications, prototyping, or embedded systems.

Note: SQLite stores data in a single file (e.g., database.db), making it portable but less suited for high-concurrency applications.

2. Can you give an example of using sqlite3 for basic database operations?

import sqlite3

# Connect to database
conn = sqlite3.connect("employees.db")
cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        salary REAL
    )
""")

# Insert data
cursor.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ("Krishna", 60000.0))
cursor.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ("Kristal", 55000.0))
conn.commit()

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

# Close connection
conn.close()

Employees: (1, 'Krishna', 60000.0) (2, 'Kristal', 55000.0)

Note: Creates employees.db and a table if not exists. Uses parameterized queries (?) for safe insertion. fetchall() retrieves all rows; commit() saves changes.

3. What are CRUD operations, and how are they implemented with sqlite3?

Q: What is CRUD?

CRUD: Create, Read, Update, Delete – the four basic database operations.

Create: Insert new records (INSERT INTO).
Read: Retrieve records (SELECT).
Update: Modify existing records (UPDATE).
Delete: Remove records (DELETE).

Implementation: Use sqlite3's cursor.execute() with SQL queries, parameterized for security.

Use Case: Managing data in applications (e.g., user records, inventory).

4. Can you give an example of CRUD operations with sqlite3?

import sqlite3

def perform_crud():
    # Connect to database
    conn = sqlite3.connect("employees.db")
    cursor = conn.cursor()

    # Create table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            salary REAL
        )
    """)

    # Create (INSERT)
    cursor.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ("Krishna", 60000.0))
    cursor.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ("Kristal", 55000.0))
    conn.commit()
    print("After Create:")
    cursor.execute("SELECT * FROM employees")
    print(cursor.fetchall())

    # Read (SELECT)
    cursor.execute("SELECT * FROM employees WHERE salary > ?", (55000,))
    print("\nRead (salary > 55000):")
    print(cursor.fetchall())

    # Update
    cursor.execute("UPDATE employees SET salary = ? WHERE name = ?", (65000.0, "Krishna"))
    conn.commit()
    print("\nAfter Update:")
    cursor.execute("SELECT * FROM employees")
    print(cursor.fetchall())

    # Delete
    cursor.execute("DELETE FROM employees WHERE name = ?", ("Kristal",))
    conn.commit()
    print("\nAfter Delete:")
    cursor.execute("SELECT * FROM employees")
    print(cursor.fetchall())

    # Close connection
    conn.close()

if __name__ == "__main__":
    try:
        perform_crud()
    except sqlite3.Error as e:
        print(f"Database error: {e}")

After Create: [(1, 'Krishna', 60000.0), (2, 'Kristal', 55000.0)] Read (salary > 55000): [(1, 'Krishna', 60000.0)] After Update: [(1, 'Krishna', 65000.0), (2, 'Kristal', 55000.0)] After Delete: [(1, 'Krishna', 65000.0)]

Note: Demonstrates Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). Uses parameterized queries to prevent SQL injection. Handles errors with try/except.

5. What is SQLAlchemy ORM, and how is it used with SQLite?

Q: What is SQLAlchemy ORM?

SQLAlchemy ORM: An Object-Relational Mapping (ORM) library that maps Python classes to database tables and objects to rows.

Components:

create_engine: Connects to the database (e.g., SQLite).
declarative_base: Defines a base class for ORM models.
sessionmaker: Creates sessions for database operations.

Benefits: Abstracts SQL queries, simplifies CRUD, supports multiple databases.

Use Case: Complex applications requiring database abstraction and portability.

Note: Requires pip install sqlalchemy.

6. Can you give an example of SQLAlchemy ORM with SQLite?

from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create engine and base
engine = create_engine("sqlite:///employees_orm.db", echo=False)
Base = declarative_base()

# Define Employee model
class Employee(Base):
    __tablename__ = "employees"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    salary = Column(Float)

# Create tables
Base.metadata.create_all(engine)

# Create session
Session = sessionmaker(bind=engine)
session = Session()

# CRUD operations
def perform_crud_orm():
    # Create
    emp1 = Employee(name="Krishna", salary=60000.0)
    emp2 = Employee(name="Kristal", salary=55000.0)
    session.add_all([emp1, emp2])
    session.commit()
    print("After Create:")
    for emp in session.query(Employee).all():
        print(f"ID: {emp.id}, Name: {emp.name}, Salary: {emp.salary}")

    # Read
    print("\nRead (salary > 55000):")
    for emp in session.query(Employee).filter(Employee.salary > 55000):
        print(f"ID: {emp.id}, Name: {emp.name}, Salary: {emp.salary}")

    # Update
    emp = session.query(Employee).filter_by(name="Krishna").first()
    emp.salary = 65000.0
    session.commit()
    print("\nAfter Update:")
    for emp in session.query(Employee).all():
        print(f"ID: {emp.id}, Name: {emp.name}, Salary: {emp.salary}")

    # Delete
    session.query(Employee).filter_by(name="Kristal").delete()
    session.commit()
    print("\nAfter Delete:")
    for emp in session.query(Employee).all():
        print(f"ID: {emp.id}, Name: {emp.name}, Salary: {emp.salary}")

if __name__ == "__main__":
    try:
        perform_crud_orm()
    except Exception as e:
        print(f"Error: {e}")
    finally:
        session.close()

After Create: ID: 1, Name: Krishna, Salary: 60000.0 ID: 2, Name: Kristal, Salary: 55000.0 Read (salary > 55000): ID: 1, Name: Krishna, Salary: 60000.0 After Update: ID: 1, Name: Krishna, Salary: 65000.0 ID: 2, Name: Kristal, Salary: 55000.0 After Delete: ID: 1, Name: Krishna, Salary: 65000.0

Note: Defines Employee model mapping to the employees table. Uses session for CRUD operations with ORM syntax. echo=False disables SQL logging; set to True for debugging. Ensures session closure in finally.

7. Comprehensive example + Common Mistakes & Best Practices

Q: Common mistakes?

Not using parameterized queries → SQL injection risk.
Forgetting commit() → changes lost.
Not closing connections/sessions → resource leaks.
Hardcoding paths → poor portability.

Q: Best practices?

Always use parameterized queries.
Use with sqlite3.connect() as conn or proper session management.
Handle exceptions (sqlite3.Error).
Use IF NOT EXISTS for tables.
For SQLAlchemy: define constraints, use relationships wisely.

Comprehensive Example (sqlite3 + SQLAlchemy):

import sqlite3
from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# SQLite with sqlite3
def sqlite3_crud():
    conn = sqlite3.connect("employees.db")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            salary REAL
        )
    """)
    
    # Create
    cursor.execute("INSERT OR IGNORE INTO employees (name, salary) VALUES (?, ?)", ("Krishna", 60000.0))
    cursor.execute("INSERT OR IGNORE INTO employees (name, salary) VALUES (?, ?)", ("Kristal", 55000.0))
    conn.commit()
    
    # Read
    cursor.execute("SELECT * FROM employees")
    print("SQLite3 - All Employees:")
    for row in cursor.fetchall():
        print(row)
    
    conn.close()

# SQLAlchemy ORM
engine = create_engine("sqlite:///employees_orm.db", echo=False)
Base = declarative_base()

class Employee(Base):
    __tablename__ = "employees"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    salary = Column(Float)

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

def sqlalchemy_crud():
    session = Session()
    
    # Create
    emp1 = Employee(name="Ram", salary=70000.0)
    emp2 = Employee(name="Ram", salary=65000.0)
    session.add_all([emp1, emp2])
    session.commit()
    
    # Read
    print("\nSQLAlchemy - All Employees:")
    for emp in session.query(Employee).all():
        print(f"ID: {emp.id}, Name: {emp.name}, Salary: {emp.salary}")
    
    # Update
    emp = session.query(Employee).filter_by(name="Ram").first()
    emp.salary = 75000.0
    session.commit()
    
    # Delete
    session.query(Employee).filter_by(name="Ram").delete()
    session.commit()
    
    print("\nSQLAlchemy - After Update/Delete:")
    for emp in session.query(Employee).all():
        print(f"ID: {emp.id}, Name: {emp.name}, Salary: {emp.salary}")
    
    session.close()

if __name__ == "__main__":
    try:
        print("Running SQLite3 CRUD:")
        sqlite3_crud()
        print("\nRunning SQLAlchemy CRUD:")
        sqlalchemy_crud()
    except (sqlite3.Error, Exception) as e:
        print(f"Error: {e}")

Running SQLite3 CRUD: SQLite3 - All Employees: (1, 'Krishna', 60000.0) (2, 'Kristal', 55000.0) Running SQLAlchemy CRUD: SQLAlchemy - All Employees: ID: 1, Name: Ram, Salary: 70000.0 ID: 2, Name: Ram, Salary: 65000.0 SQLAlchemy - After Update/Delete: ID: 1, Name: Ram, Salary: 75000.0