How to create, insert, update and delete in PostgreSQL Database in python (using psycopg2)

You need to install psycopg2 first as follows

$ pip install psycopg2-binary

Then create a database on your PostgreSQL DB (on PostgreSQL console or on pgAdmin on Windows)

$ CREATE DATABASE pythoneasy_test;

CONNECT AND CREATE TABLE

import psycopg2
connection = psycopg2.connect(database="pythoneasy_test",
                        user="postgres",
                        password="pass123",
                        host="127.0.0.1",
                        port="5432")

print("Connected to  database successfully")
cursor = conn.cursor()
cursor.execute('''CREATE TABLE Company
      (Id INT PRIMARY KEY     NOT NULL,
      Name           TEXT    NOT NULL,
      Age            INT     NOT NULL);''')
print("Table created successfully")

connection.commit()
connection.close()

INSERT

import psycopg2
connection = psycopg2.connect(database="pythoneasy_test",
                        user="postgres",
                        password="pass123",
                        host="127.0.0.1",
                        port="5432")

print("Connected to database successfully")
cursor = connection.cursor()
cursor.execute("INSERT INTO Company (Id, Name, Age) VALUES (1, 'John', 34)")
cursor.execute("INSERT INTO Company (Id, Name, Age) VALUES (2, 'Doe', 56)")
connection.commit()
connection.close()

FETCH

import psycopg2
connection = psycopg2.connect(database="pythoneasy_test",
                        user="postgres",
                        password="pass123",
                        host="127.0.0.1",
                        port="5432")

cursor = connection.cursor()
cursor.execute("SELECT id, name, Age from Company")
rows = cursor.fetchall()
for row in rows:
    print("ID is ", row[0])
    print("Name is ", row[1])
    print("Age is ", row[2])
    print("------------------------")
connection.close()

ID is 1
Name is John
Age is 34
------------------------
ID is 2
Name is Doe
Age is 56
------------------------

UPDATE

import psycopg2
connection = psycopg2.connect(database="pythoneasy_test",
                        user="postgres",
                        password="pass123",
                        host="127.0.0.1",
                        port="5432")

cursor = connection.cursor()
cursor.execute("UPDATE Company set Age = 25 where Id = 2")
connection.commit()

cursor.execute("SELECT id, name, Age from Company")
rows = cursor.fetchall()
for row in rows:
    print("ID is ", row[0])
    print("Name is ", row[1])
    print("Age is ", row[2])
    print("------------------------")
connection.close()

You can see the age value for Doe has been changed to 25

ID is 1
Name is John
Age is 34
------------------------
ID is 2
Name is Doe
Age is 56
------------------------

DELETE RECORD

import psycopg2
connection = psycopg2.connect(database="pythoneasy_test",
                        user="postgres",
                        password="pass123",
                        host="127.0.0.1",
                        port="5432")

cursor = connection.cursor()
cursor.execute("DELETE FROM Company where Id = 2")
connection.commit()

cursor.execute("SELECT id, name, Age from Company")
rows = cursor.fetchall()
for row in rows:
    print("ID is ", row[0])
    print("Name is ", row[1])
    print("Age is ", row[2])
    print("------------------------")
connection.close()

You can see the the record for Doe has been deleted

ID is 1
Name is John
Age is 34
------------------------