Skip to content

Instantly share code, notes, and snippets.

@jbanerje

jbanerje/python-sqlite-basics.ipynb

Last active June 20, 2020 23:03
Show Gist options
  • Save jbanerje/2748be0307921d6ac7132772f7828dae to your computer and use it in GitHub Desktop.
Save jbanerje/2748be0307921d6ac7132772f7828dae to your computer and use it in GitHub Desktop.
Download ZIP
Python Sqlite Basics
Display the source blob
Display the rendered blob
Raw
python-sqlite-basics.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# PYTHON SQLITE BASICS"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"SQLite comes in built with Python3. No installation is required. \n",
"We will import 3 packages here - sqlite3, pandas and sys"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Import packages"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import sqlite3\n",
"import sys\n",
"import os"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Make Sqlite3 connection Object\n",
"This sets the location of the database on disk\n",
"* If the database is not present, it will create a new database\n",
"* If the database is present, it will connect to the database"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [],
"source": [
"conn = sqlite3.connect('../Python_SQLite/data/example.db')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Create Cursor\n",
"Cursor class is an instance which can invoke methods (functions in class) that execute SQLite statements, fetch data from the result sets of the queries."
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [],
"source": [
"cursr = conn.cursor()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Create Table\n",
"We will write a class to create table in Sqlite database which can be called as necessary"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [],
"source": [
"class CreateTable:\n",
"\n",
" def __init__(self, cursr, table_name, table_defn):\n",
" self.cursr = cursr\n",
" self.table_name = table_name\n",
" self.table_defn = table_defn\n",
"\n",
" def create_table(self):\n",
" try:\n",
" self.cursr.execute(f'''CREATE TABLE {self.table_name} {tuple(self.table_defn)}''')\n",
" print ('Table Created Sucessfully - {} '.format(self.table_name))\n",
" except :\n",
" print('Table Creation Failed - {}'.format(sys.exc_info()[0]))\n",
" return"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Next we will create the definition. We will need the following to define the table\n",
"* Table Name\n",
"* Table Column Names\n",
"* Table Column DataType\n",
"* Constrains"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [],
"source": [
"table_name = 'stocks'\n",
"table_defn = { 'ID' : 'real primary key', \n",
" 'DATE' : 'text',\n",
" 'TRANS' : 'text', \n",
" 'SYMBOL' : 'text', \n",
" 'QTY' : 'real',\n",
" 'PRICE' : 'real'\n",
" }"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Create Table"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Table Created Sucessfully - stocks \n"
]
}
],
"source": [
"create_table = CreateTable(cursr, table_name, table_defn)\n",
"create_table.create_table()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Nice ! Table is created . Lets insert some data into the table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Insert Records Into Table"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [],
"source": [
"class InsertIntoTable:\n",
"\n",
" def __init__(self, conn, cursr, table_name):\n",
" self.conn = conn\n",
" self.cursr = cursr\n",
" self.table_name = table_name\n",
"\n",
" def insert_record(self, record):\n",
" try:\n",
" self.record = record\n",
" self.cursr.execute(f'''INSERT INTO {self.table_name} VALUES {self.record}''')\n",
" self.conn.commit()\n",
" print ('Record Updated Sucessfully - {} '.format(self.record))\n",
" except :\n",
" print ('Record Update Failed - {}'.format(sys.exc_info()[0]))\n",
" return"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's Update some records"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Record Updated Sucessfully - (111, '2006-01-05', 'BUY', 'RHEL', 100, 35.14) \n",
"Record Updated Sucessfully - (222, '2006-01-06', 'SELL', 'BABA', 105, 20.14) \n",
"Record Updated Sucessfully - (333, '2006-01-05', 'BUY', 'MSFT', 100, 40.21) \n",
"Record Updated Sucessfully - (444, '2006-01-06', 'SELL', 'TSLA', 105, 60.45) \n"
]
}
],
"source": [
"update_rec_into_table = InsertIntoTable(conn, cursr, table_name)\n",
"update_rec_into_table.insert_record((111, '2006-01-05','BUY', 'RHEL',100,35.14))\n",
"update_rec_into_table.insert_record((222, '2006-01-06','SELL','BABA',105,20.14))\n",
"update_rec_into_table.insert_record((333, '2006-01-05','BUY', 'MSFT',100,40.21))\n",
"update_rec_into_table.insert_record((444, '2006-01-06','SELL','TSLA',105,60.45))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Fetch Data from Table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Table is now updated with data. Let's fetch the data from table"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [],
"source": [
"class FetchFromTable:\n",
"\n",
" def __init__(self, conn):\n",
" self.conn = conn\n",
" \n",
" def fetch_data(self, query):\n",
" self.query = query \n",
" query_result_df = pd.read_sql(query, self.conn)\n",
" return query_result_df"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"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>ID</th>\n",
" <th>DATE</th>\n",
" <th>TRANS</th>\n",
" <th>SYMBOL</th>\n",
" <th>QTY</th>\n",
" <th>PRICE</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>111</td>\n",
" <td>2006-01-05</td>\n",
" <td>BUY</td>\n",
" <td>RHEL</td>\n",
" <td>100</td>\n",
" <td>35.14</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>222</td>\n",
" <td>2006-01-06</td>\n",
" <td>SELL</td>\n",
" <td>BABA</td>\n",
" <td>105</td>\n",
" <td>20.14</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>333</td>\n",
" <td>2006-01-05</td>\n",
" <td>BUY</td>\n",
" <td>MSFT</td>\n",
" <td>100</td>\n",
" <td>40.21</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>444</td>\n",
" <td>2006-01-06</td>\n",
" <td>SELL</td>\n",
" <td>TSLA</td>\n",
" <td>105</td>\n",
" <td>60.45</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" ID DATE TRANS SYMBOL QTY PRICE\n",
"0 111 2006-01-05 BUY RHEL 100 35.14\n",
"1 222 2006-01-06 SELL BABA 105 20.14\n",
"2 333 2006-01-05 BUY MSFT 100 40.21\n",
"3 444 2006-01-06 SELL TSLA 105 60.45"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#Fetch data\n",
"query = f'''SELECT * FROM {table_name}'''\n",
"fetch_data_from_table = FetchFromTable(conn)\n",
"qry_result = fetch_data_from_table.fetch_data(query)\n",
"qry_result"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Congratulations for coming so far. We created the table sucessfully, Updated Record and fetched the data. Lets try another query"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {},
"outputs": [
{
"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>ID</th>\n",
" <th>DATE</th>\n",
" <th>TRANS</th>\n",
" <th>SYMBOL</th>\n",
" <th>QTY</th>\n",
" <th>PRICE</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>111</td>\n",
" <td>2006-01-05</td>\n",
" <td>BUY</td>\n",
" <td>RHEL</td>\n",
" <td>100</td>\n",
" <td>35.14</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>222</td>\n",
" <td>2006-01-06</td>\n",
" <td>SELL</td>\n",
" <td>BABA</td>\n",
" <td>105</td>\n",
" <td>20.14</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" ID DATE TRANS SYMBOL QTY PRICE\n",
"0 111 2006-01-05 BUY RHEL 100 35.14\n",
"1 222 2006-01-06 SELL BABA 105 20.14"
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#Fetch data\n",
"query = f'''SELECT * FROM {table_name} WHERE PRICE BETWEEN 20 AND 40'''\n",
"fetch_data_from_table = FetchFromTable(conn)\n",
"fetch_data_from_table.fetch_data(query)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Update Record Into Table"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {},
"outputs": [],
"source": [
"class UpdateTable:\n",
"\n",
" def __init__(self, conn, cursr, table_name, update_query):\n",
" self.cursr = cursr\n",
" self.table_name = table_name\n",
" self.update_query = update_query\n",
" print(update_query)\n",
"\n",
" def update_record(self):\n",
" try:\n",
" self.cursr.execute(update_query)\n",
" print ('Record Updated Sucessfully!')\n",
" except :\n",
" print ('Record Update Failed - {}'.format(sys.exc_info()[0]))\n",
" return"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"UPDATE stocks SET SYMBOL='DADA' WHERE ID=222\n",
"Record Updated Sucessfully!\n"
]
}
],
"source": [
"update_query = f'''UPDATE {table_name} SET SYMBOL='DADA' WHERE ID=222'''\n",
"update_tble = UpdateTable(conn, cursr, table_name, update_query)\n",
"update_tble.update_record()"
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {},
"outputs": [
{
"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>ID</th>\n",
" <th>DATE</th>\n",
" <th>TRANS</th>\n",
" <th>SYMBOL</th>\n",
" <th>QTY</th>\n",
" <th>PRICE</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>111</td>\n",
" <td>2006-01-05</td>\n",
" <td>BUY</td>\n",
" <td>RHEL</td>\n",
" <td>100</td>\n",
" <td>35.14</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>222</td>\n",
" <td>2006-01-06</td>\n",
" <td>SELL</td>\n",
" <td>DADA</td>\n",
" <td>105</td>\n",
" <td>20.14</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>333</td>\n",
" <td>2006-01-05</td>\n",
" <td>BUY</td>\n",
" <td>MSFT</td>\n",
" <td>100</td>\n",
" <td>40.21</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>444</td>\n",
" <td>2006-01-06</td>\n",
" <td>SELL</td>\n",
" <td>TSLA</td>\n",
" <td>105</td>\n",
" <td>60.45</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" ID DATE TRANS SYMBOL QTY PRICE\n",
"0 111 2006-01-05 BUY RHEL 100 35.14\n",
"1 222 2006-01-06 SELL DADA 105 20.14\n",
"2 333 2006-01-05 BUY MSFT 100 40.21\n",
"3 444 2006-01-06 SELL TSLA 105 60.45"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#Fetch data\n",
"query = f'''SELECT * FROM {table_name}'''\n",
"fetch_data_from_table = FetchFromTable(conn)\n",
"qry_result = fetch_data_from_table.fetch_data(query)\n",
"qry_result"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## List All Tables in Database\n",
"This class will list all the tables inside a database"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {},
"outputs": [],
"source": [
"class ListAllTables:\n",
"\n",
" def __init__(self, conn):\n",
" self.conn = conn\n",
" \n",
" def list_all_tables(self, query):\n",
" self.query = query \n",
" table_list = pd.read_sql(query, self.conn)\n",
" return table_list"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [
{
"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>name</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>stocks</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" name\n",
"0 stocks"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"query_list_all_tables = f\"\"\"SELECT NAME FROM sqlite_master WHERE TYPE = 'table'\"\"\"\n",
"list_all_tabl_in_db = ListAllTables(conn)\n",
"list_all_tabl_in_db.list_all_tables(query_list_all_tables)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Drop Table From Database"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [],
"source": [
"class DropTable:\n",
"\n",
" def __init__(self, cursr, table_name):\n",
" self.cursr = cursr\n",
" self.table_name = table_name\n",
"\n",
" def drop_table(self):\n",
" try:\n",
" self.cursr.execute(f'''DROP TABLE {self.table_name}''')\n",
" print ('Table Dropped Sucessfully - {} '.format(self.table_name))\n",
" except :\n",
" print('Table Drop Failed - {}'.format(sys.exc_info()[0]))\n",
" return"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Table Dropped Sucessfully - stocks \n"
]
}
],
"source": [
"droptable = DropTable(cursr, table_name)\n",
"droptable.drop_table()"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3.7.4 64-bit ('base': conda)",
"language": "python",
"name": "python37464bitbaseconda6272ed5229aa49f383a07ef880627b0f"
},
"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.7.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment