Topics:
1. Download and Install Postgres on macOS
2. Psql Basics
3. Psql - Create Database
4. Psql - Run SQL Script file to create Tables
5. Psql - Run SQL Script file to Insert records to Tables
6. Psycopg2 - Install and setup psycopg2
7. Psycopg2 - Run SQL Script file to create Tables
8. Psycopg2 - Run SQL Script file to Insert records to Tables
9. Psycopg2 - CREATE Table
10. Psycopg2 - INSERT single records
11. Psycopg2 - INSERT multiple records
12. Psycopg2 - SELECT records (using index)
13. Psycopg2 - SELECT records (using attributes)
14. Psycopg2 - SELECT single records (with id)
15. Psycopg2 - SELECT all records
16. Psycopg2 - UPDATE records
17. Psycopg2 - DELETE records
18. Setup Postgres instances and PGAdmin with Docker
19. Use pgAdmin4 to perform basic db operations
20. Import CSV in PostgreSQL
1. Download and Install Postgres on macOS
>>Return to Menu
Download and Install with Postgresql App (Database with no pgAdmin4):
postgresapp.com/downloads.html
Download and Install only pgAdmin4:
pgadmin.org
Download and Install from EDB (Comes with Database and pgAdmin4):
enterprisedb.com/downloads/postgres-postgre..
Configure your $PATH:
sudo mkdir -p /etc/paths.d &&
echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp
Default settings:
Host = localhost
Port = 5432
User = <your system user name> | postgres
Database = <same as user>
Password = none
Connection URL = postgresql://localhost
2. Psql Basics
>>Return to Menu
Get psql version:
psql --version
Connect to Database:
psql -U <name of database>
psql -U postgres
psql -U mydb
\c mydb
List Databases:
\list
\l
List Tables:
\dt
Quit terminal:
\q
3. Psql - Create Database
>>Return to Menu
Create Database:
create database mydata;
4. Psql - Run SQL Script file to create Tables
>>Return to Menu
Script File (schema.sql):
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'mydata'
AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS mydata;
CREATE DATABASE mydata;
\c mydata
DROP TABLE IF EXISTS songs;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id serial PRIMARY KEY,
username varchar(255) UNIQUE NOT NULL,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
created_at date NOT NULL,
updated_at date
);
CREATE TABLE songs (
id serial PRIMARY KEY,
user_id int NOT NULL,
name varchar(255) NOT NULL,
genre varchar(255) NOT NULL,
created_at date NOT NULL,
updated_at date,
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
\q
Run Script File (schema.sql) from psql:
psql -U <user> -f <name-of-sql-file.sql>
psql -U ifeanyiomeata -f schema.sql
5. Psql - Run SQL Script file to Insert records to Tables
>>Return to Menu
Script File (seeder.sql):
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'mydata'
AND pid <> pg_backend_pid();
\c mydata
TRUNCATE TABLE songs;
TRUNCATE TABLE users CASCADE;
INSERT INTO users (username,first_name,last_name,created_at,updated_at)
VALUES ('aschafer','adams','schafer','2022-03-14','2022-03-14'),
('bskail','bob','skail','2022-03-14','2022-03-14'),
('tbraidey','tom','braidey','2022-03-14','2022-03-14'),
('cwhite','corey','white','2022-03-14','2022-03-14'),
('mcommbs','mary','commbs','2022-03-14','2022-03-14');
INSERT INTO songs (user_id,name,genre,created_at,updated_at)
VALUES (1,'go slow','blues','2022-03-14','2022-03-14'),
(2,'shout','rnb','2022-03-14','2022-03-14'),
(2,'say something','pop','2022-03-14','2022-03-14'),
(3,'what is love','jazz','2022-03-14','2022-03-14'),
(1,'hallo','country','2022-03-14','2022-03-14'),
(4,'allevouz','jazz','2022-03-14','2022-03-14'),
(5,'dance out','pop','2022-03-14','2022-03-14'),
(4,'places','country','2022-03-14','2022-03-14');
\q
Run Script File (seeder.sql) from psql:
psql -U <user> -f <name-of-sql-file.sql>
psql -U ifeanyiomeata -f seeder.sql
6. Psycopg2 - Install and setup psycopg2
>>Return to Menu
Install psycopg2
pip3 install psycopg2
OR
pip3 install psycopg2-binary
Get Server and Database details
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234'
)
7. Psycopg2 - Run SQL Script file to create Tables
>>Return to Menu
schema.sql:
DROP TABLE IF EXISTS songs;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id serial PRIMARY KEY,
username varchar(255) UNIQUE NOT NULL,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
created_at date NOT NULL,
updated_at date
);
CREATE TABLE songs (
id serial PRIMARY KEY,
user_id int NOT NULL,
name varchar(255) NOT NULL,
genre varchar(255) NOT NULL,
created_at date NOT NULL,
updated_at date,
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- ALTER TABLE songs ADD CONSTRAINT user_id REFERENCES users(id) ON DELETE CASCADE;
from models.db import PgsqlDb
import psycopg2.extras as psy
class ResetSchema:
@staticmethod
def run():
try:
con = PgsqlDb.connect()
cur = con.cursor(cursor_factory=psy.DictCursor)
with open('schema.sql', 'r') as f:
cur.execute(f.read())
con.commit()
print("Database Tables Created Successfully!")
return True
except Exception as err:
print(err)
finally:
if con is not None:
con.close()
import psycopg2
class PgsqlDb:
@staticmethod
def connect():
con = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
return con
except Exception as err:
print(f'Connection Failed. Error: {err}')
from models.reset import ResetSchema, ResetSeeder
from models.user import User
from models.song import Song
ResetSchema.run()
ResetSeeder.run()
8. Psycopg2 - Run SQL Script file to Insert records to Tables
>>Return to Menu
seeder.sql:
TRUNCATE TABLE songs;
TRUNCATE TABLE users CASCADE;
INSERT INTO users (username,first_name,last_name,created_at,updated_at)
VALUES ('aschafer','Adams','Schafer','2022-03-14','2022-03-14'),
('bskail','Bob','Skail','2022-03-14','2022-03-14'),
('tbraidey','Tom','Braidey','2022-03-14','2022-03-14'),
('cwhite','Corey','White','2022-03-14','2022-03-14'),
('mcommbs','Mary','Commbs','2022-03-14','2022-03-14');
INSERT INTO songs (user_id,name,genre,created_at,updated_at)
VALUES (1,'go slow','blues','2022-03-14','2022-03-14'),
(2,'shout','rnb','2022-03-14','2022-03-14'),
(2,'say something','pop','2022-03-14','2022-03-14'),
(3,'what is love','jazz','2022-03-14','2022-03-14'),
(1,'hallo','country','2022-03-14','2022-03-14'),
(4,'allevouz','jazz','2022-03-14','2022-03-14'),
(5,'dance out','pop','2022-03-14','2022-03-14'),
(4,'places','country','2022-03-14','2022-03-14');
from models.db import PgsqlDb
import psycopg2.extras as psy
class ResetSeeder:
@staticmethod
def run():
try:
con = PgsqlDb.connect()
cur = con.cursor(cursor_factory=psy.DictCursor)
with open('seeder.sql', 'r') as f:
cur.execute(f.read())
con.commit()
print("Default Table Records Added Successfully!")
return True
except Exception as err:
print(err)
finally:
if con is not None:
con.close()
import psycopg2
class PgsqlDb:
@staticmethod
def connect():
con = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
return con
except Exception as err:
print(f'Connection Failed. Error: {err}')
from models.reset import ResetSchema, ResetSeeder
from models.user import User
from models.song import Song
ResetSchema.run()
ResetSeeder.run()
9. Psycopg2 - CREATE Table
>>Return to Menu
import psycopg2
con = None
cur = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'ifeanyiomeata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
cur = con.cursor()
query = '''CREATE TABLE IF NOT EXISTS staff(
id serial PRIMARY KEY,
username varchar(255) UNIQUE NOT NULL,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
created_at date NOT NULL,
updated_at date;'''
cur.execute(query)
con.commit()
except Exception as err:
print(err)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
10. Psycopg2 - INSERT single records
>>Return to Menu
Example:
import psycopg2
con = None
cur = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'ifeanyiomeata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
cur = con.cursor()
query = '''INSERT INTO staff (id,firstname,lastname,age,salary) VALUES (%s,%s,%s,%s,%s);'''
values = (1,'Jordan','Musk',42,4000)
cur.execute(query,values)
con.commit()
except Exception as err:
print(err)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
from models.db import PgsqlDb
from psycopg2 import Error
import psycopg2.extras as psy
import datetime
class User:
id = None
username = None
first_name = None
last_name = None
created_at = None
updated_at = None
@classmethod
def set_user(cls,id=None,username=None,first_name=None,last_name=None,created_at=None,updated_at=None):
cls.id = id
cls.username = username
cls.first_name = first_name
cls.last_name = last_name
cls.created_at = created_at
cls.updated_at = updated_at
@classmethod
def create(cls,**params):
con = PgsqlDb.connect()
try:
created_at = str(datetime.datetime.now())[:11]
cls.set_user(username=params['username'],first_name=params['first_name'],last_name=params['last_name'],created_at=created_at,updated_at=created_at)
with con.cursor(cursor_factory=psy.DictCursor) as cur:
query = '''INSERT INTO users (username,first_name,last_name,created_at,updated_at) VALUES (%s,%s,%s,%s,%s);'''
values = (cls.username.lower(),cls.first_name.lower(),cls.last_name.lower(),cls.created_at,cls.updated_at)
cur.execute(query,values)
con.commit()
print("Record Created Successfully!")
return True
except Error as err:
print("Username has already been taken. Try Again!")
except Exception as err:
print(err)
finally:
if con is not None:
con.close()
import psycopg2
class PgsqlDb:
@staticmethod
def connect():
con = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
return con
except Exception as err:
print(f'Connection Failed. Error: {err}')
from models.reset import ResetSchema, ResetSeeder
from models.user import User
from models.song import Song
#Create user Record
User.create(username='Iomeata',first_name='Ifeanyi',last_name='Omeata')
11. Psycopg2 - INSERT multiple records
>>Return to Menu
import psycopg2
con = None
cur = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'ifeanyiomeata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
cur = con.cursor()
query = '''INSERT INTO staff (id,firstname,lastname,age,salary) VALUES (%s,%s,%s,%s,%s);'''
values = [(2,'Myles','Chark',30,3500),(3,'Abbey','Sheldon',40,7000),(4,'Dan','Cooks',23,3000)]
for record in values:
cur.execute(query,record)
con.commit()
except Exception as err:
print(err)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
12. Psycopg2 - SELECT records (using index)
>>Return to Menu
import psycopg2
con = None
cur = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'ifeanyiomeata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
cur = con.cursor()
query = '''SELECT id,firstname,lastname FROM staff ORDER BY id ASC;'''
cur.execute(query)
fetch = cur.fetchall()
print(fetch)
for record in fetch:
print(f'id: {record[0]}\nfirstname: {record[1]}\nlastname: {record[2]}\n\n')
con.commit()
except Exception as err:
print(err)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
13. Psycopg2 - SELECT records (using attributes)
>>Return to Menu
import psycopg2
import psycopg2.extras as psy
con = None
cur = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'ifeanyiomeata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
cur = con.cursor(cursor_factory=psy.DictCursor)
query = '''SELECT id,firstname,lastname FROM staff ORDER BY id ASC;'''
cur.execute(query)
fetch = cur.fetchall()
print(fetch)
for record in fetch:
print(f'''id: {record['id']}\nfirstname: {record['firstname']}\nlastname: {record['lastname']}\n\n''')
con.commit()
except Exception as err:
print(err)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
14. Psycopg2 - SELECT single records (with id)
>>Return to Menu
from models.db import PgsqlDb
from psycopg2 import Error
import psycopg2.extras as psy
import datetime
class User:
id = None
username = None
first_name = None
last_name = None
created_at = None
updated_at = None
@classmethod
def set_user(cls,id=None,username=None,first_name=None,last_name=None,created_at=None,updated_at=None):
cls.id = id
cls.username = username
cls.first_name = first_name
cls.last_name = last_name
cls.created_at = created_at
cls.updated_at = updated_at
@classmethod
def get(cls,id):
con = PgsqlDb.connect()
try:
cls.set_user(id=id)
with con.cursor(cursor_factory=psy.DictCursor) as cur:
query = '''SELECT * FROM users WHERE id = %s ORDER BY id ASC;'''
value = (cls.id,)
cur.execute(query,value)
fetch = cur.fetchall()
rstr = ''.join(
f'''id: {record['id']}\nusername: {record['username']}\nfirst_name: {record['first_name']}\nlast_name: {record['last_name']}\ncreated_at: {record['created_at']}\nupdated_at: {record['updated_at']}\n\n'''
for record in fetch
)
con.commit()
print(rstr)
return True
except Exception as err:
print(err)
finally:
if con is not None:
con.close()
import psycopg2
class PgsqlDb:
@staticmethod
def connect():
con = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
return con
except Exception as err:
print(f'Connection Failed. Error: {err}')
from models.reset import ResetSchema, ResetSeeder
from models.user import User
from models.song import Song
User.get(2)
15. Psycopg2 - SELECT all records
>>Return to Menu
from models.db import PgsqlDb
from psycopg2 import Error
import psycopg2.extras as psy
import datetime
class User:
id = None
username = None
first_name = None
last_name = None
created_at = None
updated_at = None
@classmethod
def set_user(cls,id=None,username=None,first_name=None,last_name=None,created_at=None,updated_at=None):
cls.id = id
cls.username = username
cls.first_name = first_name
cls.last_name = last_name
cls.created_at = created_at
cls.updated_at = updated_at
@staticmethod
def all():
con = PgsqlDb.connect()
try:
with con.cursor(cursor_factory=psy.DictCursor) as cur:
query = '''SELECT * FROM users ORDER BY id ASC;'''
cur.execute(query)
fetch = cur.fetchall()
rstr = ''.join(
f'''id: {record['id']}\nusername: {record['username']}\nfirst_name: {record['first_name']}\nlast_name: {record['last_name']}\ncreated_at: {record['created_at']}\nupdated_at: {record['updated_at']}\n\n'''
for record in fetch
)
con.commit()
print(rstr)
return True
except (Error,Exception) as err:
print(err)
finally:
if con is not None:
con.close()
import psycopg2
class PgsqlDb:
@staticmethod
def connect():
con = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
return con
except Exception as err:
print(f'Connection Failed. Error: {err}')
from models.reset import ResetSchema, ResetSeeder
from models.user import User
from models.song import Song
User.all()
16. Psycopg2 - UPDATE records
>>Return to Menu
import psycopg2
import psycopg2.extras as psy
con = None
cur = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'ifeanyiomeata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
cur = con.cursor(cursor_factory=psy.DictCursor)
query = '''UPDATE staff SET age = 18 where lastname = %s;'''
#value = ['Musk']
value = ('Musk',)
cur.execute(query,value)
con.commit()
except Exception as err:
print(err)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
from models.db import PgsqlDb
from psycopg2 import Error
import psycopg2.extras as psy
import datetime
class User:
id = None
username = None
first_name = None
last_name = None
created_at = None
updated_at = None
@classmethod
def set_user(cls,id=None,username=None,first_name=None,last_name=None,created_at=None,updated_at=None):
cls.id = id
cls.username = username
cls.first_name = first_name
cls.last_name = last_name
cls.created_at = created_at
cls.updated_at = updated_at
@classmethod
def update(cls,id,**params):
con = PgsqlDb.connect()
try:
updated_at = str(datetime.datetime.now())[:11]
cls.set_user(id=id,username=params['username'],first_name=params['first_name'],last_name=params['last_name'],updated_at=updated_at)
with con.cursor(cursor_factory=psy.DictCursor) as cur:
query = '''UPDATE users SET username=%s,first_name=%s,last_name=%s,updated_at=%s WHERE id =%s''';
values = (cls.username,cls.first_name,cls.last_name,cls.updated_at,cls.id)
cur.execute(query,values)
con.commit()
print("Record Updated Successfully!")
return True
except Exception as err:
print(err)
finally:
if con is not None:
con.close()
import psycopg2
class PgsqlDb:
@staticmethod
def connect():
con = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
return con
except Exception as err:
print(f'Connection Failed. Error: {err}')
from models.reset import ResetSchema, ResetSeeder
from models.user import User
from models.song import Song
#Update user Record
User.update(id=6,username='msean',first_name='michael',last_name='sean')
17. Psycopg2 - DELETE records
>>Return to Menu
import psycopg2
import psycopg2.extras as psy
con = None
cur = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'ifeanyiomeata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
cur = con.cursor(cursor_factory=psy.DictCursor)
query = '''DELETE FROM staff WHERE lastname = %s;'''
#value = ['Musk']
value = ('Sheldon',)
cur.execute(query,value)
con.commit()
except Exception as err:
print(err)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
from models.db import PgsqlDb
from psycopg2 import Error
import psycopg2.extras as psy
import datetime
class User:
id = None
username = None
first_name = None
last_name = None
created_at = None
updated_at = None
@classmethod
def set_user(cls,id=None,username=None,first_name=None,last_name=None,created_at=None,updated_at=None):
cls.id = id
cls.username = username
cls.first_name = first_name
cls.last_name = last_name
cls.created_at = created_at
cls.updated_at = updated_at
@classmethod
def destroy(cls,id):
con = PgsqlDb.connect()
try:
with con.cursor(cursor_factory=psy.DictCursor) as cur:
query = '''DELETE FROM users WHERE id = %s;'''
values = (id,)
cur.execute(query,values)
con.commit()
print("Record Deleted Successfully!")
return True
except Exception as err:
print(err)
finally:
if con is not None:
con.close()
import psycopg2
class PgsqlDb:
@staticmethod
def connect():
con = None
try:
con = psycopg2.connect(
host = 'localhost',
database = 'mydata',
port = 5432,
user = 'ifeanyiomeata',
password = '1234')
return con
except Exception as err:
print(f'Connection Failed. Error: {err}')
from models.reset import ResetSchema, ResetSeeder
from models.user import User
from models.song import Song
#Delete user Record
User.destroy(6)
18. Setup Postgres instances and PGAdmin with Docker
>>Return to Menu
19. Use pgAdmin4 to perform basic db operations
>>Return to Menu
20. Import CSV in PostgreSQL
>>Return to Menu
#End
Hope you enjoyed this! :) Follow me for more contents...
Get in Touch:
ifeanyiomeata.com
contact@ifeanyiomeata.com
Youtube: youtube.com/c/IfeanyiOmeata
Linkedin: linkedin.com/in/omeatai
Twitter: twitter.com/iomeata
Github: github.com/omeatai
Stackoverflow: stackoverflow.com/users/2689166/omeatai
Hashnode: hashnode.com/@omeatai
Medium: medium.com/@omeatai
© 2022
: