Python MySQL: Unexpected response on split query

I'm getting different responses depending on wether I run the query in an "all in one" query or if I run them separately.

I can't for the life of me figure out what I'm doing wrong.

Code:

import mysql.connector


def all_in_one():
    try:
        conn = mysql.connector.connect(
            host="localhost", user="fanw", password="secret", database="fanw"
        )

        cursor = conn.cursor()

        query = """
            CREATE TEMPORARY TABLE IF NOT EXISTS temp_pairs AS

            SELECT
                fd.nid,
                fd.title
            FROM
                node__field_card_display cl
            JOIN 
                node_field_data fd ON cl.entity_id = fd.nid AND cl.langcode = fd.langcode
            WHERE
                cl.field_card_display_target_id = 2049
                AND cl.langcode = 'en'
                AND cl.bundle = 'person';

            SELECT
                t.entity_id
            FROM
                node__body AS t
            JOIN
                temp_pairs AS tp ON BINARY t.body_value LIKE CONCAT('%', tp.title, '%') 
                AND NOT (t.body_value LIKE CONCAT('%', tp.nid, '">', tp.title, '<', '%'))
            WHERE
                t.body_format <> 'plain_text'
                AND t.entity_id <> tp.nid;
        """
        for each in cursor.execute(query, multi=True):
            result = each.fetchall()
            print(len(result))

        conn.commit()

    except Exception as e:
        # Handle any errors and potentially roll back the transaction
        conn.rollback()
        print(f"Error: {str(e)}")

    finally:
        cursor.close()
        conn.close()


def separate():
    try:
        conn = mysql.connector.connect(
            host="localhost", user="fanw", password="secret", database="fanw"
        )
        cursor = conn.cursor()

        query = """
            SELECT
                fd.nid,
                fd.title
            FROM
                node__field_card_display cl
            JOIN 
                node_field_data fd ON cl.entity_id = fd.nid AND cl.langcode = fd.langcode
            WHERE
                cl.field_card_display_target_id = 2049
                AND cl.langcode = 'en'
                AND cl.bundle = 'person';
        """
        cursor.execute(query)
        result = cursor.fetchall()
        people = []
        for person in result:
            nid, name = person
            people.append((nid, name))

        cursor.execute(
            "CREATE TEMPORARY TABLE IF NOT EXISTS temp_pairs (nid INT(10) unsigned, title VARCHAR(255));"
        )
        cursor.executemany(
            "INSERT INTO temp_pairs (nid, title) VALUES (%s, %s);", people
        )

        query = f"""
            SELECT
                t.entity_id
            FROM
                node__body AS t
            JOIN
                temp_pairs AS tp ON BINARY t.body_value LIKE CONCAT('%', tp.title, '%') 
                AND NOT (t.body_value LIKE CONCAT('%', tp.nid, '">', tp.title, '<', '%'))
            WHERE
                t.body_format <> 'plain_text'
                AND t.entity_id <> tp.nid;
        """
        cursor.execute(query)
        result = cursor.fetchall()
        print(len(result))

        conn.commit()

    except Exception as e:
        # Handle any errors and potentially roll back the transaction
        conn.rollback()
        print(f"Error: {str(e)}")

    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    print("All in one: ")
    all_in_one()

    print("Separate:")
    separate()
    print("DONE!")

Running the code gives:

❯ python3 migrate.py                     
All in one: 
0
96
Separate:
0
DONE!

I would expect the "separate" query to also give 96

I've tried both mysql-connector-python==8.3.0 and PyMySQL==1.1.0.

I've run the "all in one" query in both the mysql CLI and using DBeaver and both gives me 96

I'm running this on a MacBook Pro M1, but given the "all in one" works, I don't think that is relevant.