C3M2_UGL_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b7826a19-2b9a-453a-8a97-d85feccc3f9a",
   "metadata": {},
   "source": [
    "# Ungraded Lab - Lecture Implementing CRUD operations\n",
    "\n",
    "This ungraded lab has the content discussed in the lecture Implementing CRUD operations, your task is to implement the CRUD operations for the tables Product, Order and OrderItem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "29d08c47-7383-45a4-9bec-f3e66775b7a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-05 22:11:21,371 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-05 22:11:21,372 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"users\")\n",
      "2024-10-05 22:11:21,372 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,417 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"users\")\n",
      "2024-10-05 22:11:21,417 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,418 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"products\")\n",
      "2024-10-05 22:11:21,419 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,439 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"products\")\n",
      "2024-10-05 22:11:21,440 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,440 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"orders\")\n",
      "2024-10-05 22:11:21,441 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,461 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"orders\")\n",
      "2024-10-05 22:11:21,462 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,462 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"order_items\")\n",
      "2024-10-05 22:11:21,463 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,484 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"order_items\")\n",
      "2024-10-05 22:11:21,485 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:11:21,486 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE users (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tname VARCHAR, \n",
      "\temail VARCHAR, \n",
      "\tPRIMARY KEY (id), \n",
      "\tUNIQUE (email)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-05 22:11:21,486 INFO sqlalchemy.engine.Engine [no key 0.00053s] ()\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_258/95043806.py:6: 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"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-05 22:11:21,566 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE products (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tname VARCHAR, \n",
      "\tprice INTEGER, \n",
      "\tPRIMARY KEY (id)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-05 22:11:21,566 INFO sqlalchemy.engine.Engine [no key 0.00070s] ()\n",
      "2024-10-05 22:11:21,658 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE orders (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tuser_id INTEGER, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(user_id) REFERENCES users (id)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-05 22:11:21,659 INFO sqlalchemy.engine.Engine [no key 0.00062s] ()\n",
      "2024-10-05 22:11:21,749 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE order_items (\n",
      "\tid INTEGER NOT NULL, \n",
      "\torder_id INTEGER, \n",
      "\tproduct_id INTEGER, \n",
      "\tquantity INTEGER, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(order_id) REFERENCES orders (id), \n",
      "\tFOREIGN KEY(product_id) REFERENCES products (id)\n",
      ")\n",
      "\n",
      "\n",
      "2024-10-05 22:11:21,749 INFO sqlalchemy.engine.Engine [no key 0.00061s] ()\n",
      "2024-10-05 22:11:21,836 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, Column, Integer, String, ForeignKey\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker, relationship\n",
    "import pickle\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    email = Column(String, unique=True)\n",
    "    orders = relationship(\"Order\", back_populates=\"user\")\n",
    "\n",
    "class Product(Base):\n",
    "    __tablename__ = 'products'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    price = Column(Integer)\n",
    "    order_items = relationship(\"OrderItem\", back_populates=\"product\")\n",
    "\n",
    "class Order(Base):\n",
    "    __tablename__ = 'orders'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    user_id = Column(Integer, ForeignKey('users.id'))\n",
    "    user = relationship(\"User\", back_populates=\"orders\")\n",
    "    order_items = relationship(\"OrderItem\", back_populates=\"order\")\n",
    "\n",
    "class OrderItem(Base):\n",
    "    __tablename__ = 'order_items'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    order_id = Column(Integer, ForeignKey('orders.id'))\n",
    "    product_id = Column(Integer, ForeignKey('products.id'))\n",
    "    quantity = Column(Integer)\n",
    "    order = relationship(\"Order\", back_populates=\"order_items\")\n",
    "    product = relationship(\"Product\", back_populates=\"order_items\")\n",
    "\n",
    "engine = create_engine('sqlite:///ecommerce.db', echo=True)\n",
    "Base.metadata.create_all(engine)\n",
    "\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "\n",
    "## USER TABLE CRUD\n",
    "# Create\n",
    "def add_user(name, email):\n",
    "    new_user = User(name=name, email=email)\n",
    "    session.add(new_user)\n",
    "    session.commit()\n",
    "\n",
    "# Read\n",
    "def get_user(user_id):\n",
    "    return session.query(User).filter(User.id == user_id).first()\n",
    "\n",
    "# Update\n",
    "def update_user(user_id, name=None, email=None):\n",
    "    user = session.query(User).filter(User.id == user_id).first()\n",
    "    if user:\n",
    "        if name:\n",
    "            user.name = name\n",
    "        if email:\n",
    "            user.email = email\n",
    "        session.commit()\n",
    "\n",
    "# Delete\n",
    "def delete_user(user_id):\n",
    "    user = session.query(User).filter(User.id == user_id).first()\n",
    "    if user:\n",
    "        session.delete(user)\n",
    "        session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a5b4f4a-5ab6-45d7-93d5-501dcd32ca63",
   "metadata": {},
   "source": [
    "### Exercise 1 \n",
    "\n",
    "Using an LLM, implement the CRUD operations in the Product table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "08cc66d6-53ca-4c79-a50b-3fdb4bd8cc01",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-10-05 22:14:32,168 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-10-05 22:14:32,168 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"products\")\n",
      "2024-10-05 22:14:32,169 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-10-05 22:14:32,217 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_258/2085073620.py:7: 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": [
    "## PRODUCT TABLE CRUD\n",
    "from sqlalchemy import create_engine, Column, Integer, String, ForeignKey\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker, relationship\n",
    "\n",
    "# Setting up SQLAlchemy base and session\n",
    "Base = declarative_base()\n",
    "\n",
    "# Define the Product class with relevant fields and relationships\n",
    "class Product(Base):\n",
    "    __tablename__ = 'products'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    price = Column(Integer)\n",
    "    order_items = relationship(\"OrderItem\", back_populates=\"product\")\n",
    "\n",
    "# Create the SQLite database for eCommerce\n",
    "engine = create_engine('sqlite:///ecommerce.db', echo=True)\n",
    "Base.metadata.create_all(engine)\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "# Implement CRUD operations for the Product table\n",
    "# Create\n",
    "def add_product(name, price):\n",
    "    new_product = Product(name=name, price=price)\n",
    "    session.add(new_product)\n",
    "    session.commit()\n",
    "\n",
    "# Read\n",
    "def get_product(product_id):\n",
    "    return session.query(Product).filter(Product.id == product_id).first()\n",
    "\n",
    "# Update\n",
    "def update_product(product_id, name=None, price=None):\n",
    "    product = session.query(Product).filter(Product.id == product_id).first()\n",
    "    if product:\n",
    "        if name:\n",
    "            product.name = name\n",
    "        if price:\n",
    "            product.price = price\n",
    "        session.commit()\n",
    "\n",
    "# Delete\n",
    "def delete_product(product_id):\n",
    "    product = session.query(Product).filter(Product.id == product_id).first()\n",
    "    if product:\n",
    "        session.delete(product)\n",
    "        session.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b246d28f-7748-4072-9329-d06057ab8582",
   "metadata": {},
   "source": [
    "### Exercise 2 \n",
    "\n",
    "Using an LLM, implement the CRUD operations in the Order table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "33104dbb-30f4-4ba1-9fca-4d3c10a1b6be",
   "metadata": {},
   "outputs": [],
   "source": [
    "## ORDERS TABLE CRUD\n",
    "# Define the Order class with relevant fields and relationships\n",
    "class Order(Base):\n",
    "    __tablename__ = 'orders'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    user_id = Column(Integer, ForeignKey('users.id'))\n",
    "    user = relationship(\"User\", back_populates=\"orders\")\n",
    "    order_items = relationship(\"OrderItem\", back_populates=\"order\")\n",
    "\n",
    "# Create\n",
    "def add_order(user_id):\n",
    "    new_order = Order(user_id=user_id)\n",
    "    session.add(new_order)\n",
    "    session.commit()\n",
    "\n",
    "# Read\n",
    "def get_order(order_id):\n",
    "    return session.query(Order).filter(Order.id == order_id).first()\n",
    "\n",
    "# Update\n",
    "def update_order(order_id, user_id=None):\n",
    "    order = session.query(Order).filter(Order.id == order_id).first()\n",
    "    if order:\n",
    "        if user_id:\n",
    "            order.user_id = user_id\n",
    "        session.commit()\n",
    "\n",
    "# Delete\n",
    "def delete_order(order_id):\n",
    "    order = session.query(Order).filter(Order.id == order_id).first()\n",
    "    if order:\n",
    "        session.delete(order)\n",
    "        session.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a751f49-490b-4037-a328-41c116e01b80",
   "metadata": {},
   "source": [
    "### Exercise 3\n",
    "\n",
    "Using an LLM, implement the CRUD operations in the OrderItem table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "61b985f1-63c0-418b-b1a7-34bc703b5420",
   "metadata": {},
   "outputs": [],
   "source": [
    "## ORDER ITEMS CRUD\n",
    "# Define the OrderItem class with relevant fields and relationships\n",
    "class OrderItem(Base):\n",
    "    __tablename__ = 'order_items'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    order_id = Column(Integer, ForeignKey('orders.id'))\n",
    "    product_id = Column(Integer, ForeignKey('products.id'))\n",
    "    quantity = Column(Integer)\n",
    "    order = relationship(\"Order\", back_populates=\"order_items\")\n",
    "    product = relationship(\"Product\", back_populates=\"order_items\")\n",
    "\n",
    "# Create\n",
    "def add_order_item(order_id, product_id, quantity):\n",
    "    new_order_item = OrderItem(order_id=order_id, product_id=product_id, quantity=quantity)\n",
    "    session.add(new_order_item)\n",
    "    session.commit()\n",
    "\n",
    "# Read\n",
    "def get_order_item(order_item_id):\n",
    "    return session.query(OrderItem).filter(OrderItem.id == order_item_id).first()\n",
    "\n",
    "# Update\n",
    "def update_order_item(order_item_id, quantity=None):\n",
    "    order_item = session.query(OrderItem).filter(OrderItem.id == order_item_id).first()\n",
    "    if order_item:\n",
    "        if quantity:\n",
    "            order_item.quantity = quantity\n",
    "        session.commit()\n",
    "\n",
    "# Delete\n",
    "def delete_order_item(order_item_id):\n",
    "    order_item = session.query(OrderItem).filter(OrderItem.id == order_item_id).first()\n",
    "    if order_item:\n",
    "        session.delete(order_item)\n",
    "        session.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1500eaf-4752-48d1-8724-66a6b80f41a1",
   "metadata": {},
   "source": [
    "## Solution"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0ddd503-0e5b-48f1-b72f-8ed25ba077a0",
   "metadata": {},
   "source": [
    "### Exercise 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8ab4baa9-d2c6-4138-a153-596967edad95",
   "metadata": {},
   "outputs": [],
   "source": [
    "## PRODUCT TABLE CRUD\n",
    "# Create\n",
    "def add_product(name, price):\n",
    "    new_product = Product(name=name, price=price)\n",
    "    session.add(new_product)\n",
    "    session.commit()\n",
    "\n",
    "# Read\n",
    "def get_product(product_id):\n",
    "    return session.query(Product).filter(Product.id == product_id).first()\n",
    "\n",
    "# Update\n",
    "def update_product(product_id, name=None, price=None):\n",
    "    product = session.query(Product).filter(Product.id == product_id).first()\n",
    "    if product:\n",
    "        if name:\n",
    "            product.name = name\n",
    "        if price:\n",
    "            product.price = price\n",
    "        session.commit()\n",
    "\n",
    "# Delete\n",
    "def delete_product(product_id):\n",
    "    product = session.query(Product).filter(Product.id == product_id).first()\n",
    "    if product:\n",
    "        session.delete(product)\n",
    "        session.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c84f28d0-095a-44f4-b83e-f5ecf73a8961",
   "metadata": {},
   "source": [
    "## Exercise 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "adf1d6c6-84bc-4f8a-842b-b71530f9eadc",
   "metadata": {},
   "outputs": [],
   "source": [
    "## ORDERS TABLE CRUD\n",
    "# Create\n",
    "def add_order(user_id):\n",
    "    new_order = Order(user_id=user_id)\n",
    "    session.add(new_order)\n",
    "    session.commit()\n",
    "\n",
    "# Read\n",
    "def get_order(order_id):\n",
    "    return session.query(Order).filter(Order.id == order_id).first()\n",
    "\n",
    "# Update\n",
    "def update_order(order_id, user_id=None):\n",
    "    order = session.query(Order).filter(Order.id == order_id).first()\n",
    "    if order:\n",
    "        if user_id:\n",
    "            order.user_id = user_id\n",
    "        session.commit()\n",
    "\n",
    "# Delete\n",
    "def delete_order(order_id):\n",
    "    order = session.query(Order).filter(Order.id == order_id).first()\n",
    "    if order:\n",
    "        session.delete(order)\n",
    "        session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d10fe78-0c26-470d-bdbe-049978824134",
   "metadata": {},
   "source": [
    "### Exercise 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7c226cfa-9aef-4a72-a2f2-c309f2ba0783",
   "metadata": {},
   "outputs": [],
   "source": [
    "## ORDER ITEMS CRUD\n",
    "# Create\n",
    "def add_order_item(order_id, product_id, quantity):\n",
    "    new_order_item = OrderItem(order_id=order_id, product_id=product_id, quantity=quantity)\n",
    "    session.add(new_order_item)\n",
    "    session.commit()\n",
    "\n",
    "# Read\n",
    "def get_order_item(order_item_id):\n",
    "    return session.query(OrderItem).filter(OrderItem.id == order_item_id).first()\n",
    "\n",
    "# Update\n",
    "def update_order_item(order_item_id, order_id=None, product_id=None, quantity=None):\n",
    "    order_item = session.query(OrderItem).filter(OrderItem.id == order_item_id).first()\n",
    "    if order_item:\n",
    "        if order_id:\n",
    "            order_item.order_id = order_id\n",
    "        if product_id:\n",
    "            order_item.product_id = product_id\n",
    "        if quantity:\n",
    "            order_item.quantity = quantity\n",
    "        session.commit()\n",
    "\n",
    "# Delete\n",
    "def delete_order_item(order_item_id):\n",
    "    order_item = session.query(OrderItem).filter(OrderItem.id == order_item_id).first()\n",
    "    if order_item:\n",
    "        session.delete(order_item)\n",
    "        session.commit()"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}