# import Statements
import numpy as np
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
print("-------------------->> Imports successful")
#Setting up Path of the Database
path = "data/"
database = path + 'database.sqlite'
#Connection to Database
conn = sqlite3.connect(database)
print("-------------------->>Debug: Database connected")
#Command used to check tables in DB
tables = pd.read_sql("""SELECT * FROM sqlite_sequence""",conn)
tables
Countries & Leauges
country = pd.read_sql("SELECT * from Country",conn)
country
country.count()
#information about leauges
league = pd.read_sql("SELECT * from League",conn)
league
league.count()
Looking at the above two tables of coutriees and leauges.
Both have a count of 11. This means there 11 vertical's for country and legues we have to process.
#The variable name con_leauge is country + Leauge, need to clean this table Later)
con_league = pd.read_sql("""SELECT * from League
JOIN
Country on Country.id = League.country_id; """,conn)
con_league
teams = pd.read_sql("SELECT * from Team ORDER BY id LIMIT 10" ,conn)
teams
matches = pd.read_sql("""SELECT * FROM Match LIMIT 0""",conn)
matches
matches = pd.read_sql("""
SELECT Match.id,
Match.country_id,
league_id ,
season,
stage,
date,
home_team_api_id as home_id,
away_team_api_id as away_id,
home_team_goal,
away_team_goal
from Match
JOIN Country on Country.id = Match.country_id
JOIN League on League.id = Match.league_id
LEFT JOIN Team on Team.id = Match.home_team_api_id
WHERE Match.country_id = '21518'
LIMIT 10
"""
,conn)
# JOIN Team on Team.id = Match.away_team_api_id
matches