Database & Models

# Login ke PostgreSQL
psql -U postgres -c "SELECT version();"

# Jika berhasil, akan menampilkan versi PostgreSQL

# Login ke PostgreSQL sebagai superuser
# Ganti username dengan user PostgreSQL Kalian
psql -U postgres

-- 1. Buat database
CREATE DATABASE pyramid_mahasiswa;

-- 2. Buat user baru
CREATE USER pyramid_user WITH ENCRYPTED PASSWORD 'pyramid_pass';

-- 3. Beri user izin ke database
GRANT ALL PRIVILEGES ON DATABASE pyramid_mahasiswa TO pyramid_user;

-- 4. Pindah ke database pyramid_mahasiswa
\c pyramid_mahasiswa

-- 5. Beri izin schema public ke user
GRANT USAGE, CREATE ON SCHEMA public TO pyramid_user;

-- 6. Ubah owner schema public (opsional tapi paling aman)
ALTER SCHEMA public OWNER TO pyramid_user;

-- 7. Pastikan owner default table/sequence future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO pyramid_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO pyramid_user;

-- 8. Keluar dari psql
\q

pip install psycopg2-binary

# Cari dan ganti baris sqlalchemy.url
sqlalchemy.url = sqlite:///%(here)s/pyramid_mahasiswa.sqlite

# Menjadi
sqlalchemy.url = postgresql://pyramid_user:pyramid_pass@localhost:5432/pyramid_mahasiswa

postgresql://[user]:[password]@[host]:[port]/[database]

from sqlalchemy import (
    Column,
    Integer,
    Text,
    Date,
)

from .meta import Base


class Mahasiswa(Base):
    """ Model untuk tabel mahasiswa """
    __tablename__ = 'mahasiswa'
    id = Column(Integer, primary_key=True)
    nim = Column(Text, unique=True, nullable=False)
    nama = Column(Text, nullable=False)
    jurusan = Column(Text, nullable=False)
    tanggal_lahir = Column(Date)
    alamat = Column(Text)

    def to_dict(self):
        return {
            'id': self.id,
            'nim': self.nim,
            'nama': self.nama,
            'jurusan': self.jurusan,
            'tanggal_lahir': self.tanggal_lahir.isoformat() if self.tanggal_lahir else None,
            'alamat': self.alamat,
        }

from sqlalchemy import engine_from_config
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import configure_mappers
import zope.sqlalchemy

from .mahasiswa import Mahasiswa
from .mymodel import MyModel

configure_mappers()

def get_engine(settings, prefix='sqlalchemy.'):
    return engine_from_config(settings, prefix)

def get_session_factory(engine):
    factory = sessionmaker()
    factory.configure(bind=engine)
    return factory

def get_tm_session(session_factory, transaction_manager):
    dbsession = session_factory()
    zope.sqlalchemy.register(dbsession, transaction_manager=transaction_manager)
    return dbsession

def includeme(config):
    settings = config.get_settings()

    engine = get_engine(settings)
    session_factory = get_session_factory(engine)
    config.registry['dbsession_factory'] = session_factory

    # request.tm disediakan oleh pyramid_tm
    config.add_request_method(
        lambda request: get_tm_session(session_factory, request.tm),
        "dbsession",
        reify=True
    )

import argparse
import sys
from datetime import date

from pyramid.paster import bootstrap, setup_logging
from sqlalchemy.exc import OperationalError

from .. import models

def setup_models(dbsession):
    """
    Add initial model objects.
    """

    existing_mahasiswa1 = dbsession.query(models.Mahasiswa).filter_by(nim='12345').first()
    existing_mahasiswa2 = dbsession.query(models.Mahasiswa).filter_by(nim='54321').first()
    
    if not existing_mahasiswa1:
        mahasiswa1 = models.Mahasiswa(
            nim='12345',
            nama='Budi Santoso',
            jurusan='Teknik Informatika',
            tanggal_lahir=date(2000, 5, 15),
            alamat='Jl. Merdeka No. 123, Bandung'
        )
        dbsession.add(mahasiswa1)
        print("Mahasiswa 12345 added.")
    else:
        print("Mahasiswa 12345 already exists.")
    
    if not existing_mahasiswa2:
        mahasiswa2 = models.Mahasiswa(
            nim='54321',
            nama='Siti Aminah',
            jurusan='Sistem Informasi',
            tanggal_lahir=date(2001, 8, 22),
            alamat='Jl. Mawar No. 45, Jakarta'
        )
        dbsession.add(mahasiswa2)
        print("Mahasiswa 54321 added.")
    else:
        print("Mahasiswa 54321 already exists.")

def parse_args(argv):
    parser = argparse.ArgumentParser()
    parser.add_argument(
        'config_uri',
        help='Configuration file, e.g., development.ini',
    )
    return parser.parse_args(argv[1:])


def main(argv=sys.argv):
    args = parse_args(argv)
    setup_logging(args.config_uri)

    # bootstrap will return a context with request + closer
    env = bootstrap(args.config_uri)
    request = env['request']

    try:
        # gunakan request.tm (bukan tm_manager)
        with request.tm:
            dbsession = request.dbsession
            setup_models(dbsession)

        print("Database initialized successfully.")

    except OperationalError:
        print('''
Pyramid is having a problem using your SQL database.

Your database should be up and running before you
initialize your project. Make sure your database server
is running and your connection string in development.ini
is correctly configured.
''')

    finally:
        env['closer']()


if __name__ == '__main__':
    main()

# Verifikasi Alembic sudah terpasang
pip list | grep alembic

# Jika tidak ada, install
pip install alembic

# Di root proyek pyramid_mahasiswa
# Pastikan virtual environment aktif

# Buat file migrasi
alembic -c development.ini revision --autogenerate -m "create mahasiswa table"

# Jalankan migrasi
alembic -c development.ini upgrade head

INFO  [alembic.runtime.migration] Running upgrade -> 1234567890ab, create mahasiswa table

# Jalankan dari direktori root proyek
python -m pyramid_mahasiswa.scripts.initialize_db development.ini