Keep an Upsert Window Calm While Streamlit and Docker Layers Continue to Poll

Suggested cover: a vision pipeline where bounding boxes, track ids and database inserts sit close together instead of being hidden behind abstractions.

The first real workload arrives when frames start turning into rows

After the imports and configuration settle down, the script stops introducing itself and begins to work. One function asks the detector for tracked objects, another annotates the frame and another pushes a simplified record into PostgreSQL. None of those steps are exotic in isolation, but together they define the central bargain of the whole system: visual motion must become structured data without losing too much context on the way.

That is why this section benefits from a little extra space around the code. Terms like tracking state, overlay pass and write path become easier to feel once the reader sees that the script is not simply detecting objects. It is assigning identities, estimating movement, formatting a human-facing label and preparing a row suitable for storage.

The hard part is not only running a model. The hard part is deciding what details survive the journey from frame to database table and what details remain local to rendering. Good engineering articles usually become memorable right at that boundary.

Tracking term	Meaning	Why it helps the reader
Tracking state	The in-memory record that lets one frame relate to the next.	It explains how a lightweight speed estimate can exist without a heavier motion model.
Overlay pass	The drawing step that turns machine output into a readable operator frame.	It shows where observability is being created, not just stored.
Write path	The section of code that takes computed facts and makes them durable.	It clarifies where the system stops being visual and starts becoming analytical.

The tracking function opens by defining what counts as an observable object

def process_and_draw(frame, model, track_history, font):
    results = model.track(frame, persist=True, classes=[2, 5, 7],
                          conf=0.3, iou=0.5, imgsz=1280, verbose=False)
    db_rows = []
    if results[0].boxes.id is not None:
        boxes = results[0].boxes.xyxy.int().cpu().numpy()
        ids = results[0].boxes.id.int().cpu().tolist()
        clss = results[0].boxes.cls.int().cpu().tolist()
        centers = results[0].boxes.xywh.cpu().numpy()
        img_pil = Image.fromarray(cv2.cvtColor(frame, cv2.COLOR_BGR2RGB))
        draw = ImageDraw.Draw(img_pil)

The function begins by locking in detection settings and then unpacking just enough geometry to support both rendering and database writes.

That opening tells us something subtle but important: the script is not treating tracking as an opaque library trick. It is immediately reshaping the result into data structures the rest of the file can reason about. This is the kind of moment where a technical article starts to feel solid, because the reader can see the bridge forming between model output and application logic.

The inner loop turns geometry into a compact event record

        for box, track_id, cls, center in zip(boxes, ids, clss, centers):
            x1, y1 = box[0], box[1]
            x_c, y_c = int(center[0]), int(center[1])
            label = model.names[cls].upper()
            speed = round(abs(y_c - track_history.get(track_id, y_c)) * 2.5, 1)
            track_history[track_id] = y_c
            db_rows.append(('Cam_01', label, int(track_id), float(x_c), float(y_c), float(speed)))

This middle slice is where the runtime stops merely observing and starts declaring facts about identity, position and speed.

There is a pleasing economy here. The script does not try to save everything. Instead it selects the camera id, class label, track id, coordinates and a lightweight speed estimate. That restraint matters. Good analytical pipelines often feel substantial not because they store more, but because they store the right small set of facts consistently.

The overlay pass keeps the live frame readable before it returns

            time_str = datetime.datetime.now().strftime("%H:%M:%S")
            display_text = f" {label} ID:{track_id} | {time_str} | {speed} km/h "
            tw, th = draw.textbbox((0, 0), display_text, font=font)[2:]
            draw.rectangle([x1, y1 - th - 10, x1 + tw, y1], fill=(255, 100, 0))
            draw.text((x1, y1 - th - 7), display_text, font=font, fill=(255, 255, 255))
        frame = cv2.cvtColor(np.array(img_pil), cv2.COLOR_RGB2BGR)
    return db_rows, frame

The frame annotation remains close to the structured output, which helps the live operator and the database pipeline keep telling the same story.

That closeness is worth calling out because many articles treat overlay code as purely cosmetic. In real systems it often acts as a trust layer. If the on-screen label, track id and estimated speed look plausible, people become much more willing to believe the database rows that arrive a moment later.

The write path is deliberately plain and repetitive

def push_to_database(cur, conn, data_list):
    """Load: Save data"""
    for row in data_list:
        try:
            cur.execute("""
                INSERT INTO mart.current_traffic 
                (camera_id, vehicle_type, track_id, x_coord, y_coord, avg_speed, count, last_update) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP)
            """, (row[0], row[1], row[2], row[3], row[4], row[5], 1))
            conn.commit()
        except:
            conn.rollback()

The database writer is intentionally literal, which makes the persistence boundary easy to identify even in a busy mixed-purpose file.

Not every operationally important function needs to look clever. Sometimes the most useful thing a write path can do is remain legible enough that a teammate immediately sees what table is being populated, what columns are being trusted and where rollback behavior begins.

• The second `sql` post now covers the real transition from tracked frame data into database-ready rows.
• The code blocks stay medium-sized so the movement from detection to storage remains easy to reconstruct.
• The surrounding text does more explanatory lifting without inventing extra helper functions.

A pipeline starts feeling real the moment a frame stops being just an image and becomes a row someone can query later.