Connecting Python with a RDBMS (Postgres)

In [1]:
import psycopg2
import pandas as pd
import sqlalchemy as sa
import time
import seaborn as sns
import re
In [2]:
!pip install psycopg2
Requirement already satisfied: psycopg2 in c:\users\dell\anaconda3\lib\site-packages
In [3]:
parameters = { 
               'username': 'postgres', 
               'password': 'root',
               'server':   'localhost',
               'database': 'datascience'
             }
In [4]:
connection= 'postgresql://{username}:{password}@{server}:5432/{database}'.format(**parameters)
In [5]:
print (connection)
postgresql://postgres:root@localhost:5432/datascience
In [6]:
engine = sa.create_engine(connection, encoding="utf-8")
In [7]:
insp = sa.inspect(engine)
db_list = insp.get_schema_names()
print(db_list)
['information_schema', 'public']
In [8]:
print(insp)
<sqlalchemy.dialects.postgresql.base.PGInspector object at 0x000000000B166748>
In [9]:
engine.table_names()
Out[9]:
['weather', 'cities', 'sales', 'sales23', 'sales77']
In [11]:
data3= pd.read_sql_query('select * from "sales77" limit 10',con=engine)
In [12]:
print(data3)
   customer_id  sales        date  product_id
0        10001    845  2017-07-05         407
1        10002   2370  2015-11-18         617
2        10003   5744  2017-02-10         928
3        10004   3230  2017-01-13         500
4        10005   8781  2017-04-23         555
5        10006   2544  2016-01-14         316
6        10007    217  2015-06-21         187
7        10008    306  2015-02-27         880
8        10009   8720  2015-09-03         900
9        10010   6137  2016-06-08         110

https://github.com/decisionstats/pythonfordatascience/blob/master/Python%2Bwith%2BPostgres%20(3).ipynb

Author: Ajay Ohri

http://about.me/ajayohri

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s