
import cx_Oracle as oci
from faker import Faker
import random
fake = Faker('ko-KR')
sid = 'XE'
host = '210.119.14.71'
port = 1521
username = 'attendance'
password = '12345'
def generate_date_of_birth(year: int):
month = random.randint(1, 12)
day = random.randint(1, 28)
return f"{year}-{month:02d}-{day:02d}"
class AddData:
def addCdata(self):
print("addCdata 함수 실행됨")
isSucceed = False
conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
cursor = conn.cursor()
class_no_list = []
try:
conn.begin()
query = '''
INSERT INTO ATTENDANCE.CLASS(CLASS_NO, CLASS_NAME, T_NO)
VALUES(class_class_no_seq.nextval, :1, :2)
'''
# data_list = [(fake.pyint(min_value=1, max_value=10), fake.pyint(min_value=1, max_value=100)) for _ in range(10)]
data_list = [(fake.pyint(min_value=1, max_value=10), fake.pyint(min_value=1, max_value=100))]
print("삽입할 데이터 목록:", data_list)
cursor.executemany(query, data_list)
conn.commit()
print("10개 데이터 삽입 완료")
isSucceed = True
cursor.execute('SELECT class_class_no_seq.currval FROM dual')
for _ in range(10):
class_no = cursor.fetchone()
if class_no:
class_no_list.append(class_no[0])
print("삽입된 CLASS_NO들:", class_no_list)
return class_no_list
except Exception as e:
print("오류 발생:", e)
conn.rollback() # DB rollback
isSucceed = False
finally:
cursor.close()
conn.close()
return isSucceed # 트랜잭션 여부를 리턴
def addTdata(self, class_no_list):
print("addTdata 함수 실행됨")
isSucceed = False
conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
cursor = conn.cursor()
try:
conn.begin()
query = '''
INSERT INTO ATTENDANCE.TEACHER (T_NO, T_ID, T_PW, T_NAME, T_TEL, CLASS_NO)
VALUES(teacher_t_no_seq.nextval, :1, :2, :3, :4, :5)
'''
data_list = [(fake.user_name(), fake.password(), fake.name(), fake.phone_number(), class_no) for class_no in class_no_list]
print("삽입할 데이터 목록:", data_list)
cursor.executemany(query, data_list)
conn.commit()
print("10개 데이터 삽입 완료")
isSucceed = True
except Exception as e:
print("오류 발생:", e)
conn.rollback() # DB rollback
isSucceed = False
finally:
cursor.close()
conn.close()
return isSucceed # 트랜잭션 여부를 리턴
def addSdata(self, class_no_list):
print("addSdata 함수 실행됨")
isSucceed = False
conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
cursor = conn.cursor()
try:
conn.begin()
query = '''
INSERT INTO ATTENDANCE.STUDENT (S_NO, S_ID, S_PW, S_NAME, S_BIRTH, S_TEL, S_ADDR, CLASS_NO)
VALUES(student_s_no_seq.nextval, :1, :2, :3, TO_DATE(:4, 'YYYY-MM-DD'), :5, :6, :7)
'''
birth_year = 2022
student_data = []
for class_no in class_no_list:
for _ in range(30):
student_data.append((
str(fake.random_number(digits=6)),
fake.password(),
fake.name(),
generate_date_of_birth(birth_year),
fake.phone_number(),
fake.address(),
class_no
))
print("삽입할 학생 데이터 목록:", student_data)
cursor.executemany(query, student_data)
conn.commit()
print("학생 데이터 30개 삽입 완료 (각 반에 30명씩 배정)")
isSucceed = True
except Exception as e:
print("오류 발생:", e)
conn.rollback()
isSucceed = False
finally:
cursor.close()
conn.close()
return isSucceed # 트랜잭션 여부를 리턴
def addAdata(self, class_no_list):
print("addAdata 함수 실행됨")
isSucceed = False
conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
cursor = conn.cursor()
try:
conn.begin()
cursor.execute("SELECT S_NO, CLASS_NO FROM ATTENDANCE.STUDENT")
student_list = cursor.fetchall()
cursor.execute("SELECT T_NO, CLASS_NO FROM ATTENDANCE.TEACHER")
teacher_dict = {row[1]: row[0] for row in cursor.fetchall()}
status_choices = ['P', 'A', 'L']
attendance_data = []
for s_no, class_no in student_list:
t_no = teacher_dict.get(class_no)
if t_no:
attendance_data.append((s_no, random.choice(status_choices), t_no))
query = '''
INSERT INTO ATTENDANCE.ATD (ATD_NO, S_NO, ATD_DATE, ATD_TIME, STATUS, T_NO)
VALUES(atd_no_seq.nextval, :1, SYSDATE, SYSTIMESTAMP, :2, :3)
'''
cursor.executemany(query, attendance_data)
conn.commit()
print(f"출석 데이터 {len(attendance_data)}개 삽입 완료")
isSucceed = True
except Exception as e:
print("오류 발생:", e)
conn.rollback()
isSucceed = False
finally:
cursor.close()
conn.close()
return isSucceed
def loadSdata(self):
conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
cursor = conn.cursor()
query = '''SELECT S_NO, S_ID, S_PW, S_NAME, S_BIRTH, S_TEL, S_ADDR, CLASS_NO
FROM ATTENDANCE.STUDENT'''
cursor.execute(query)
lst_student = [item for item in cursor]
cursor.close()
conn.close()
return lst_student
if __name__ == "__main__":
add_data = AddData()
class_no_list = add_data.addCdata()
print("addCdata 실행 후 받은 CLASS_NO 목록:", class_no_list)
if class_no_list:
result = add_data.addTdata(class_no_list)
print("addTdata 실행 결과:", result)
result = add_data.addSdata(class_no_list)
print("addSdata 실행 결과:", result)
result = add_data.addAdata(class_no_list)
print("addAdata 실행 결과:", result)
# loadSdata 실행 후 결과 출력
student_data = add_data.loadSdata()
print("loadSdata 실행 결과:", student_data)
else:
print("class_no_list가 비어 있음.")
- [x] 반번호와 교사 불일치 문제 → 3일차에 해결
- [x] 시간에 따른 출석, 결석, 지각 처리 안됨→ 3일차에 해결