In [3]:
import psycopg2
import pandas as pd
import sqlalchemy as sa
import time
import seaborn as sns
import re

In [36]:
! pip install pandasql


Requirement already satisfied: pandasql in c:\users\dell\anaconda3\lib\site-packages
Requirement already satisfied: pandas in c:\users\dell\anaconda3\lib\site-packages (from pandasql)
Requirement already satisfied: sqlalchemy in c:\users\dell\anaconda3\lib\site-packages (from pandasql)
Requirement already satisfied: numpy in c:\users\dell\anaconda3\lib\site-packages (from pandasql)
Requirement already satisfied: python-dateutil>=2 in c:\users\dell\anaconda3\lib\site-packages (from pandas->pandasql)
Requirement already satisfied: pytz>=2011k in c:\users\dell\anaconda3\lib\site-packages (from pandas->pandasql)
Requirement already satisfied: six>=1.5 in c:\users\dell\anaconda3\lib\site-packages (from python-dateutil>=2->pandas->pandasql)

In [2]:
! pip install psycopg2


Requirement already satisfied: psycopg2 in c:\users\dell\anaconda3\lib\site-packages

In [4]:
parameters = { 
               'username': 'postgres', 
               'password': 'root',
               'server':   'localhost',
               'database': 'ajay'
             }

In [6]:
connection= 'postgresql://{username}:{password}@{server}:5432/{database}'.format(**parameters)

In [17]:
print (connection)


postgresql://postgres:root@localhost:5432/ajay

In [18]:
engine = sa.create_engine(connection, encoding="utf-8")

In [19]:
insp = sa.inspect(engine)

In [10]:
print(insp)


<sqlalchemy.dialects.postgresql.base.PGInspector object at 0x000000000B2E5278>

In [11]:
db_list = insp.get_schema_names()
print(db_list)


['information_schema', 'public']

In [12]:
engine.table_names()


Out[12]:
['iris', 'temp', 'sales']

In [13]:
data3= pd.read_sql_query('select * from "sales" limit 10',con=engine)

In [14]:
data3.info()


<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 4 columns):
customer_id    10 non-null int64
sales          10 non-null int64
date           10 non-null object
product_id     10 non-null int64
dtypes: int64(3), object(1)
memory usage: 400.0+ bytes

In [14]:
data3


Out[14]:
customer_id sales date product_id
0 10001 5230 2017-02-07 524
1 10002 2781 2017-05-12 469
2 10003 2083 2016-12-18 917
3 10004 214 2015-01-19 354
4 10005 9407 2016-09-26 292
5 10006 4705 2015-10-17 380
6 10007 4729 2016-01-02 469
7 10008 7715 2015-09-12 480
8 10009 9898 2015-04-05 611
9 10010 5797 2015-08-13 959

In [29]:
data5= pd.read_sql_query('select * from "sales" limit 20',con=engine)

In [30]:
data5


Out[30]:
customer_id sales date product_id
0 10001 5230 2017-02-07 524
1 10002 2781 2017-05-12 469
2 10003 2083 2016-12-18 917
3 10004 214 2015-01-19 354
4 10005 9407 2016-09-26 292
5 10006 4705 2015-10-17 380
6 10007 4729 2016-01-02 469
7 10008 7715 2015-09-12 480
8 10009 9898 2015-04-05 611
9 10010 5797 2015-08-13 959
10 10011 1283 2016-04-22 950
11 10012 2751 2015-01-01 322
12 10013 4422 2017-07-11 965
13 10014 6235 2015-03-07 783
14 10015 7302 2016-04-06 792
15 10016 6408 2016-10-21 347
16 10017 1880 2015-08-23 187
17 10018 3738 2017-03-12 222
18 10019 900 2015-07-20 236
19 10020 5516 2017-05-10 828

In [23]:
import pandasql as pdsql

In [32]:
str1="select *  from data5 limit 5;"

In [33]:
df11=pdsql.sqldf(str1)

In [34]:
df11


Out[34]:
customer_id sales date product_id
0 10001 5230 2017-02-07 524
1 10002 2781 2017-05-12 469
2 10003 2083 2016-12-18 917
3 10004 214 2015-01-19 354
4 10005 9407 2016-09-26 292

In [35]:
type(data5)


Out[35]:
pandas.core.frame.DataFrame

In [43]:
data5= pd.read_sql_query('select * from "sales" limit 250',con=engine)

In [47]:
data5.head()


Out[47]:
customer_id sales date product_id
0 10001 5230 2017-02-07 524
1 10002 2781 2017-05-12 469
2 10003 2083 2016-12-18 917
3 10004 214 2015-01-19 354
4 10005 9407 2016-09-26 292

In [44]:
str2="select avg(sales)  from data5 ;"

In [45]:
df111=pdsql.sqldf(str2)

In [46]:
df111


Out[46]:
avg(sales)
0 5226.868

In [ ]: