Get started with python and postgres

import psycopg2
from psycopg2.extras import RealDictCursor
from datetime import date
connection = psycopg2.connect(user = "root",
                              password = "password",
                              host = "localhost",
                              port = "5432",
                              database = "database")
cursor = connection.cursor(cursor_factory=RealDictCursor)
cursor.execute("""select * from table where date(created_at)=%s order by id desc limit 100""",(date.today(),))
res=cursor.fetchall()

Explanation :

First you need to import required module.For postgres database access,psycopg2 is latest and recommended modue. RealDictCursor is required to get data in proper dictinary form otherwise data will arrive without column name( hard to guess for wide tables).

Create a connection object with defined connection string. Prepare query. Execute it with a cursor and fetch result from cursor . Fetching and selections will be discussed in later series.

You can start with it. Use the data to apply different types of visualization or draw useful conclusions using python,postgres,pandas and python visualization library

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.