How to setup PostgreSQL with Jupyter Notebook

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 for use on python or how to uninstall python if you have any issue on python configuration, You can follow from link.

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

Third step, Install Jupyter Notebook on Windows, MacOS or Linux and try to install $ pip install notebook in terminal and run $ jupyter notebook

Fourth step, Install 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

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

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;

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

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



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

Reference : Connect To PostgreSQL Database Server, Install Jupyter Notebook on Ubuntu

Blog

How to setup PostgreSQL with Jupyter Notebook

Suttipong Kullawattana

Join Our Newsletter. No Spam, Only the good stuff.

Related