Statement of Completion#7dbd0f66
Bia Almeida has successfully completed this project.
Beginning SQL: Basic Selection Statements
medium
Practice querying MySQL from Python using Sakila
Resolution
Activities
Importing and connecting¶
In [20]:
import pymysql.cursors
import pandas as pd
In [13]:
HOST = "192.121.118.3"
USER = "datawars"
DATABASE = "sakila"
PASSWORD = ""
PORT = 3306
Verify the connection works:
In [14]:
conn = pymysql.connect(
host=HOST, user=USER, port=PORT,
password=PASSWORD, database=DATABASE,
cursorclass=pymysql.cursors.DictCursor)
conn
Out[14]:
<pymysql.connections.Connection at 0x7de7565f3e10>
In [15]:
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) as film_count FROM film")
cursor.fetchall()
Out[15]:
[{'film_count': 1000}]
1) Read the top 10 more expensive films that have a length lower than 100 minutes¶
In [18]:
cursor.execute("SELECT film_id, title, release_year, length, replacement_cost FROM film WHERE length<100 ORDER BY replacement_cost DESC, length DESC LIMIT 10")
results_activity_1 = cursor.fetchall()
2) Create a pandas DataFrame with all the actors from Sakila¶
In [27]:
cursor.execute("SELECT actor_id AS 'Actor ID', first_name AS 'First Name', last_name AS 'Last Name' FROM actor")
result = cursor.fetchall()
results_activity_2 = pd.DataFrame(result).set_index('Actor ID')
results_activity_2
Out[27]:
| First Name | Last Name | |
|---|---|---|
| Actor ID | ||
| 1 | PENELOPE | GUINESS |
| 2 | NICK | WAHLBERG |
| 3 | ED | CHASE |
| 4 | JENNIFER | DAVIS |
| 5 | JOHNNY | LOLLOBRIGIDA |
| ... | ... | ... |
| 196 | BELA | WALKEN |
| 197 | REESE | WEST |
| 198 | MARY | KEITEL |
| 199 | JULIA | FAWCETT |
| 200 | THORA | TEMPLE |
200 rows × 2 columns
3) Retrieve all films with rental_duration equals to 5 and a rental_rate equals to $0.99¶
In [36]:
query = """
SELECT
film_id, title, rental_duration, length, rental_rate
FROM film
WHERE rental_duration = 5
ORDER BY length"""
cursor.execute(query)
results = cursor.fetchall()
from decimal import Decimal
results_activity_3 = []
for result in results:
if result['rental_rate'] == Decimal("0.99"):
results_activity_3.append(result)
4) Find the customer with most rentals¶
In [99]:
cursor.execute("SELECT * FROM rental")
data = cursor.fetchall()
df = pd.DataFrame(data)
df2 = (df.groupby('customer_id')
.agg('count')
.sort_values('rental_id',ascending=False)
.iloc[:1,0]
.reset_index()
.rename(columns={'rental_id':'TOTAL_NUMBER_OF_RENTALS',
'customer_id':'CUSTOMER_ID'}))
top_customer=tuple(df2.iloc[0])
5) Find a list of all the films with Deleted Scenes¶
In [107]:
cursor.execute("SELECT film_id, special_features FROM film")
df = pd.DataFrame(cursor.fetchall())
df2 = df.loc[df['special_features'].str.contains('Deleted Scenes',na=False)]
results_activity_5 = list(df2['film_id'])
6) What's the total sum of replacement_cost of films in with rating G?¶
In [111]:
cursor.execute("SELECT replacement_cost FROM film WHERE rating='G'")
df = pd.DataFrame(cursor.fetchall())
total_sum_replacement_g_films = df['replacement_cost'].sum()
7) Who's the actor that performed the most?¶
In [114]:
cursor.execute("SELECT * FROM film_actor")
df = pd.DataFrame(cursor.fetchall())
df.groupby('actor_id').count.sort_values('film_id')
--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) Cell In[114], line 3 1 cursor.execute("SELECT * FROM film_actor") 2 df = pd.DataFrame(cursor.fetchall()) ----> 3 df.groupby('actor_id').count.sort_values('film_id') AttributeError: 'function' object has no attribute 'sort_values'
In [ ]:
