week 5

Week 5: SQL with SQLAlchemy - 40 Snippets

This week covers SQLAlchemy ORM, CRUD operations, relational databases, and Faker for generating test data. Here are 40 Python snippets categorized into 4 topics (10 per topic).

---

1️⃣ Introduction to Relational Databases (10 Snippets)

1. Connecting to SQLite Database

from sqlalchemy import create_engine

engine = create_engine("sqlite:///example.db")
print("Connected to database!")

2. Connecting to MySQL

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://user:password@localhost/mydb")
print("Connected to MySQL!")

3. Connecting to PostgreSQL

from sqlalchemy import create_engine

engine = create_engine("postgresql://user:password@localhost/mydb")
print("Connected to PostgreSQL!")

4. Creating a Table using SQLAlchemy

from sqlalchemy import Column, Integer, String, MetaData, Table

metadata = MetaData()
users = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
    Column("age", Integer)
)
metadata.create_all(engine)

5. Viewing Table Schema

print(users.columns.keys())

6. Dropping a Table

users.drop(engine)

7. Fetching All Tables in a Database

print(engine.table_names())

8. Using Transactions in SQLAlchemy

with engine.begin() as conn:
    conn.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")

9. Checking if a Table Exists

from sqlalchemy.engine.reflection import Inspector

inspector = Inspector.from_engine(engine)
print("users" in inspector.get_table_names())  # True/False

10. Using RAW SQL Queries

with engine.connect() as conn:
    result = conn.execute("SELECT * FROM users")
    for row in result:
        print(row)

---

2️⃣ SQLAlchemy ORM (Object Relational Mapping) (10 Snippets)

11. Setting Up ORM with SQLAlchemy

from sqlalchemy.orm import declarative_base

Base = declarative_base()

12. Defining a User Model

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

13. Creating ORM Tables

Base.metadata.create_all(engine)

14. Creating a Session

from sqlalchemy.orm import sessionmaker

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

15. Adding a New Record

new_user = User(name="Alice", age=25)
session.add(new_user)
session.commit()

16. Querying All Users

users = session.query(User).all()
for user in users:
    print(user.name, user.age)

17. Querying a Single User

user = session.query(User).filter_by(name="Alice").first()
print(user.id, user.name, user.age)

18. Updating a Record

user = session.query(User).filter_by(name="Alice").first()
user.age = 30
session.commit()

19. Deleting a Record

user = session.query(User).filter_by(name="Alice").first()
session.delete(user)
session.commit()

20. Closing the Session

session.close()

---

3️⃣ CRUD Operations with SQLite/MySQL/PostgreSQL (10 Snippets)

21. Inserting Multiple Records

users = [User(name="Bob", age=28), User(name="Charlie", age=22)]
session.add_all(users)
session.commit()

22. Filtering with .filter()

young_users = session.query(User).filter(User.age < 30).all()
print(young_users)

23. Using .like() for Pattern Matching

matching_users = session.query(User).filter(User.name.like("A%")).all()
print(matching_users)

24. Sorting Results

sorted_users = session.query(User).order_by(User.age.desc()).all()
print(sorted_users)

25. Using .count()

user_count = session.query(User).count()
print(f"Total users: {user_count}")

26. Using .distinct()

unique_ages = session.query(User.age).distinct().all()
print(unique_ages)

27. Using .limit()

first_3_users = session.query(User).limit(3).all()
print(first_3_users)

28. Using .group_by()

from sqlalchemy import func

age_groups = session.query(User.age, func.count(User.id)).group_by(User.age).all()
print(age_groups)

29. Using Transactions with Rollback

try:
    new_user = User(name="David", age=35)
    session.add(new_user)
    raise Exception("Test rollback")  # Simulating error
    session.commit()
except:
    session.rollback()
    print("Transaction rolled back!")

30. Executing RAW SQL in ORM

result = session.execute("SELECT name, age FROM users WHERE age > 25").fetchall()
print(result)

---

4️⃣ Faker for Generating Fake Test Data (10 Snippets)

31. Installing Faker

pip install faker

32. Generating a Fake Name

from faker import Faker

faker = Faker()
print(faker.name())

33. Generating Fake Email

print(faker.email())

34. Generating Fake Address

print(faker.address())

35. Generating Fake Phone Number

print(faker.phone_number())

36. Generating Random Integer for Age

print(faker.random_int(min=18, max=80))

37. Generating Multiple Fake Users

fake_users = [{"name": faker.name(), "age": faker.random_int(min=18, max=80)} for _ in range(5)]
print(fake_users)

38. Inserting Fake Data into Database

fake_users = [User(name=faker.name(), age=faker.random_int(18, 80)) for _ in range(10)]
session.add_all(fake_users)
session.commit()

39. Generating Fake Company and Job Titles

print(faker.company(), "-", faker.job())

40. Generating Fake Data for Testing APIs

fake_data = {
    "name": faker.name(),
    "email": faker.email(),
    "address": faker.address(),
    "phone": faker.phone_number(),
    "age": faker.random_int(min=18, max=80)
}
print(fake_data)

---

🔥 Summary of Topics

✅ Relational Databases (10) - Connections, Table Schema, Transactions. ✅ SQLAlchemy ORM (10) - Models, Sessions, Queries. ✅ CRUD Operations (10) - Insert, Update, Delete, Filtering. ✅ Faker (10) - Fake Names, Emails, Data Insertion.

---