psycopg2 python adapter for PostgreSQL databases

Download and install the latest version of python from Python Website if not already done.

Install psycopg2 using the command: pip install psycopg2

Or to installed pre-compiled binary version, use: pip install psycopg2-binary

Type command pip freeze
Should give an output like:

psycopg2==2.9.2

To connect to PostgreSQL database using psycopg2, we have to import psycopg2, define connection string and then use psycopg2.connect() method. Copy the below code to a python file in your Virtual Environment and run it.

import psycopg2 
s = '' #Your server name 
d = '' #Your database name
u = '' #Your login
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
print(conn)

To run the code, use command python filename.py

On running the code should see the below output if connection successful.

connection object at 0x000001B62D641360; dsn: 'user=username password=xxx dbname=dbname host=hostname', closed: 0

psycopg2 Cursor object represents a database cursor, that we can use to manage data from an SQL query. We can create multiple cursors from same connection. Any changes done to data using one cursor is visible to the other cursor immediately.

To read data from PostgreSQL database, execute the command and use fetchall() method.

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchall():
    print(row)

Insert to table sample below.

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("INSERT INTO TableName (id, name) VALUES (%s, %s)", (someid, 'somename'))
conn.commit()

Update data to table sample below.

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("UPDATE TableName SET name = ? WHERE id = ?", 'somename', someid)
conn.commit()

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("DELETE FROM TableName WHERE id = ?", someid)
conn.commit()

Stored procedure without parameters

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("CALL updateall()")
conn.commit()

Stored procedure with parameters

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("CALL updatebyid(%s, %s)", str(id), name) #convert all params to string
conn.commit()

Function without parameters

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("SELECT getallfunc()")
for row in cursor.fetchall():
    print(row)

Function with parameters

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("SELECT getallbyidfunc(%s)", str(1)) #convert all params to string
for row in cursor.fetchall():
    print(row)

fetchone() returns next row.
fetchall() returns all remaining rows.

import psycopg2
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = psycopg2.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
c = cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchone():
    print(row)
for row in cursor.fetchall():
    print(row)

commit() Commits all transactions since last commit or rollback.
rollback() Rolls back all uncommitted transactions.

Make sure to close connection after use. Use conn.close()