Interaction Python & PostgreSQL

PostgreSQL est un système de gestion de bases de données relationnelles open source. Nous verrons connecter se connecter à une base de donnée PostgreSQL et effectuer les opérations CRUD.

Prerequisites

Postgres
Pysycopg

1. Install Postgres

Download Postgres's setup here https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

2. Install Pysycopg

$ pip install -q psycopg2-binary

3. Create a Postgres database

Create a new database called algojungle.

Packages

# !pip install -q psycopg2-binary
# !pip install -q pandas

from getpass import getpass

import psycopg2
import pandas as pd

Paths & Config

HOST = 'localhost'
DATABASE = 'algojungle'
USER = 'postgres'

Connection to database

PASSWORD = getpass("Enter Password: ")

conn = psycopg2.connect(host=HOST, 
                        database=DATABASE, 
                        user=USER, 
                        password=PASSWORD)

Enter Password:  ········

# Create a cursor
cur = conn.cursor()

Interaction with database

0. Create a table

sql1 = "DROP TABLE IF EXISTS customers;";

sql2 = """
CREATE TABLE customers
(
    DepartmentID INTEGER PRIMARY KEY NOT NULL,
    Name VARCHAR(100),
    GroupName VARCHAR(100)
);
"""

cur.execute(sql1)
cur.execute(sql2)

conn.commit()

1. CREATE : Insert data

sql3 = """
INSERT INTO customers (DepartmentID, Name, GroupName) 
VALUES (1, 'Engineering', 'Research and Development'),
(2, 'Tool Design', 'Research and Development'),
(3, 'Sales', 'Sales and Marketing'),
(4, 'Marketing', 'Sales and Marketing'),
(5, 'Purchasing', 'Inventory Management'),
(6, 'Research and Development', 'Research and Development'),
(7, 'Production', 'Manufacturing'),
(8, 'Production Control', 'Manufacturing'),
(9, 'Human Resources', 'Executive General and Administration'),
(10, 'Finance', 'Executive General and Administration'),
(11, 'Information Services', 'Executive General and Administration'),
(12, 'Document Control', 'Quality Assurance'),
(13, 'Quality Assurance', 'Quality Assurance'),
(14, 'Facilities and Maintenance', 'Executive General and Administration'),
(15, 'Shipping and Receiving', 'Inventory Management'),
(16, 'Executive', 'Executive General and Administration');
"""

cur.execute(sql3)

conn.commit()

2. READ : Read data

sql4 = "SELECT * FROM customers;"

cur.execute(sql4)
results = pd.DataFrame(cur.fetchall(), 
                       columns=[desc[0] for desc in cur.description])
results

	departmentid	name	groupname
0	1	Engineering	Research and Development
1	2	Tool Design	Research and Development
2	3	Sales	Sales and Marketing
3	4	Marketing	Sales and Marketing
4	5	Purchasing	Inventory Management
5	6	Research and Development	Research and Development
6	7	Production	Manufacturing
7	8	Production Control	Manufacturing
8	9	Human Resources	Executive General and Administration
9	10	Finance	Executive General and Administration
10	11	Information Services	Executive General and Administration
11	12	Document Control	Quality Assurance
12	13	Quality Assurance	Quality Assurance
13	14	Facilities and Maintenance	Executive General and Administration
14	15	Shipping and Receiving	Inventory Management
15	16	Executive	Executive General and Administration

3. UPDATE : Update table

sql5 = """
UPDATE customers 
SET Name = 'Updated Engineering' 
WHERE DepartmentID = 1;
"""

cur.execute(sql5)

conn.commit()

cur.execute(sql4)
results = pd.DataFrame(cur.fetchall(), 
                       columns=[desc[0] for desc in cur.description])
results

	departmentid	name	groupname
0	2	Tool Design	Research and Development
1	3	Sales	Sales and Marketing
2	4	Marketing	Sales and Marketing
3	5	Purchasing	Inventory Management
4	6	Research and Development	Research and Development
5	7	Production	Manufacturing
6	8	Production Control	Manufacturing
7	9	Human Resources	Executive General and Administration
8	10	Finance	Executive General and Administration
9	11	Information Services	Executive General and Administration
10	12	Document Control	Quality Assurance
11	13	Quality Assurance	Quality Assurance
12	14	Facilities and Maintenance	Executive General and Administration
13	15	Shipping and Receiving	Inventory Management
14	16	Executive	Executive General and Administration
15	1	Updated Engineering	Research and Development

4. DELETE : Delete data

sql6 = """
DELETE FROM customers 
WHERE DepartmentID = 1;
"""

cur.execute(sql6)

conn.commit()

cur.execute(sql4)
results = pd.DataFrame(cur.fetchall(), 
                       columns=[desc[0] for desc in cur.description])
results

	departmentid	name	groupname
0	2	Tool Design	Research and Development
1	3	Sales	Sales and Marketing
2	4	Marketing	Sales and Marketing
3	5	Purchasing	Inventory Management
4	6	Research and Development	Research and Development
5	7	Production	Manufacturing
6	8	Production Control	Manufacturing
7	9	Human Resources	Executive General and Administration
8	10	Finance	Executive General and Administration
9	11	Information Services	Executive General and Administration
10	12	Document Control	Quality Assurance
11	13	Quality Assurance	Quality Assurance
12	14	Facilities and Maintenance	Executive General and Administration
13	15	Shipping and Receiving	Inventory Management
14	16	Executive	Executive General and Administration

Close connection

# close the communication with the PostgreSQL
cur.close()
conn.close()
print('Database connection closed.')

Database connection closed.

References

Postgres: https://www.postgresql.org/download/
Pysycopg Docs: https://www.psycopg.org/docs/install.html