{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL SERVER ODBC CONNECTION WITH PYTHON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "import pandas\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = pyodbc.connect('DRIVER={SQL Server};SERVER=ip_address;DATABASE=database;UID=username;PWD=password')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_server = create_engine('mssql+pyodbc://username:password@ip_address:1433/database?driver=SQL+Server+Native+Client+11.0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='SELECT * FROM SALES_2016_2019'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "satis_data = pandas.read_sql(sql,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pandas.DataFrame(satis_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "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>YEAR</th>\n",
       "      <th>PERIOD</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>HALF</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>TL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>8325.000000</td>\n",
       "      <td>8325.000000</td>\n",
       "      <td>8325.000000</td>\n",
       "      <td>8325.00000</td>\n",
       "      <td>8325.000000</td>\n",
       "      <td>8325.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2017.004565</td>\n",
       "      <td>201706.959159</td>\n",
       "      <td>6.502703</td>\n",
       "      <td>1.50030</td>\n",
       "      <td>2156.509309</td>\n",
       "      <td>16138.632971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.814470</td>\n",
       "      <td>81.479015</td>\n",
       "      <td>3.430773</td>\n",
       "      <td>0.50003</td>\n",
       "      <td>4142.297068</td>\n",
       "      <td>27792.027252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2016.000000</td>\n",
       "      <td>201601.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>-1250.000000</td>\n",
       "      <td>-40080.240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2016.000000</td>\n",
       "      <td>201610.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>2243.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2017.000000</td>\n",
       "      <td>201707.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>2.00000</td>\n",
       "      <td>550.000000</td>\n",
       "      <td>6528.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>201803.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>2.00000</td>\n",
       "      <td>2160.000000</td>\n",
       "      <td>17945.440000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>201812.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>2.00000</td>\n",
       "      <td>54325.000000</td>\n",
       "      <td>506224.070000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              YEAR         PERIOD        MONTH        HALF      QUANTITY  \\\n",
       "count  8325.000000    8325.000000  8325.000000  8325.00000   8325.000000   \n",
       "mean   2017.004565  201706.959159     6.502703     1.50030   2156.509309   \n",
       "std       0.814470      81.479015     3.430773     0.50003   4142.297068   \n",
       "min    2016.000000  201601.000000     1.000000     1.00000  -1250.000000   \n",
       "25%    2016.000000  201610.000000     4.000000     1.00000    150.000000   \n",
       "50%    2017.000000  201707.000000     7.000000     2.00000    550.000000   \n",
       "75%    2018.000000  201803.000000     9.000000     2.00000   2160.000000   \n",
       "max    2018.000000  201812.000000    12.000000     2.00000  54325.000000   \n",
       "\n",
       "                  TL  \n",
       "count    8325.000000  \n",
       "mean    16138.632971  \n",
       "std     27792.027252  \n",
       "min    -40080.240000  \n",
       "25%      2243.180000  \n",
       "50%      6528.000000  \n",
       "75%     17945.440000  \n",
       "max    506224.070000  "
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql('SALES_FINAL',sql_server,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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
