database/databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Databases\n",
    "\n",
    "This will be an effort to write to databases\n",
    "\n",
    "You need sqlite.  Windows users probably need to download it.  It's very small and easy to configure.\n",
    "\n",
    "Download here: https://www.sqlite.org/download.html\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3  # we use sqllite because most systems have it already installed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('example.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c = conn.cursor()\n",
    "\n",
    "c.execute('''DROP TABLE IF EXISTS stocks''')\n",
    "# Create table\n",
    "c.execute('''CREATE TABLE stocks\n",
    "             (id integer primary key, date text, trans text, symbol text, qty real, price real)''')\n",
    "\n",
    "# Insert a row of data\n",
    "c.execute(\"INSERT INTO stocks VALUES (1, '2006-01-05','BUY','RHAT',100,35.14)\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO:  Insert some more rows into stocks table\n",
    "\n",
    "c.execute(\"INSERT INTO stocks VALUES (2, '2019-01-05','BUY','AAPL',100,35.14)\")\n",
    "c.execute(\"INSERT INTO stocks VALUES (3, '2019-02-05','BUY','DELL',100,35.14)\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Save (commit) the changes\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's do some bulk inserts\n",
    "\n",
    "We'll do some bulk inserts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "t = ('RHAT',)\n",
    "c.execute('SELECT * FROM stocks WHERE symbol=?', t)\n",
    "print(c.fetchone())\n",
    "\n",
    "# Larger example that inserts many records at a time\n",
    "purchases = [(10,'2006-03-28', 'BUY', 'IBM', 1000, 45.00),\n",
    "             (11,'2006-04-05', 'BUY', 'MSFT', 1000, 72.00),\n",
    "             (12, '2006-04-06', 'SELL', 'IBM', 500, 53.00),\n",
    "            ]\n",
    "c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?,?)', purchases)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in c.execute('SELECT * FROM stocks ORDER BY price'):\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's use pandas to read into a dataframe\n",
    "\n",
    "This should be easier.  Make a dataframe by saying pd.read_sql.query\n",
    "\n",
    "TODO: You can read the stocks table by saying \"select * from stocks\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_sql_query(\"select * from stocks;\", conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_data = pd.DataFrame({\n",
    "    'id': [21, 22, 23],\n",
    "    'date': ['2018-01-01', '2018-04-13', '2018-04-15'],\n",
    "    'trans': ['BUY', 'BUY', 'BUY'],\n",
    "    'symbol': ['AAPL', 'DELL', 'CSCO'],\n",
    "    'qty': [100.0, 200.0, 300.0],\n",
    "    'price': [45.0, 55.0, 66.0]\n",
    "})\n",
    "\n",
    "df = pd.concat([df, new_data], ignore_index=True)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(\"stocks\", conn, if_exists=\"replace\", index=False) # write to table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Do a read from the stocks table\n",
    "\n",
    "Use pandas to read from the stocks table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: DO a read from the stocks table and make sure the new rows are there?\n",
    "\n",
    "df = pd.read_sql_query(\"select * from stocks;\", conn)\n",
    " # TODO:How to read from table.\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ORM in Python\n",
    "\n",
    "Python supports ORMs (Object-Relational Mapping) like other languages. Examples of ORMs are (N)Hibernate (Java/.NET), Spring Framework (Java), Entity Framework (.NET) and many others.\n",
    "\n",
    "In Python, there are a couple of ORMs that are popular. One is the very popular django framework, which is an all-inclusive MVC framework similar to Spring in Java, both of which which also have a ORM (among a huge number of other features).\n",
    "\n",
    "In keeping with the philosophy of \"do one thing and do it well\" SQLAlchemy is designed to provide a lightweight database layer + ORM for python.  We will look at SQLAlchemy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, ForeignKey, Float, String\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import relationship, create_session\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy import Table, MetaData\n",
    "\n",
    "Base = automap_base()\n",
    "\n",
    "engine = create_engine('sqlite:///example.db')\n",
    "meta = MetaData()\n",
    "meta.reflect(bind=engine)  # Reflect all tables available in the database\n",
    "Stocks = meta.tables['stocks']  # Access the 'stocks' table\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query in SQLAlchemy ORM\n",
    "\n",
    "Let's do a query of stocks in sqlalchemy ORM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session = create_session(bind=engine)\n",
    "\n",
    "stocks = session.query(Stocks).all()    \n",
    "\n",
    "for s in stocks:\n",
    "    print((s.id,s.date,s.symbol,s.trans,s.price,s.qty))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Close the connection\n",
    "\n",
    "Run this cell to close the connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}