{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Database Integration \n",
    "\n",
    "`featuretools_sql` is an add-on library that supports automatic `EntitySet` creation from a relational database.\n",
    "\n",
    "Currently, `featuretools_sql` is compatible with the following systems:\n",
    "\n",
    "* `MySQL` \n",
    "* `PostgreSQL`\n",
    "* `Snowflake`\n",
    "\n",
    "The `DBConnector` object exposed by the `featuretools_sql` library provides the interface to connecting to the DBMS."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Installing featuretools_sql \n",
    "\n",
    "Install with pip\n",
    "\n",
    "```\n",
    "python -m pip install \"featuretools[sql]\" \n",
    "``` "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connecting to your database instance \n",
    "\n",
    "Depending on your choice of DBMS, you may have to provide different pieces of information to the `DBConnector` object.\n",
    "\n",
    "If you want to connect to a `MySQL` instance, you must pass the string `\"mysql\"` into the `system_name` argument.\n",
    "\n",
    "If you want to connect to a `PostgreSQL` instance, you must pass the string `\"postgresql\"` into the `system_name` argument.\n",
    "\n",
    "If you want to connect to a `Snowflake` instance, you must pass the string `\"snowflake\"` into the `system_name` argument.\n",
    "\n",
    "Here is an example call to the constructor of the object, connecting to a `PostgreSQL` database:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python \n",
    "from featuretools_sql.connector import DBConnector\n",
    "\n",
    "connector_object = DBConnector(\n",
    "    system_name=\"postgresql\",\n",
    "    user=\"postgres\",\n",
    "    host=\"localhost\",\n",
    "    port=\"5432\",\n",
    "    database=\"postgres\",\n",
    "    schema=\"public\",\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the choice of RDBMS does affect the required arguments -- for example, if you were connecting to a `MySQL` instance, you would not need a `schema` argument.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting to an EntitySet \n",
    "\n",
    "You can call the `get_entityset` method to instruct the `DBConnector` object to build an EntitySet. \n",
    "\n",
    "This method will loop through all the tables in the database and copy them into dataframes. Then it will populate the relationships data structure. It will finally pass those two arguments into the EntitySet constructor in Featuretools, and return the object."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python \n",
    "es = connector_object.get_entityset()\n",
    "``` \n",
    "\n",
    "Optionally, you can pass in table names to the `select_only` parameter if you only want to include a subset of the tables in the database. \n",
    "\n",
    "```python \n",
    "es = connector_object.get_entityset(select_only=[\"Products\", \"Transactions\"])\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examining the EntitySet's member data \n",
    "\n",
    "You can examine the member data of the `DBConnector` object to ensure that it imported data correctly.\n",
    "\n",
    "To access the dataframes it imported, access the `.dataframes` attribute. To access the relationships data structure, access the `.relationships` attribute.\n",
    "\n",
    "If you would like to visualize the EntitySet as a graph, you can call `es.plot()`. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calling DFS \n",
    "\n",
    "The EntitySet object is ready to be passed into Featuretools's `DFS` algorithm! Read more about `DFS` [here]([https://featuretools.alteryx.com/en/stable/getting_started/afe.html#running-dfs). "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.12 64-bit ('venv_x86')",
   "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.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "3f6b062a214ec48d1657976024d6bc68979519d14a33afb6ad033fc2e4189514"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}