Connect from python pandas to a postgresql database and pull data.

pandas.postgres.py

import psycopg2 as pg
import pandas.io.sql as psql
 
# get connected to the database
connection = pg.connect("dbname=mydatabase user=postgres")
 
dataframe = psql.frame_query("SELECT * FROM <tablename>", connection)

import psycopg2 as pg
import pandas.io.sql as psql

get connected to the database . This works for newer versions of pandas ..... .from_query has been depricated

connection = pg.connect("dbname=mydatabase user=postgres")

dataframe = psql.read_sql("SELECT * FROM ", connection)

If you would like to pass the table name dynamically, you can use from psycopg2.extensions import AsIs and then pass the value as table name using AsIs function

Thanks Frank, helpful snippet

# how to get pandas data from postgree sql using python 
import psycopg2 as pg
import pandas.io.sql as psql
import pandas as pd
connection = pg.connect("host='102.153.103.22' dbname=dbtest user=admin password='passwords'")
#dataframe = psql.DataFrame("SELECT * FROM category", connection)
df = pd.read_sql_query('select * from category',con=connection)
print(df)

out in good pandas format:
id version description name
0 3410 0 alcohol alcohol
1 3411 0 animals animals
2 3412 0 beauty beauty
3 3413 0 berries berries

#exelent worked with 
#pandas | 0.23.1 | 0.23.1
#psycopg2 | 2.7.5 | 2.7.5

maybe this article can help
https://www.datacamp.com/community/tutorials/tutorial-postgresql-python