How to setup PostgreSQL with Jupyter Notebook
Tags: beginners, postgres, tutorial, python
Published: 2022-10-15 12:27:51

I have to conclusion how to setup PostgreSQL (RDBMS) on development environment with Jupyter Notebook by summary step like this.

**First step**, Install [python 3.9.1] (https://www.python.org/downloads/release/python-391/) for use on python or [how to uninstall python](https://www.educative.io/answers/how-to-uninstall-python) if you have any issue on python configuration, You can follow from link.

**Second step**, Install [homebrew](https://brew.sh/) with `$ /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"`

**Third step**, Install [Jupyter Notebook](https://jupyter.org/install) on Windows, MacOS or Linux and try to install `$ pip install notebook` in terminal and run `$ jupyter notebook`

**Fourth step**, Install [psycopg2](https://pypi.org/project/psycopg2/) and try to install `$ pip install psycopg2-binary` on terminal.

**If you have any install psycopg2 on Ubuntu, please follow step like this.** 
`$ sudo apt-get update`
`$ pip install psycopg2-binary ` and then install 
`$ sudo apt-get install libpq-dev python-dev`
`$ sudo pip install psycopg2`

**Five step**, Install [PostgreSQL](https://www.postgresql.org/download/) 

**Install PostgreSQL on Ubuntu**
`$ sudo apt update`
`$ sudo apt install postgresql postgresql-contrib`
`$ sudo systemctl start postgresql.service`

**_Checking config of pg_hba.conf and postgresql.conf on development mode_**

`$ sudo nano /etc/postgresql/14/main/pg_hba.conf`
        host  all  all 0.0.0.0/0 md5

`$ sudo nano /etc/postgresql/14/main/postgresql.conf`
        listen_addresses = '*'          # what IP address(es) to listen on;

Restart service again with `$ sudo service postgresql restart`

**Six step**, Create role user to super user by loggin on **PgAdmin4** 
go to **Object > Create > Login/Group Role**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kgc6z227l4dj4vllqx78.png)

- Create the **"username"** that was named in the psql.

**_How to create username, password for database on Ubuntu, you can followup like this._**

`$ sudo -u postgres psql`
`$ postgres=# create database mydb;`
`$ postgres=# create user myuser with encrypted password 'mypass';`
`$ postgres=# grant all privileges on database mydb to myuser;`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/elk6myetlhxry1rcv3yr.png)

- Create **password** for user.
- Give it all the rights and save information.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3ae1rqppjt8djxcat7nd.png)

- Try the password immediately in the psql terminal.

**_How to run and testing PostgreSQL on terminal._**

`$ export PGPASSWD=mypass`
`$ psql -h 127.0.0.1 -U myuser mydb`
`$ mydb=> `

**Example**

```
$ mydb=> DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE
$ mydb=> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age INT,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255));
$ CREATE TABLE
$ mydb=> \dt
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+--------
 public | cricketers | table | myuser
(1 rows)
```

**Insert Data**
```
$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Virat', 'Kohli', 30, 'Delhi', 'India');
$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Rohit', 'Sharma', 32, 'Nagpur', 'India');

$ mydb=> SELECT * FROM cricketers;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+----------
 Shikhar    | Dhawan     |  33 | Delhi          | India
 Kumara     | Sangakkara |  41 | Matale         | Srilanka
 Virat      | Kohli      |  30 | Delhi          | India
 Rohit      | Sharma     |  32 | Nagpur         | India
(4 rows)
```

**_How to exit PostgreSQL on Terminal of MacOS or Ubuntu Linux._**

`$ sudo -i -u postgres psql`
`$ postgres=# \q  => to exit`

**write the code below**

```python
import psycopg2
from psycopg2 import Error

try:
    # Connect to an existing database
    connection = psycopg2.connect(user="myuser",
                                  password="mypass",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres")

    # Create a cursor to perform database operations
    cursor = connection.cursor()
    # Print PostgreSQL details
    print("PostgreSQL server information")
    print(connection.get_dsn_parameters(), "\n")
    # Executing a SQL query
    cursor.execute("SELECT version();")
    # Fetch result
    record = cursor.fetchone()
    print("You are connected to - ", record, "\n")

    #Doping EMPLOYEE table if already exists.
    cursor.execute("DROP TABLE IF EXISTS CRICKETERS")

    #Creating table as per requirement
    sql =''' 
    CREATE TABLE CRICKETERS (
    First_Name VARCHAR(255),
    Last_Name VARCHAR(255),
    Age INT,
    Place_Of_Birth VARCHAR(255),
    Country VARCHAR(255));
    '''
    cursor.execute(sql)
    print("Table created successfully........")
    connection.commit()
    #Closing the connection
    connection.close()

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

Result of field in **PgAdmin4**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xr6r8tsokdheaqqz53aw.png)

Reference : [Connect To PostgreSQL Database Server](https://www.postgresqltutorial.com/postgresql-python/connect/), [Install Jupyter Notebook on Ubuntu](https://linuxhint.com/install-jupyter-notebook-on-ubuntu-20-04/)