week-6/6-sqlite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLite\n",
    "- serverless\n",
    "- raw SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "creating list for insterting it to db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "\n",
    "def random_int():\n",
    "    return(random.randint(1, 100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_list = [\n",
    "    (random_int(), \"apple\", \"red\"),\n",
    "    (random_int(), \"banana\", \"yellow\"),\n",
    "    (random_int(), \"cherry\", \"red\"),\n",
    "    (random_int(), \"date\", \"brown\"),\n",
    "    (random_int(), \"grape\", \"purple\"),\n",
    "    (random_int(), \"kiwi\", \"brown\"),\n",
    "    (random_int(), \"lemon\", \"yellow\"),\n",
    "    (random_int(), \"orange\", \"orange\"),\n",
    "    (random_int(), \"pear\", \"green\"),\n",
    "    (random_int(), \"watermelon\", \"green\")\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(75, 'apple', 'red'),\n",
       " (65, 'banana', 'yellow'),\n",
       " (55, 'cherry', 'red'),\n",
       " (73, 'date', 'brown'),\n",
       " (42, 'grape', 'purple'),\n",
       " (61, 'kiwi', 'brown'),\n",
       " (11, 'lemon', 'yellow'),\n",
       " (100, 'orange', 'orange'),\n",
       " (72, 'pear', 'green'),\n",
       " (32, 'watermelon', 'green')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "basic usage of sqlite 3\n",
    "    - create connection\n",
    "    - create cursor object\n",
    "    - create a new table\n",
    "    - instert my_list data to the new table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "connection = sqlite3.connect(\"fruits.db\")\n",
    "\n",
    "cursor = connection.cursor()\n",
    "\n",
    "cursor.execute(\"CREATE TABLE fruits (amount interger, fruit text, color text)\")\n",
    "\n",
    "cursor.executemany(\"INSERT INTO fruits VALUES (?,?,?)\", my_list)\n",
    "\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print database rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update script\n",
    "\n",
    "connection = sqlite3.connect(\"fruits.db\")\n",
    "\n",
    "cursor = connection.cursor()\n",
    "\n",
    "cursor.execute(\"CREATE TABLE fruits (amount interger, fruit text, color text)\")\n",
    "\n",
    "cursor.executemany(\"INSERT INTO fruits VALUES (?,?,?)\", my_list)\n",
    "\n",
    "for row in cursor.execute(\"SELECT * FROM fruits\"):\n",
    "    print(row)\n",
    "\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add to script: print spesific rows using WHERE\n",
    "\n",
    "cursor.execute(\"SELECT * FROM fruits WHERE color=:c\", {\"c\": \"green\"})\n",
    "color_search = cursor.fetchall()\n",
    "print(color_search)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new table for our db\n",
    "\n",
    "cursor.execute(\"CREATE TABLE translates (en_color text, tr_color text)\")\n",
    "cursor.execute(\"INSERT INTO translates VALUES (?,?)\", (\"green\", \"yeşil\"))\n",
    "cursor.execute(\"SELECT * FROM translates WHERE en_color=:c\", {\"c\": \"green\"})\n",
    "translate_search = cursor.fetchall()  \n",
    "print(translate_search)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# manipulate database\n",
    "\n",
    "for data in color_search:\n",
    "    qs = [translate_search[0][1] if value==color_search[0][0] else value for value in data]\n",
    "    print(qs)\n",
    "\n",
    "# can use INNER JOIN method"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- exercise: add all color translations to table and make query for all data"
   ]
  }
 ],
 "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}