SQLAlchemy Lesson.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in c:\\users\\sengu\\anaconda3\\lib\\site-packages (2.0.4)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in c:\\users\\sengu\\anaconda3\\lib\\site-packages (from sqlalchemy) (4.3.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\sengu\\anaconda3\\lib\\site-packages (from sqlalchemy) (2.0.2)\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine , ForeignKey,Column, String, CHAR ,Integer\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\sengu\\AppData\\Local\\Temp\\ipykernel_20188\\4196137762.py:1: MovedIn20Warning: The ``declarative_base()`` function is now available as sqlalchemy.orm.declarative_base(). (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  Base = declarative_base()\n"
     ]
    }
   ],
   "source": [
    "Base = declarative_base()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Person(Base):\n",
    "    '''Creation of the tablr class and defining the columns and it's constraints over here'''\n",
    "    __tablename__ = \"person\"\n",
    "    adhaar = Column(\"adhaar\",Integer,primary_key=True)\n",
    "    firstname = Column(\"firstname\",String)\n",
    "    lastname = Column(\"lastname\",String)\n",
    "    gender = Column(\"gender\",CHAR)\n",
    "    age = Column(\"age\",Integer)\n",
    "\n",
    "    ''' Defining of the function that stays within the class and takes all the columns defined over in the previous line as the argument parameter'''\n",
    "    def __init__(self,adhaar,firstname,lastname,gender,age):\n",
    "        self.adhaar = adhaar\n",
    "        self.firstname = firstname\n",
    "        self.lastname = lastname\n",
    "        self.gender = gender\n",
    "        self.age = age\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return f\"({self.adhaar} , {self.firstname} ,{self.lastname},{self.gender},{self.age})\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-02-20 13:03:43,135 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-02-20 13:03:43,137 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"person\")\n",
      "2023-02-20 13:03:43,138 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-02-20 13:03:43,139 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"person\")\n",
      "2023-02-20 13:03:43,140 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-02-20 13:03:43,142 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE person (\n",
      "\tadhaar INTEGER NOT NULL, \n",
      "\tfirstname VARCHAR, \n",
      "\tlastname VARCHAR, \n",
      "\tgender CHAR, \n",
      "\tage INTEGER, \n",
      "\tPRIMARY KEY (adhaar)\n",
      ")\n",
      "\n",
      "\n",
      "2023-02-20 13:03:43,143 INFO sqlalchemy.engine.Engine [no key 0.00073s] ()\n",
      "2023-02-20 13:03:43,152 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "engine = create_engine(\"sqlite:///mydb.db\",echo=True)\n",
    "Base.metadata.create_all(bind = engine)\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "person = Person(615330967381,\"Chandan\",\"Sengupta\",\"Male\",35)\n",
    "session.add(person)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-02-20 13:05:32,842 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-02-20 13:05:32,845 INFO sqlalchemy.engine.Engine INSERT INTO person (adhaar, firstname, lastname, gender, age) VALUES (?, ?, ?, ?, ?)\n",
      "2023-02-20 13:05:32,847 INFO sqlalchemy.engine.Engine [generated in 0.00117s] (615330967381, 'Chandan', 'Sengupta', 'Male', 35)\n",
      "2023-02-20 13:05:32,849 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-02-20 13:08:39,973 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-02-20 13:08:39,973 INFO sqlalchemy.engine.Engine INSERT INTO person (adhaar, firstname, lastname, gender, age) VALUES (?, ?, ?, ?, ?)\n",
      "2023-02-20 13:08:39,973 INFO sqlalchemy.engine.Engine [generated in 0.00085s] [(615330967391, 'Angela', 'Martin', 'Female', 38), (615330967371, 'Jim', 'Halpert', 'Male', 36), (615330967361, 'Michael', 'Scott', 'Male', 55), (615430967381, 'Pam', 'Beesly', 'Female', 32)]\n",
      "2023-02-20 13:08:39,973 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "p1 = Person(615330967391,\"Angela\",\"Martin\",\"Female\",38)\n",
    "p2 = Person(615330967371,\"Jim\",\"Halpert\",\"Male\",36)\n",
    "p3 = Person(615330967361,\"Michael\",\"Scott\",\"Male\",55)\n",
    "p4 = Person(615430967381,\"Pam\",\"Beesly\",\"Female\",32)\n",
    "session.add(p1)\n",
    "session.add(p2)\n",
    "session.add(p3)\n",
    "session.add(p4)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-02-20 13:10:32,125 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-02-20 13:10:32,130 INFO sqlalchemy.engine.Engine SELECT person.adhaar AS person_adhaar, person.firstname AS person_firstname, person.lastname AS person_lastname, person.gender AS person_gender, person.age AS person_age \n",
      "FROM person\n",
      "2023-02-20 13:10:32,131 INFO sqlalchemy.engine.Engine [generated in 0.00099s] ()\n",
      "[(615330967361 , Michael ,Scott,Male,55), (615330967371 , Jim ,Halpert,Male,36), (615330967381 , Chandan ,Sengupta,Male,35), (615330967391 , Angela ,Martin,Female,38), (615430967381 , Pam ,Beesly,Female,32)]\n"
     ]
    }
   ],
   "source": [
    "''' Query Making'''\n",
    "results = session.query(Person).all()\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.9.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "c715fae038ea3023ce1b7da7ba9885d98010e1b6ab84c7b957e0940952b2221c"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}