{ "cells": [ { "cell_type": "markdown", "id": "19a4929d", "metadata": {}, "source": [ "# Soccer Database" ] }, { "cell_type": "code", "execution_count": 3, "id": "e7701241", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "5493" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "import sqlite3\n", "\n", "\n", "df = pd.read_csv('all_players.csv')\n", "df['Club'].isna().sum()\n" ] }, { "cell_type": "code", "execution_count": null, "id": "9ee97163", "metadata": {}, "outputs": [], "source": [ "conn = sqlite3.connect('all_players.db')\n", "cur = conn.cursor()\n", "df.to_sql('players', conn, if_exists='replace', index=False) # - writes the all_players.df to SQLIte DB\n", "conn.commit()\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": 6, "id": "d779ebe2", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import sqlite3\n", "from sqlite3 import Error\n", "\n", "\n", "def create_connection(db_file, delete_db=False):\n", " import os\n", " if delete_db and os.path.exists(db_file):\n", " os.remove(db_file)\n", "\n", " conn = None\n", " try:\n", " conn = sqlite3.connect(db_file)\n", " conn.execute(\"PRAGMA foreign_keys = 1\")\n", " except Error as e:\n", " print(e)\n", "\n", " return conn\n", "\n", "\n", "def create_table(conn, create_table_sql):\n", " try:\n", " c = conn.cursor()\n", " c.execute(create_table_sql)\n", " except Error as e:\n", " print(e)\n", " \n", "def execute_sql_statement(sql_statement, conn):\n", " cur = conn.cursor()\n", " cur.execute(sql_statement)\n", " rows = cur.fetchall()\n", "\n", " return rows\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "0fb4b5cb", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " | Player | \n", "Club | \n", "POS | \n", "GP | \n", "GS | \n", "MINS | \n", "Year | \n", "Season | \n", "
---|---|---|---|---|---|---|---|---|
0 | \n", "Roy Lassiter | \n", "TB | \n", "F | \n", "30 | \n", "30 | \n", "2580 | \n", "1996 | \n", "reg | \n", "
1 | \n", "Raul Diaz Arce | \n", "DC | \n", "F | \n", "28 | \n", "28 | \n", "2351 | \n", "1996 | \n", "reg | \n", "
2 | \n", "Eduardo Hurtado | \n", "LA | \n", "F | \n", "26 | \n", "26 | \n", "2323 | \n", "1996 | \n", "reg | \n", "
3 | \n", "Preki | \n", "KC | \n", "M | \n", "32 | \n", "32 | \n", "2880 | \n", "1996 | \n", "reg | \n", "
4 | \n", "Brian McBride | \n", "CLB | \n", "F | \n", "28 | \n", "28 | \n", "2307 | \n", "1996 | \n", "reg | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
15762 | \n", "Alejandro Pozuelo | \n", "NaN | \n", "M | \n", "1 | \n", "1 | \n", "120 | \n", "2020 | \n", "post | \n", "
15763 | \n", "Franco Jara | \n", "NaN | \n", "F | \n", "2 | \n", "2 | \n", "210 | \n", "2020 | \n", "post | \n", "
15764 | \n", "Andy Polo | \n", "NaN | \n", "M | \n", "1 | \n", "1 | \n", "105 | \n", "2020 | \n", "post | \n", "
15765 | \n", "Cristian Roldan | \n", "NaN | \n", "M | \n", "4 | \n", "4 | \n", "360 | \n", "2020 | \n", "post | \n", "
15766 | \n", "Joevin Jones | \n", "NaN | \n", "D-M | \n", "4 | \n", "4 | \n", "260 | \n", "2020 | \n", "post | \n", "
15767 rows × 8 columns
\n", "