python sqlite3 multiple LEFT JOIN grouped by column

I've tried looking at all the "duplicates" already suggested but cannot figure out the right query for this.

For the sake of simplicity, I've translated my problem into a different one - so I'm not looking for a different way to structure my data, but a way to query it given its existing schema.

The code to replicate the issue as well as the queries I've tried is located here .

Given the following schema

CREATE TABLE PERSONS(id, name, c1, c2);
CREATE TABLE FRIENDS(id, person_id, name, c1, c2);
CREATE TABLE ENEMIES(id, person_id, name, c1, c2);

And some sample data (used sqlite only to print it out - I'm communicating through python in code )

sqlite> select * from PERSONS;

p1|tom|p1c1v|p1c2v
p2|sam|p2c1v|p2c2v
p3|tim|p3c1v|p3c2v

sqlite> select * from FRIENDS;

f1p1|p1|toms friend 1|f1p1c1v|f1p1c2v
f2p1|p1|toms friend 2|f2p1c1v|f2p1c2v
f3p1|p1|toms friend 3|f3p1c1v|f3p1c2v
f4p1|p1|toms friend 4|f4p1c1v|f4p1c2v
f1p2|p2|sams friend 1|f1p2c1v|f1p2c2v
f2p2|p2|sams friend 2|f2p2c1v|f2p2c2v
f3p2|p2|sams friend 3|f3p2c1v|f3p2c2v
f4p2|p2|sams friend 4|f4p2c1v|f4p2c2v

sqlite> select * from ENEMIES;

e1p1|p1|toms enemy 1|e1p1c1v|e1p1c2v
e2p1|p1|toms enemy 2|e2p1c1v|e2p1c2v
e3p1|p1|toms enemy 3|e3p1c1v|e3p1c2v
e4p1|p1|toms enemy 4|e4p1c1v|e4p1c2v
e1p2|p2|sams enemy 1|e1p2c1v|e1p2c2v
e2p2|p2|sams enemy 2|e2p2c1v|e2p2c2v
e3p2|p2|sams enemy 3|e3p2c1v|e3p2c2v
e4p2|p2|sams enemy 4|e4p2c1v|e4p2c2v
e1p3|p3|tims enemy 1|e1p3c1v|e1p3c2v

I want to be able to iterate through the persons table, and for every person, get all the friends and enemies. ( I would like this in one query, as I don't want to make multiple queries to friends and enemies table for every person).

p1 tom p1c1v p1c2v 
(some information about toms friends) together
[
    f1p1 "toms friend 1"
    f2p1 "toms friend 2"
    f3p1 "toms friend 3"
    f4p1 "toms friend 4"
]
(some information about toms enemies) togetner
[
    e1p1 "toms enemy 1"
    e2p1 "toms enemy 2"
    e3p1 "toms enemy 3"
    e4p1 "toms enemy 4"
]

As seen in the code, I've tried these queries through sqlite

SELECT p.id, p.name, f.id, f.person_id, f.name, e.id, e.person_id, e.name
FROM persons as p
LEFT JOIN friends as f on p.id = f.person_id
LEFT JOIN enemies as e on p.id = e.id
GROUP BY p.id

But this query just returns one row for that id, which I dont want, I want all rows for it.

On removing the GROUP BY, I do get many rows, but it has no enemy information.

Essentially I need the following, but as one query (without changes to the existing schema - except adding indexes )

for person_row in "select * from persons"
    friend_rows_for_person = "select * from friends where person_id=person_row.id"
    enemies_rows_for_person = "select * from enemies where person_id=person_row.id"
    # I continue processing this person with friends 
    # and enemies before moving on to the next person