How to install and connect Python3 to PostgreSQL

PostgreSQLAndPython.md

PostgreSQL on Ubuntu And Python 3

I used the reference below to make this simple tutorial!

• Install PostgreSQL

• Avoid error password connection

• Doc about psycopg2

• Python code to connect

• Change network access

Install PostgreSQL on Ubuntu

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

Access the user postgres

$ sudo -i -u postgres

Create new user:

(postgres)$ createuser --interactive

Output
Enter name of role to add: udagramaciolidev
Shall the new role be a superuser? (y/n) y

Create new db:

(postgres)$ createdb udagramaciolidev

Add user and access db on Ubuntu (on server, exit user postgres)

$ sudo adduser udagramaciolidev
$ sudo -i -u udagramaciolidev
$ psql

# To exit type \q
udagramaciolidev=>

Change the password to avoid connections issues:

udagramaciolidev=>ALTER USER udagramaciolidev PASSWORD '123456';

Connect PostgreSQL on Python

Create your directory for the project

$ mkdir project
$ cd project

Inside the project folder create the virtual environment:

$ python3 -m venv venv

Activate the virtual environment and install psycopg2-binary:

$ source venv/bin/activate
(venv) $ pip3 install psycopg2-binary

Create a requirements file

(venv) $ pip3 freeze > requirements.txt

Test Python and PostgreSQL on Ubuntu

Create the file connect.py

(venv) $ touch connect.py

Inside then input the code:

import psycopg2
user = "udagramaciolidev"
database = "udagramaciolidev"
host = "localhost"
port = "5432"
password = "123456"
connection = None
cursor = None
try:
    connection = psycopg2.connect(user=user,
                                  password=password,
                                  host=host,
                                  port=port,
                                  database=database)
    cursor = connection.cursor()
    print(connection.get_dsn_parameters(), "\n")

    cursor.execute("select version();")
    record = cursor.fetchone()
    print("Version ", record, "\n")

except(Exception, psycopg2.Error) as error:
    print("Error while connecting to PostgreSQL", error)
finally:
    if connection:
        cursor.close()
        connection.close()
        print("PosgreSQL connnection is closed")

execute then:

(venv) $ python3 connect.py