Python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the various modules required to make a simple Snowflake connection from Python\n",
    "import snowflake.connector\n",
    "from snowflake.connector.converter_null import SnowflakeNoConverterToPython\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Modify this cell to include information about your demo account\n",
    "ACCOUNT = 'xxxx'\n",
    "USER = 'xxxx'\n",
    "PASSWORD = 'xxxx'\n",
    "\n",
    "con = snowflake.connector.connect(\n",
    "  user=USER,\n",
    "  password=PASSWORD,\n",
    "  account=ACCOUNT\n",
    "    ,converter_class=SnowflakeNoConverterToPython\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a variable called sql and specify a query that it will store\n",
    "sql = \"select * from sales.public.customer limit 10000\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<snowflake.connector.cursor.SnowflakeCursor at 0x7f689ce3e5f8>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Specify the virtual warehouse and role we want to use\n",
    "con.cursor().execute(\"USE WAREHOUSE xxxx\")\n",
    "con.cursor().execute(\"USE role xxxx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute the query using the Python connector\n",
    "#%%time\n",
    "res = con.cursor().execute(sql).fetchall()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10000 entries, 0 to 9999\n",
      "Data columns (total 8 columns):\n",
      "C_CUSTKEY       10000 non-null object\n",
      "C_NAME          10000 non-null object\n",
      "C_ADDRESS       10000 non-null object\n",
      "C_NATIONKEY     10000 non-null object\n",
      "C_PHONE         10000 non-null object\n",
      "C_ACCTBAL       10000 non-null object\n",
      "C_MKTSEGMENT    10000 non-null object\n",
      "C_COMMENT       10000 non-null object\n",
      "dtypes: object(8)\n",
      "memory usage: 625.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# Run that same query, but this time use the read_sql method\n",
    "# in the Pandas data frame object\n",
    "#%%time\n",
    "df = pd.read_sql(sql, con)\n",
    "df.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>C_CUSTKEY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_MKTSEGMENT</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>AUTOMOBILE</td>\n",
       "      <td>2043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>BUILDING</td>\n",
       "      <td>1938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>FURNITURE</td>\n",
       "      <td>2060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>HOUSEHOLD</td>\n",
       "      <td>1989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>MACHINERY</td>\n",
       "      <td>1970</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              C_CUSTKEY\n",
       "C_MKTSEGMENT           \n",
       "AUTOMOBILE         2043\n",
       "BUILDING           1938\n",
       "FURNITURE          2060\n",
       "HOUSEHOLD          1989\n",
       "MACHINERY          1970"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get a count of distinct customers by market segment\n",
    "df.groupby('C_MKTSEGMENT')[['C_CUSTKEY']].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "C_CUSTKEY       False\n",
       "C_NAME          False\n",
       "C_ADDRESS       False\n",
       "C_NATIONKEY     False\n",
       "C_PHONE         False\n",
       "C_ACCTBAL       False\n",
       "C_MKTSEGMENT    False\n",
       "C_COMMENT       False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check to see if any of the columns have null values\n",
    "pd.isnull(df).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('5050001', 'Customer#005050001', 'h2Q2lfB QpSuOt32ZDV7S8RsTKgedv4w9s9wa', '18', '28-680-716-8960', '4571.61', 'AUTOMOBILE', 'e thinly bold ideas. carefully final pinto beans cajole across')\n"
     ]
    }
   ],
   "source": [
    "print (res[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AUTOMOBILE has occured 1974 times\n",
      "BUILDING has occured 1964 times\n",
      "MACHINERY has occured 1989 times\n",
      "HOUSEHOLD has occured 2025 times\n",
      "FURNITURE has occured 2048 times\n"
     ]
    }
   ],
   "source": [
    "unique_cust_key = []\n",
    "z = []\n",
    "for x in res:\n",
    "    z.append((x[0],x[6]))\n",
    "\n",
    "for x in z:\n",
    "    if x not in unique_cust_key:\n",
    "        unique_cust_key.append(x)\n",
    "    \n",
    "# initailize a null list \n",
    "unique_list = []\n",
    "\n",
    "# traverse for all elements \n",
    "for x in unique_cust_key:\n",
    "    # check if exists in unique_list or not \n",
    "    if x[1] not in unique_list:\n",
    "        unique_list.append(x[1])\n",
    "       \n",
    "def countX(lst, x):\n",
    "    count = 0\n",
    "    for y in lst:\n",
    "        if (y[1] == x):\n",
    "            count = count + 1\n",
    "    return count\n",
    "\n",
    "for a in unique_list:\n",
    "    print('{} has occured {} times'.format(a, countX(unique_cust_key, a))) \n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}