Create a SQLAlchemy /Python ORM Model for the following PostgreSQL Tables: CREATE TYPE…
- 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() );
- 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:
-
Composite Type Handling: The
AgreementContentTypeclass represents the PostgreSQL composite type with proper__composite_values__()method for SQLAlchemy integration. -
Array Support: Uses
ARRAY(agreement_content_composite)to handle the PostgreSQL array of composite types. -
Default Values: Properly handles PostgreSQL defaults using
server_defaultfor database-level defaults. -
Timestamps: Uses
DateTime(timezone=True)forTIMESTAMPTZfields with automaticNOW()defaults and update triggers. -
Type Safety: Includes type hints for better development experience.
-
Database Setup: The
create_composite_type()function ensures the PostgreSQL composite type exists before creating tables.
To use this model, make sure to:
- Run
create_composite_type(engine)before creating tables - Install required dependencies:
pip install sqlalchemy psycopg2-binary - Adjust the table name casing if needed (PostgreSQL typically uses lowercase)
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.