ConnectionSQL_localhost.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connect to MSSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-06T09:56:22.036079Z",
     "start_time": "2019-05-06T09:56:21.975159Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pyodbc \n",
    "conn = pyodbc.connect('Driver={SQL Server Native Client 11.0};'\n",
    "                      'Server=localhost;'\n",
    "                      'Database=data_modeling;'\n",
    "                      'Trusted_Connection=yes;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-06T09:56:22.902349Z",
     "start_time": "2019-05-06T09:56:22.885876Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyodbc.Cursor at 0x238208da4e0>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute('SELECT * FROM data_modeling.dbo.dim_marketing')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-06T10:05:04.014184Z",
     "start_time": "2019-05-06T10:05:03.749570Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "churn\n",
      "dim_marketing\n",
      "sysdiagrams\n",
      "titanic\n",
      "trace_xe_action_map\n",
      "trace_xe_event_map\n",
      "CHECK_CONSTRAINTS\n",
      "VIEW_TABLE_USAGE\n",
      "VIEWS\n",
      "all_columns\n",
      "all_objects\n",
      "all_parameters\n",
      "all_sql_modules\n",
      "all_views\n",
      "allocation_units\n",
      "assemblies\n",
      "assembly_files\n",
      "assembly_modules\n",
      "assembly_references\n",
      "assembly_types\n",
      "asymmetric_keys\n",
      "availability_databases_cluster\n",
      "xml_schema_namespaces\n",
      "xml_schema_types\n",
      "xml_schema_wildcard_namespaces\n",
      "xml_schema_wildcards\n"
     ]
    }
   ],
   "source": [
    "for row in cursor.tables():\n",
    "    print (row.table_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-06T09:56:24.249685Z",
     "start_time": "2019-05-06T09:56:23.681692Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fetching tables from DB into Pandas df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-06T09:54:55.610808Z",
     "start_time": "2019-05-06T09:54:55.512936Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(\"SELECT * FROM data_modeling.dbo.dim_marketing;\", con= conn )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-06T09:54:56.432292Z",
     "start_time": "2019-05-06T09:54:56.410351Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CompanyId</th>\n",
       "      <th>CompanyShort</th>\n",
       "      <th>Company</th>\n",
       "      <th>PlanDate</th>\n",
       "      <th>ChannelPlanning</th>\n",
       "      <th>Channel</th>\n",
       "      <th>Country</th>\n",
       "      <th>PlanRevenueBeforeReturnsIntern</th>\n",
       "      <th>PlanRevenueAfterReturnsIntern</th>\n",
       "      <th>PlanRevenueAfterReturnsExtern</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [CompanyId, CompanyShort, Company, PlanDate, ChannelPlanning, Channel, Country, PlanRevenueBeforeReturnsIntern, PlanRevenueAfterReturnsIntern, PlanRevenueAfterReturnsExtern]\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-04T12:40:23.034567Z",
     "start_time": "2019-05-04T12:40:22.992175Z"
    }
   },
   "outputs": [],
   "source": [
    "churn = pd.read_csv(\"/Users/Churn_Modelling.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-04T13:09:20.947041Z",
     "start_time": "2019-05-04T13:09:20.939518Z"
    }
   },
   "outputs": [],
   "source": [
    "df = churn.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-04T13:09:21.408688Z",
     "start_time": "2019-05-04T13:09:21.402447Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100, 14)"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-04T13:09:21.941957Z",
     "start_time": "2019-05-04T13:09:21.936020Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, select\n",
    "from six.moves import urllib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-04T13:09:22.270003Z",
     "start_time": "2019-05-04T13:09:22.262941Z"
    }
   },
   "outputs": [],
   "source": [
    "params = urllib.parse.quote_plus(\"DRIVER={SQL Server Native Client 11.0};SERVER=localhost;DATABASE=data_modeling;Trusted_Connection=yes\")\n",
    "engine = create_engine(\"mssql+pyodbc:///?odbc_connect=%s\" % params) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-04T13:09:22.591591Z",
     "start_time": "2019-05-04T13:09:22.583546Z"
    }
   },
   "source": [
    "# Transfering dataframe to SQL server "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Step 1: Create table in SQL server with exact column names and column type \n",
    "Step 2: Refresh SQL database and match the structure of DB table and"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-04T13:09:24.694691Z",
     "start_time": "2019-05-04T13:09:22.944587Z"
    }
   },
   "outputs": [],
   "source": [
    "engine.connect() \n",
    "# suppose df is the data-frame that we want to insert in database\n",
    "df.to_sql(name='churn',con=engine, index=False, if_exists='replace')"
   ]
  }
 ],
 "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.6.5"
  },
  "nbTranslate": {
   "displayLangs": [
    "*"
   ],
   "hotkey": "alt-t",
   "langInMainMenu": true,
   "sourceLang": "en",
   "targetLang": "fr",
   "useGoogleTranslate": true
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}