$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
