SQL Alchemy

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import pandas as pd\n",
    "import sqlalchemy as sa\n",
    "import time\n",
    "import seaborn as sns\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "parameters = { \n",
    "               'username': 'postgres', \n",
    "               'password': 'root',\n",
    "               'server':   'localhost',\n",
    "               'database': 'ajay'\n",
    "             }\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "connection= 'postgresql://{username}:{password}@{server}:5432/{database}'.format(**parameters)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://postgres:root@localhost:5432/ajay\n"
     ]
    }
   ],
   "source": [
    "# The database connection \n",
    "print (connection)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "engine = sa.create_engine(connection_string, encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['information_schema', 'public']\n"
     ]
    }
   ],
   "source": [
    "insp = sa.inspect(engine)\n",
    "db_list = insp.get_schema_names()\n",
    "print(db_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['__class__',\n",
       " '__delattr__',\n",
       " '__dict__',\n",
       " '__dir__',\n",
       " '__doc__',\n",
       " '__eq__',\n",
       " '__format__',\n",
       " '__ge__',\n",
       " '__getattribute__',\n",
       " '__gt__',\n",
       " '__hash__',\n",
       " '__init__',\n",
       " '__le__',\n",
       " '__lt__',\n",
       " '__module__',\n",
       " '__ne__',\n",
       " '__new__',\n",
       " '__reduce__',\n",
       " '__reduce_ex__',\n",
       " '__repr__',\n",
       " '__setattr__',\n",
       " '__sizeof__',\n",
       " '__str__',\n",
       " '__subclasshook__',\n",
       " '__weakref__',\n",
       " '_connection_cls',\n",
       " '_echo',\n",
       " '_execute_clauseelement',\n",
       " '_execute_compiled',\n",
       " '_execute_default',\n",
       " '_execution_options',\n",
       " '_has_events',\n",
       " '_optional_conn_ctx_manager',\n",
       " '_run_visitor',\n",
       " '_should_log_debug',\n",
       " '_should_log_info',\n",
       " '_trans_ctx',\n",
       " '_wrap_pool_connect',\n",
       " 'begin',\n",
       " 'connect',\n",
       " 'contextual_connect',\n",
       " 'create',\n",
       " 'dialect',\n",
       " 'dispatch',\n",
       " 'dispose',\n",
       " 'driver',\n",
       " 'drop',\n",
       " 'echo',\n",
       " 'engine',\n",
       " 'execute',\n",
       " 'execution_options',\n",
       " 'has_table',\n",
       " 'logger',\n",
       " 'logging_name',\n",
       " 'name',\n",
       " 'pool',\n",
       " 'raw_connection',\n",
       " 'run_callable',\n",
       " 'scalar',\n",
       " 'table_names',\n",
       " 'transaction',\n",
       " 'update_execution_options',\n",
       " 'url']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['sales']"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    " data3= pd.read_sql_query('select * from \"sales\" limit 10',con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 4 columns):\n",
      "customer_id    10 non-null int64\n",
      "sales          10 non-null int64\n",
      "date           10 non-null object\n",
      "product_id     10 non-null int64\n",
      "dtypes: int64(3), object(1)\n",
      "memory usage: 400.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "data3.info() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>sales</th>\n",
       "      <th>date</th>\n",
       "      <th>product_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>5230</td>\n",
       "      <td>2017-02-07</td>\n",
       "      <td>524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002</td>\n",
       "      <td>2781</td>\n",
       "      <td>2017-05-12</td>\n",
       "      <td>469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10003</td>\n",
       "      <td>2083</td>\n",
       "      <td>2016-12-18</td>\n",
       "      <td>917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10004</td>\n",
       "      <td>214</td>\n",
       "      <td>2015-01-19</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10005</td>\n",
       "      <td>9407</td>\n",
       "      <td>2016-09-26</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  sales        date  product_id\n",
       "0        10001   5230  2017-02-07         524\n",
       "1        10002   2781  2017-05-12         469\n",
       "2        10003   2083  2016-12-18         917\n",
       "3        10004    214  2015-01-19         354\n",
       "4        10005   9407  2016-09-26         292"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}