In [1]:
import pymysql
In [2]:
db = pymysql.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"sakila",
charset='utf8',
)
In [3]:
rental_df = pd.read_sql("SELECT * FROM rental;", db)
In [4]:
rental_df.head(2)
Out[4]:
In [5]:
SQL_QUERY = """
SELECT
LEFT(rental_date, 7) month,
COUNT(*) total_rentals_per_month,
COUNT(DISTINCT customer_id) unique_customers,
COUNT(*) / COUNT(DISTINCT customer_id) average_rentals_per_customer
FROM rental
GROUP BY month
ORDER BY average_rentals_per_customer DESC
;
"""
pd.read_sql(SQL_QUERY, db)
Out[5]:
In [ ]:
In [9]:
inventory_df = pd.read_sql("SELECT * FROM inventory;", db)
film_df = pd.read_sql("SELECT * FROM film;", db)
In [12]:
rental_df.head(2)
Out[12]:
In [10]:
inventory_df.head(2)
Out[10]:
In [11]:
film_df.head(2)
Out[11]:
In [21]:
SQL_QUERY = """
SELECT
LEFT(rental_date, 7) month,
COUNT(*) MONTH_PER_RENTALS,
COUNT(DISTINCT f.film_id) unique_films,
COUNT(*) / COUNT(DISTINCT f.film_id) average_rentals_per_film
FROM
rental r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON f.film_id = i.film_id
GROUP BY month
;
"""
pd.read_sql(SQL_QUERY, db)
Out[21]: