Connect or Access postgresql with python

In this Section we will learn how to connect or Access postgresql with python.

Step 1: install psycopg2

Inorder to access PostgreSQL databases using Python, you must install a package named psycopg2.

Connect or Access postrgresql with python

Step 2: Establish connection with credentials

The following code sample demonstrates how to use the pg module to connect to a PostgreSQL database. Replace user_name with the PostgreSQL database username, p@$$word with the database user’s password, and name_of_db with the database name.

import psycopg2
import sys

try:
conn = psycopg2.connect("dbname='name_of_db' user='user_name' host='localhost' password='p@$$word'")
except:
print "I am unable to connect to the database"

cur = conn.cursor()

''' sql statement is run within execute()'''
cur.execute("select * from table1 limit 10")
records = cur.fetchall()
records

conn.close()
  • When you have a Connectionobject associated with a database, you can create a Cursor The Cursor object enables you to run the execute() method
  • In execute() method we have to run raw SQL statements
  • fetchall() method gets the output of all the sql queries. In our example fetchall() methods is copied to the records object
  • Finally, the close()method closes the connection to the database

 

Connect or Access postgresql with python                                                                                                           Connect or Access postgresql with python

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.

    View all posts