Skip to content
The Chat Archive
claude-sonnet-4-20250514-thinking-32k· Anthropic LMArena Human Preference 140K

Create a SQLAlchemy /Python ORM Model for the following PostgreSQL Tables: CREATE TYPE…

  1. User

    Create a SQLAlchemy /Python ORM Model for the following PostgreSQL Tables: CREATE TYPE agreement_content_type AS ( primary_key TEXT, secondary_key TEXT, #Optional, so it can be NULL value TEXT );

    Create the main table for agreements

    CREATE TABLE Agreement_Templates ( Agreement_Template_id VARCHAR(50) PRIMARY KEY, title TEXT NOT NULL, version TEXT NOT NULL DEFAULT '1.0.0', #-- Automatically generated once, so we set a default html_template_path TEXT, html_template_name TEXT, css_template_path TEXT, css_template_name TEXT, pdf_output_path TEXT, pdf_output_name TEXT, agreement_content agreement_content_type[] NOT NULL DEFAULT '{}', date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(), date_updated TIMESTAMPTZ NOT NULL DEFAULT NOW() );

  2. Assistant

    Here's the SQLAlchemy ORM model for your PostgreSQL tables:

    from sqlalchemy import Column, String, Text, DateTime, func, text
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.dialects.postgresql import ARRAY, composite
    from sqlalchemy.types import UserDefinedType
    from sqlalchemy.sql import sqltypes
    from datetime import datetime
    from typing import Optional, List
    
    Base = declarative_base()
    
    # Define the composite type class
    class AgreementContentType:
        def __init__(self, primary_key: str, secondary_key: Optional[str] = None, value: str = ""):
            self.primary_key = primary_key
            self.secondary_key = secondary_key
            self.value = value
        
        def __composite_values__(self):
            return self.primary_key, self.secondary_key, self.value
        
        def __repr__(self):
            return f"AgreementContentType(primary_key='{self.primary_key}', secondary_key='{self.secondary_key}', value='{self.value}')"
        
        def __eq__(self, other):
            return isinstance(other, AgreementContentType) and \
                   other.primary_key == self.primary_key and \
                   other.secondary_key == self.secondary_key and \
                   other.value == self.value
        
        def __ne__(self, other):
            return not self.__eq__(other)
    
    # Define the SQLAlchemy composite type
    agreement_content_composite = composite(
        AgreementContentType,
        Column('primary_key', Text),
        Column('secondary_key', Text),
        Column('value', Text)
    )
    
    class AgreementTemplate(Base):
        __tablename__ = 'agreement_templates'
        
        agreement_template_id = Column(
            'agreement_template_id', 
            String(50), 
            primary_key=True
        )
        
        title = Column(Text, nullable=False)
        
        version = Column(
            Text, 
            nullable=False, 
            server_default=text("'1.0.0'")
        )
        
        html_template_path = Column(Text)
        html_template_name = Column(Text)
        css_template_path = Column(Text)
        css_template_name = Column(Text)
        pdf_output_path = Column(Text)
        pdf_output_name = Column(Text)
        
        # Array of composite types
        agreement_content = Column(
            ARRAY(agreement_content_composite),
            nullable=False,
            server_default=text("'{}'")
        )
        
        date_created = Column(
            DateTime(timezone=True),
            nullable=False,
            server_default=func.now()
        )
        
        date_updated = Column(
            DateTime(timezone=True),
            nullable=False,
            server_default=func.now(),
            onupdate=func.now()
        )
    
        def __repr__(self):
            return f"<AgreementTemplate(id='{self.agreement_template_id}', title='{self.title}', version='{self.version}')>"
    
    # Database setup function to create the composite type
    def create_composite_type(engine):
        """
        Create the composite type in the database before creating tables.
        This should be run once before creating the tables.
        """
        with engine.connect() as conn:
            conn.execute(text("""
                DO $$ BEGIN
                    CREATE TYPE agreement_content_type AS (
                        primary_key     TEXT,
                        secondary_key   TEXT,
                        value           TEXT
                    );
                EXCEPTION
                    WHEN duplicate_object THEN null;
                END $$;
            """))
            conn.commit()
    
    # Example usage:
    """
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    
    # Create engine
    engine = create_engine('postgresql://username:password@localhost/database_name')
    
    # Create the composite type first
    create_composite_type(engine)
    
    # Create tables
    Base.metadata.create_all(engine)
    
    # Create session
    Session = sessionmaker(bind=engine)
    session = Session()
    
    # Example of creating a new agreement template
    new_template = AgreementTemplate(
        agreement_template_id="TEMP001",
        title="Sample Agreement",
        agreement_content=[
            AgreementContentType("party1", "name", "John Doe"),
            AgreementContentType("party2", "name", "Jane Smith"),
            AgreementContentType("terms", None, "Standard terms and conditions")
        ]
    )
    
    session.add(new_template)
    session.commit()
    """
    

    Key points about this implementation:

    1. Composite Type Handling: The AgreementContentType class represents the PostgreSQL composite type with proper __composite_values__() method for SQLAlchemy integration.

    2. Array Support: Uses ARRAY(agreement_content_composite) to handle the PostgreSQL array of composite types.

    3. Default Values: Properly handles PostgreSQL defaults using server_default for database-level defaults.

    4. Timestamps: Uses DateTime(timezone=True) for TIMESTAMPTZ fields with automatic NOW() defaults and update triggers.

    5. Type Safety: Includes type hints for better development experience.

    6. Database Setup: The create_composite_type() function ensures the PostgreSQL composite type exists before creating tables.

    To use this model, make sure to:

    1. Run create_composite_type(engine) before creating tables
    2. Install required dependencies: pip install sqlalchemy psycopg2-binary
    3. Adjust the table name casing if needed (PostgreSQL typically uses lowercase)

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.