SQLite-with-Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "23ae745b-2d0f-47af-be6b-2c47f3eb2512",
   "metadata": {
    "tags": []
   },
   "source": [
    "# SQLite with Python notes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1e0444c-ac0c-49c3-bc61-407558e6e758",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa8973ad-86b5-4dd4-b323-29dce5b8156a",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Connecting to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2320a0e7-b12b-4262-9fb7-92edbf169232",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to database\n",
    "# To make not necessary to manually commit and enable auto-commit mode, add the parameter isolation_level=None\n",
    "# See: https://stackoverflow.com/questions/4699605/why-doesn-t-sqlite3-require-a-commit-call-to-save-data\n",
    "\n",
    "conn = sqlite3.connect('database.db', isolation_level = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1301c3be-ee94-441a-a826-45c9dbd57afc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating cursor\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f8dd3ba-7d9a-42d4-b05d-f1956c28a78a",
   "metadata": {},
   "source": [
    "## Setting up UTF-8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3a1a18c-01a9-4659-a2e0-9bebd01940e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('PRAGMA encoding=\"utf-8\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f0bf549-9c7f-42da-98ed-b52548473c98",
   "metadata": {},
   "source": [
    "## CREATE Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5b921df-9634-41f8-aef9-6cd4a92f040a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CREATE query\n",
    "create_query = 'CREATE TABLE courses \\\n",
    "(id INTEGER PRIMARY KEY AUTOINCREMENT, \\\n",
    "title VARCHAR(128) NOT NULL, \\\n",
    "category VARCHAR(128))'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30dfba0f-27f1-4eed-a68b-2b3ad6573043",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute CREATE query\n",
    "cur.execute(create_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ede2ab80-b91a-40ba-bf91-30dbc66450b9",
   "metadata": {},
   "source": [
    "## INSERT Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff786c72-7c30-4c06-8b04-62b26c70aec0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# INSERT query with placeholders (?)\n",
    "insert_query = 'INSERT INTO courses(title, category) VALUES(?, ?)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "000041e8-0f40-4130-a2dc-e06ab6787d10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# INSERT data\n",
    "data = [('Ciência de Dados', 'Data Science'),\n",
    "         ('Big Data Fundamentos', 'Big Data'),\n",
    "         ('Python Fundamentos', 'Análise de Dados'),\n",
    "         ('Gestão de Dados com MongoDB', 'Big Data'),\n",
    "         ('R Fundamentos', 'Análise de Dados')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f09a886-e2b1-446e-9a57-293071d03f5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Executes INSERT query\n",
    "for course in data:\n",
    "    cur.execute(insert_query, course)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0982ca7d-420c-46cc-ba85-df60bad57051",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Commits the transaction explicity\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4c620a0-d4e8-4c92-a756-b73d3d0f4269",
   "metadata": {},
   "source": [
    "## Select Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e511e49c-1d15-41c0-8ded-abd032d2f954",
   "metadata": {},
   "outputs": [],
   "source": [
    "# SELECT query\n",
    "select_query = 'SELECT * FROM courses'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3299976b-04fe-4681-9442-35fcf689f478",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Executes SELECT query\n",
    "cur1 = cur.execute(select_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb2b1a88-702e-4c84-aca0-b80fa8083d0a",
   "metadata": {},
   "source": [
    "### Fetchall Method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff01509e-b885-4432-8f22-1cea7af39be6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetch data (this method loads all data into memory)\n",
    "# Not good if we have large ammounts of data\n",
    "\n",
    "db_data = cur1.fetchall()\n",
    "\n",
    "for id, name, category in db_data:\n",
    "    print(f'ID: {id}; Name: {name}; Category: {category}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee028bfb-ce71-4dce-85c8-e0840da1580a",
   "metadata": {},
   "source": [
    "### Cursor Iterator Method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0f2427e-822d-46dd-9ea0-37cfb71ee535",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This method, using cursor iterator, is more efficient for large ammounts of data\n",
    "# Return query's rows one by one only when the program requires it\n",
    "\n",
    "for id, name, category in cur1:\n",
    "    print(f'ID: {id}; Name: {name}; Category: {category}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d315e149-a0ac-41b3-a8f7-9f2ca73193cf",
   "metadata": {},
   "source": [
    "## Closing the Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c225b33-448b-4472-9780-a2e8d7eb1426",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d2f11f7-6dc5-47f4-9825-f0053ea27740",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}