BLP205 - 18.10.24

crud.sql

-- Yeni öğrenci ekleme
INSERT INTO students (first_name, last_name, email) VALUES ('Ali', 'Yılmaz', 'ali.yilmaz@example.com');

-- Yeni kurs ekleme
INSERT INTO courses (course_name, course_code) VALUES ('Veritabanı Sistemleri', 'BIL205');

-- Tüm öğrencileri listele
SELECT * FROM students;

-- Öğrenci ve kayıtlı olduğu kursları listele
SELECT s.first_name, s.last_name, c.course_name
FROM students s
JOIN students_courses sc ON s.id = sc.student_id
JOIN courses c ON c.id = sc.course_id;

-- Öğrencinin email adresini güncelleme
UPDATE students SET email = 'ali.yeni@example.com' WHERE id = 1;

docker-compose.yml

services:
  db:
    image: postgres:latest
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: blp205
    ports:
      - "5432:5432"
    volumes:
      - ./db-data:/var/lib/postgresql/data

  python:
    build: .
    depends_on:
      - db
    volumes:
      - .:/usr/src/app
    environment:
      POSTGRES_DB: blp205
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_HOST: db
    command: python3 seed_data.py

Dockerfile

FROM python:3.9

# PostgreSQL istemcisini yükle
RUN apt-get update && apt-get install -y postgresql-client

# Diğer Dockerfile komutlarınız
WORKDIR /usr/src/app
COPY . .

# Gerekli Python paketlerini yükleyin
RUN pip install -r requirements.txt

# Entry point scriptinizi kopyalayın
COPY entrypoint.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/entrypoint.sh
ENTRYPOINT ["entrypoint.sh"]

entrypoint.sh

#!/bin/bash
# entrypoint.sh

# PostgreSQL'e bağlanana kadar bekle
until pg_isready -h db -p 5432; do
  echo "Waiting for PostgreSQL to be ready..."
  sleep 2
done

echo "PostgreSQL ready!"

# Ardından, uygulamanızı başlatın
exec python /usr/src/app/seed_data.py

faker-bin.py

import psycopg2
from faker import Faker
import random

# Veritabanı bağlantı bilgileri
db_config = {
    'dbname': 'exampledb',
    'user': 'postgres',
    'password': 'password',
    'host': 'db',
    'port': '5432'
}

# Bağlantıyı kur
conn = psycopg2.connect(**db_config)
cur = conn.cursor()

# Tabloları oluştur
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        first_name VARCHAR(100),
        last_name VARCHAR(100),
        email VARCHAR(100) UNIQUE NOT NULL
    );
""")

cur.execute("""
    CREATE TABLE IF NOT EXISTS courses (
        id SERIAL PRIMARY KEY,
        course_name VARCHAR(100) NOT NULL
    );
""")

cur.execute("""
    CREATE TABLE IF NOT EXISTS enrollments (
        student_id INTEGER REFERENCES students(id),
        course_id INTEGER REFERENCES courses(id),
        PRIMARY KEY (student_id, course_id)
    );
""")

conn.commit()

# Faker nesnesi oluştur
fake = Faker('tr_TR')

# 100 ders oluştur
courses = []
for _ in range(100):
    course_name = fake.catch_phrase()  # Rastgele ders adı
    courses.append(course_name)
    cur.execute("INSERT INTO courses (course_name) VALUES (%s)", (course_name,))

conn.commit()

# 1000 rastgele öğrenci oluştur
students = []
for _ in range(1000):
    first_name = fake.first_name()
    last_name = fake.last_name()
    email = fake.unique.email()
    students.append((first_name, last_name, email))
    cur.execute(
        "INSERT INTO students (first_name, last_name, email) VALUES (%s, %s, %s)",
        (first_name, last_name, email)
    )

conn.commit()

# Öğrencileri rastgele derslere kaydet
for student_id in range(1, 1001):
    # Her öğrenci için rastgele dersler seç
    enrolled_courses = random.sample(range(1, 101), k=random.randint(1, 5))  # 1 ila 5 ders
    for course_id in enrolled_courses:
        cur.execute(
            "INSERT INTO enrollments (student_id, course_id) VALUES (%s, %s)",
            (student_id, course_id)
        )

conn.commit()

# Temizlik
cur.close()
conn.close()

faker.py

# FAKER ile veri seti üretimi
import psycopg2
from faker import Faker

# Veritabanı bağlantısı
conn = psycopg2.connect(
    dbname="bil205",
    user="postgres",
    password="password",
    host="localhost"
)
cur = conn.cursor()

# Faker ile veri üretimi
fake = Faker()

# 10 öğrenci ekleme
for _ in range(10):
    first_name = fake.first_name()
    last_name = fake.last_name()
    email = fake.email()
    cur.execute("INSERT INTO students (first_name, last_name, email) VALUES (%s, %s, %s)", 
                (first_name, last_name, email))

# 5 kurs ekleme
courses = [('Matematik', 'MAT101'), ('Fizik', 'FIZ102'), ('Kimya', 'KIM103'), ('Biyoloji', 'BIO104'), ('Programlama', 'PRG105')]
for course in courses:
    cur.execute("INSERT INTO courses (course_name, course_code) VALUES (%s, %s)", course)

conn.commit()
cur.close()
conn.close()

init.sql

CREATE DATABASE bil205;

-- students tablosu
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- courses tablosu
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    course_code VARCHAR(10) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- students_courses (ara tablo) - öğrenci ve kurs ilişkisi
CREATE TABLE students_courses (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

seed_data.py

import psycopg2
from faker import Faker
import random

# Geçici vt bağlantı Bilgileri
db_config = {
    'dbname': 'postgres',
    'user': 'postgres',
    'password': 'password',
    'host': 'db',
    'port': '5432'
}

# İlk bağlantıyı kurarak veritabanını oluştur
conn = psycopg2.connect(**db_config)
conn.autocommit = True  # Autocommit modunu açıyoruz
cur = conn.cursor()

# Veritabanını oluştur
# Veritabanını oluştur
try:
    cur.execute("DROP DATABASE IF EXISTS blp205;")
    cur.execute("CREATE DATABASE blp205;")
except psycopg2.errors.DuplicateDatabase:
    print("Veritabanı zaten mevcut. 'blp205' veritabanı oluşturulamıyor.")
cur.close()
conn.close()

# Yeni veritabanına bağlanıyoruz
db_config['dbname'] = 'blp205'  # Artık yeni veritabanına bağlanıyoruz

# Bağlantıyı kur
conn = psycopg2.connect(**db_config)
cur = conn.cursor()

# Tabloları oluştur
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        student_id VARCHAR(10) UNIQUE NOT NULL,
        first_name VARCHAR(100),
        last_name VARCHAR(100),
        email VARCHAR(100) UNIQUE NOT NULL
    );
""")

cur.execute("""
    CREATE TABLE IF NOT EXISTS courses (
        id SERIAL PRIMARY KEY,
        course_name VARCHAR(100) NOT NULL
    );
""")

cur.execute("""
    CREATE TABLE IF NOT EXISTS enrollments (
        student_id INTEGER REFERENCES students(id),
        course_id INTEGER REFERENCES courses(id),
        PRIMARY KEY (student_id, course_id)
    );
""")

conn.commit()

# Faker nesnesi oluştur
fake = Faker('tr_TR')

def generate_student_id():
    """8 basamaklı öğrenci kimlik numarası ve 2 kontrol basamağı oluşturur."""
    base_id = random.randint(10000000, 99999999)  # 8 basamaklı
    base_id_str = str(base_id)

    # Kontrol basamaklarını hesaplayalım (örneğin, Luhn algoritması kullanarak)
    sum_digits = sum(int(digit) for digit in base_id_str)
    checksum1 = (10 - (sum_digits % 10)) % 10  # İlk kontrol basamağı
    checksum2 = (10 - ((sum_digits + checksum1) % 10)) % 10  # İkinci kontrol basamağı

    return f"{base_id_str}{checksum1}{checksum2}"

# 100 ders oluştur
courses = []
for _ in range(100):
    course_name = fake.catch_phrase()
    courses.append(course_name)
    cur.execute("INSERT INTO courses (course_name) VALUES (%s)", (course_name,))

conn.commit()

# 1000 rastgele öğrenci oluştur
students = []
for _ in range(1000):
    first_name = fake.first_name()
    last_name = fake.last_name()
    email = fake.unique.email()
    student_id = generate_student_id()  # Rastgele öğrenci kimliği
    students.append((student_id, first_name, last_name, email))
    cur.execute(
        "INSERT INTO students (student_id, first_name, last_name, email) VALUES (%s, %s, %s, %s)",
        (student_id, first_name, last_name, email)
    )

conn.commit()

# Öğrencileri rastgele derslere kaydet
for student_id in range(1, 1001):
    enrolled_courses = random.sample(range(1, 101), k=random.randint(1, 5))  # 1 ila 5 ders
    for course_id in enrolled_courses:
        cur.execute(
            "INSERT INTO enrollments (student_id, course_id) VALUES (%s, %s)",
            (student_id, course_id)
        )

conn.commit()

# Temizlik
cur.close()
conn.close()