🌐 APIs & SQL Databases

# HTTP requests from Session 12
import requests

# Make a GET request
url = "https://api.github.com/users/python"
response = requests.get(url)

# Check status code
print("Status Code:", response.status_code)
print("Success?", response.status_code == 200)

# Common status codes:
# 200 = Success
# 404 = Not Found
# 401 = Unauthorized
# 500 = Server Error

# Working with JSON from Session 12
import requests
import json

# Get JSON data from API
url = "https://api.github.com/users/python"
response = requests.get(url)

# Parse JSON response
data = response.json()  # Converts JSON to Python dict

print("Type:", type(data))
print("Login:", data['login'])
print("Name:", data.get('name'))
print("Followers:", data.get('followers'))

# Working with JSON strings
json_string = '{"name": "Alice", "age": 25}'
parsed = json.loads(json_string)  # String to dict
print("\\nParsed:", parsed)

# Convert dict to JSON string
my_dict = {"product": "Laptop", "price": 999}
json_str = json.dumps(my_dict, indent=2)
print("\\nJSON string:")
print(json_str)

# API example from Session 12
import requests
import pandas as pd

# Example: JSONPlaceholder API (free test API)
url = "https://jsonplaceholder.typicode.com/users"
response = requests.get(url)
users = response.json()

# Convert to DataFrame
df = pd.DataFrame(users)
print("Users from API:")
print(df[['id', 'name', 'email']].head())

# Get todos from API
todos_url = "https://jsonplaceholder.typicode.com/todos"
todos = requests.get(todos_url).json()
todos_df = pd.DataFrame(todos)
print("\\nTodos:")
print(todos_df.head())

# Analyze the data
print("\\nCompleted tasks by user:")
print(todos_df.groupby('userId')['completed'].sum().head())

# SQLite basics from Session 12
import sqlite3

# Connect to database (creates if doesn't exist)
conn = sqlite3.connect(':memory:')  # In-memory database
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        department TEXT,
        salary REAL
    )
''')

# Insert data
cursor.execute("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)",
               ('Alice', 'HR', 50000))
cursor.execute("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)",
               ('Bob', 'IT', 60000))
cursor.execute("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)",
               ('Charlie', 'IT', 70000))

# Commit changes
conn.commit()

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

# Close connection
conn.close()

# SQL queries from Session 12
import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create and populate table
cursor.execute('CREATE TABLE products (id INTEGER, name TEXT, price REAL, stock INTEGER)')
cursor.executemany('INSERT INTO products VALUES (?, ?, ?, ?)', [
    (1, 'Laptop', 999.99, 50),
    (2, 'Mouse', 29.99, 200),
    (3, 'Keyboard', 79.99, 150),
    (4, 'Monitor', 299.99, 75),
    (5, 'USB Cable', 9.99, 500)
])
conn.commit()

# SELECT with WHERE
print("Products over $50:")
cursor.execute("SELECT * FROM products WHERE price > 50")
for row in cursor.fetchall():
    print(row)

# ORDER BY
print("\\nProducts by price (descending):")
cursor.execute("SELECT name, price FROM products ORDER BY price DESC")
for row in cursor.fetchall():
    print(row)

# Aggregate functions
cursor.execute("SELECT AVG(price), SUM(stock), COUNT(*) FROM products")
avg_price, total_stock, count = cursor.fetchone()
print(f"\\nAverage price: ${avg_price:.2f}")
print(f"Total stock: {total_stock}")
print(f"Product count: {count}")

conn.close()

# Pandas + SQL from Session 12
import sqlite3
import pandas as pd

# Create database with data
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE sales (id INTEGER, product TEXT, amount REAL, date TEXT)')
cursor.executemany('INSERT INTO sales VALUES (?, ?, ?, ?)', [
    (1, 'Laptop', 999.99, '2024-01-15'),
    (2, 'Mouse', 29.99, '2024-01-15'),
    (3, 'Laptop', 999.99, '2024-01-16'),
    (4, 'Keyboard', 79.99, '2024-01-16'),
    (5, 'Mouse', 29.99, '2024-01-17')
])
conn.commit()

# Read SQL query into DataFrame
df = pd.read_sql_query("SELECT * FROM sales", conn)
print("Sales DataFrame:")
print(df)

# Use Pandas for analysis
print("\\nTotal sales by product:")
print(df.groupby('product')['amount'].sum())

# Write DataFrame to SQL
new_data = pd.DataFrame({
    'name': ['Alice', 'Bob'],
    'score': [95, 87]
})
new_data.to_sql('scores', conn, index=False)
print("\\nCreated scores table from DataFrame!")

conn.close()