Connecting with Python
Out of the many python packages used tow ork with Postgresql database server, we will be using psycopg2 package.
Installing psycopg2
- pip command to install psycopg2
- Works on different operating systems including Windows, MacOS, Linux, and Unix
pip install psycopg2
- Installing specific version:
pip install psycopg2=2.8.6
Accessing PostgresSQL from Python using Psycopg
Creating Connection:
- Importing the package
import psycopg2
- This imports the python package psycopg2.
psycopg2.connect(database="mydb", user="myuser", password="mypass"
host="127.0.0.1", port="5432")
- It is a module API that opens a connection to the PostgreSQL database. If successfully opened, it returns a connection object.
- Thus, the connection command should be included in the try-except block in order to handle the error if the connection is not successfully opened.
- After doing the required work, connection should be closed finally. So we need to close the exception in the finally block.
Below is the code for openning and closing a basic connection object.
import psycopg2
def connectDb(dbname, usrname, pwd, address, portnum):
conn = None
try:
# connect to the PostgreSQL database
conn = psycopg2.connect(database = dbname, user = usrname, \
password = pwd, host = address, port = portnum)
print ("Database connected successfully")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
conn.close() # close the connection
connectDb("mydb", "myuser", "mypass", "127.0.0.1", "5432") # function call
OUTPUT:
Database Connected Successfully
Creating a cursor
connection.cursor()
This routine creates a cursor which will be used throughout the program.
cursor.close()
This method closes the cursor.
Executing the query using cursor
cursor.execute(sql [, optional parameters])
This routine executes an SQL statement. The SQL statement may be parameterized (i.e., placeholders instead of SQL literals). The psycopg2 module supports placeholder using %s sign. For example:
cursor.execute("insert into people values (%s,%s)", (who, age))
cursor.executemany(sql, seq of parameters)
This routine executes an SQL command against all parameter sequences or mappings found in the sequence SQL.
cursor.callproc(procname[, parameters])
This routine executes a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects.
cursor.rowcount
This is a read-only attribute which returns the total number of database rows that have
been modified, inserted, or deleted by the last execute().
Fetching the results of the query
cursor.fetchone()
This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available.
cursor.fetchmany([size=cursor.arraysize])
This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter.
cursor.fetchall()
This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.
Commit to make changed persistent/Rollback to reverse
connection.commit()
This method commits the current transaction. If you do not call this method, anything you did since the last call to commit() is not visible to other database connections.
connection.rollback()
This method rolls back any changes to the database since the last call to commit().
import psycopg2
def createTable():
conn = None
try:
conn = psycopg2.connect(database = "mydb", user = "myuser", \
password = "mypass", host = "127.0.0.1", port = "5432") # connect to the database
cur = conn.cursor() # create a new cursor
cur.execute('''CREATE TABLE EMPLOYEE \
(emp_num INT PRIMARY KEY NOT NULL, \
emp_name VARCHAR(40) NOT NULL, \
department VARCHAR(40) NOT NULL)''') # execute the CREATE TABLE statement
conn.commit() # commit the changes to the database
print ("Table created successfully")
cur.close() # close the cursor
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close() # close the connection
createTable() #function call
OUTPUT: * If the employee table already exists:
relation "employee" already exists.
- If the employee tables does not already exist:
Table created successfully
INSERT QUERY EXECUTION USING PYTHON:
import psycopg2
def insertRecord(num, name, dept):
conn = None
try:
# connect to the PostgreSQL database
conn = psycopg2.connect(database = "mydb", user = "myuser", \
password = "mypass", host = "127.0.0.1", port = "5432")
cur = conn.cursor() # create a new cursor
# execute the INSERT statement
cur.execute("INSERT INTO EMPLOYEE (emp_num, emp_name, department) \
VALUES (%s, %s, %s)", (num, name, dept))
conn.commit() # commit the changes to the database
print ("Total number of rows inserted :", cur.rowcount);
cur.close() # close the cursor
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close() # close the connection
insertRecord(110, 'Bhaskar', 'HR') #function call
OUTPUT: * If a row already exists with emp_num=110:
duplicate key value violates unique constraint "employee_pkey"
DETAIL: Key (emp_num)=(110) already exists.
- If a row with emp_num=110 does not already exist:
Total nnumber of rows inserted : 1
DELETE QUERY EXECUTION USING PYTHON:
import psycopg2
def deleteRecord(num):
conn = None
try:
# connect to the PostgreSQL database
conn = psycopg2.connect(database = "mydb", user = "myuser", \
password = "mypass", host = "127.0.0.1", port = "5432")
cur = conn.cursor() # create a new cursor
# execute the DELETE statement
cur.execute("DELETE FROM EMPLOYEE WHERE emp_num = %s", (num,))
conn.commit() # commit the changes to the database
print ("Total number of rows deleted :", cur.rowcount)
cur.close() # close the cursor
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
conn.close() # close the connection
deleteRecord(110) #function call
OUTPUT: * If a row does not already exist:
Total number of rows deleted : 0
- If a row already exists:
Total number of rows deleted : 1
UPDATE QUERY EXECUTION USING PYTHON:
import psycopg2
def updateRecord(num, dept):
conn = None
try:
# connect to the PostgreSQL database
conn = psycopg2.connect(database = "mydb", user = "myuser", \
password = "mypass", host = "127.0.0.1", port = "5432")
cur = conn.cursor() # create a new cursor
# execute the UPDATE statement
cur.execute("UPDATE EMPLOYEE set department = %s where emp_num = \
%s", (dept, num))
conn.commit() # commit the changes to the database
print ("Total number of rows updated :", cur.rowcount)
cur.close() # close the cursor
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
conn.close() # close the connection
updateRecord(110, "Finance") #function call
OUTPUT: * If a row does not already exist:
Total number of rows updated : 0
- If a row already exists:
Total number of rows updated : 1
SELECT QUERY EXECUTION USING PYTHON:
import psycopg2
def selectAll():
conn = None
try:
# connect to the PostgreSQL database
conn = psycopg2.connect(database = "mydb", user = "myuser", \
password = "mypass", host = "127.0.0.1", port = "5432")
cur = conn.cursor() # create a new cursor
# execute the SELECT statement
cur.execute("SELECT emp_num, emp_name, department FROM EMPLOYEE")
rows = cur.fetchall() # fetches all rows of the query result set
for row in rows:
print ("Employee ID = ", row[0], ", NAME = ", \
row[1], ", DEPARTMENT = ", row[2])
cur.close() # close the cursor
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
conn.close() # close the connection
selectAll() # function call
OUTPUT:
EMPLOYEE ID = 110, NAME = Bhaskar, DEPARTMENT = HR
EMPLOYEE ID = 111, NAME = Ishaan, DEPARTMENT = FINANCE
EMPLOYEE ID = 112, NAME = Jairaj, DEPARTMENT = TECHNOLOGY
EMPLOYEE ID = 113, NAME = Ananya, DEPARTMENT = TECHNOLOGY