import psycopg2
import pandas as pd
import sqlalchemy as sa
import time
import seaborn as sns
import re
parameters = {
'username': 'postgres',
'password': 'root',
'server': 'localhost',
'database': 'ajay'
}
connection= 'postgresql://{username}:{password}@{server}:5432/{database}'.format(**parameters)
# The database connection
print (connection)
postgresql://postgres:root@localhost:5432/ajay
engine = sa.create_engine(connection_string, encoding="utf-8")
insp = sa.inspect(engine)
db_list = insp.get_schema_names()
print(db_list)
['information_schema', 'public']
dir(engine)
['__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_connection_cls', '_echo', '_execute_clauseelement', '_execute_compiled', '_execute_default', '_execution_options', '_has_events', '_optional_conn_ctx_manager', '_run_visitor', '_should_log_debug', '_should_log_info', '_trans_ctx', '_wrap_pool_connect', 'begin', 'connect', 'contextual_connect', 'create', 'dialect', 'dispatch', 'dispose', 'driver', 'drop', 'echo', 'engine', 'execute', 'execution_options', 'has_table', 'logger', 'logging_name', 'name', 'pool', 'raw_connection', 'run_callable', 'scalar', 'table_names', 'transaction', 'update_execution_options', 'url']
engine.table_names()
['sales']
data3= pd.read_sql_query('select * from "sales" limit 10',con=engine)
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
data3.head()
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 |