Quiz12. JOIN을 활용하여 선수 성으로 데이터를 조회하는 함수를 만들어보고 활용해보자.

[kyohoonsim]

집에서 꼭 해서 제출해주세요

[expediore]

def read_player_withlastname_data(lastname) :
    '''순서 성으로 선수 데이터를 반환해주는 함수'''
     with engine.connect() as conn:
         rows = conn.execute(text("SELECT pitching.yearID, pitching.SO, MAX(batting.HR), people.nameFirst, people.nameLast FROM people INNER JOIN batting ON batting.playerID = people.playerID INNER JOIN pitching ON pitching.playerID = people.playerID WHERE people.nameLast = :성 GROUP BY people.playerID, pitching.yearID, pitching.SO, people.nameFirst, people.nameLast"), {'성' : lastname})

        row_list = [row for row in rows]
        return row_list

[KUSFTWOO]

def read_player_lastname_data(lastname):
    '''lastname으로 선수 데이터를 반환해주는 함수'''
    with engine.connect() as conn:
        rows = conn.execute(text("SELECT pitching.yearID, pitching.SO, MAX(batting.HR), people.nameFirst, people.nameLast FROM people INNER JOIN batting.playerID = people.playerID INNER JOIN pitching ON pitching.playerID = people.playerID WHERE people.nameLast = : 성 GROUP BY people.playerID, pitching.yearID, pitching.SO, people.nameFirst, people.nameLast"), {"성": lastname})

        row_list = [row for row in rows]
        return row_list

[monimanimo]

from sqlalchemy import create_engine, text

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

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_lastnamepitching_data(lastname: str):
    '''투수의 성을 입력받아 데이터를 반환해주는 함수'''
    with engine.connect() as conn:
        rows = conn.execute(text("SELECT *FROM pitching LEFT OUTER JOIN people ON pitching.playerID = people.playerID WHERE people.nameLast = :nameLast"), {'nameLast': lastname})

    row_list = [row for row in rows]
    return row_list

if __name__ == "__main__":  
    park_pitching_data = read_player_lastnamepitching_data('park')
    print(park_pitching_data)

[WooJHo]

from sqlalchemy import create_engine, text

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

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 read_player_data(namelast: str): with engine.connect() as conn: rows = conn.execute(text("select * from batting as b left outer join people as p on b.playerID =\ p.playerID where p.namelast = :namelast"),{'namelast' : namelast})

row_list = [row for row in rows]
return row_list

if name == "main": choo_data = read_player_data('choo') print(choo_data)

kang_data = read_player_data(('kang'))
print(kang_data)