I am querying a postgresql database with the psycopg2 python module.
I wrote a sql query with a “INNER JOIN” statement. It it supposed to get all the technologies related to an asset that I create. The database shemes are:

Assets table :

Indexes:
    "assets_pkey" PRIMARY KEY, btree (asset_id)
Foreign-key constraints:
    "assets_asset_owner_fkey" FOREIGN KEY (asset_owner) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "assets_tech" CONSTRAINT "assets_tech_asset_id_fkey" FOREIGN KEY (asset_id) REFERENCES assets(asset_id)
    TABLE "scans" CONSTRAINT "scans_asset_id_fkey" FOREIGN KEY (asset_id) REFERENCES assets(asset_id)
    TABLE "scans" CONSTRAINT "scans_asset_id_fkey1" FOREIGN KEY (asset_id) REFERENCES assets(asset_id) ON DELETE CASCADE

Assets_tech table

Indexes:
    "assets_tech_pkey" PRIMARY KEY, btree (ta_relation_id)
Foreign-key constraints:
    "assets_tech_asset_id_fkey" FOREIGN KEY (asset_id) REFERENCES assets(asset_id)
    "assets_tech_tech_id_fkey" FOREIGN KEY (tech_id) REFERENCES technologies(id)

and technologies table :

Indexes:
    "technologies_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "assets_tech" CONSTRAINT "assets_tech_tech_id_fkey" FOREIGN KEY (tech_id) REFERENCES technologies(id)

The application is composed of a root class called “db” and the init function and the execution function are :

class db:
    def __init__(self,con, cur, sheme={}, table=None, id_name="id"):
        self.con = con
        self.cur = cur

        if table == None:
            assert False, "You must provide a table name"
        else:
            self.TABLE = table

        self.SHEME = sheme
        self.ID_NAME = id_name

    def execute(self, query, commit=False, fetchone=False, fetchall=False, reset=False):
        try:
            logging.info(repr(query))
            self.cur.execute(query)
            if commit:
                self.con.commit()
            if fetchone:
                return self.cur.fetchone()
            if fetchall:
                r = self.cur.fetchall()
                return r
        except psycopg2.ProgrammingError as exc:
            logging.exception("Querry error !")
            self.con.rollback() 
            return None
        except Exception as exp:
            logging.exception("Querry error !")
            time.sleep(2)
            self.con = psycopg2.connect(DBConfig.DB_URI)
            self.cur = self.con.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            if not reset:
                # here, we re-execute the command after it failed. reset variable is set to True so it doesnt enter in a endless recurtion
                self.execute(query, commit, fetchone, fetchall, True)
            return None

A child class is supposed to handle the technologies related part :

class Technologies(db):
    def __init__(self, con, cur):
        super().__init__(con, cur, {"id":int, "name": str, "color_hex": str}, "technologies", "id")
    
    def new_row(self, data):
        if super().exists({'name': data['name']}):
            pass
        else:
            data['color_hex'] = f"#{secrets.token_hex(3)}"
            return super().new_row(data)
        
    def get_assets_tech(self, asset_id):
        result = super().execute(f"SELECT * FROM technologies INNER JOIN assets_tech ON assets_tech.tech_id=technologies.id  WHERE assets_tech.asset_id={int(asset_id)} ;", fetchall=True)
        print(f"res : {result}")
        return result if result != None else []

I execute the get_assets_tech with the asset_id as 40.
The query that I want to execute is :

SELECT * FROM technologies INNER JOIN assets_tech ON assets_tech.tech_id=technologies.id  WHERE assets_tech.asset_id=40 ;

Which works correctly :

root=> SELECT * FROM technologies INNER JOIN assets_tech ON assets_tech.tech_id=technologies.id  WHERE assets_tech.asset_id=40 ;
 id |  name  | color_hex | tech_id | asset_id | ta_relation_id 
----+--------+-----------+---------+----------+----------------
  1 | flask  | #56261c   |       1 |       40 |              1
  2 | Python | #f35ed4   |       2 |       40 |              2
(2 rows)

But the problem is that psycopg2 doesnt return the same result than the cli and the function prints :

res : []

Has anyone the same problem or a solution to propose ?

I tried to execute a sql query that works in the psql cli but to with psycopg2.