PostgreSQL With Python

 

Setup For Work

---

step 1: Install pgadmin4 in your system (Note* please remember root password)
 
step 2: create a database in the name 'test-all'

step 3: create a virtual environment (if you don't know then visit here)

step 4: pip install psycopg2

step 5: Now create a python script and try with the below code

Code

---

Connecting Database:

import psycopg2

def connect_my_db(db, user, password, host):

    try:

        connection = psycopg2.connect(

        database=db, user = user, 

        password = password, host = host, 

        port = "5432")

        print("Database Connected")
       
        return connection

    except Exception as e:

        print("Unable to connect DB")

connection  = connect_my_db('test-all', 'postgres', '12345', '127.0.0.1')

Create Table :

import psycopg2

import db_connect

connection = db_connect.connect_my_db('test-all', 'postgres', '12345', '127.0.0.1')

cursor_object = connection.cursor()

query = ''' CREATE TABLE DemoTable (

    id SERIAL PRIMARY KEY,

    firstname Text,

    lastname Text,

    phone CHAR(10)

); '''

try:

    cursor_object.execute(query)

    print("Table created successfully")

except Exception as e:

    print(e)

connection.commit()

connection.close()

Insert Data Into Table :

import psycopg2

import db_connect

connection = db_connect.connect_my_db('test-all', 'postgres', '12345', '127.0.0.1')

cursor_object = connection.cursor()

query = '''INSERT INTO DemoTable 

(id, firstname, lastname, phone) 

VALUES (1, 'Kuntal', 'Samanta', 7407501378);'''

try:

    cursor_object.execute(query)

    print("Record created successfully")

except Exception as e:

    print(e)

connection.commit()

connection.close()

Fetching Data From Table:

import psycopg2

import db_connect

connection = db_connect.connect_my_db('test-all', 'postgres', '12345', '127.0.0.1')

cursor_object = connection.cursor()

query = '''SELECT * FROM DemoTable'''

try:

    cursor_object.execute(query)

except Exception as e:

    print(e)

else:

    rows = cursor_object.fetchall()

    for i in rows:

        print("Your Name is : {0} {1}".format(i[1], i[2]))

Screenshot :

Enjoy Database Tutorial 😉

Quiz Level 1

Quiz Level 2

Request Me

Click Here For More