프로젝트

from microbit import *
import radio
radio.on()
radio.config(group=1)
student_id = "20260101"

while True:
    if button_a.was_pressed():
        radio.send("IN:" + student_id)
        display.show(Image.YES)
        sleep(1000)
        display.clear()

    if button_b.was_pressed():
        radio.send("OUT:" + student_id)
        display.show(Image.ARROW_S)
        sleep(1000)
        display.clear()

from microbit import *
import radio
radio.on()
radio.config(group=1)

while True:
    msg = radio.receive()
    if msg:
        if msg.startswith("IN:"):
            print(msg)
            display.show(Image.YES)
            sleep(1000)
        elif msg.startswith("OUT:"):
            print(msg)
            display.show(Image.NO)
            sleep(1000)
        display.clear()

import serial
import datetime

ser = serial.Serial('/dev/ttyACM0',115200,timeout=1)

while True:
    data = ser.readline().decode(errors="ignore").strip()
    if data:
        try:
            status, sid = data.split(":")
            now = datetime.datetime.now()

            if status == "IN":
                action = "입실"
            elif status == "OUT":
                action = "퇴실"

            with open("attendance.csv","a",encoding="utf-8") as f:
                f.write(f"{sid},{action},{now}\n")

            print(action, sid)
        except:
            print("데이터 오류:", data)

	
	from flask import Flask, render_template, request
import pymysql

app = Flask(__name__)

def get_db():
    return pymysql.connect(
        host="localhost",
        user="master",
        password="123456",
        database="attendance_system",
        charset="utf8"
    )

@app.route("/")
def index():

    db = get_db()
    cursor = db.cursor()

    cursor.execute("""
        SELECT * FROM attendance
        ORDER BY check_time DESC
    """)

    data = cursor.fetchall()

    return render_template("index.html", data=data)

@app.route("/checkin", methods=["POST"])
def checkin():

    student_id = request.form["student_id"]
    status = request.form["status"]
    method = request.form["method"]

    db = get_db()
    cursor = db.cursor()

    cursor.execute("""
        INSERT INTO attendance
        (student_id, check_time, status, method)
        VALUES (%s, NOW(), %s, %s)
    """,(student_id,status,method))

    db.commit()

    return "OK"

app.run(host="0.0.0.0",port=5000)
	

	
	학생 정보 테이블

CREATE TABLE students (
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(50),
grade INT,
class VARCHAR(10)
); INSERT INTO students VALUES
('S01','조영우',3,'A'),
('S02','이성원',1,'A'),
('S03','홍길동',1,'A');

수강 과목명 테이블

CREATE TABLE subjects (
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO subjects VALUES
('MATH','수학'),
('ENG','영어'),
('SCI','과학');

등록 테이블

CREATE TABLE enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id VARCHAR(10),
subject_id VARCHAR(10),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (subject_id) REFERENCES subjects(id),
UNIQUE (student_id, subject_id)
);
INSERT INTO enrollments (student_id, subject_id) VALUES
('S01','MATH'),
('S01','ENG'),
('S02','MATH'),
('S03','SCI');

출석부 테이블

CREATE TABLE attendance (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id VARCHAR(10),
subject_id VARCHAR(10),
check_time DATETIME,
status ENUM('IN','OUT'),
method ENUM('MICROBIT','RFID','QR','FACE'),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

INSERT INTO attendance (student_id, subject_id, check_time, status, method)
VALUES
('S01','MATH',NOW(),'IN','MICROBIT'),
('S02','MATH',NOW(),'IN','RFID'),
('S01','MATH',NOW(),'OUT','QR');

과목 시간 테이블

CREATE TABLE schedule (
id INT AUTO_INCREMENT PRIMARY KEY,
subject_id VARCHAR(10),
day_of_week INT, -- 0=월, 1=화 ... 6=일
start_time TIME,
end_time TIME,

FOREIGN KEY (subject_id) REFERENCES subjects(id)
);
INSERT INTO schedule (subject_id, day_of_week, start_time, end_time) VALUES
('MATH', 0, '14:00:00', '15:00:00'), -- 월요일 수학
('ENG', 0, '15:00:00', '16:00:00'), -- 월요일 영어
('SCI', 1, '14:00:00', '15:00:00'); -- 화요일 과학

-------------------------------------------------------------------------------

#db에 저장해야하는 코드

import pymysql
import serial
import datetime
conn = pymysql.connect(
host="localhost",
user="master",
password="123456",
database="master"
)

cursor = conn.cursor()
ser	= serial.Serial('/dev/ttyACM0',115200,timeout=1)
while True:
	data = ser.readline().decode(errors="ignore").strip()
	if data:
		try:
			status,sid = data.split(":")
			now	= datetime.datetime.now()
			sql	= "INSERT INTO attendance (student_id, check_time, status, method) VALUES (%s,%s,%s,%s)"
			cursor.execute(sql,(sid,now,status,"MICROBIT"))
			conn.commit()
			print("저장:",sid,status)
		except:
			print("데이터 오류:",data)