Friday, November 27, 2020

Python Scripting for Databases

$PostgreSQL$ 

 

 #Prerequisites

We need to verify the installed packages . I have installed "psycopg2 2.8.6" using pip. 

The below "pip list" command will list down the details:

C:\Users\welcome>pip list
Package    Version
---------- -------
cx-Oracle  8.0.1
pip        20.2.4
psycopg2   2.8.6
setuptools 28.8.0

We have another way to check all the installed modules using ' help ("module") ' command after connecting to python console.

C:\Users\welcome>python
Python 3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> help('modules');

Please wait a moment while I gather a list of all available modules...

Fetchdata           _weakrefset         gzip                runpy
SELECT ALL          _winapi             hashlib             sched
SELECTMANY          abc                 heapq               secrets
_string             errno               pstats              weakref
_strptime           faulthandler        psycopg2            webbrowser

Enter any module name to get more help.  Or, type "modules spam" to search
for modules whose name or summary contain the string "spam".

>>>

 >>> help('modules psy');

Here is a list of modules whose name or summary contains 'psy'.
If there are any, enter a module name to get more help.

psycopg2 - A Python driver for PostgreSQL
psycopg2._ipaddress - Implementation of the ipaddres-based network types adaptation
psycopg2._json - Implementation of the JSON adaptation objects
psycopg2._lru_cache - LRU cache implementation for Python 2.7
psycopg2._psycopg
psycopg2._range - Implementation of the Range type and adaptation
psycopg2.compat
psycopg2.errorcodes - Error codes for PostgresSQL
psycopg2.errors - Error classes for PostgreSQL error codes
psycopg2.extensions - psycopg extensions to the DBAPI-2.0
psycopg2.extras - Miscellaneous goodies for psycopg2
psycopg2.pool - Connection pooling for psycopg2
psycopg2.sql - SQL composition utility module
psycopg2.tz - tzinfo implementations for psycopg2

>>>
C:\Users\welcome> 


------------------------------------------------------------------------------------------------------------------------------


# creating table

Creating a table  by using connect() method that belongs to connection objects.  Then we call execute method of the cursor object to help us in creating table then we will commit and close the connection.

 

# Script

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()
cur.execute('''CREATE TABLE SCHOLAR
      (ADMISSION INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      COURSE        CHAR(50),
      DEPARTMENT    CHAR(50));''')
print("Table created successfully")

con.commit()
con.close()






 

 

# Inserting data 

We will insert data after connecting to the database by using connection object. Then, we use execute() method to insert single record.

#Script

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3420, 'John', 18, 'Computer Science', 'ICT')");

con.commit()
print("Record inserted successfully")
con.close()



 


 

 




# Insert multiple rows 

We use execute() method to insert multiple records then commit and close method() to finish the tasks.


 import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3419, 'Abel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3421, 'Joel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3422, 'Antony', 19, 'Electrical Engineering', 'Engineering')");
cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3423, 'Alice', 18, 'Information Technology', 'ICT')");

con.commit()
print("Records inserted successfully")
con.close()






 


 

# Retrieving Data (  Query the record from table  )

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()
cur.execute("SELECT admission, name, age, course, department from SCHOLAR")
rows = cur.fetchall()

for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")

print("Operation done successfully")
con.close()


 

 

# Updating Data

After connecting to the database using connect() method then by using cursor() method we will execute update statement with input.

 

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("UPDATE SCHOLAR set AGE = 20 where ADMISSION = 3420")
con.commit()
print("Total updated rows:", cur.rowcount)

cur.execute("SELECT admission, age, name, course, department from SCHOLAR")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[2])
    print("DEPARTMENT =", row[3], "\n")

print("Operation done successfully")
con.close()


 


We have updated the John Age is 20 for 3420 admission.




##  Deleting Rows
To delete a record from a Postgres database table, we should first establish a connection to the database server. Secondly, a cursor object should be created by calling the cursor() function then, run the DELETE statement to perform the deletion.

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("DELETE from SCHOLAR where ADMISSION=3420;")
con.commit()
print("Total deleted rows:", cur.rowcount)

cur.execute("SELECT admission, name, age, course, department from SCHOLAR")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")

print("Deletion successful")
con.close()

SCRIPT OUTPUT

Python Scripting for Databases

$PostgreSQL$     #Prerequisites We need to verify the installed packages . I have installed "psycopg2 2.8.6" using pip.  The below...