SQLAlchemy connection to PostgreSQL

Connection

Filename: sql_connection.py

1) Install:

pip install sqlalchemy

2) Import libs:

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
from dotenv import load_dotenv
import os

3) Load environment parameters for connection:

load_dotenv()
host = os.environ['SUPABASE_HOST']
database = os.environ['SUPABASE_DB']
port = os.environ['SUPABASE_PORT']
username = os.environ['SUPABASE_USER']
password = os.environ['SUPABASE_PASSWD']

4) Connection:

engine = create_engine(f'postgresql://{username}:{password}@{host}:{port}/{database}', echo=True)
base = declarative_base()
session_maker = sessionmaker(bind=engine)
session = session_maker()

Table List

Filename: sql_table_list.py

1) Import:

from sql_connection import base                
from sqlalchemy import Column, Integer, String

2) Table properties:

class TableCustomers(base):
    __tablename__ = 'Customers'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)
    grade = Column(String(50))

Create Table

Filename: sql_create_table.py

1) Import:

from sql_connection import base, engine
from sql_table_list import TableCustomers

2) RUN: Create Table

table = TableCustomers
base.metadata.create_all(engine)

Insert data

Filename: sql_insert_data.py

Upload one string:

1) Import:

from sql_connection import session
from sql_table_list import TableCustomers

2) Parameters:

table = TableCustomers

3) Data:

row_0 = table(name="Marry", age=26, grade="R")

4) Add data and commit:

session.add(row)
session.commit()

5) LOG: SQL Format:

INSERT INTO  "Customers" (name, age, grade) 
VALUES      (%(name)s, %(age)s, %(grade)s) 
RETURNING   "Customers".id

Upload more string:

3) Data

row_1 = table(name="Fredy", age=38, grade="C")
row_2 = table(name="Mark", age=42, grade="D")
row_3 = table(name="Jack", age=41, grade="A")
row_4 = table(name="Brain", age=38, grade="B")
row_5 = table(name="Alice", age=41, grade="E")

4) Add data and commit:

session.add_all([row_1, row_2, row_3, row_4, row_5])
session.commit()

5) LOG: SQL Format:

INSERT INTO "Customers" (name, age, grade) 
VALUES  (%(name__0)s, %(age__0)s, %(grade__0)s), 
        (%(name__1)s, %(age__1)s, %(grade__1)s), 
        (%(name__2)s, %(age__2)s, %(grade__2)s), 
        (%(name__3)s, %(age__3)s, %(grade__3)s), 
        (%(name__4)s, %(age__4)s, %(grade__4)s) 
RETURNING "Customers".id