sqlite3-introduction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "75eda298",
   "metadata": {},
   "source": [
    "# Python: Everything\n",
    " - 21) **Sqlite3 module, Introduction:** From creating a database, and tables, to update, delete, insert, and to query the tables<br>\n",
    "از آفریدن یک داده‌پایگاه، و پیچازه‌ها، تا به روزرسانی، وافکنی، گنجاندن، و پُرسه‌گری پیچازه‌ها\n",
    "<br>Instagram: @words.persian\n",
    "<br>https://github.com/ostad-ai/Python-Everything\n",
    "<br> Slides in English:\n",
    "https://www.pinterest.com/HamedShahHosseini/programming-languages/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b95a0049",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing sqlite3 module\n",
    "# درونبَری سنجانه یادشده\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1420965a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connecting to a dababase\n",
    "# if it doesn't exist, it creates one implicitly\n",
    "# همبست شدن به یک داده‌پایگاه\n",
    "# اگر این داده‌پایگاه، هستیمند نباشد، آن را، درپرده، میآفریند\n",
    "connection=sqlite3.connect('ostad.db')\n",
    "# to speed performance (برای چابکی کارایی)\n",
    "connection.row_factory=sqlite3.Row"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bee532c",
   "metadata": {},
   "source": [
    "CREATE TABLE table_name (\n",
    "    <br>column1 datatype,\n",
    "    <br>column2 datatype,\n",
    "    <br>column3 datatype,\n",
    "   <br>....\n",
    "<br>) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "15811a87",
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating a table; datatypes: NULL,INTEGER,REAL,TEXT,BLOB\n",
    "# Hint: with sqlite3 only, you can leave datatypes\n",
    "# آفریندن یک پیچازه در داده‌پایگاه\n",
    "# نکته: میتوان داده‌گونه ها را برای اسکیولایت3 نیاوریم\n",
    "command='''CREATE TABLE students\n",
    "        (id INTEGER PRIMARY KEY NOT NULL,\n",
    "         firstname TEXT NOT NULL,\n",
    "         lastname TEXT  NOT NULL,\n",
    "         gender TEXT,\n",
    "         department TEXT NOT NULL,\n",
    "         address TEXT NOT NULL,\n",
    "         yearofbirth INTEGER NOT NULL check(yearofbirth>0)\n",
    "        );'''\n",
    "# deleting table if it exists in the database\n",
    "# وافکندن پیچازه، اگر در داده‌پایگاه باشد\n",
    "connection.execute('drop table if exists students')\n",
    "# create the table (پیچازه را بیافرین)\n",
    "connection.execute(command)\n",
    "cursor=connection.cursor()\n",
    "# close if u'r done working with database\n",
    "# اگر کار با داده‌پایگاه به پایان رسید، آن را ببند\n",
    "# cursor.close(); connection.close() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a763140f",
   "metadata": {},
   "source": [
    "INSERT INTO table_name (column1, column2, column3, ...)\n",
    "<br>VALUES (value1, value2, value3, ...)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "854b0987",
   "metadata": {},
   "outputs": [],
   "source": [
    "# inserting a single record (row)\n",
    "# گنجاندن یک آگاشت (رج) تکین\n",
    "command='''insert into students\n",
    "(firstname,lastname,gender,department,address,yearofbirth)\n",
    "values(?,?,?,?,?,?)'''\n",
    "data=('Pirooz','Daneshi','male','CS','Second Alley','2001')\n",
    "cursor.execute(command,data)\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "98dc8f16",
   "metadata": {},
   "outputs": [],
   "source": [
    "# inserting multiple records (rows) together\n",
    "# گنجاندن چند آگاشت (رج) باهم\n",
    "command='''insert into students\n",
    "(firstname,lastname,gender,department,address,yearofbirth)\n",
    "values(?,?,?,?,?,?)'''\n",
    "data=[('Jane','Foda','female','CS','Sixth Avenue','2002'),\n",
    "     ('David','White','male','EE','Five square','1999'),\n",
    "     ('Arash','Dirin','male','CS','Street Red','2002'),\n",
    "     ('Mina','Dastin','female','EE','Street White','2000')]\n",
    "cursor.executemany(command,data)\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b130441",
   "metadata": {},
   "source": [
    "SELECT column1, column2, ...\n",
    "<br>FROM table_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "037a7cfd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'firstname', 'lastname', 'gender', 'department', 'address', 'yearofbirth']\n",
      "(1, 'Pirooz', 'Daneshi', 'male', 'CS', 'Second Alley', 2001)\n",
      "(2, 'Jane', 'Foda', 'female', 'CS', 'Sixth Avenue', 2002)\n",
      "(3, 'David', 'White', 'male', 'EE', 'Five square', 1999)\n",
      "(4, 'Arash', 'Dirin', 'male', 'CS', 'Street Red', 2002)\n",
      "(5, 'Mina', 'Dastin', 'female', 'EE', 'Street White', 2000)\n"
     ]
    }
   ],
   "source": [
    "# querying the table\n",
    "# پُرسه‌گری پیچازه\n",
    "command='select * from students'\n",
    "rows=cursor.execute(command)\n",
    "for i,row in enumerate(rows):\n",
    "    if i==0:\n",
    "        print(row.keys())\n",
    "    print(row[:])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07143cd9",
   "metadata": {},
   "source": [
    "SELECT column_name(s) FROM table_name\n",
    "<br>WHERE condition\n",
    "<br>GROUP BY column_name(s)\n",
    "<br>ORDER BY column_name(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8e2a98a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'firstname', 'lastname', 'gender', 'department', 'address', 'yearofbirth']\n",
      "(3, 'David', 'White', 'male', 'EE', 'Five square', 1999)\n",
      "(1, 'Pirooz', 'Daneshi', 'male', 'CS', 'Second Alley', 2001)\n",
      "(4, 'Arash', 'Dirin', 'male', 'CS', 'Street Red', 2002)\n"
     ]
    }
   ],
   "source": [
    "# querying the table with where\n",
    "# پُرسه‌گری پیچازه با ور\n",
    "command='''select * from students where gender='male'\n",
    "order by yearofbirth ASC'''\n",
    "rows=cursor.execute(command)\n",
    "for i,row in enumerate(rows):\n",
    "    if i==0:\n",
    "        print(row.keys())\n",
    "    print(row[:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9ca742e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['count(id)', 'gender', 'department']\n",
      "(2, 'male', 'CS')\n",
      "(1, 'male', 'EE')\n"
     ]
    }
   ],
   "source": [
    "# querying the table with WHERE and COUNT and GROUP\n",
    "# پُرسه‌گری پیچازه، با گزینه‌های بیشتر\n",
    "command='''select count(id),gender,department\n",
    "from students where gender='male'\n",
    "group by department '''\n",
    "rows=cursor.execute(command)\n",
    "for i,row in enumerate(rows):\n",
    "    if i==0:\n",
    "        print(row.keys())\n",
    "    print(row[:])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68cf6ac2",
   "metadata": {},
   "source": [
    "UPDATE table_name\n",
    "<br>SET column1 = value1, column2 = value2, ...\n",
    "<br>WHERE condition "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0a5fcda3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'firstname', 'lastname', 'gender', 'department', 'address', 'yearofbirth']\n",
      "(1, 'Pirooz', 'Daneshipour', 'male', 'CS', 'Second Alley', 2001)\n",
      "(2, 'Jane', 'Foda', 'female', 'CS', 'Sixth Avenue', 2002)\n",
      "(3, 'David', 'White', 'male', 'EE', 'Five square', 1999)\n",
      "(4, 'Arash', 'Dirin', 'male', 'CS', 'Street Red', 2002)\n",
      "(5, 'Mina', 'Dastin', 'female', 'EE', 'Street White', 2000)\n"
     ]
    }
   ],
   "source": [
    "# lets update a single record (row)\n",
    "# به روزرسانی یک آگاشت (رج) تکین\n",
    "command='''update students\n",
    " set lastname='Daneshipour' where lastname='Daneshi' '''\n",
    "cursor.execute(command)\n",
    "connection.commit()\n",
    "#---------------------to see the effects\n",
    "command='select * from students'\n",
    "rows=cursor.execute(command)\n",
    "for i,row in enumerate(rows):\n",
    "    if i==0:\n",
    "        print(row.keys())\n",
    "    print(row[:])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3bea81f",
   "metadata": {},
   "source": [
    "DELETE FROM table_name WHERE condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "780bc63c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'firstname', 'lastname', 'gender', 'department', 'address', 'yearofbirth']\n",
      "(1, 'Pirooz', 'Daneshipour', 'male', 'CS', 'Second Alley', 2001)\n",
      "(2, 'Jane', 'Foda', 'female', 'CS', 'Sixth Avenue', 2002)\n",
      "(4, 'Arash', 'Dirin', 'male', 'CS', 'Street Red', 2002)\n",
      "(5, 'Mina', 'Dastin', 'female', 'EE', 'Street White', 2000)\n"
     ]
    }
   ],
   "source": [
    "# to delete a row or some rows from table\n",
    "# if we don't use where, all rows are deleted\n",
    "command='''delete from students\n",
    "where firstname='David' and lastname='White' '''\n",
    "cursor.execute(command)\n",
    "connection.commit()\n",
    "#-----------------------\n",
    "command='select * from students'\n",
    "rows=cursor.execute(command)\n",
    "for i,row in enumerate(rows):\n",
    "    if i==0:\n",
    "        print(row.keys())\n",
    "    print(row[:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2893032d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# be sure to uncomment this finally\n",
    "# دلاسوده شوید که سرانجام، دستور زیر را از منش‌داشت درآورید\n",
    "#cursor.close(); connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3b11c69",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}