with engine.connect() as conn:
    df = pd.read_sql("""
        SELECT TOP 100
               s.PROCHI,
               s.PRESCRIBED_DATE,
               s.SCAN_REF_NO,
               s.line_no,
               i.Approved_Name,
               i.formatted_BNF_Code,
               i.Strength,
               i.Formulation_code
        FROM Project_12345..Prescribing_Scripts s
        INNER JOIN Project_12345..Prescribing_Scripts_Prescribing_Item i
            ON s.hic_drugID = i.hic_drugID
        ORDER BY NEWID()
    """, conn)

df.head()

df <- dbGetQuery(conn, "
    SELECT TOP 100
           s.PROCHI,
           s.PRESCRIBED_DATE,
           s.SCAN_REF_NO,
           s.line_no,
           i.Approved_Name,
           i.formatted_BNF_Code,
           i.Strength,
           i.Formulation_code
    FROM Project_12345..Prescribing_Scripts s
    INNER JOIN Project_12345..Prescribing_Scripts_Prescribing_Item i
        ON s.hic_drugID = i.hic_drugID
    ORDER BY NEWID()
")

head(df)