Required package

sqlalchemy
psycopg2 #windows
psycopg2-bin #mac

Setup and create session

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base

# Database URL (adjust username/password as needed)
DATABASE_URL = "postgresql://postgres:password@localhost:5432/dbname"

# Set up SQLAlchemy Engine and Base
engine = create_engine(DATABASE_URL[, echo=True]) #echo=True for debugging
Base = declarative_base()

# Create a session factory
Session = sessionmaker(bind=engine)
session = Session()

Tables

# Define a sample model (e.g., User table)
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)
    # phone = Column(String, nullable=True)

# Create tables
Base.metadata.create_all(engine)

Create

new_user = User(name="John Doe", email="[email protected]")
session.add(new_user)
session.commit()
print(f"Added user with ID: {new_user.id}")

Read

# Get user by ID
user = session.query(User).filter_by(id=1).first()
if user:
    print(f"Found user: {user.name}")

# Get all users
users = session.query(User).all()
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")

# Get all users (order by id)
users = session.query(User).order_by(User.id).all()
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")

# Get all users (order by id desc)
users = session.query(User).order_by(User.id.desc()).all()
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")