5일차 - MySQL, pymysql, tkinter와 pymysql 연동, 기상청 데이터 분석, 인구 현황 분석

 

 

ddl.txt

0.00MB

insert.txt

0.00MB

 

 

관계형 데이터베이스

유일한 이름을 갖는 릴레이션으로 구성

카디널리티 : 레코드의 개수

 

SQL

: 데이터를 읽고 쓰는 프로그래밍 언어

관계형 데이터베이스를 위해 만들어짐

 

Table : 데이터가 row, colum으로 구성되어 있다.

도메인, 주키 

 

---

 

학점이 3.5이상 성별이 여자인 학생만 출력

select sid, sname, grade from student where gen = "F" and grade > 3.5;

 

 

컴퓨터공학과 예산 출력

select budget from department where dname = "Computer";

 

 

1995년 이후에 입사한 교수 이름, 입사 일자 출력

select pname, hiredate from professor where hiredate >= "1995-01-01";

 

 

2002년에 태어난 학생들의 이름과 생일 출력 (between 사용)

select sname, birthdate from student where birthdate between "2002-01-01" and "2002-12-31";

 

---

 

LIKE 연산자

• '%' 전체 문자열 혹은 여러 문자를 구별
• '_' 문자 개수 구별

LIKE "Kim%" : Kim으로 시작하는 문자열 출력

LIKE "%TH_" : 마지막 글자 전의 문자열이 TH면 출력

 

 

생일이 4월인 학생의 이름, 생년월일 출력

select sname, birthdate from student where birthdate like "%-04-%";

 

 

---

 

• LOWER() : 소문자
• UPPER() : 대문자
• LPAD(필드, 문자 범위, ‘형식’)에 따라 결과 출력
• Instr(필드, '문자') : 필드에서 문자가 있는 위치를 출력
• REPLACE(필드, ‘A’, ‘B’) : 필드에서 문자 A를 찾아 B로 변환한 후 출력(문자 단위 변경)
• ROUND(필드 혹은 입력값, 숫자) : 입력값을 숫자만큼 반올림
• LENGTH : 필드나 입력한 문자열의 문자 개수를 출력

 

이름이 5자 이상인 학생을 찾아서 그 학생의 학번과 이름을 출력, 이름은 대문자로 출력

select sid, upper(sname) from student where length(sname) >= 5;

 

 

학생의 학번, 이름, 성적, 100점 만점으로 치환한 성적(grade100) 출력, 10번 학과만 출력

select sid, sname, grade, round(grade/4.5 *100, 2) as grade100 from student where deptno = 10;

 

---

 

Join

• 하나 이상의 테이블에 포함된 속성들을 검색 결과로 출력할 때 사용
• Join에 참여할 테이블들은 공통된 속성(join column)을 가져야 함
• 두 테이블에서 Join column의 값이 동일한 레코드들이 연결

 

Engineering 공대 학생들의 학번, 이름, 학과 이름 출력

select a.sid, a.sname, b.college from student a, department b 

where a.deptno = b.deptno and college = "Engineering";

 

 

2000년 이후에 임용된 교수와 교수의 지도학생들을 출력

select a.pname, b.sname from professor a, student b
where b.advisor= a.pid and hiredate >= "2000-01-01";

 

---

AI : 레코드 넣을때마다 값이 1씩 증가

 

테이블 생성

insert into logs(type, place, time) values(10, "대구", "2023-01-10");
insert into logs(type, place, time) values(20, "서울", "2024-01-10");

 

type = 10의  place를 대전으로 변경

update logs set place = "대전" where type = 10;

 

"부산"의 내용 지우기

delete from logs where place = "부산";

 

• drop table logs; 테이블 완전 지우기
• delete from logs; 테이블의 내용 지우기

 

execute 와 fetchall

1. execute +select문 한개
2. executemany + select문 여러개 (insert문과 executemany로 여러개의 레코드 추가 가능)
3. fetchone + 하나만 가져온다
4. fetchmany(갯수) + 여러개 가져옴
5. fetchall +다 가져옴

---

 

지도교수를 input으로 받아 지도교수가 관리하는 학생 출력

pid = input("지도교수의 id? ")
sql = "select * from student where advisor = %s"
val = (pid, )
mc.execute(sql, val)
result = mc.fetchall()
for st in result:
    print(st)

 

-> %s 를 두어 인자를 설정할 수 있다.

insert문 실행 후 mydb.commit() 해줘야함

 

 

logs 테이블에 (8, "대구", 현재 시간)의 값을 레코드에 추가하고, 결과 확인

현재 시간은 datetime 모듈 사용

import datetime
sql = "insert into logs(type, place, time) values (%s, %s, %s)"
vals = (8, "대구", datetime.datetime.now())
mc.execute(sql, vals)
mydb.commit()
print(mc.rowcount, "개의 레코드가 추가")

 

---

 

logs테이블에 무작위로 생성된 100개의 레코드 추가

places=['대구', '서울', '부산', '울산', '광주', '인천', '대전']

types = list(range(1,11))

시간 : datetime.datetime.now(), datetime.timedelta()

place는 places 리스트에서 무작위로 선택

type은 types 리스트에서 무작위로 선택

time은 timedelta의 seconds 인자의 값을 난수로 생성

delta : -365 * 24 * 3600 ~ 0 사이의 난수

timedelta(seconds = delta)

 

import random
import datetime

places = ['대구','서울', '부산', '대전', '광주', '춘천', '경주', '제주']
types = list(range(1, 11))
vals = []
for _ in range(100):
    p = random.choice(places)
    t = random.choice(types)
    delta = random.randint(-23 * 365 * 3600, 0)
    ti = datetime.datetime.now() + datetime.timedelta(seconds=delta)
    vals.append((t, p, ti))
print(vals[:10])

sql = "insert into logs(type, place, time) values (%s, %s, %s)"
mc.executemany(sql, vals)
mydb.commit()
print(mc.rowcount, "개의 레코드가 추가되었습니다.")

 

 

---

 

학생 테이블에 대한 CRUD 기능을 수행하는 tkinter 윈도우 작성

검색 : 학번을 입력하고, 검색 버튼을 누르면 학생 정보가 출력

추가 : 새로운 학생 레코드 추가

수정 : 학번에 해당하는 학생 레코드의 내용을 수정

삭제 : 학번을 키로 하는 학생 레코드를 삭제

다시 입력 : 모든 entry의 내용을 삭제, 학번으로 focus_set()

 

from tkinter import *
import pymysql
from tkinter import messagebox

def search():
    try:
        sql = "select * from student where sid= %s"
        vals = (entries[0].get(), )
        mc.execute(sql, vals)
        result = mc.fetchone()
        for x in range(1,8):
            entries[x].delete(0, END)
            entries[x].insert(0, result[x])
    except TypeError as e:
        messagebox.showerror("검색 오류", e)

def insert():
    vals = []
    for x in range(8):
        vals.append(entries[x].get())
    sql = "insert into student values (%s, %s, %s, %s, %s, %s, %s, %s)"
    mc.execute(sql, vals)
    mydb.commit()
    messagebox.showinfo("레코드 추가", f"{mc.rowcount} 개의 레코드가 추가되었습니다.")

def update():
    pass

def delete():
    sid = entries[0].get()
    sql = "delete from student where sid = %s"
    vals=(sid, )
    mc.execute(sql, vals)
    mydb.commit()
    messagebox.showinfo("레코드 삭제", f"{mc.rowcount} 개의 레코드가 삭제되었습니다.")
    reset()

def reset():
    for x in range(8):
        entries[x].delete(0, END)
    entries[0].focus_set()

window = Tk()
window.title("학생 관리")
window.geometry("450x300")
labels = ['학번', '이름', '학과', '지도교수', '성별', '주소', '생일', '학점']
entries = []

mydb = pymysql.connect(host= "localhost", user="root", password="1234", database="studentdb")
mc = mydb.cursor()

for x in range(8):
    Label(window, text=labels[x], font=("Arial", 14, "bold")).grid(row=x, column=0)
    en = Entry(window, font=("Arial", 16, "bold"), fg = "blue", width=30)
    en.grid(row=x, column=1)
    entries.append(en)

frame = Frame(window)
frame.grid(row=8, column=1)

Button(frame, text="검색", font=("Arial", 14, "bold"), command=search).pack(side=LEFT)
Button(frame, text="추가", font=("Arial", 14, "bold"), command=insert).pack(side=LEFT)
Button(frame, text="수정", font=("Arial", 14, "bold"), command=update).pack(side=LEFT)
Button(frame, text="삭제", font=("Arial", 14, "bold"), command=delete).pack(side=LEFT)
Button(frame, text="다시 입력", font=("Arial", 14, "bold"), command=reset).pack(side=LEFT)

window.mainloop()

 

1002 입력시 검색되는 모습

 

정보 입력 후 추가 버튼을 누른 모습

 

레코드가 삭제된 모습

 

 

---

 

 

프로젝트

Text Mining

Top - K 단어 찾기

파일을 읽어서 가장 많이 등장하는 k-개의 단어와 빈도수를 출력

입력? file_name minten k 한 줄에 모든 입력 받기

file_name : 파일 이름

minten : 최소 문자수 // minten 보다 짧은 단어는 고려 x

 

파일에서 라인 단어로 읽은 다음 split() 후 길이 5이상인거 사전에 집어 넣기

fname, minlen, k = input("파일의 최소 문자수 k를 입력: ").split()
minlen, k = int(minlen), int(k)

file = open(fname, "r")
freq = {}
for line in file:
    words = list(filter(lambda x: len(x) >= minlen, line.split()))
    for word in words:
        freq[word] = freq.get(word, 0) + 1

slist = sorted(freq.items(), key = lambda x: x[1], reverse = True)
slist[:k]

tale.txt에 8자 이상 문자 가장 많이 나온 10개 출력

 

 

---

 

자주 나타나는 단어의 쌍 찾기

문장에서 상관 관계가 높은 단어들의 쌍 구하기

• 문서를 문장 단위로 나누기
• 문장을 다시 단어들의 리스트로 나누기
• 길이가 짧은 단어나, stop word들은 삭제 - nltk 사용
• 문장에 포함된 단어의 쌍에 대해 빈도수 계산
• 가장 빈도수가 높은 n 개의 쌍들을 출력
• ****************아래 코드 수정*********************

import nltk

fname, minlen, k  = input("파일 이름, 최소 길이, 정수(k) 입력: ").split()
minlen, k = int(minlen), int(k)

file = open(fname, "r")
freq = {}
contents = file.read().lower()

for sentence in re.split('[.?!]'.contents):
    words = sorted(filter(lambda x : len(x) >= minlen and x not in stop_words, sentence.split()))
    for x in range(len(words) - 1):
        for y in range(x+f, len(words)):
            if words[x] == words[y]:
                continue
            freq[(words[x], words[y])] = freq.get((words[x], words[y]), 0) + 1

            
slist = sorted(freq.items(), key = lambda x: x[1], reverse=True)
slist[:k]

 

---

 

두 파일의 유사도 분석

fname1, fname2 = input("두 파일의 이름?").split()
k = int(input("Shingle 의 크기?"))

def make_shingle(fname, k):
    file = open(fname, "r")
    S = set()
    
    words = file.read().split()
    for x in range(len(words) - k + 1):
        S.add(tuple(word[x:x+k]))
    
    return S

S1 = make_shingle(fname1, k)
S2 = make_shingle(fname2, k)
print(S1, S2)

print("두 문서의 유사도 = ", len(S1 & S2)/len(S1 | S2))

 

 

---

 

 

추천 시스템

1. 어떤 식으로 저장을하고
2. 어떻게 데이터를 뽑아낼것이냐)

파일 이름, 추천인 수, 추천인 콘텐츠를 받아 사용자의 이름을 입력 받아 

성향이 비슷한 추천인수와 몇개의 추천 콘텐츠를 추천할지 출력

fname, nusers, ncontents = input("파일 이름, 추천인 수, 추천 콘텐츠 수 : ").split()
nusers, ncontents = int(nusers), int(ncontents)

user = input("누구에게 추천할까요? ")

file = open(fname, "r")
V = {}
for line in file:
    words = line.split()
    V[words[0]] = set([int(x) for x in words[1:]]) # key, value
    
target = V[user]

ulist = sorted(V.keys(), key=lambda x: len(V[x] & target) / len(V[x] | target), reverse = True)

rlist = []
for u in ulist[1:nusers+1]: # 자기자신은 제외
    rlist.extend(list(V[u] - target))

from collections import Counter

c = Counter(rlist)
c.most_common(ncontents)