Python3 Connect PostgreSQL With Psycopg2 On Ubuntu

By:   –  Last updated:   –  #psycopg2 ·  #python3 ·  #postgresql ·  #python ·  #ubuntu

Code Theme [Dark]

Content Overview [Hide]

Psycopg2 is an opensource PostgreSQL driver library in Python. It is widely used and actively developed. The current release of Psycopg2 support both Python3 and PostgreSQL9.4. This tutorial will show how to play with PostgreSQL using Psycopg2 on Ubuntu.

1 Install Psycopg2

To install Psycopg2 on Ubuntu, make sure you have installed libpq-dev package:

$ sudo apt-get install libpq-dev

Then there are two ways to install Python3 version Psycopg2 on Ubuntu: by apt or by pip.

1.1 Install Psycopg2 by apt

$ sudo apt-get install python3-Psycopg2

Make sure add 3 suffix to python, otherwise you will install Python2 version Psycopg2 by default.

1.2 Install Psycopg2 by Python pip

First of all you need Python3 version pip:

$ sudo apt-get install python3-pip

This will install Python3 version pip command: pip3. Now you can install Psycopg2 using pip3:

$ sudo pip3 install Psycopg2

Installation done!

2 Connect PostgreSQL

Like all other database drivers, you need a dsn string to connect to database.

dsn = "host={} dbname={} user={} password={}".format(your_db_host, your_db_name, your_user, your_password)

Connect to database and get a connection object:

conn = psycopg2.connect(dsn)

3 Fetch Data Record With Cursor

Get the cursor object using the obtained connection above:

cur = conn.cursor()

Execute query with cursor, for example, query employees from employee table:

query_sql = "SELECT id, name, gender, age, job FROM public.employee"
cur.execute(query_sql)
for row in cur:
    print(row)

Every row in cursor is a list of database record, so you can get column data conveniently:

for row in cur:
    print("name is: " + row[1])
    print("age is: " + row[3])

4 Update Record

Similarly, you can also update database record using cursor.

update_sql = "UPDATE employee SET gender = 'male' WHERE id IN (1,3,5)"
cur.execute(update_sql)
conn.commit()

The only difference and you must remember here is you should call commit to flush the change.

The last thing is to close the connection and cursor.

cur.close()
conn.close()

This will release the cursor and connection resources.