db.close() # 이전에 연결된 데이터베이스 끊어주세요 (에러나면 이미 끊어진 것임)
host_name = "0.0.0.0"
username = "root"
password = "korea123"
database_name = "sakila" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
db = pymysql.connect(
host=host_name, # DATABASE_HOST
port=3306,
user=username, # DATABASE_USERNAME
passwd=password, # DATABASE_PASSWORD
db=database_name, # DATABASE_NAME
charset='utf8'
)
category_df = pd.read_sql("SELECT * FROM category;", db)
category_df
SQL = """
SELECT name, category_id FROM category
WHERE name = 'Comedy' OR name = 'Sports' OR name = 'Family'
"""
category_df = pd.read_sql(SQL, db)
category_df
film_category_df = pd.read_sql("SELECT * FROM film_category;", db)
film_category_df.head()
SQL = """
SELECT category_id FROM film_category WHERE film_id = 2;
"""
film_category_df = pd.read_sql(SQL, db)
film_category_df
SQL_QUERY = """
SELECT COUNT(*) FROM film_category GROUP BY category_id;
"""
category_film_count_df = pd.read_sql(SQL_QUERY, db)
category_film_count_df
SQL_QUERY = """
SELECT *
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy'
"""
pd.read_sql(SQL_QUERY, db)
SQL_QUERY = """
SELECT COUNT(*)
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy'
"""
pd.read_sql(SQL_QUERY, db)
SQL_QUERY = """
SELECT category.name, COUNT(*)
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy' OR category.name = 'Sports' OR category.name = 'Family'
GROUP BY category.category_id
"""
pd.read_sql(SQL_QUERY, db)
SQL_QUERY = """
SELECT category.name, COUNT(*)
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy' OR category.name = 'Sports' OR category.name = 'Family'
GROUP BY category.category_id
"""
pd.read_sql(SQL_QUERY, db)
SQL_QUERY = """
SELECT category.name, COUNT(*) AS category_film_count
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy' OR category.name = 'Sports' OR category.name = 'Family'
GROUP BY category.category_id
"""
pd.read_sql(SQL_QUERY, db)