Skip to content

Instantly share code, notes, and snippets.

@decisionstats

decisionstats/postgres with sqlalchemy

Created March 9, 2017 15:48
Show Gist options
  • Save decisionstats/eda779bf1122384b1f2d0eba268cd81f to your computer and use it in GitHub Desktop.
Save decisionstats/eda779bf1122384b1f2d0eba268cd81f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgres with sqlalchemy
{
"cells": [
{
"cell_type": "code",
"execution_count": 4,
"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": 2,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Requirement already satisfied: psycopg2 in c:\\users\\dell\\anaconda3\\lib\\site-packages\n"
]
}
],
"source": [
"! pip install psycopg2"
]
},
{
"cell_type": "code",
"execution_count": 5,
"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": 6,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"connection= 'postgresql://{username}:{password}@{server}:5432/{database}'.format(**parameters)"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"postgresql://postgres:root@localhost:5432/ajay\n"
]
}
],
"source": [
"print (connection)\n",
"\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"engine = sa.create_engine(connection, encoding=\"utf-8\")\n",
"\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"insp = sa.inspect(engine)\n"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"['information_schema', 'public']\n"
]
}
],
"source": [
"db_list = insp.get_schema_names()\n",
"print(db_list)\n"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"['sales', 'iris']"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"engine.table_names()"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"data3= pd.read_sql_query('select * from \"sales\" limit 10',con=engine)"
]
},
{
"cell_type": "code",
"execution_count": 14,
"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": 15,
"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",
" <tr>\n",
" <th>5</th>\n",
" <td>10006</td>\n",
" <td>4705</td>\n",
" <td>2015-10-17</td>\n",
" <td>380</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>10007</td>\n",
" <td>4729</td>\n",
" <td>2016-01-02</td>\n",
" <td>469</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>10008</td>\n",
" <td>7715</td>\n",
" <td>2015-09-12</td>\n",
" <td>480</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>10009</td>\n",
" <td>9898</td>\n",
" <td>2015-04-05</td>\n",
" <td>611</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>10010</td>\n",
" <td>5797</td>\n",
" <td>2015-08-13</td>\n",
" <td>959</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\n",
"5 10006 4705 2015-10-17 380\n",
"6 10007 4729 2016-01-02 469\n",
"7 10008 7715 2015-09-12 480\n",
"8 10009 9898 2015-04-05 611\n",
"9 10010 5797 2015-08-13 959"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"data3"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"data5= pd.read_sql_query('select * from \"iris\" limit 10',con=engine)"
]
},
{
"cell_type": "code",
"execution_count": 17,
"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>sepal_length</th>\n",
" <th>sepal_width</th>\n",
" <th>petal_length</th>\n",
" <th>petal_width</th>\n",
" <th>species</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>5.1</td>\n",
" <td>3.5</td>\n",
" <td>1.4</td>\n",
" <td>0.2</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>4.9</td>\n",
" <td>3.0</td>\n",
" <td>1.4</td>\n",
" <td>0.2</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>4.7</td>\n",
" <td>3.2</td>\n",
" <td>1.3</td>\n",
" <td>0.2</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>4.6</td>\n",
" <td>3.1</td>\n",
" <td>1.5</td>\n",
" <td>0.2</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>5.0</td>\n",
" <td>3.6</td>\n",
" <td>1.4</td>\n",
" <td>0.2</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>5.4</td>\n",
" <td>3.9</td>\n",
" <td>1.7</td>\n",
" <td>0.4</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>4.6</td>\n",
" <td>3.4</td>\n",
" <td>1.4</td>\n",
" <td>0.3</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>5.0</td>\n",
" <td>3.4</td>\n",
" <td>1.5</td>\n",
" <td>0.2</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>4.4</td>\n",
" <td>2.9</td>\n",
" <td>1.4</td>\n",
" <td>0.2</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>4.9</td>\n",
" <td>3.1</td>\n",
" <td>1.5</td>\n",
" <td>0.1</td>\n",
" <td>setosa</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" sepal_length sepal_width petal_length petal_width species\n",
"0 5.1 3.5 1.4 0.2 setosa\n",
"1 4.9 3.0 1.4 0.2 setosa\n",
"2 4.7 3.2 1.3 0.2 setosa\n",
"3 4.6 3.1 1.5 0.2 setosa\n",
"4 5.0 3.6 1.4 0.2 setosa\n",
"5 5.4 3.9 1.7 0.4 setosa\n",
"6 4.6 3.4 1.4 0.3 setosa\n",
"7 5.0 3.4 1.5 0.2 setosa\n",
"8 4.4 2.9 1.4 0.2 setosa\n",
"9 4.9 3.1 1.5 0.1 setosa"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"data5"
]
},
{
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment