search

kyohoonsim / kusf-data-2023-1

2023-1 KUSF data 수업 강의자료 레포지토리
17 stars 1 forks source link

Quiz13. 기획한 서비스에서 중요한 데이터를 담을 수 있는 테이블을 만들고, 데이터를 넣고, 그 데이터를 FastAPI를 통해서 제공해보자. #15

Open kyohoonsim opened 1 year ago

kyohoonsim commented 1 year ago

코드 및 화면 캡쳐본 제출

kyohoonsim commented 1 year ago
  1. 데이터베이스 생성
  2. 테이블 생성
  3. 데이터 입력(insert)
  4. sqlalchemy로 데이터 접근, crud.py 파일 내 함수 생성
  5. fastapi에서 엔드포인트 생성해서 crud.함수 호출해서 데이터 제공
Y0nghwan commented 1 year ago

1. kusf 라는 DB를 생성했음

2. user_info라는 테이블을 생성했음

CREATE TABLE user_info(
    User_ID INT NOT NULL AUTO_INCREMENT,
    Username VARCHAR(20),
    Email_Address VARCHAR(30),
    Password VARCHAR(20),
    Created_At TIMESTAMP,
    PRIMARY KEY (User_ID)
    );

3. 데이터 입력 후 확인

INSERT INTO user_info
(Username, Email_Address, Password, Created_At)
VALUES ('yonghwan', 'kusf0330@naver.com', 'asdf1234!', CURRENT_TIMESTAMP);

INSERT INTO user_info
(Username, Email_Address, Password, Created_At)
VALUES ('jiho', 'kusf0101@naver.com', 'asdf1234!', CURRENT_TIMESTAMP);

SELECT * FROM user_info;
image

4. sqlalchemy로 데이터 접근, crud.py 파일 내 함수 생성

from sqlalchemy import create_engine, text

db_connection_info = {
    'user': 'root',
    'password': 'asdf1234!',
    'host': '127.0.0.1',
    'port': 3306,
    'database': 'kusf'
}

db_url = f"mysql+mysqlconnector://{db_connection_info['user']}:{db_connection_info['password']}@{db_connection_info['host']}:{db_connection_info['port']}/{db_connection_info['database']}?charset=utf8"
print(db_url)

engine = create_engine(db_url, max_overflow=0)

def read_username_data(Username: str):
    '''Username을 입력받아 전체 데이터를 반환하는 함수'''
    with engine.connect() as conn:
        rows = conn.execute(text("SELECT * FROM user_info WHERE Username = :Username"),{'Username': Username})     
    row_list = [row for row in rows]
    return row_list

if __name__ == "__main__":   # 엔트리 포인트
    yonghwan_data = read_username_data('yonghwan')
    print(yonghwan_data)
image
KUSFTWOO commented 1 year ago

  1. KUSF database 생성

  2. pitcher_data 테이블 생성

    CREATE TABLE pitcher_data(
pitcher_ID INT NOT NULL AUTO_INCREMENT,
pitcher_name VARCHAR(50),
pitcher_backnum INT,
team VARCHAR(50),
ball_speed INT,
PRIMARY KEY(pitcher_ID)
) CHARSET=utf8;

  3. 데이터 입력 후 출력

    
INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed)
VALUES(230001,"곽빈",47, "Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230002,"김명신",46,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230003,"박신지",66,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230004,"박정수",60,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230005,"박치국",1,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230006,"브랜든",48,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230007,"알칸타라",43,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230008,"이영하",50,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230009,"장원준",28,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230010,"정철원",65,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230011,"최승용",64,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230012,"최원준",61,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230013,"홍건희",17,"Doosan Bears",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230014,"강재민",55,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230015,"김범수",47,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230016,"남지민",11,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230017,"문동주",1,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230018,"박상원",58,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230019,"박준영",29,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230020,"산체스",34,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230021,"윤대경",5,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230022,"이태양",46,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230023,"장지수",69,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230024,"정우람",57,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230025,"주현상",66,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230026,"페냐",20,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230027,"한승주",59,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230028,"한승혁",26,"Hanhwa Eagles",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230029,"김기훈",53,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230030,"김승현",24,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230031,"김유신",49,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230032,"박준표",31,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230033,"앤더슨",64,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230034,"양현종",54,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230035,"윤영철",13,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230036,"윤중현",19,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230037,"이준영",20,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230038,"임기영",17,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230039,"장현식",50,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230040,"전상현",51,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230041,"최지민",39,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230042,"황동하",10,"Kia Tigers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230043,"김건희",12,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230044,"김동혁",60,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230045,"김선기",49,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230046,"김재웅",28,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230047,"맥키니",22,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230048,"안우진",41,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230049,"양현",39,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230050,"원종현",46,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230051,"이명종",97,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230052,"임창민",45,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230053,"장재영",61,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230054,"하영민",50,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230055,"후라도",75,"Kiwoom Heros",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230056,"고영표",1,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230057,"김민수",26,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230058,"김재윤",62,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230059,"박영현",60,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230060,"배제성",19,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230061,"벤자민",43,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230062,"손동현",41,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230063,"엄상백",18,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230064,"이상동",37,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230065,"이선우",68,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230066,"전용주",64,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230067,"주권",38,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230068,"쿠에바스",32,"KT Wiz",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230069,"고우석",19,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230070,"김진성",42,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230071,"박명근",39,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230072,"송은범",46,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230073,"오석주",57,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230074,"이우찬",37,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230075,"이정용",31,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230076,"이지강",50,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230077,"임찬규",1,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230078,"정우영",18,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230079,"최동환",13,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230080,"켈리",3,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230081,"플럿코",45,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230082,"함덕주",11,"LG Twins",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230083,"구승민",22,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230084,"김상수",24,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230085,"김원중",34,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230086,"김진욱",15,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230087,"박세웅",21,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230088,"반즈",28,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230089,"스트레일리",58,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230090,"신정락",31,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230091,"심재민",39,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230092,"이인복",35,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230093,"정성종",56,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230094,"진승현",26,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230095,"최이준",33,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230096,"한현희",16,"Lotte Giants",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230097,"김시훈",21,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230098,"김영규",17,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230099,"류진욱",41,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230100,"배민서",43,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230101,"송명기",11,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230102,"신민혁",53,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230103,"와이드너",27,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230104,"이용찬",22,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230105,"임정호",30,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230106,"전사민",57,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230107,"조민석",64,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230108,"페디",13,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230109,"하준영",39,"NC Dinos",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230110,"김대우",17,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230111,"김태훈",27,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230112,"뷰캐넌",4,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230113,"수아레즈",57,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230114,"양창섭",1,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230115,"오승환",21,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230116,"우규민",2,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230117,"원태인",18,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230118,"이승현",20,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230119,"이승현",54,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230120,"이재익",45,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230121,"최채흥",56,"Samsung Lions",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230122,"고효준",15,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230123,"김광현",29,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230124,"노경은",38,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230125,"문승원",42,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230126,"박종훈",50,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230127,"서진용",22,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230128,"송영진",98,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230129,"엘리아스",25,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230130,"오원석",47,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230131,"이건욱",16,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230132,"이로운",92,"Ssg Landers",NULL);

INSERT INTO pitcher_data(pitcher_ID, pitcher_name,pitcher_backnum, team, ball_speed) VALUES(230133,"임준섭",48,"Ssg Landers",NULL);

SELECT * FROM pitcher_data;

![image](https://github.com/kyohoonsim/kusf-data-2023-1/assets/134114521/974cc7a1-06a2-42f2-96d5-6127ec8e6c59)

4. pitcher.py 파일 생성 후 함수 생성

from sqlalchemy import create_engine, text

db_connection_info = { 'user': 'root', 'password': 'asdf1234!', 'host': 'localhost', # 127.0.0.1 'port': '3306', 'database': 'kusf' }

db_url = f"mysql+mysqlconnector://{db_connection_info['user']}:{db_connection_info['password']}@{db_connection_info['host']}:{db_connection_info['port']}/{db_connection_info['database']}?charset=utf8"

print(db_url)

engine = create_engine(db_url, max_overflow=0)

def read_player_pitcher_data(pitcher_ID: int): '''pitcher_ID를 입력하여 선수정보를 반환해주는 함수''' with engine.connect() as conn: rows = conn.execute(text("select * from pitcher_data where pitcher_ID = :pitcher_ID"), {'pitcher_ID' : pitcher_ID})

row_list = [row for row in rows]
return row_list

if name == "main": kim_pitching_data = read_player_pitcher_data('230085') print(kim_pitching_data)


![image](https://github.com/kyohoonsim/kusf-data-2023-1/assets/134114521/f65e7a99-0c11-477d-95c0-b8ad28bf13aa)
expediore commented 1 year ago

1. 2. study DB 안에 kutigers 라는 table 생성

CREATE TABLE kutigers (
    studentID VARCHAR(20) NOT NULL,
    name VARCHAR(20) NOT NULL,
    main_position VARCHAR(5),
    sub_position VARCHAR(20),
    defense_hand VARCHAR(5) NOT NULL,
    batting_hand VARCHAR(5) NOT NULL,
    back_number INT,
    PRIMARY KEY(studentID)  
) CHARSET = utf8;

3. kutigers에 데이터 삽입

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2018160312', '이준수', 'P', 'SS', '우투', '우타', '23');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2018170966', '이현우', 'CF', 'LF', '우투', '우타', '1');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2018130111', '장세윤', 'SS', '1B', '우투', '우타', '29');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2018170222', '조성진', 'LF', 'RF', '좌투', '우타', '5');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2018120119', '김현석', 'C', 'SS', '우투', '우타', '88');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2019170333', '신윤철', 'LF', 'RF', '우투', '우타', '3');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2020100017', '김성진', 'P', 'C', '우투', '우타', '99');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2018120119', '김현석', 'C', 'SS', '우투', '우타', '88');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2021170444', '정원재', 'RF', 'C', '우투', '우타', '11');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2021160211', '강경태', '1B', '3B', '우투', '우타', '17');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2021130555', '정도훈', '2B', NULL , '우투', '우타', '2');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2022110444', '김한겸', '3B', 'C', '우투', '우타', '24');

INSERT INTO kutigers(studentID, name, main_position, sub_position, defense_hand, batting_hand, back_number)
VALUES ('2022160115', '안준혁', 'P', 'RF', '우투', '좌타', '18');

image

4. sqlalchemy로 데이터 접근, kutigers.py 파일 내 함수 생성

from sqlalchemy import create_engine, text

db_connection_info = {
    'user' : 'root',
    'password' : 'asdf1234!',
    'host' : '127.0.0.1',
    'port' : 3306,
    'database' : 'study'
}

db_url = f"mysql+mysqlconnector://{db_connection_info['user']}:{db_connection_info['password']}@{db_connection_info['host']}:{db_connection_info['port']}/{db_connection_info['database']}?charset=utf8"
engine = create_engine(db_url, max_overflow=0)

def show_kutigers_position(position : str) :
    with engine.connect() as conn :
        rows = conn.execute(text("select * from kutigers where main_position = :position or sub_position = :position"), {'position' : position})

    row_list = [row for row in rows]
    return row_list

5. fastapi에서 엔드포인트 생성해서 kutigers.함수 호출해서 데이터 제공

from fastapi import FastAPI

import kutigers

app = FastAPI()

@app.get("/kutigers/{position}")
def get_kutigers_data(position) :

    kutigers_player = kutigers.show_kutigers_position(position)

    temp = list()
    for data in kutigers_player :
        temp.append(list(data))

    return temp

image image

monimanimo commented 1 year ago

1.데이터베이스 생성 CREATE DATABASE foulball; 2.테이블 생성 CREATE table KUSF_foul( playerid INT(20) not null, name VARCHAR(20), backnum INT(20), birth DATE, primary key(playerid) )charset=utf8; 3.데이터 입력(insert) INSERT INTO KUSF_foul(playerid, name, backnum, birth) values(000001, '성준모', 1,'2002-03-19'); INSERT INTO KUSF_foul(playerid, name, backnum, birth) values(000002, '이준수', 2, '2000-01-01'); INSERT INTO KUSF_foul(playerid, name, backnum, birth) values(000003, '유예린', 3, '2003-02-02'); INSERT INTO KUSF_foul(playerid, name, backnum, birth) values(000004, '이규헌', 4, '2001-03-03'); image

4.sqlalchemy로 데이터 접근, crud.py 파일 내 함수 생성

db_connection_info = {
    'user': 'root',
    'password': 'asdf1234!',
    'host': '127.0.0.1', 
    'port': 3306,
    'database': 'foulball'
}

db_url = f"mysql+mysqlconnector://{db_connection_info['user']}:{db_connection_info['password']}@{db_connection_info['host']}:{db_connection_info['port']}/{db_connection_info['database']}"

engine = create_engine(db_url, max_overflow=0)

def read_player_information_data(playerid: int):
    '''선수ID를 입력받아 선수정보 데이터를 반환해주는 함수'''
    with engine.connect() as conn:
        rows = conn.execute(text("SELECT * FROM KUSF_foul WHERE playerid = :playerid"), {'playerid': playerid})

    row_list = [row for row in rows]
    return row_list
if __name__ == "__main__":  
    junmo_player_data = read_player_information_data(1)
    print(junmo_player_data)

image

5.fastapi에서 엔드포인트 생성해서 crud.함수 호출해서 데이터 제공

from fastapi import FastAPI

import foul 

app = FastAPI()

@app.get("/KUSF_foul/{playerid}")
def get_playerinfo_data(playerid: int):
    junmo_data = foul.read_player_information_data(playerid)
    print(junmo_data)

    temp = []
    for data in junmo_data:
        temp.append(list(data))

    return {'junmo_data': temp}

image

xinnazim commented 1 year ago

  1. 데이터베이스 생성 kusf3 이라는 데이터 베이스 생성

  2. 테이블 생성

    
USE kusf3;

CREATE TABLE baseball_game_date ( game_ID VARCHAR(10) ,date DATE , time TIME , away_team VARCHAR(10) , home_team VARCHAR(10) , stadium VARCHAR(20) ,PRIMARY KEY(game_ID) ) CHARSET=utf8;


3.  데이터 입력(insert)

0701

INSERT INTO baseball_game_date (game_ID, date, time, away_team,home_team,stadium) VALUES('0701_1', '2023-07-01', '18:00:00', '한화', '삼성', '대구삼성라이온즈파크');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0701_2', '2023-07-01', '18:00:00', '기아', 'LG', '잠실야구장');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0701_3', '2023-07-01', '18:00:00', 'NC', 'KT', '수원케이티위즈파크');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0701_4', '2023-07-01', '18:00:00', '두산', '롯데', '울산문수야구장');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0701_5', '2023-07-01', '18:00:00', 'SSG', '키움', '고척스카이돔'); ...

0713

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0713_1', '2023-07-13', '18:30:00', '한화', 'LG', '잠실야구장');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0713_2', '2023-07-13', '18:30:00', 'KT', '키움', '고척스카이돔');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0713_3', '2023-07-13', '18:30:00', '롯데', 'NC', '창원NC파크');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0713_4', '2023-07-13', '18:30:00', '두산', 'SSG', '인천SSG랜더스필드');

INSERT INTO baseball_game_date (game_ID,date,time,away_team,home_team,stadium) VALUES ('0713_5', '2023-07-13', '18:30:00', '삼성', '기아', '광주-기아챔피언스필드');

4. sqlalchemy로 데이터 접근, crud.py 파일 내 함수 생성

from sqlalchemy import create_engine, text

db_connection_info = { 'user': 'root', 'password': 'asdf1234!', 'host': '127.0.0.1', 'port': 3306, 'database': 'kusf3' }

db_url = f"mysql+mysqlconnector://{db_connection_info['user']}:{db_connection_info['password']}@{db_connection_info['host']}:{db_connection_info['port']}/{db_connection_info['database']}?charset=utf8" print(db_url)

engine = create_engine(db_url, max_overflow=0)

홈 팀에 따라 경기 일정을 반환해주는 함수

def home_team_baseball_date(home_team: str): with engine.connect() as conn: rows = conn.execute(text("select * from baseball_game_date where home_team = :home_team"), {'home_team': home_team})

row_list = [row for row in rows]
return row_list

if name == "main": kiwoom_game_date = home_team_baseball_date('키움') print(kiwoom_game_date)

경기 날짜 별로 일정을 알려주는 함수

def read_baseball_game_date(date): with engine.connect() as conn: rows = conn.execute(text("select * from baseball_game_date where date = :date"), {'date': date})

row_list = [row for row in rows]
return row_list

if name == "main": baseball_game_0711 = read_baseball_game_date('2023-07-11') print(baseball_game_0711)


![스크린샷 2023-07-08 003833](https://github.com/kyohoonsim/kusf-data-2023-1/assets/127488424/6493a7da-2864-4cd2-a6d6-a5fd0ef6237f)

5. fastapi에서 엔드포인트 생성해서 crud.함수 호출해서 데이터 제공

from fastapi import FastAPI

import crud

app = FastAPI()

@app.get("/test") def test(): return "테스트"

@app.get("/home_team/{home_team}") def get_home_date(home_team:str): home_data = crud.home_team_baseball_date(home_team) print(home_data)

temp = []
for data in home_data:
    temp.append(list(data))
return {'home_data': temp}


![스크린샷 2023-07-08 010717](https://github.com/kyohoonsim/kusf-data-2023-1/assets/127488424/16f56478-6c45-459d-a47d-aaafe0c20fd3)

![스크린샷 2023-07-08 010740](https://github.com/kyohoonsim/kusf-data-2023-1/assets/127488424/e7390f7b-ca79-417f-b4d7-e202afdcfede)
dhEhdod commented 1 year ago

image image image image