{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e7abd670",
   "metadata": {},
   "source": [
    "# fastsql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "271382a7",
   "metadata": {},
   "source": [
    "A bit of extra usability for sqlalchemy v2."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62a2c695",
   "metadata": {},
   "source": [
    "## Install"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a54514bf",
   "metadata": {},
   "source": [
    "```\n",
    "pip install fastsql\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64464f9c",
   "metadata": {},
   "source": [
    "## Example"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3832283d",
   "metadata": {},
   "source": [
    "This little library provides a single function, `conn_db`, which returns an extended sqlalchemy `MetaData` object which you can use for accessing your database with full dynamic autocomplete support in Jupyter and IPython. So it's particularly useful for interactive development.\n",
    "\n",
    "We demonstrate it here using the 'chinook' sample database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "017b12d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from fastsql import conn_db\n",
    "from fastcore.utils import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ced7dace",
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite'\n",
    "path = Path('chinook.sqlite')\n",
    "if not path.exists(): urlsave(url, path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "861c6838",
   "metadata": {},
   "outputs": [],
   "source": [
    "connstr = f\"sqlite:///{path}\"\n",
    "db = conn_db(connstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6da1df75",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Album Artist Customer Employee Genre Invoice InvoiceLine Track MediaType Playlist PlaylistTrack'"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "' '.join(db.tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3bf2d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "a = db.Album"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "142520a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Column('AlbumId', INTEGER(), table=<Album>, primary_key=True, nullable=False),\n",
       " Column('Title', NVARCHAR(length=160), table=<Album>, nullable=False),\n",
       " Column('ArtistId', INTEGER(), ForeignKey('Artist.ArtistId'), table=<Album>, nullable=False)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(a.c)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e34d86e3",
   "metadata": {},
   "source": [
    "Rows are returned as named tuples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48b52184",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(AlbumId=1, Title='For Those About To Rock We Salute You')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rs = db.sql('select AlbumId,Title from Album')\n",
    "rs[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b351d1b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(AlbumId=1, Title='For Those About To Rock We Salute You', ArtistId=1),\n",
       " Row(AlbumId=7, Title='Facelift', ArtistId=5),\n",
       " Row(AlbumId=60, Title='Fireball', ArtistId=58),\n",
       " Row(AlbumId=88, Title='Faceless', ArtistId=87),\n",
       " Row(AlbumId=99, Title='Fear Of The Dark', ArtistId=90)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a.get(a.c.Title.startswith('F'), limit=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a3153b9",
   "metadata": {},
   "source": [
    "This is the query that ran behind the scenes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f4caf6c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"Album\".\"AlbumId\", \"Album\".\"Title\", \"Album\".\"ArtistId\" \n",
      "FROM \"Album\" \n",
      "WHERE (\"Album\".\"Title\" LIKE :Title_1 || '%')\n",
      " LIMIT :param_1\n"
     ]
    }
   ],
   "source": [
    "print(a.select().where(a.c.Title.startswith('F')).limit(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1524e42c",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e30614c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}