Quiz 22. 파이썬 백엔드 서버 만들기

[dolzi]

2개의 함수를 crud.py에 추가하고, 그에 대응하는 endpoint를 main.py에 추가할 것.

[chiseongan]

#crud.py
def read_player_homerun(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(text("select HR, yearID from batting where playerID = :playerID"),
        {'playerID': playerID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return(data)

#main.py
@app.get("/batting/{playerID}")
def player_HR_route(playerID: str):
    return crud.read_player_homerun(playerID)

#crud.py
def read_player_W(yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(text("select W, playerID from pitching where yearID = :yearID order by W DESC limit 5"),
        {'yearID': yearID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return(data)

#main.py
@app.get("/win/{yearID}")
def player_W_route(yearID: str):
    return crud.read_player_W(yearID)

1조 안치성 완료했습니다

[vincentius0405]

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
    return data

def read_player_pitching_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(text("select * from pitching where playerID =:playerID AND yearID = :yearID"), {'playerID': playerID, 'yearID':
yearID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

        return data

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_batting_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(text("select * from pitching where playerID =:playerID AND yearID = :yearID"), {'playerID': playerID, 'yearID':
yearID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)7

    return data

def read_player_pitching_so_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(text("select SO from pitching where playerID =:playerID AND yearID = :yearID"), {'playerID': playerID, 'yearID': yearID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

from fastapi import FastAPI, Body

import crud

app = FastAPI(title="레먼데이터베이스 API")

@app.get("/players/{plyaerID}")
def player_info_route(playerID: str):
    return crud.read_player_info(playerID)

@app.get("/batting")
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

@app.post("/pitching")
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_pitching_data(playerID, season)

@app.post("/batting/season")
def batting_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_pitching_data(playerID, season)

@app.get("/picthing/SO")
def batting_route(playerID: str, yearID: str):
    return crud.read_player_pitching_so_data(playerID, yearID)

3조 장훈 완료하였습니다.

[selfrescue]

# 레먼 데이터 베이스의 데이터를 API 서버를 통해 제공하기
from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"
engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_pitching_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from pitching where playerID = :playerID and yearID = :yearID"),
            {'playerID': playerID, 'yearID' : yearID}
        )

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_info(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(text('select * from people where playerID = :playerID'), {'playerID': playerID})

    columns = rows.keys()
    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
    return data

# 두 개의 함수 추가
# 해당 타자의 역대 홈런 데이터 출력
def read_player_batting_HR_data(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(text('select yearID, HR from batting where playerID = :playerID'), {'playerID': playerID})

    columns = rows.keys()
    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
    return data

# 해당 선수의 역대 팀 데이터 출력
def read_player_batting_teamID_data(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(text('select yearID, teamID from batting where playerID = :playerID'), {'playerID': playerID})

    columns = rows.keys()
    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
    return data

from fastapi import FastAPI, Body

import crud                                 # crud.py import

app = FastAPI(title = '레먼데이터베이스 API')   # API 제목 부여

# END POINT 1
@app.get("/players/{playerID}")             # 경로 매개변수 활용 예시
                                            # /players/ryuhy01
def player_info_route(playerID: str):
    return crud.read_player_info(playerID)

# END POINT 2
@app.get("/batting")                        # 쿼리 매개변수 활용 예시
                                            # /batting?playerID=ryuhy01
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

# END POINT 3
@app.post("/pitching")                      # Request Body 활용 예시
def pitching_route(
    playerId: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_pitching_data

# 두개의 endpoint 추가
@app.get("/batting/HR")                     # http://127.0.0.1:8999/batting/HR?playerID=ryuhy01
def batting_HR_route(playerID: str):
    return crud.read_player_batting_HR_data(playerID)

@app.get("/batting/teams")            # http://127.0.0.1:8999/batting/teams?playerID=ryuhy01
def batting_teamID_route(playerID: str):
    return crud.read_player_batting_teamID_data(playerID)

2조 유혜연 완료했습니다

[kgh10235]

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)

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

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

def read_player_pitching_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from pitching where playerID = :playerID AND yearID = :yearID"),
        {'playerID': playerID, 'yearID': yearID}
        )

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

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

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
    return data

def read_player_HR(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select HR from batting where playerID = :playerID and yearID = :yearID"), 
            {"playerID" : playerID, "yearID": yearID})

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

def read_player_debutdate(playerID: str):
    with engine. connect() as conn:
        rows = conn.execute(text("select debut_date from people where playerID= :playerID"), {"playerID":playerID})

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
    return data

from fastapi import FastAPI, Body

import crud

app = FastAPI(title="레먼데이터베이스 API")

@app.get("/players/{playerID}")
def player_info_route(playerID: str):
    return crud.read_player_info(playerID)

@app.get("/batting")
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

@app.post("/pitching")
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_pitching_data(playerID, season)

@app.post("/HR")
def game_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_HR(playerID, season)

@app.get("/debutdate")
def debut_date_route(playerID: str):
    return crud.read_player_debutdate(playerID)

5조 김기훈 완료했습니다

[jieunlisa]

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL="sqlite:///./lahmansbaseballdb.sqlite"

engine=create_engine( SQLALCHEMY_DATABASE_URL )

def read_player_batting_season_data(playerID: str, yearID: int): with engine.connect() as conn: rows=conn.execute( text("select* from batting where playerID= :playerID and yearID= :yearID"), {'playerID': playerID, 'yearID': yearID} ) columns=rows.keys()

data=[]
for row in rows:
    data_dict={column: row[idx] for idx, column in enumerate(columns)}
    data.append(data_dict)

return data

def read_player_pitching_team_data(playerID: str, teamID: str): with engine.connect() as conn: rows=conn.execute( text("select* from pitching where playerID= :playerID and teamID= :teamID"), {'playerID': playerID, 'teamID': teamID} ) columns=rows.keys()

data=[]
for row in rows:
    data_dict={column: row[idx] for idx, column in enumerate(columns)}
    data.append(data_dict)

return data

from fastapi import FastAPI, Body

import crud

app=FastAPI(title="레먼데이터베이스 API")

@app.post("/batting1") def batting_season_route(playerID: str=Body(...), season: int=Body(...)): return crud.read_player_batting_season_data(playerID, season)

@app.post("/pitching1") def pitching_team_route(playerID: str=Body(...), teamID: str=Body(...)): return crud.read_player_pitching_team_data(playerID, teamID)

4조 이지은 완료했습니다

[benmoo1292]

2조 배은빈 완료하였습니다

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"

engine = create_engine(
 SQLALCHEMY_DATABASE_URL
)

def read_player_batting_data(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from batting where playerID = :playerID"),
            {'playerID': playerID}
        )
    columns = rows.keys()
    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
    return data

def read_player_pitching_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from pitching where playerID = :playerID AND yearID = :yearID"), 
            {'playerID': playerID, 'yearID':yearID}
        )

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_awardsplayers_data(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from awardsplayers where playerID = :playerID"),
            {'playerID': playerID}
        )
    columns = rows.keys()
    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
    return data

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

from fastapi import FastAPI, Body

import crud

app = FastAPI(title="레먼데이터베이스 API")

@app.get("/players/{playerID}") # 경로 매개변수 활용 예시
def player_info_route(playerID: str):
    return crud.read_player_info(playerID)

@app.get("/batting") # 쿼리 매개변수 활용 예시
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

@app.post("/pitching") # Request Body 활용 예시
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
      return crud.read_player_pitching_data(playerID, season)

@app.get("/awardsplayers")
def awardsplayers_route(playerID: str):

    return crud.read_player_awardsplayers_data(playerID)

@app.get("/schools")
def schools_route(playerID: str):

    return crud.read_player_awardsplayers_data(playerID)

[yunseeo]

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)

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

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

def read_player_pitching_season_data(playerID: str, yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from pitching where playerID = :playerID AND yearID = :yearID"), #sql문 실행. 
        {'playerID': playerID, 'yearID': yearID}
        )

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

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

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)

        return data

#함수 추가. 
def read_player_batting_season_data(playerID: str, yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from batting where playerID = :playerID AND yearID = :yearID"), #sql문 실행. 
        {'playerID': playerID, 'yearID': yearID}
        )

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

def read_player_salaries_season_data(playerID: str, yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from salaries where playerID = :playerID AND yearID = :yearID"),
        {'playerID': playerID, 'yearID': yearID}
        )

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

from fastapi import FastAPI, Body

import crud  #crud 쓰기 위해 가져옴. 

app = FastAPI(title="레먼데이터베이스 API")

#엔드포인트.
@app.get("/players/{playerID}") #이 {} 추가해야 쿼리 매개변수 아니고 경로 매개변수로 인식. 
def player_info_route(playerID: str):
    return crud.read_player_info(playerID)

@app.get("/batting")
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

@app.post("/pitching")
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_pitching_season_data(playerID, season)

@app.get("/batting/season") #위 배팅 경로와 루트 모두 다 다르게 해줘야 함. 
def batting_season_route(
    playerID: str,
    season: int 
):
    return crud.read_player_batting_season_data(playerID, season)

@app.get("/salaries")
def salaries_route(
    playerID: str,
    season: int
):
    return crud.read_player_salaries_season_data(playerID, season)

1조 정윤서 완료했습니다

[rkskffktk]

def read_player_batting_HR_data(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(text('select yearID, HR from batting where playerID = :playerID'), {'playerID': playerID})

    columns = rows.keys()
    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
    return data

def read_player_debutdate(playerID: str):
    with engine. connect() as conn:
        rows = conn.execute(text("select debut_date from people where playerID= :playerID"), {"playerID":playerID})

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
    return data

@app.get("/batting/HR")                     
def batting_HR_route(playerID: str):
    return crud.read_player_batting_HR_data(playerID)

@app.get("/debutdate")
def debut_date_route(playerID: str):
    return crud.read_player_debutdate(playerID)

[kim-sung-yeon]

#teamID를 치면 PlayerID가 나오도록 설정
def read_team_info(teamID: str):
    with engine.connect() as conn:
      rows=conn.execute(
        text("select playerID from batting where teamID= :teamID"),
       {'teamID':teamID}
      )
    columns = rows.keys()

    data=[]
    for row in rows:
        data_dict={column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data
    @app.get("/team/{teamID}") 
def team_info_route(teamID:str):
        return crud.read_team_info(teamID)

#선수이름을 검색하면 AVG가 나오도록
def read_AVG_info(playerID: str):
    with engine.connect() as conn:
      rows=conn.execute(
        text("select playerID,HR,H,(H+0.0)/AB as AVG from batting where playerID= :playerID"),
       {'playerID':playerID}
      )
    columns = rows.keys()

    data=[]
    for row in rows:
        data_dict={column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data
    @app.get("/AVG/{playerID}") 
def AVG_info_route(playerID:str):
        return crud.read_AVG_info(playerID)

[limgw97]

crud

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
        return data

def read_player_pitching_season_data(playerID: str, yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from pitching where playerID = :playerID and yearID = :yearID"),
            {'playerID': playerID, 'yearID': yearID}

        )
    columns = rows.keys()
    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)
        return data

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

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
    return data

def read_SO_leaders(yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(text("select pe.nameFirst, pe.nameLast, p.SO from pitching AS p LEFT OUTER JOIN people AS pe ON pe.playerID = p.playerID where yearID = :yearID order by SO desc limit 30;"), {'yearID': yearID})

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
    return data

def read_HR_leaders(yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(text("select pe.nameFirst, pe.nameLast, b.HR from batting AS b LEFT OUTER JOIN people AS pe ON pe.playerID = b.playerID where yearID = :yearID order by HR desc limit 30;"), {'yearID': yearID})

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)

    return data

main

from fastapi import Body, FastAPI

import crud

app = FastAPI(title="레먼데이터베이스 API")

@app.get("/players/{playerID}") # /players/ryuhy01
def player_info_route(playerID: str):
    return crud.read_player_info(playerID)

@app.get("/batting")
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

@app.post("/pitching")
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_pitching_season_data(playerID, season)

@app.get("/SOLeaders")
def SOleaders(yearID: int):
    return crud.read_SO_leaders(yearID)

@app.get("/HRLeaders")
def HRleaders(yearID: int):
    return crud.read_HR_leaders(yearID)

임규원 완료했습니다.

[hyunah0815]

from sqlalchemy import create_engine, text
SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"
engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)
def read_player_homerun_data(playerID: str):
     with engine.connect() as conn:
        rows= conn.execute (text("select HR, yearID from batting where playerID= :playerID "),{'playerID': playerID})

        columns=rows.keys()

        data=[]
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)

     return data

def read_player_pitching_data(playerID: str, yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from pitching where playerID = :playerID AND yearID = :yearID"), #sql문 실행. 
        {'playerID': playerID, 'yearID': yearID}
        )

        columns = rows.keys()

        data = []
        for row in rows:
            data_dict = {column: row[idx] for idx, column in enumerate(columns)}
            data.append(data_dict)
        return data

python
from fastapi import FastAPI, Body

import crud

app = FastAPI(title="레먼데이터베이스 API")
@app.get("/HR/{playerID}")
def player_HR_route(playerID: str):
    return crud.read_player_homerun(playerID)

@app.get("/pitching/{playerID}")
def player_pitching_route(playerID: str):
    return crud.read_player_pitching(playerID)

최현아 완료했습니다

[duungkang]

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"
engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_pitching_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows= conn.execute(
            text("select * from pitching where playerID = :playerID and yearID = :yearID "),
            {'playerID': playerID, 'yearID': yearID}
        )

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

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

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_batting_seasonHR(playerID:str, yearID:int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select HR from batting where playerID = :playerID and yearID = :yearID"), {'playerID' : playerID, 'yearID' : yearID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_pitching_seasonSO(playerID:str, yearID:int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select SO from pitching where playerID = :playerID and yearID = :yearID"),
            {'playerID' : playerID, 'yearID' : yearID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

from fastapi import FastAPI, Body

import crud 

app = FastAPI(title="레먼데이터베이스 API")

@app.get("/players/{playerID}") #경로 매개변수 활용 예시
def player_info_route(playerID:str):
    return crud.read_player_info(playerID)

@app.get("/batting") #쿼리 매개변수 활용 예시 
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

@app.post("/pitching") # Request Body 활용 예시
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
): return crud.read_player_pitching_data(playerID, season)   

@app.post("/batting/homeruns") 
def batting_route(
    playerID: str = Body(...),
    season: int = Body(...)
): return crud.read_player_batting_seasonHR(playerID, season)

@app.post("/pitching/strikeouts")
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
): return crud.read_player_pitching_seasonSO(playerID, season)

[hyunsoda]

from sqlalchemy import create_engine, text

SQLALCHEMY_DATABASE_URL = "sqlite:///./lahmansbaseballdb.sqlite"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)
def read_player_batting_data(playerID: str):
    with engine.connect() as conn:
        rows = conn.execute(
            text("select * from batting where playerID = :playerID"),
            {'playerID': playerID}
        )

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in
enumerate(columns)}
        data.append(data_dict)

    return data

def read_player_pitching_data(playerID: str, yearID: str):
    with engine.connect() as conn:
        rows = conn.execute(text("select * from pitching where playerID =:playerID AND yearID = :yearID"), {'playerID': playerID, 'yearID':yearID})

    columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
        data.append(data_dict)

    return data

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

        columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column: row[idx] for idx, column in enumerate(columns)}
    data.append(data_dict)

    return data

def read_player_bb_data(playerID: str, yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(text("select BB from pitching where playerID =:playerID AND yearID =:yearID"),{'playerID':playerID})

        columns = rows.keys()

    data = []
    for row in rows:
        data_dict = {column : row[idx] for idx, column in enumerate(columns)}
    data.append(data_dict)

    return data

def read_player_HR_data(playerID: str, yearID: int):
    with engine.connect() as conn:
        rows = conn.execute(text("select HR from batting where playerID =:playerID AND yearID = :yearID"),{'playerID' = playerID})

        columns = rows.keys()```

```python
from fastapi import FastAPI, Body

import crud

app = FastAPI(title="레먼데이터베이스 API")

@app.get("/players/{playerID}")
def player_info_route(playerID: str):
    return crud.read_player_info(playerID)

@app.get("/batting")
def batting_route(playerID: str):
    return crud.read_player_batting_data(playerID)

@app.post("/pitching")
def pitching_route(
    playerID: str = Body(...),
    season: int = Body(...)
):
    return crud.read_player_pitching_data(playerID, season)

@app.get("/pitching/bb")
def pitching_bb_route(playerID:str,yearID: int):
    return crud.read_player_bb_data(playerID, yearID)

@app.get("/batting/HR")
def batting_HR_route(playerID:str, yearID:int):
    return crud.read_player_HR_data(playerID, yearID)```

안소현 완료했습니다