{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e7abd670",
   "metadata": {},
   "source": [
    "# Core"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "650591aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#| default_exp core"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0ff612b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#| export\n",
    "import sqlalchemy\n",
    "\n",
    "from fastcore.utils import *\n",
    "from fastcore.net import urlsave\n",
    "\n",
    "from collections import namedtuple\n",
    "from sqlalchemy import create_engine,text,MetaData,Table,Column,engine,sql\n",
    "from sqlalchemy.sql.base import ReadOnlyColumnCollection\n",
    "from sqlalchemy.engine.base import Connection\n",
    "from sqlalchemy.engine.cursor import CursorResult"
   ]
  },
  {
   "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}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66e767f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#| export\n",
    "def conn_db(connstr, **kwargs):\n",
    "    \"Connect to DB using `url.URL()` params and return filled `MetaData`\"\n",
    "    eng = create_engine(connstr, **kwargs)\n",
    "    conn = eng.connect()\n",
    "    meta = MetaData()\n",
    "    meta.reflect(bind=eng)\n",
    "    meta.bind = eng\n",
    "    meta.conn = conn\n",
    "    return meta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee9fa411",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = conn_db(connstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "758c3772",
   "metadata": {},
   "outputs": [],
   "source": [
    "#| export\n",
    "old_md_dir = MetaData.__dir__\n",
    "old_cc_dir = ReadOnlyColumnCollection.__dir__\n",
    "\n",
    "@patch\n",
    "def __dir__(self:MetaData): return old_md_dir(self) + list(self.tables)\n",
    "\n",
    "@patch\n",
    "def __dir__(self:ReadOnlyColumnCollection): return old_cc_dir(self) + self.keys()\n",
    "\n",
    "def _getattr_(self, n):\n",
    "    if n[0]=='_': raise AttributeError\n",
    "    if n in self.tables: return self.tables[n]\n",
    "    #return super().__getattr__(n)\n",
    "    raise AttributeError\n",
    "MetaData.__getattr__ = _getattr_"
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "id": "e9f98354",
   "metadata": {},
   "outputs": [],
   "source": [
    "#| export\n",
    "@patch\n",
    "def tuples(self:CursorResult, nm='Row'):\n",
    "    \"Get all results as named tuples\"\n",
    "    rs = self.mappings().fetchall()\n",
    "    nt = namedtuple(nm, self.keys())\n",
    "    return [nt(**o) for o in rs]\n",
    "\n",
    "@patch\n",
    "def sql(self:Connection, statement, nm='Row', *args, **kwargs):\n",
    "    \"Execute `statement` string and return results (if any)\"\n",
    "    if isinstance(statement,str): statement=text(statement)\n",
    "    t = self.execute(statement)\n",
    "    return t.tuples()\n",
    "\n",
    "@patch\n",
    "def sql(self:MetaData, statement, *args, **kwargs):\n",
    "    \"Execute `statement` string and return `DataFrame` of results (if any)\"\n",
    "    return self.conn.sql(statement, *args, **kwargs)"
   ]
  },
  {
   "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": "7541b79d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#| export\n",
    "@patch\n",
    "def get(self:Table, where=None, limit=None):\n",
    "    \"Select from table, optionally limited by `where` and `limit` clauses\"\n",
    "    return self.metadata.conn.sql(self.select().where(where).limit(limit))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb6461fb",
   "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 will run 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": "dd1d409d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#| export\n",
    "@patch\n",
    "def close(self:MetaData):\n",
    "    \"Close the connection\"\n",
    "    self.conn.close()"
   ]
  },
  {
   "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
}