<jupyter_start><jupyter_text>[IT Academy - Data Science with Python](https://www.barcelonactiva.cat/es/itacademy) [S17 T02: SQL Database](https://github.com/jesussantana/SQL-Database/blob/main/notebooks/S17_T02_SQL_Database.ipynb)[![forthebadge made-with-python](http://ForTheBadge.com/images/badges/made-with-python.svg)](https://www.python.org/)  [![Made withJupyter](https://img.shields.io/badge/Made%20with-Jupyter-orange?style=for-the-badge&logo=Jupyter)](https://jupyter.org/try)  [![Linkedin: JesusSantana](https://img.shields.io/badge/-JesusSantana-blue?style=flat-square&logo=Linkedin&logoColor=white&link=https://www.linkedin.com/in/chus-santana/)](https://www.linkedin.com/in/chus-santana/)  [![GitHub JesusSantana](https://img.shields.io/github/followers/jesussantana?label=follow&style=social)](https://github.com/jesussantana)  **********************  Level 1- Exercise 1    - Create a simple relational database using MySQL (https://www.mysql.com/) and connect it to Python ******************<jupyter_code>import seaborn as sns
import numpy as np
import matplotlib.pyplot as plt
# ^^^ pyforest auto-imports - don't write above this line
# ==============================================================================
# Auto Import Dependencies
# ==============================================================================
# pyforest imports dependencies according to use in the notebook
# ==============================================================================

#sudo pip install mysql-connector-python
#sudo apt-get install mysql-server
#%pip install sqlalchemy
#%pip install pymysql
#%pip install python-dotenv
# Dependencies not Included in Auto Import*
# ==============================================================================
import pymysql

import mysql.connector
from mysql.connector import connect, Error

from getpass import getpass
from sqlalchemy import create_engine

from dotenv import load_dotenv
from matplotlib.pyplot import figure

from wordcloud import WordCloud, STOPWORDS

import nltk
from io import StringIO
import collections as co
# Pandas configuration
# ==============================================================================
pd.set_option('display.max_columns', None)

# Matplotlib configuration
# ==============================================================================
from matplotlib.ticker import StrMethodFormatter
%matplotlib inline

# Warnings configuration
# ==============================================================================
import warnings
warnings.filterwarnings('ignore')

# Folder configuration
# ==============================================================================
from os import path
import sys
new_path = '../scripts/'
if new_path not in sys.path:
    sys.path.append(new_path)<jupyter_output><empty_output><jupyter_text>***********************  Start MySQL Workbench server mysql-workbench  Check is runing ![SQL Workbench Server](img/MySQL_Workbench_Server.png)  Enter credentials<jupyter_code>user_name = getpass("Enter username: ")
passw = getpass("Enter password: ")<jupyter_output>Enter username: ········
Enter password: ········<jupyter_text>Create Connection to the MySQL server<jupyter_code>connection = mysql.connector.connect(
  host = "localhost",
  user = user_name,
  password = <PASSWORD>,
  )<jupyter_output><empty_output><jupyter_text>Create a connection cursor<jupyter_code>mycursor = connection.cursor()<jupyter_output><empty_output><jupyter_text>Create Database<jupyter_code>mycursor.execute("DROP DATABASE IF EXISTS Mydatabase; CREATE DATABASE Mydatabase")

print(mycursor)
mydb = 'Mydatabase'
connection = mysql.connector.connect(
  host = "localhost",
  user = user_name,
  password = <PASSWORD>,
  database = mydb
)

mycursor = connection.cursor()<jupyter_output><empty_output><jupyter_text>Check Databases<jupyter_code>mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)<jupyter_output>('Mydatabase',)
('airbnb',)
('information_schema',)
('mydatabase',)
('mysql',)
('netflix',)
('performance_schema',)
('sakila',)
('sys',)
('world_x',)<jupyter_text>Create Table<jupyter_code>mycursor.execute("DROP TABLE IF EXISTS Customers; CREATE TABLE Customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255), city VARCHAR(255), phone VARCHAR(255))")
print(mycursor)
mydb = mysql.connector.connect(
  host = "localhost",
  user = user_name,
  password = <PASSWORD>,
  database = mydb
)

mycursor = mydb.cursor()<jupyter_output><empty_output><jupyter_text>Check Tables<jupyter_code>mycursor.execute("SHOW TABLES")
print(mycursor)
for x in mycursor:
  print(x)<jupyter_output><empty_output><jupyter_text>Insert Data to Table<jupyter_code>sql = "INSERT INTO Customers (name, address, city, phone) VALUES (%s, %s, %s, %s)"
values = [
    ('Hannah', 'Mountain 21', 'Espluges de Ll.', '973908304'),
    ('Michael', 'Valley 345', 'Barcelona', '657434898'),
    ('Laura', 'Gran Via 124', 'Barcelona', '973908309'), 
    ('Peter', 'Lowstreet 4', 'Barcelona', '657432837'),
    ('Amy', 'Apple st 652', 'Barcelona', '657432222'),
    ('Sandy', 'Ocean blvd 2', 'Mataró', '657432228'),
    ('Betty', 'Green Grass 1', 'Barcelona', '973908309'),
    ('Richard', 'Sky st 331', 'Barcelona', '637432678'),
    ('Susan', 'One way 98', 'Badalona', '655432888'),
    ('Vicky', 'Yellow Garden 2', 'Barcelona', '699432888'),
    ('Ben', 'Park Lane 38', 'Barcelona', '657432771'),
    ('William', 'Central st 954', 'Sant Cugat', '621432888'),
    ('Chuck', 'Main Road 989', 'Barcelona', '657145808'),
    ('Laura', 'Sideway 1633', 'Barcelona', '667430089'),
    ('Betty', 'Green Grass 1', 'Barcelona', '657423488'),
    ('Richard', 'Sky st 331', 'Barcelona', '637432678'),
    ('Susan', 'One way 98', 'Badalona', '655432888'),
    ('Vicky', 'Yellow Garden 2', 'Barcelona', '699432888'),
    ('Ben', 'Park Lane 38', 'Barcelona', '657432771'),
    ('William', 'Central st 954', 'Sant Cugat', '621432888'),
    ('Chuck', 'Main Road 989', 'Barcelona', '657145808'),
    ('Laura', 'Sideway 1633', 'Barcelona', '667430089')
]
mycursor.executemany(sql, values)

mydb.commit()

print(mycursor)<jupyter_output>CMySQLCursor: INSERT INTO Customers (name, address, ci..<jupyter_text>Check Data Table<jupyter_code>query = "SELECT * FROM Customers"

mycursor.execute(query)

result = mycursor.fetchall()

for row in result:
    print(row)<jupyter_output>(1, 'Hannah', 'Mountain 21', 'Espluges de Ll.', '973908304')
(2, 'Michael', 'Valley 345', 'Barcelona', '657434898')
(3, 'Laura', 'Gran Via 124', 'Barcelona', '973908309')
(4, 'Peter', 'Lowstreet 4', 'Barcelona', '657432837')
(5, 'Amy', 'Apple st 652', 'Barcelona', '657432222')
(6, 'Sandy', 'Ocean blvd 2', 'Mataró', '657432228')
(7, 'Betty', 'Green Grass 1', 'Barcelona', '973908309')
(8, 'Richard', 'Sky st 331', 'Barcelona', '637432678')
(9, 'Susan', 'One way 98', 'Badalona', '655432888')
(10, 'Vicky', 'Yellow Garden 2', 'Barcelona', '699432888')
(11, 'Ben', 'Park Lane 38', 'Barcelona', '657432771')
(12, 'William', 'Central st 954', 'Sant Cugat', '621432888')
(13, 'Chuck', 'Main Road 989', 'Barcelona', '657145808')
(14, 'Laura', 'Sideway 1633', 'Barcelona', '667430089')
(15, 'Betty', 'Green Grass 1', 'Barcelona', '657423488')
(16, 'Richard', 'Sky st 331', 'Barcelona', '637432678')
(17, 'Susan', 'One way 98', 'Badalona', '655432888')
(18, 'Vicky', 'Yellow Garden 2', 'Barcelona', '6994328[...]<jupyter_text>All Fields from Customers of Barcelona<jupyter_code>query = "SELECT * FROM Customers WHERE city ='Barcelona'"

mycursor.execute(query)

result = mycursor.fetchall()

for row in result:
    print(row)<jupyter_output>(2, 'Michael', '<NAME>', 'Barcelona', '657434898')
(3, 'Laura', 'Gran Via 124', 'Barcelona', '973908309')
(4, 'Peter', 'Lowstreet 4', 'Barcelona', '657432837')
(5, 'Amy', 'Apple st 652', 'Barcelona', '657432222')
(7, 'Betty', 'Green Grass 1', 'Barcelona', '973908309')
(8, 'Richard', 'Sky st 331', 'Barcelona', '637432678')
(10, 'Vicky', 'Yellow Garden 2', 'Barcelona', '699432888')
(11, 'Ben', 'Park Lane 38', 'Barcelona', '657432771')
(13, 'Chuck', 'Main Road 989', 'Barcelona', '657145808')
(14, 'Laura', 'Sideway 1633', 'Barcelona', '667430089')
(15, 'Betty', 'Green Grass 1', 'Barcelona', '657423488')
(16, 'Richard', 'Sky st 331', 'Barcelona', '637432678')
(18, 'Vicky', 'Yellow Garden 2', 'Barcelona', '699432888')
(19, 'Ben', 'Park Lane 38', 'Barcelona', '657432771')
(21, 'Chuck', 'Main Road 989', 'Barcelona', '657145808')
(22, 'Laura', 'Sideway 1633', 'Barcelona', '667430089')<jupyter_text>Fields name and telephone, of the Customers that are not of Barcelona<jupyter_code>query = "SELECT name, phone FROM Customers WHERE city != 'Barcelona'"

mycursor.execute(query)

result = mycursor.fetchall()

for row in result:
    print(row)<jupyter_output>('Hannah', '973908304')
('Sandy', '657432228')
('Susan', '655432888')
('William', '621432888')
('Susan', '655432888')
('William', '621432888')<jupyter_text>Fields name and telephone, of the Customers that are not of Barcelona and his names is Sandy<jupyter_code>query = "SELECT name, phone FROM Customers WHERE city != 'Barcelona' AND name = 'Sandy'"

mycursor.execute(query)

result = mycursor.fetchall()

for row in result:
    print(row)<jupyter_output>('Sandy', '657432228')<jupyter_text>************************************  Level 2- Exercise 2:   - Show that you can upload some simple queries to a Pandas Dataframe. ![Engine sqla](img/sqla_engine_arch.png)  Import data from an external database to the workbench [netflix_mysql.sql](SQL/netflix_mysql.sql) ![netflix_mysql database](img/Netflix_database.png)  Enter Credentials<jupyter_code>user_name = getpass("Enter username: ")
passw = getpass("Enter password: ")<jupyter_output>Enter username: ········
Enter password: ········<jupyter_text>Create Connection to netflix Table<jupyter_code>#import connection
#sqlEngine = connection.Sql_Engine()"""

DB = "netflix"
SQL_URI = f'mysql+pymysql://{user_name}:{passw}@127.0.0.1/{DB}'
PORT = 3306

sqlEngine = create_engine(SQL_URI, pool_recycle = PORT)

dbConnection = sqlEngine.connect()<jupyter_output><empty_output><jupyter_text>Display the title, rating, country, release_year, and director,  for all movies directed by <NAME><jupyter_code>sql = "SELECT title, rating, country, release_year, director FROM movies WHERE director = '<NAME>' ORDER BY release_year"
df1 = pd.read_sql(sql, dbConnection);

pd.set_option('display.expand_frame_repr', False)

dbConnection.close()
df1<jupyter_output><empty_output><jupyter_text>Display the title, country, duration_in_minutes, and date_added,  for all movies that were added before March 2011.  Sort the output by duration_in_minutes (ascending)<jupyter_code>sql = "SELECT title, country, duration_in_minutes, date_added FROM movies WHERE date_added < '2011-03-01' ORDER BY duration_in_minutes"
dbConnection = sqlEngine.connect()

df2 = pd.read_sql(sql, dbConnection);

pd.set_option('display.expand_frame_repr', False)

dbConnection.close()
df2<jupyter_output><empty_output><jupyter_text>Display the title, country, duration_in_minutes, and release_year,  for all movies that were released between 2014 and 2016.  Sort the output by duration_in_minutes (descending)<jupyter_code>sql = "SELECT title, country, duration_in_minutes, release_year FROM movies WHERE release_year BETWEEN 2014 AND 2016 ORDER BY duration_in_minutes DESC"
dbConnection = sqlEngine.connect()

df3 = pd.read_sql(sql, dbConnection);

dbConnection.close()

df3<jupyter_output><empty_output><jupyter_text>Display the title, director, country, and duration_in_minutes,  for all movies with duration between 3-4 hours.  Sort the output by duration_in_minutes (descending)<jupyter_code>sql = "SELECT title, director, country, duration_in_minutes FROM movies WHERE duration_in_minutes/60 BETWEEN 3 AND 4 ORDER BY duration_in_minutes DESC"
dbConnection = sqlEngine.connect()

df4 = pd.read_sql(sql, dbConnection);

dbConnection.close()

df4<jupyter_output><empty_output><jupyter_text>Display the title, director, rating, num_of_seasons for all series with 10 to 14 seasons.  Sort the output by num_of_seasons (descending)<jupyter_code>sql = "SELECT title, director, rating, num_of_seasons FROM series WHERE num_of_seasons BETWEEN 10 AND 14 ORDER BY num_of_seasons DESC"
dbConnection = sqlEngine.connect()

df5 = pd.read_sql(sql, dbConnection);

dbConnection.close()

df5<jupyter_output><empty_output><jupyter_text>Display title, director, cast, country, and release_year, for all series released in 2014, 2016, or 2018.  Sort the output by title (ascending)<jupyter_code>sql = "SELECT title, director, cast, country, release_year FROM series WHERE release_year IN (2014,2016,2018)"
dbConnection = sqlEngine.connect()

df6 = pd.read_sql(sql, dbConnection);

dbConnection.close()

df6<jupyter_output><empty_output><jupyter_text>Level 3- Exercise 3:   - Generate a graph that summarizes the data.<jupyter_code>sql = "SELECT * FROM series"
dbConnection = sqlEngine.connect()

df = pd.read_sql(sql, dbConnection);

dbConnection.close()<jupyter_output><empty_output><jupyter_text>Exploratory analysis<jupyter_code>df.head()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2410 entries, 0 to 2409
Data columns (total 10 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   title           2410 non-null   object        
 1   director        184 non-null    object        
 2   cast            2118 non-null   object        
 3   country         2133 non-null   object        
 4   date_added      2400 non-null   datetime64[ns]
 5   release_year    2410 non-null   int64         
 6   rating          2408 non-null   object        
 7   listed_in       2410 non-null   object        
 8   description     2410 non-null   object        
 9   num_of_seasons  2410 non-null   int64         
dtypes: datetime64[ns](1), int64(2), object(7)
memory usage: 188.4+ KB<jupyter_text>Numerical variables<jupyter_code>df.select_dtypes(include=['float64', 'int']).describe()
# Distribution graph for each numerical variable
# ==============================================================================
# Adjust number of subplots based on the number of columns

fig, axes = plt.subplots(ncols=2, nrows=1, figsize=(10, 5))
axes = axes.flat
columnas_numeric = df.select_dtypes(include=['float64', 'int']).columns


for i, colum in enumerate(columnas_numeric):
    sns.histplot(
        data    = df,
        x       = colum,
        stat    = "count",
        kde     = True,
        color   = (list(plt.rcParams['axes.prop_cycle'])*2)[i]["color"],
        line_kws= {'linewidth': 2},
        alpha   = 0.3,
        ax      = axes[i]
    )
    axes[i].set_title(colum, fontsize = 8, fontweight = "bold")
    axes[i].tick_params(labelsize = 8)
    axes[i].set_xlabel("")
    
    
fig.tight_layout()
plt.subplots_adjust(top = 0.9)
fig.suptitle('Distribution Numerical Variable', fontsize = 15, fontweight = "bold")
plt.savefig("../reports/figures/Distribution_Numerical_Variable.png");<jupyter_output><empty_output><jupyter_text>Numerical variables correlation<jupyter_code># Correlation between numeric columns
# ==============================================================================

def tidy_corr_matrix(corr_mat):
    
    # Function to convert a pandas correlation matrix to tidy format
    
    corr_mat = corr_mat.stack().reset_index()
    corr_mat.columns = ['variable_1','variable_2','r']
    corr_mat = corr_mat.loc[corr_mat['variable_1'] != corr_mat['variable_2'], :]
    corr_mat['abs_r'] = np.abs(corr_mat['r'])
    corr_mat = corr_mat.sort_values('abs_r', ascending=False)
    
    return(corr_mat)

corr_matrix = df.select_dtypes(include=['float64', 'int']).corr(method='pearson')
tidy_corr_matrix(corr_matrix).head(20)
# Heatmap matrix of correlations
# ==============================================================================

fig, ax = plt.subplots(nrows=1, ncols=1, figsize=(5, 5))

corr= df.select_dtypes(include=['float64', 'int']).corr(method='pearson').corr()

# Getting the Upper Triangle of the co-relation matrix

matrix = np.triu(corr)

# using the upper triangle matrix as mask 
sns.heatmap(corr, 
            annot=True, 
            mask=matrix, 
            cmap=sns.diverging_palette(150, 275, s=80, l=55, n=9),
            annot_kws = {"size": 10})

ax.set_xticklabels(
    ax.get_xticklabels(),
    rotation = 45,
    horizontalalignment = 'right',
)
ax.set_yticklabels(
    ax.get_yticklabels(),
    rotation = 0,
    horizontalalignment = 'right',
)
ax.tick_params(labelsize = 15)

fig.suptitle('Heatmap Correlation Matrix', fontsize = 20, fontweight = "bold")
plt.savefig("../reports/figures/Heatmap_Matrix_Correlations.png")<jupyter_output><empty_output><jupyter_text>Graphical visualization of ther interesting datas  Number of series from Country<jupyter_code># Pie chart Series from Countries
# ================================

plot = df['country'].head(25).value_counts().plot(kind='pie', autopct='%.0f', 
                                            figsize=(12, 12),
                                            title='Series from Countries ')<jupyter_output><empty_output><jupyter_text>Years with the number of Netlfix Shows<jupyter_code>release = df["release_year"].value_counts().sort_values(ascending=False)

plt.figure(figsize=(12,4))
release.plot(title='Years with the number of Netlfix Shows', kind="bar")
plt.xticks(rotation=45)
plt.savefig("../reports/figures/Years with the number of Netlfix Shows.png");<jupyter_output><empty_output><jupyter_text>Series rating<jupyter_code>rating = df.groupby("rating")['title'].count().reset_index().sort_values(by='rating',ascending=False).reset_index(drop=True)

plt.figure(figsize=(12,6))
sns.barplot(x='rating',y='title', data=rating)
plt.xticks(rotation=45)
plt.savefig("../reports/figures/rating series.png");<jupyter_output><empty_output><jupyter_text>Cloud Word from Series Title<jupyter_code>string = StringIO()

df['title'].apply(lambda x: string.write(x))

x = string.getvalue()

string.close()

x = x.lower()
x = x.split()
words = co.Counter(nltk.corpus.words.words())

stopWords = co.Counter( nltk.corpus.stopwords.words() )

x = [i for i in x if i in words and i not in stopWords]

string = " ".join(x)

c = co.Counter(x)
most_common_10 = c.most_common(10)

print('The 10 Most Common Words in title are: ')

most_common_10
text = string
wordcloud2 = WordCloud(stopwords=STOPWORDS,
                          background_color='white',
                          relative_scaling = 1.0).generate(text)

plt.figure(figsize=(20,10))
plt.imshow(wordcloud2, interpolation="bilinear")
plt.axis("off")
plt.savefig("../reports/figures/worcloud.png")
plt.show();<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.19077825159914713, 'fraction_numerical': 0.05447761194029851, 'mean_word_length': 5.557497378538972, 'pattern_counts': {'":': 1, '<': 116, '<?xml version=': 0, '>': 115, 'https://': 11, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '29', 'dir': 'jupyter-structured-clean-dedup', 'id': '287226', 'license': 'MIT', 'n_tokens_mistral': 7146, 'n_tokens_neox': 6128, 'n_words': 1471, 'path': 'notebooks/S17_T02_SQL_Database.ipynb', 'repo_name': 'jesussantana/SQL-database', 'size': '18759'}