PostgreSQL and PdAdmin Installation

---

Installation

 
sudo apt update
sudo apt install postgresql postgresql-contrib
    

Command Lines

 
sudo -u postgres psql # username and postgresql --> postgres=#
\q # quit
\dt # list table
\d # list table and sequence

### SQL Commands
CREATE TABLE color (
id INT,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id));

### Insert
INSERT INTO color (id, name) VALUES (1,'red');

### Select
SELECT * FROM color;
    

Python Connection

 
pip install psycopg2
### Script
import psycopg2
from psycopg2 import Error

try:
    connection = psycopg2.connect(user="postgres",
                                  password="postgres",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres")
    # database name can be from 'SELECT current_database();'

    cursor = connection.cursor()
    print("PostgreSQL server information")
    print(connection.get_dsn_parameters(), "\n")
    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected to - ", record, "\n")

except (Exception, Error) as error:
    print("Error while connecting to PostgreSQL", error)

### Insert
cursor.execute("INSERT INTO color (id, name) VALUES (2, 'blue')")
connection.commit()

### Select
cursor.execute('SELECT * FROM color;')
record = cursor.fetchall()
print('Result ',record)

### Close
if (connection):
    cursor.close()
    connection.close()
    print("PostgreSQL connection is closed")
    

References

1. An Introduction to Postgres with Python
2. PostgreSQL - PgAdmin 4 - Import csv file using PgAdmin
3. PostgreSQL Python: Create Tables
4. Python: Read a CSV file line by line with or without header
5. Import CSV File Into PostgreSQL Table
6. How To Install and Use PostgreSQL on Ubuntu 18.04
7. Python PostgreSQL Tutorial Using Psycopg2

---