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
Top comments (0)