Spaces:
Running
Running
File size: 5,718 Bytes
854c114 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 | {
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# ML Practice Series: Module 22 - SQL & Databases for Data Science\n",
"\n",
"In the real world, data lives in databases, not just CSVs. This module teaches you how to bridge the gap between **SQL (Structured Query Language)** and **Python/Pandas**.\n",
"\n",
"### Objectives:\n",
"1. **Connecting to Databases**: Using `sqlite3` (built into Python).\n",
"2. **Basic Queries**: SELECT, WHERE, and JOIN in Python.\n",
"3. **SQL to Pandas**: Loading query results directly into a DataFrame.\n",
"4. **Database Design**: Understanding primary keys and foreign keys.\n",
"\n",
"---"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 1. Setting up a Virtual Database\n",
"We will create an in-memory database and populate it with some sample Data Science job data."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import sqlite3\n",
"import pandas as pd\n",
"\n",
"# Create a connection to an in-memory database\n",
"conn = sqlite3.connect(':memory:')\n",
"cursor = conn.cursor()\n",
"\n",
"# Create a sample table\n",
"cursor.execute('''\n",
" CREATE TABLE jobs (\n",
" id INTEGER PRIMARY KEY,\n",
" title TEXT,\n",
" company TEXT,\n",
" salary INTEGER\n",
" )\n",
"''')\n",
"\n",
"# Insert sample records\n",
"jobs = [\n",
" (1, 'Data Scientist', 'Google', 150000),\n",
" (2, 'ML Engineer', 'Tesla', 160000),\n",
" (3, 'Data Analyst', 'Netflix', 120000),\n",
" (4, 'AI Research', 'OpenAI', 200000)\n",
"]\n",
"cursor.executemany('INSERT INTO jobs VALUES (?,?,?,?)', jobs)\n",
"conn.commit()\n",
"\n",
"print(\"Database created and table populated!\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 2. Basic SQL Queries in Python\n",
"\n",
"### Task 1: Fetching Data\n",
"Use standard SQL to fetch all jobs where the salary is greater than 140,000."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# YOUR CODE HERE\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<details>\n",
"<summary><b>Click to see Solution</b></summary>\n",
"\n",
"```python\n",
"query = \"SELECT * FROM jobs WHERE salary > 140000\"\n",
"cursor.execute(query)\n",
"results = cursor.fetchall()\n",
"for row in results:\n",
" print(row)\n",
"```\n",
"</details>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 3. SQL to Pandas: The Professional Way\n",
"\n",
"### Task 2: pd.read_sql_query\n",
"Professionals use `pd.read_sql_query()` to pull data directly into a DataFrame. Try it now."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# YOUR CODE HERE\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<details>\n",
"<summary><b>Click to see Solution</b></summary>\n",
"\n",
"```python\n",
"df_sql = pd.read_sql_query(\"SELECT * FROM jobs\", conn)\n",
"print(df_sql.head())\n",
"```\n",
"</details>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"--- \n",
"### Bridge Completed! \n",
"You now know how to pull data from any standard relational database.\n",
"Next: **Model Explainability (SHAP)**."
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.12.7"
}
},
"nbformat": 4,
"nbformat_minor": 4
} |