Demo Prompt Anatomy and Best Practices

# app/models/casting.py (original)
from sqlalchemy import Column, Integer, String, Float, Text
from app.db.database import Base

class Casting(Base):
    """SQLAlchemy model for casting data."""
    __tablename__ = "castings"

    id = Column(Integer, primary_key=True, index=True)
    casting_number = Column(Integer, unique=True, index=True, nullable=False)
    name = Column(String, index=True)
    description = Column(Text, nullable=True)
    material = Column(String, nullable=True)
    weight = Column(Float, nullable=True)
    dimensions = Column(String, nullable=True)
    manufacturer = Column(String, nullable=True)
    year_introduced = Column(Integer, nullable=True)

# sample_generic_castings.csv
casting_number,name,description,material,weight,dimensions,manufacturer,year_introduced
1001,"Engine Block","V8 engine block for high-performance applications","Cast Iron",75.5,"24x18x12","ABC Castings",1985
1002,"Cylinder Head","4-cylinder head with improved cooling channels","Aluminum",12.3,"18x12x4","XYZ Manufacturing",1990
1003,"Crankshaft","Forged steel crankshaft for durability","Steel",28.7,"36x6x6","Precision Parts Inc.",1988
1004,"Connecting Rod","High-strength connecting rod for performance engines","Forged Steel",1.2,"8x2x1","Performance Engineering",1992
1005,"Piston","Lightweight piston with thermal barrier coating","Aluminum Alloy",0.8,"4x4x5","Advanced Components",1995
1006,"Camshaft","Performance camshaft with hardened lobes","Hardened Steel",5.6,"24x2x2","Valve Train Specialists",1987
1007,"Intake Manifold","Flow-optimized intake manifold","Aluminum",8.9,"18x12x8","Flow Masters",1993
1008,"Exhaust Manifold","High-flow exhaust manifold","Cast Iron",12.4,"16x10x6","Exhaust Systems Co.",1986
1009,"Valve Cover","Decorative valve cover with improved sealing","Aluminum",3.2,"16x8x3","Custom Components",1991
1010,"Oil Pan","Deep sump oil pan for increased capacity","Stamped Steel",6.7,"20x14x8","Lubrication Systems",1989

# chev-casting.csv (excerpt)
Years,Casting,CID,Low Power,High Power,Main Caps,Comments
1980-85,332817,400,150,180,2,cars
1973-80,331817,400,150,180,2,"car, truck"
1975,355909,262,110,110,2,"car, truck"
1976-85,355909,305,,-,2,A
1975,360851,262,,-,2,Monza
1976-79,361979,305,,-,2,"car, truck"
1978-86,366245,350,,-,,-,car
1982-86,366286,350,,-,4,"Chevrolet, siamese"
1982,366299,350,,-,4,"Chevrolet, aluminium"
1956-67,383810,283,,-,2,-
1967-68,389257,302,,-,2,Z-28
1968-73,391436,307,,-,2,-
1965-67,393288,283,,-,2,"car, truck"
1976-79,460776,305,,-,2,"car, truck"
1976-79,460777,305,,-,2,car
1978-79,460778,305,,-,2,"car, truck"
1979-82,471511,267,,-,2,"car, truck"
1976-85,581671,305,,-,2,A
1985-94,1489363,350,,-,2,-
1979-82,2135412,267,,-,2,-
1973-76,3030817,400,,-,2,"car, truck"
1958-61,3556519,283,,-,2,"car, truck"
1955,3703524,265,195,,-,2,"car, no filter"

# messy_sample_rows.csv (illustrative only)
7,1006,"Camshaft","Performance camshaft with hardened lobes","Hardened Steel",1987
8,1007,"Intake Manifold","Flow-optimized intake manifold","Aluminum"
9,1008,"Exhaust Manifold","High-flow exhaust manifold","Cast Iron",
10,1009,"Valve Cover","Decorative valve cover with improved sealing",1991
11,1010,"Oil Pan","Deep sump oil pan for increased capacity","Stamped",1989

(venv) jeremy@MACSTUDIO CastingLookup % python migrate_database.py --skip-drop
Traceback (most recent call last):
  File ".../sqlalchemy/orm/decl_base.py", line 2142, in _declare
    raise TypeError(...)
TypeError: 'Unnamed: 7' is an invalid keyword argument for Casting

# app/models/casting.py (refactored)
from sqlalchemy import Column, Integer, String, Text
from app.db.database import Base

class Casting(Base):
    """SQLAlchemy model for Chevrolet casting data."""
    __tablename__ = "castings"

    id = Column(Integer, primary_key=True, index=True)
    years = Column(String, index=True, nullable=True)
    casting = Column(String, unique=True, index=True, nullable=False)
    cid = Column(Integer, nullable=True)
    low_power = Column(String, nullable=True)
    high_power = Column(String, nullable=True)
    main_caps = Column(String, nullable=True)
    comments = Column(Text, nullable=True)

# app/schemas/casting.py
from pydantic import BaseModel

class CastingBase(BaseModel):
    years: str | None = None
    casting: str
    cid: int | None = None
    low_power: str | None = None
    high_power: str | None = None
    main_caps: str | None = None
    comments: str | None = None

class CastingInDB(CastingBase):
    id: int
    model_config = {"from_attributes": True}

class Casting(CastingInDB):
    pass

#!/usr/bin/env python3
# reset_db.py (generated by assistant)
"""
Script to reset the database and import the Chevrolet casting data.
This script will:
1. Drop the existing database
2. Create the new tables
3. Import the data from chev-casting.csv
"""

import os
import sys
import pandas as pd

# Add the current directory to the path to allow imports
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from app.db.database import engine, SessionLocal
from app.models import casting as casting_models
from app.models.casting import Casting as CastingModel
from app.utils.import_data import clean_data

def import_data(file_path, db):
    """
    Import data from CSV file, handling duplicates by skipping them.
    """
    df = pd.read_csv(file_path, dtype=str)
    # Drop unnamed columns produced by malformed CSVs
    df = df.loc[:, ~df.columns.str.startswith("Unnamed")]
    for _, row in df.iterrows():
        record = clean_data(row.to_dict())
        # upsert/skip logic here

curl -X 'GET' \
  'http://0.0.0.0:8000/api/castings/366299' \
  -H 'accept: application/json'

{
  "casting": "366299",
  "years": "1982",
  "cid": 350,
  "low_power": null,
  "high_power": null,
  "main_caps": "4",
  "comments": "Chevrolet, aluminium",
  "id": 8
}