Skip to content

Instantly share code, notes, and snippets.

@iampramodyadav

iampramodyadav/creating-multiple-table-sql-database-csv-sqlite3.ipynb

Last active June 30, 2021 11:17
Show Gist options
  • Save iampramodyadav/793ec2b0ea71c3bcbfd6deea636907e2 to your computer and use it in GitHub Desktop.
Save iampramodyadav/793ec2b0ea71c3bcbfd6deea636907e2 to your computer and use it in GitHub Desktop.
Download ZIP
Creating-Multiple-Table-SQL-DATABASE-CSV-sqlite3.ipynb
Display the source blob
Display the rendered blob
Raw
creating-multiple-table-sql-database-csv-sqlite3.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "Creating-Multiple-Table-SQL-DATABASE-CSV-sqlite3.ipynb",
"provenance": [],
"authorship_tag": "ABX9TyOoh7trytcqbVutTK3Fk2TE",
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/iampramodyadav/793ec2b0ea71c3bcbfd6deea636907e2/creating-multiple-table-sql-database-csv-sqlite3.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "qKw_9WDZhrkC"
},
"source": [
"# Creating SQL-Databse with multiple table from csv files\n",
"\n",
"\n",
"---\n",
"Pramod kumar yadav\n",
"@iampramodyadav\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "8NZPz9EciCGf"
},
"source": [
"## Loading CSV files from google dive via CURLGET and unzipping it"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "IDDMFh8PdZBm",
"outputId": "4354d054-75fd-4fc9-c829-ec1f7d7d9772"
},
"source": [
"!wget --header=\"Host: doc-14-5o-drive-data-export.googleusercontent.com\" --header=\"User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.114 Safari/537.36 Edg/91.0.864.59\" --header=\"Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9\" --header=\"Accept-Language: en-US,en;q=0.9\" --header=\"Cookie: AUTH_vhphm33fusgecji7kuoa3rc0nh79nagd_nonce=jdp107cten97o\" --header=\"Connection: keep-alive\" \"https://doc-14-5o-drive-data-export.googleusercontent.com/download/t2i47i2m4qr4e0uv406c834hlo4c0t3q/gle617ab1v23voi6u9csdc2hc93k06ld/1625044500000/b3cc7eab-d800-429a-a5b3-c9a93048f40c/111118528152204709033/ADt3v-PXOmb44enIpYej-r-sqno796-URPkiQgKwK8FbgFGZIvh9HUZskywUrtvVwAsQRUBB1LVB3OKgjjhOtXLsZ6mXRuKkaLKX92YEZLlKeSIZqPeotOHcwUiHUAGaPUAhgptH8Jey7QbWxp0ysMlGf8fMEKzDuX7p4uFURTQ3CQ8Yju7vUNZuJH0oeXuxkWjHBwPG15IP7mNuEpPIyaSbV9hDJDpZ6TrMzmS3N2tZbubiq7lrZFbPybvaLOTXUwKtdHKyAHqLdc6zvXbsw2SSQUKADLdVOwObLYcCvIAt2wHUy-4Az2iYQcVGFPA5SFRCzGFGf92y?authuser=0&nonce=jdp107cten97o&user=111118528152204709033&hash=irrsofj9c5vmhlnktqc1dcpsis679l7r\" -c -O 'dataset-20210630T102656Z-001.zip'"
],
"execution_count": 9,
"outputs": [
{
"output_type": "stream",
"text": [
"--2021-06-30 10:27:31-- https://doc-14-5o-drive-data-export.googleusercontent.com/download/t2i47i2m4qr4e0uv406c834hlo4c0t3q/gle617ab1v23voi6u9csdc2hc93k06ld/1625044500000/b3cc7eab-d800-429a-a5b3-c9a93048f40c/111118528152204709033/ADt3v-PXOmb44enIpYej-r-sqno796-URPkiQgKwK8FbgFGZIvh9HUZskywUrtvVwAsQRUBB1LVB3OKgjjhOtXLsZ6mXRuKkaLKX92YEZLlKeSIZqPeotOHcwUiHUAGaPUAhgptH8Jey7QbWxp0ysMlGf8fMEKzDuX7p4uFURTQ3CQ8Yju7vUNZuJH0oeXuxkWjHBwPG15IP7mNuEpPIyaSbV9hDJDpZ6TrMzmS3N2tZbubiq7lrZFbPybvaLOTXUwKtdHKyAHqLdc6zvXbsw2SSQUKADLdVOwObLYcCvIAt2wHUy-4Az2iYQcVGFPA5SFRCzGFGf92y?authuser=0&nonce=jdp107cten97o&user=111118528152204709033&hash=irrsofj9c5vmhlnktqc1dcpsis679l7r\n",
"Resolving doc-14-5o-drive-data-export.googleusercontent.com (doc-14-5o-drive-data-export.googleusercontent.com)... 172.217.2.97, 2607:f8b0:4004:80a::2001\n",
"Connecting to doc-14-5o-drive-data-export.googleusercontent.com (doc-14-5o-drive-data-export.googleusercontent.com)|172.217.2.97|:443... connected.\n",
"HTTP request sent, awaiting response... 200 OK\n",
"Length: 2251 (2.2K) [application/octet-stream]\n",
"Saving to: ‘dataset-20210630T102656Z-001.zip’\n",
"\n",
"\r dataset-2 0%[ ] 0 --.-KB/s \rdataset-20210630T10 100%[===================>] 2.20K --.-KB/s in 0s \n",
"\n",
"2021-06-30 10:27:32 (29.8 MB/s) - ‘dataset-20210630T102656Z-001.zip’ saved [2251/2251]\n",
"\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "m0WavIbpdeCt",
"outputId": "691f5c11-1c3d-4f89-e77c-c170a476ea2c"
},
"source": [
"!unzip \"dataset-20210630T102656Z-001.zip\""
],
"execution_count": 10,
"outputs": [
{
"output_type": "stream",
"text": [
"Archive: dataset-20210630T102656Z-001.zip\n",
"replace dataset/dataset.csv? [y]es, [n]o, [A]ll, [N]one, [r]ename: y\n",
" inflating: dataset/dataset.csv \n",
" inflating: dataset/department.csv \n",
" inflating: dataset/locatiodata.csv \n",
" inflating: dataset/emplyees.csv \n",
" inflating: dataset/jobsdata.csv \n",
" inflating: dataset/jobhistory.csv \n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "E_uKwIqUiT1h"
},
"source": [
"# Creating database"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "HJNC5cCWbtOp",
"outputId": "d80a287b-a896-45b2-bd5f-f56832ba98d4"
},
"source": [
"import sqlite3\n",
"import pandas as pd\n",
"sqlite3.connect('PETRESCUE.db')"
],
"execution_count": 11,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"<sqlite3.Connection at 0x7f8cf1f78b90>"
]
},
"metadata": {
"tags": []
},
"execution_count": 11
}
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 328
},
"id": "peI_QYGFb2X0",
"outputId": "e2932957-a0f9-40b7-d9ae-b98dd06bbb27"
},
"source": [
"pd.read_csv('dataset/jobhistory.csv')"
],
"execution_count": 12,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>EMPL_ID</th>\n",
" <th>START_DATE</th>\n",
" <th>JOBS_ID</th>\n",
" <th>DEPT_ID</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>E1002</td>\n",
" <td>2001-08-01</td>\n",
" <td>200</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>E1003</td>\n",
" <td>2001-08-16</td>\n",
" <td>300</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>E1004</td>\n",
" <td>2000-08-16</td>\n",
" <td>400</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>E1005</td>\n",
" <td>2000-05-30</td>\n",
" <td>500</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>E1006</td>\n",
" <td>2001-08-16</td>\n",
" <td>600</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>E1007</td>\n",
" <td>2002-05-30</td>\n",
" <td>650</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>E1008</td>\n",
" <td>2010-05-06</td>\n",
" <td>660</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>E1009</td>\n",
" <td>2016-08-16</td>\n",
" <td>234</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>E1010</td>\n",
" <td>2016-08-16</td>\n",
" <td>220</td>\n",
" <td>5</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" EMPL_ID START_DATE JOBS_ID DEPT_ID\n",
"0 E1002 2001-08-01 200 5\n",
"1 E1003 2001-08-16 300 5\n",
"2 E1004 2000-08-16 400 5\n",
"3 E1005 2000-05-30 500 2\n",
"4 E1006 2001-08-16 600 2\n",
"5 E1007 2002-05-30 650 7\n",
"6 E1008 2010-05-06 660 7\n",
"7 E1009 2016-08-16 234 7\n",
"8 E1010 2016-08-16 220 5"
]
},
"metadata": {
"tags": []
},
"execution_count": 12
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "PKlWMaXzifj8"
},
"source": [
"## adding first table"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "TbD0lqrKb9is",
"outputId": "642fc6ea-2095-4818-8341-ba4d833cb657"
},
"source": [
"#credit @geekforgeeks\n",
"\n",
"# Import required libraries\n",
"import sqlite3\n",
"import pandas as pd\n",
"\n",
"# Connect to SQLite database\n",
"conn = sqlite3.connect(r'dataset.db')\n",
"\n",
"# Load CSV data into Pandas DataFrame\n",
"employ_data = pd.read_csv('dataset/emplyees.csv')\n",
"# Write the data to a sqlite table\n",
"employ_data.to_sql('EMPLOYEE', conn, if_exists='replace', index=False)\n",
"\n",
"# Create a cursor object\n",
"cur = conn.cursor()\n",
"# Fetch and display result\n",
"for row in cur.execute('SELECT * FROM EMPLOYEE'):\n",
"\tprint(row)\n",
"# Close connection to SQLite database\n",
"conn.close()"
],
"execution_count": 14,
"outputs": [
{
"output_type": "stream",
"text": [
"('E1002 ', 'Alice', 'James', 123457, '1972-07-31', 'F', '980 Berry ln, Elgin,IL', 200, 80000.0, 30002, 5)\n",
"('E1003 ', 'Steve', 'Wells', 123458, '1980-08-10', 'M', '291 Springs, Gary,IL', 300, 50000.0, 30002, 5)\n",
"('E1004 ', 'Santosh', 'Kumar', 123459, '1985-07-20', 'M', '511 Aurora Av, Aurora,IL', 400, 60000.0, 30004, 5)\n",
"('E1005 ', 'Ahmed', 'Hussain', 123410, '1981-01-04', 'M', '216 Oak Tree, Geneva,IL', 500, 70000.0, 30001, 2)\n",
"('E1006 ', 'Nancy', 'Allen', 123411, '1978-02-06', 'F', '111 Green Pl, Elgin,IL', 600, 90000.0, 30001, 2)\n",
"('E1007 ', 'Mary', 'Thomas', 123412, '1975-05-05', 'F', '100 Rose Pl, Gary,IL', 650, 65000.0, 30003, 7)\n",
"('E1008 ', 'Bharath', 'Gupta', 123413, '1985-05-06', 'M', '145 Berry Ln, Naperville,IL', 660, 65000.0, 30003, 7)\n",
"('E1009 ', 'Andrea', 'Jones', 123414, '1990-07-09', 'F', '120 Fall Creek, Gary,IL', 234, 70000.0, 30003, 7)\n",
"('E1010 ', 'Ann', 'Jacob', 123415, '1982-03-30', 'F', '111 Britany Springs,Elgin,IL', 220, 70000.0, 30004, 5)\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "FudoDn35ioYE"
},
"source": [
"## adding 2nd table"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "T_vwVgbgcCht",
"outputId": "68c0d3d6-45a5-461d-f35f-b70d2207e3ee"
},
"source": [
"conn = sqlite3.connect(r'dataset.db')\n",
"\n",
"dept_data = pd.read_csv('dataset/department.csv')\n",
"\n",
"dept_data.to_sql('DEPARTMENT', conn, if_exists='replace', index=False)\n",
"\n",
"\n",
"cur = conn.cursor()\n",
"for row in cur.execute('SELECT * FROM DEPARTMENT'):\n",
"\tprint(row)\n",
"conn.close()"
],
"execution_count": 16,
"outputs": [
{
"output_type": "stream",
"text": [
"(5, 'Software Group', 30002, 'L0002 ')\n",
"(7, 'Design Team', 30003, 'L0003 ')\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "RV89tHwtirUw"
},
"source": [
"## adding 3rd table"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "WuOL5AZhfPl1",
"outputId": "fb9998db-0109-4c8b-c5ab-5259e618db59"
},
"source": [
"conn = sqlite3.connect(r'dataset.db')\n",
"\n",
"loc_data = pd.read_csv('dataset/locatiodata.csv')\n",
"loc_data.to_sql('LOCATION', conn, if_exists='replace', index=False)\n",
"\n",
"cur = conn.cursor()\n",
"for row in cur.execute('SELECT * FROM LOCATION'):\n",
"\tprint(row)\n",
"conn.close()"
],
"execution_count": 19,
"outputs": [
{
"output_type": "stream",
"text": [
"('L0002 ', 5)\n",
"('L0003 ', 7)\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "kbEtt0eeivkP"
},
"source": [
"## adding 4th table"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "jqGgKDhnfqrY",
"outputId": "fbce72fa-6e27-485a-fadf-8c0a994e4ea5"
},
"source": [
"conn = sqlite3.connect(r'dataset.db')\n",
"\n",
"job_data = pd.read_csv('dataset/jobsdata.csv')\n",
"job_data.to_sql('JOBS', conn, if_exists='replace', index=False)\n",
"\n",
"cur = conn.cursor()\n",
"for row in cur.execute('SELECT * FROM JOBS'):\n",
"\tprint(row)\n",
"conn.close()"
],
"execution_count": 21,
"outputs": [
{
"output_type": "stream",
"text": [
"(200, 'Sr.Software Dev', 60000.0, 80000.0)\n",
"(220, 'Sr. Designer', 70000.0, 90000.0)\n",
"(234, 'Sr. Designer', 70000.0, 90000.0)\n",
"(300, 'Jr.Software Dev', 40000.0, 60000.0)\n",
"(400, 'Jr.Software Dev', 40000.0, 60000.0)\n",
"(500, 'Jr. Architect', 50000.0, 70000.0)\n",
"(600, 'Lead Architect', 70000.0, 100000.0)\n",
"(650, 'Jr. Designer', 60000.0, 70000.0)\n",
"(660, 'Jr. Designer', 60000.0, 70000.0)\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "IAlJDRIki06Z"
},
"source": [
"## adding 5th table"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "90pUI2f1gGyH",
"outputId": "b885d61b-4ec5-4adf-dae9-25de20718e2a"
},
"source": [
"conn = sqlite3.connect(r'dataset.db')\n",
"\n",
"jobh_data = pd.read_csv('dataset/jobhistory.csv')\n",
"jobh_data.to_sql('JOBHISTORY', conn, if_exists='replace', index=False)\n",
"\n",
"cur = conn.cursor()\n",
"for row in cur.execute('SELECT * FROM JOBHISTORY'):\n",
"\tprint(row)\n",
"conn.close()"
],
"execution_count": 23,
"outputs": [
{
"output_type": "stream",
"text": [
"('E1002 ', '2001-08-01', 200, 5)\n",
"('E1003 ', '2001-08-16', 300, 5)\n",
"('E1004 ', '2000-08-16', 400, 5)\n",
"('E1005 ', '2000-05-30', 500, 2)\n",
"('E1006 ', '2001-08-16', 600, 2)\n",
"('E1007 ', '2002-05-30', 650, 7)\n",
"('E1008 ', '2010-05-06', 660, 7)\n",
"('E1009 ', '2016-08-16', 234, 7)\n",
"('E1010 ', '2016-08-16', 220, 5)\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "hn_lBOGJjNNu"
},
"source": [
"# Printing schema "
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 204
},
"id": "6H5Lxe5tcC9E",
"outputId": "e2d63cef-f514-4ae3-ae6a-d126c4f8d202"
},
"source": [
"con = sqlite3.connect('dataset.db') \n",
"pd.read_sql_query(\"SELECT * FROM sqlite_master where type = 'table';\", con)"
],
"execution_count": 25,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>type</th>\n",
" <th>name</th>\n",
" <th>tbl_name</th>\n",
" <th>rootpage</th>\n",
" <th>sql</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>table</td>\n",
" <td>EMPLOYEE</td>\n",
" <td>EMPLOYEE</td>\n",
" <td>2</td>\n",
" <td>CREATE TABLE \"EMPLOYEE\" (\\n\"EMP_ID\" TEXT,\\n \"...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>table</td>\n",
" <td>DEPARTMENT</td>\n",
" <td>DEPARTMENT</td>\n",
" <td>3</td>\n",
" <td>CREATE TABLE \"DEPARTMENT\" (\\n\"DEPT_ID_DEP\" INT...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>table</td>\n",
" <td>LOCATION</td>\n",
" <td>LOCATION</td>\n",
" <td>4</td>\n",
" <td>CREATE TABLE \"LOCATION\" (\\n\"LOCT_ID\" TEXT,\\n ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>table</td>\n",
" <td>JOBS</td>\n",
" <td>JOBS</td>\n",
" <td>5</td>\n",
" <td>CREATE TABLE \"JOBS\" (\\n\"JOB_IDENT\" INTEGER,\\n ...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>table</td>\n",
" <td>JOBHISTORY</td>\n",
" <td>JOBHISTORY</td>\n",
" <td>6</td>\n",
" <td>CREATE TABLE \"JOBHISTORY\" (\\n\"EMPL_ID\" TEXT,\\n...</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" type ... sql\n",
"0 table ... CREATE TABLE \"EMPLOYEE\" (\\n\"EMP_ID\" TEXT,\\n \"...\n",
"1 table ... CREATE TABLE \"DEPARTMENT\" (\\n\"DEPT_ID_DEP\" INT...\n",
"2 table ... CREATE TABLE \"LOCATION\" (\\n\"LOCT_ID\" TEXT,\\n ...\n",
"3 table ... CREATE TABLE \"JOBS\" (\\n\"JOB_IDENT\" INTEGER,\\n ...\n",
"4 table ... CREATE TABLE \"JOBHISTORY\" (\\n\"EMPL_ID\" TEXT,\\n...\n",
"\n",
"[5 rows x 5 columns]"
]
},
"metadata": {
"tags": []
},
"execution_count": 25
}
]
},
{
"cell_type": "code",
"metadata": {
"id": "_1nqOGI4kQwv"
},
"source": [
"tables = pd.read_sql_query(\"SELECT NAME AS 'Table_Name' FROM sqlite_master WHERE type='table'\",con)\n",
"tables = tables[\"Table_Name\"].values.tolist()"
],
"execution_count": 28,
"outputs": []
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 1000
},
"id": "07gUNhmJj1gI",
"outputId": "a5ad0f9f-fb2c-4413-8e99-016d7d9f6a18"
},
"source": [
"for table in tables:\n",
" query = \"PRAGMA TABLE_INFO({})\".format(table)\n",
" schema = pd.read_sql_query(query,con)\n",
" print(\"Schema of\",table)\n",
" display(schema)\n",
" print(\"-\"*100)\n",
" print(\"\\n\")"
],
"execution_count": 29,
"outputs": [
{
"output_type": "stream",
"text": [
"Schema of EMPLOYEE\n"
],
"name": "stdout"
},
{
"output_type": "display_data",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>cid</th>\n",
" <th>name</th>\n",
" <th>type</th>\n",
" <th>notnull</th>\n",
" <th>dflt_value</th>\n",
" <th>pk</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>EMP_ID</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>F_NAME</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>L_NAME</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>SSN</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>4</td>\n",
" <td>B_DATE</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>5</td>\n",
" <td>SEX</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>6</td>\n",
" <td>ADDRESS</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>7</td>\n",
" <td>JOB_ID</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>8</td>\n",
" <td>SALARY</td>\n",
" <td>REAL</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>9</td>\n",
" <td>MANAGER_ID</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>10</td>\n",
" <td>DEP_ID</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" cid name type notnull dflt_value pk\n",
"0 0 EMP_ID TEXT 0 None 0\n",
"1 1 F_NAME TEXT 0 None 0\n",
"2 2 L_NAME TEXT 0 None 0\n",
"3 3 SSN INTEGER 0 None 0\n",
"4 4 B_DATE TEXT 0 None 0\n",
"5 5 SEX TEXT 0 None 0\n",
"6 6 ADDRESS TEXT 0 None 0\n",
"7 7 JOB_ID INTEGER 0 None 0\n",
"8 8 SALARY REAL 0 None 0\n",
"9 9 MANAGER_ID INTEGER 0 None 0\n",
"10 10 DEP_ID INTEGER 0 None 0"
]
},
"metadata": {
"tags": []
}
},
{
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n",
"\n",
"\n",
"Schema of DEPARTMENT\n"
],
"name": "stdout"
},
{
"output_type": "display_data",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>cid</th>\n",
" <th>name</th>\n",
" <th>type</th>\n",
" <th>notnull</th>\n",
" <th>dflt_value</th>\n",
" <th>pk</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>DEPT_ID_DEP</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>DEP_NAME</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>MANAGER_ID</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>LOC_ID</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" cid name type notnull dflt_value pk\n",
"0 0 DEPT_ID_DEP INTEGER 0 None 0\n",
"1 1 DEP_NAME TEXT 0 None 0\n",
"2 2 MANAGER_ID INTEGER 0 None 0\n",
"3 3 LOC_ID TEXT 0 None 0"
]
},
"metadata": {
"tags": []
}
},
{
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n",
"\n",
"\n",
"Schema of LOCATION\n"
],
"name": "stdout"
},
{
"output_type": "display_data",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>cid</th>\n",
" <th>name</th>\n",
" <th>type</th>\n",
" <th>notnull</th>\n",
" <th>dflt_value</th>\n",
" <th>pk</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>LOCT_ID</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>DEP_ID_LOC</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" cid name type notnull dflt_value pk\n",
"0 0 LOCT_ID TEXT 0 None 0\n",
"1 1 DEP_ID_LOC INTEGER 0 None 0"
]
},
"metadata": {
"tags": []
}
},
{
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n",
"\n",
"\n",
"Schema of JOBS\n"
],
"name": "stdout"
},
{
"output_type": "display_data",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>cid</th>\n",
" <th>name</th>\n",
" <th>type</th>\n",
" <th>notnull</th>\n",
" <th>dflt_value</th>\n",
" <th>pk</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>JOB_IDENT</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>JOB_TITLE</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>MIN_SALARY</td>\n",
" <td>REAL</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>MAX_SALARY</td>\n",
" <td>REAL</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" cid name type notnull dflt_value pk\n",
"0 0 JOB_IDENT INTEGER 0 None 0\n",
"1 1 JOB_TITLE TEXT 0 None 0\n",
"2 2 MIN_SALARY REAL 0 None 0\n",
"3 3 MAX_SALARY REAL 0 None 0"
]
},
"metadata": {
"tags": []
}
},
{
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n",
"\n",
"\n",
"Schema of JOBHISTORY\n"
],
"name": "stdout"
},
{
"output_type": "display_data",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>cid</th>\n",
" <th>name</th>\n",
" <th>type</th>\n",
" <th>notnull</th>\n",
" <th>dflt_value</th>\n",
" <th>pk</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>EMPL_ID</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>START_DATE</td>\n",
" <td>TEXT</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>JOBS_ID</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>DEPT_ID</td>\n",
" <td>INTEGER</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" cid name type notnull dflt_value pk\n",
"0 0 EMPL_ID TEXT 0 None 0\n",
"1 1 START_DATE TEXT 0 None 0\n",
"2 2 JOBS_ID INTEGER 0 None 0\n",
"3 3 DEPT_ID INTEGER 0 None 0"
]
},
"metadata": {
"tags": []
}
},
{
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n",
"\n",
"\n"
],
"name": "stdout"
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "PJJcdLs0kpf3"
},
"source": [
"## Writing SQL query"
]
},
{
"cell_type": "code",
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 328
},
"id": "opIVd5yocDct",
"outputId": "c63036f0-1951-428f-802f-26893f8667da"
},
"source": [
"pd.read_sql_query(\"select * from EMPLOYEE where JOB_ID IN (select JOB_IDENT from JOBS);\", con)"
],
"execution_count": 26,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>EMP_ID</th>\n",
" <th>F_NAME</th>\n",
" <th>L_NAME</th>\n",
" <th>SSN</th>\n",
" <th>B_DATE</th>\n",
" <th>SEX</th>\n",
" <th>ADDRESS</th>\n",
" <th>JOB_ID</th>\n",
" <th>SALARY</th>\n",
" <th>MANAGER_ID</th>\n",
" <th>DEP_ID</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>E1002</td>\n",
" <td>Alice</td>\n",
" <td>James</td>\n",
" <td>123457</td>\n",
" <td>1972-07-31</td>\n",
" <td>F</td>\n",
" <td>980 Berry ln, Elgin,IL</td>\n",
" <td>200</td>\n",
" <td>80000.0</td>\n",
" <td>30002</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>E1003</td>\n",
" <td>Steve</td>\n",
" <td>Wells</td>\n",
" <td>123458</td>\n",
" <td>1980-08-10</td>\n",
" <td>M</td>\n",
" <td>291 Springs, Gary,IL</td>\n",
" <td>300</td>\n",
" <td>50000.0</td>\n",
" <td>30002</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>E1004</td>\n",
" <td>Santosh</td>\n",
" <td>Kumar</td>\n",
" <td>123459</td>\n",
" <td>1985-07-20</td>\n",
" <td>M</td>\n",
" <td>511 Aurora Av, Aurora,IL</td>\n",
" <td>400</td>\n",
" <td>60000.0</td>\n",
" <td>30004</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>E1005</td>\n",
" <td>Ahmed</td>\n",
" <td>Hussain</td>\n",
" <td>123410</td>\n",
" <td>1981-01-04</td>\n",
" <td>M</td>\n",
" <td>216 Oak Tree, Geneva,IL</td>\n",
" <td>500</td>\n",
" <td>70000.0</td>\n",
" <td>30001</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>E1006</td>\n",
" <td>Nancy</td>\n",
" <td>Allen</td>\n",
" <td>123411</td>\n",
" <td>1978-02-06</td>\n",
" <td>F</td>\n",
" <td>111 Green Pl, Elgin,IL</td>\n",
" <td>600</td>\n",
" <td>90000.0</td>\n",
" <td>30001</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>E1007</td>\n",
" <td>Mary</td>\n",
" <td>Thomas</td>\n",
" <td>123412</td>\n",
" <td>1975-05-05</td>\n",
" <td>F</td>\n",
" <td>100 Rose Pl, Gary,IL</td>\n",
" <td>650</td>\n",
" <td>65000.0</td>\n",
" <td>30003</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>E1008</td>\n",
" <td>Bharath</td>\n",
" <td>Gupta</td>\n",
" <td>123413</td>\n",
" <td>1985-05-06</td>\n",
" <td>M</td>\n",
" <td>145 Berry Ln, Naperville,IL</td>\n",
" <td>660</td>\n",
" <td>65000.0</td>\n",
" <td>30003</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>E1009</td>\n",
" <td>Andrea</td>\n",
" <td>Jones</td>\n",
" <td>123414</td>\n",
" <td>1990-07-09</td>\n",
" <td>F</td>\n",
" <td>120 Fall Creek, Gary,IL</td>\n",
" <td>234</td>\n",
" <td>70000.0</td>\n",
" <td>30003</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>E1010</td>\n",
" <td>Ann</td>\n",
" <td>Jacob</td>\n",
" <td>123415</td>\n",
" <td>1982-03-30</td>\n",
" <td>F</td>\n",
" <td>111 Britany Springs,Elgin,IL</td>\n",
" <td>220</td>\n",
" <td>70000.0</td>\n",
" <td>30004</td>\n",
" <td>5</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" EMP_ID F_NAME L_NAME SSN ... JOB_ID SALARY MANAGER_ID DEP_ID\n",
"0 E1002 Alice James 123457 ... 200 80000.0 30002 5\n",
"1 E1003 Steve Wells 123458 ... 300 50000.0 30002 5\n",
"2 E1004 Santosh Kumar 123459 ... 400 60000.0 30004 5\n",
"3 E1005 Ahmed Hussain 123410 ... 500 70000.0 30001 2\n",
"4 E1006 Nancy Allen 123411 ... 600 90000.0 30001 2\n",
"5 E1007 Mary Thomas 123412 ... 650 65000.0 30003 7\n",
"6 E1008 Bharath Gupta 123413 ... 660 65000.0 30003 7\n",
"7 E1009 Andrea Jones 123414 ... 234 70000.0 30003 7\n",
"8 E1010 Ann Jacob 123415 ... 220 70000.0 30004 5\n",
"\n",
"[9 rows x 11 columns]"
]
},
"metadata": {
"tags": []
},
"execution_count": 26
}
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "4zSzLCgWljWo"
},
"source": [
"\n",
"\n",
"---\n",
"\n"
]
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment