mjY40QvS

1# %%
2print("hello")
3
4# %%
5import sqlite3
6conn = sqlite3.connect('uas2.db')
7cursor = conn.cursor()
8
9# %%
10cursor.execute('DROP TABLE IF EXISTS siswa')
11
12cursor.execute('''
13    CREATE TABLE  IF NOT EXISTS siswa (
14        id INTEGER PRIMARY KEY AUTOINCREMENT,
15        nim TEXT UNIQUE NOT NULL,
16        nama TEXT NOT NULL,
17        skor INTEGER
18    )
19''')
20conn.commit()
21
22# %%
23users_data = [
24    ('0501', 'Budi'),
25    ('0502', 'Tita'),
26]
27cursor.executemany("INSERT INTO siswa (nim, nama) VALUES (?, ?)", users_data)
28conn.commit()
29
30# %%
31cursor.execute('DROP TABLE IF EXISTS soal')
32
33cursor.execute('''
34    CREATE TABLE IF NOT EXISTS soal (
35        id INTEGER PRIMARY KEY AUTOINCREMENT,
36        deskripsi TEXT UNIQUE NOT NULL,
37        tingkat_kesulitan integer NOT NULL -- 1:mudah, 2:sedang, 3:sulit       
38    )
39''')
40conn.commit()
41
42
43# %%
44data_soal = [
45    ('Ibu kota Jawa Barat', 1),
46    ('Ibu kota Jawa Tengah', 2),
47    ('Ibu kota Maluku Utara', 3),
48]
49cursor.executemany("INSERT INTO soal (deskripsi, tingkat_kesulitan) VALUES (?, ?)", data_soal)
50conn.commit
51
52# %%
53cursor.execute('DROP TABLE IF EXISTS pilihan')
54
55cursor.execute('''
56    CREATE TABLE IF NOT EXISTS pilihan (
57        id INTEGER PRIMARY KEY AUTOINCREMENT,
58        id_soal INTEGER NOT NULL,
59        is_benar BOOLEAN NOT NULL,
60        deskripsi TEXT UNIQUE NOT NULL
61    )
62''')
63conn.commit()
64
65# %%
66data_pilihan_soal = [
67    ( 1,"Bandung",1),
68    ( 1,"Jakarta", 0),
69    ( 1,"Banten",0),    
70    ( 2,"Semarang",1),
71    ( 2,"Yogyakarta",0),
72    ( 2,"Solo",0),
73    ( 3,"Sofifi",1),
74    ( 3,"Tidore",0),
75    ( 3,"Maluku",0),
76]
77cursor.executemany("INSERT INTO pilihan (id_soal,deskripsi, is_benar) VALUES (?, ?, ?)", data_pilihan_soal)
78conn.commit()
79
80
81# %%
82
83
84# %%
85
86
87# %%
88cursor.execute('DROP TABLE IF EXISTS jawaban')
89cursor.execute('''
90    CREATE TABLE IF NOT EXISTS jawaban (
91        id INTEGER PRIMARY KEY AUTOINCREMENT,
92        id_siswa INTEGER NOT NULL,
93        id_soal INTEGER NOT NULL,   
94        jawaban_id_pilihan  INTEGER NOT NULL       
95    )
96''')
97conn.commit()
98
99# %%
100def proses_test(id):
101    
102    conn = sqlite3.connect('uas2.db')
103    cursor = conn.cursor()
104    
105    cursor.execute("SELECT nama FROM siswa WHERE id=?", (id,))
106    siswa = cursor.fetchone()
107    print(f"Selamat datang {siswa[0]}")
108
109    cursor.execute("SELECT id, deskripsi, tingkat_kesulitan FROM soal ORDER BY RANDOM()")
110    soal = cursor.fetchall()
111
112    cc = 0
113    for s in soal:
114        cc = cc + 1
115        print(f"No: {cc}")
116        print(f"{s[1]}")
117
118        cursor.execute("SELECT id, deskripsi, is_benar FROM pilihan WHERE id_soal=? ORDER BY RANDOM()", (s[0],))
119        pilihan = cursor.fetchall()
120        
121        cc2 = 1        
122        peta = []
123        for p in pilihan:
124            print(f"{cc2} {p[1]}")
125            peta.append(p[0]) # simpan id pilihan
126            cc2 = cc2 + 1
127        
128        print(peta)
129        jawaban = int(input("Jawaban anda: "))  
130        jawab_id = peta[jawaban-1] #jawab akan berisi id pilihan, harus seperti ini karena random   
131        print(jawab_id)
132        cursor.execute("INSERT INTO jawaban (id_siswa, id_soal, jawaban_id_pilihan) VALUES (?, ?, ?)", (id, s[0], jawab_id))
133    
134    conn.commit()
135
136# %%
137def hitung_skor(id):
138    conn = sqlite3.connect('uas2.db')
139    cursor = conn.cursor()
140    
141    cursor.execute("SELECT id, nama FROM siswa WHERE id=?", (id,))
142    siswa = cursor.fetchone()
143    print(f"Hitung skor {siswa[1]}")
144
145    cursor.execute("SELECT id_soal, jawaban_id_pilihan FROM jawaban WHERE id_siswa=?", (id,))
146    jawaban = cursor.fetchall()
147
148    skor = 0
149    for j in jawaban:
150        cursor.execute("SELECT id, is_benar FROM pilihan WHERE id_soal=? and is_benar", (j[0],))
151        jawab_benar = cursor.fetchone()
152        if jawab_benar[0] == j[1]:
153            skor += 1
154        # if is_benar[0] == 1:
155        #     skor += 1
156
157    print(f"Skor: {skor}")
158    cursor.execute("UPDATE siswa SET skor=? WHERE id=?", (skor, id))
159    conn.commit()
160
161# %%
162hitung_skor(1)
163
164# %%
165
166
167# %%
168conn = sqlite3.connect('uas2.db')
169cursor = conn.cursor()
170    
171print("Masukkan NIM siswa : ")
172nim = input()
173cursor.execute("SELECT id FROM siswa WHERE nim = ?", (nim,))    
174result = cursor.fetchone()
175if result:
176    id = result[0]
177    print("ID siswa dengan NIM", nim, "adalah", id)
178    proses_test(id)
179    hitung_skor(id)
180else:
181    print("NIM tidak ditemukan")
182conn.close()
183
184
185
186