import pandas as pd
import sqlite3
import sqlalchemy
import os
In addition, here, we use FIFA 20 Complete Player Datasetet and European Soccer Database from Kaggle.com.
The datasets that we use from the above likes are:
The datasets also publicly available website sofifa.com.
Engine = sqlalchemy.create_engine('sqlite:///FIFA.db')
for file in os.listdir("Data"):
df = pd.read_csv('Data/' + file, index_col=0)
df.to_sql(file.replace('.csv',''), Engine, if_exists='replace')
del file, df, Engine
In this article, we provide a summary of basic SQL Server commands and syntaxes. For sake of demonstrations SQL Server syntaxes, we use SQLite3. Moreover, the following function can help us to mimic SQL Server Environment in this Jupyter notebook.
connection = sqlite3.connect('FIFA.db')
cursor = connection.cursor()
def mySQL(Syntax): return pd.read_sql_query(Syntax, connection)
Here we provide a summary of popular SQL Server command with examples. For full notes, please see the official SQL Server documents at docs.microsoft.com.
SELECT column_list FROM Table [into New_Table];
To select all columns use *
SELECT * FROM Table;
mySQL("Select short_name from players_20")
SELECT column_list FROM Table
WHERE Condition;
mySQL("""
Select
short_name,
club,
age
from
players_20
where
age <20
""")
SELECT DISTINCT column_list FROM Table;
mySQL("""
Select
distinct nationality
from
players_20
where
age <20
""")
SELECT column_list FROM Table
ORDER BY column_list [ASC |DESC]
Ascending:
mySQL("""
Select
short_name,
age
from
players_20
where
age <25
order by
age asc
""")
Descending:
mySQL("""
Select
short_name,
age
from
players_20
where
age <25
order by
age desc
""")
Moreover, orderby has some options Offse and Fetch
SELECT column_list FROM Table
ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
SELECT
column_list,
aggregate(a Column)
FROM
Table
GROUP BY
column_list;
mySQL("""
Select
count(sofifa_id),
age,
nationality
from
players_20
Group by
nationality,
age
""")
SELECT
column_list,
aggregate(a Column)
FROM
Table
GROUP BY
column_list
HAVING
Condition
mySQL("""
Select
count(sofifa_id),
age,
nationality
from
players_20
Group by
nationality,
age
Having
nationality in ('Scotland')
""")