Return name instead of foreign key ID

Question

I’m developing a Python system and wanted to return data from the specific database, only instead of the foreign key id I wanted to return the name of that person.

The creation of tables:

self.cursor.execute("CREATE TABLE IF NOT EXISTS pessoas(id INTEGER PRIMARY KEY AUTOINCREMENT,nome TEXT, cpf TEXT UNIQUE, tipo TEXT, nascimento DATETIME, telefone TEXT)")

self.cursor.execute("CREATE TABLE IF NOT EXISTS doacoes(id INTEGER PRIMARY KEY AUTOINCREMENT, doador_id INTEGER, litros INTEGER, tipo TEXT, FOREIGN KEY(doador_id) REFERENCES pessoas(id))")

Function that returns the data from the donation table:

def consultar_estoque(self):
        estoque = self.cursor.execute("SELECT * from doacoes ORDER BY id DESC")
        self.db.commit()
        return estoque.fetchall()

As I said above, it returns the id, but wanted the name of the person related to that id. For that I would have to make another query? Or is there an easier way to accomplish the relationship?

Answer 1

Hello, there are two ways I know how to solve your problem:

• First Form

def consultar_estoque(self):
    estoque = self.cursor.execute("""SELECT doacoes.nome,
                                     doacoes.litros,
                                     doacoes.tipo,
                                     pessoas.nome FROM doacoes, pessoas WHERE
                                     pessoas.id = doacoes.doadorID
                                     ORDER BY id DESC""")
    self.db.commit()
    return estoque.fetchall()

Here you have returned all table data doacoes and the donor name of the table pessoas.

• Second form

def consultar_estoque(self):
    estoque = self.cursor.execute("""SELECT doacoes.litros,
                                            doacoes.tipo,
                                            doacoes.doador_id,
                                            pessoas.nome FROM doacoes, pessoas INNER JOIN pessoas ON doacoes.doador_id=pessoas.id""")
    self.db.commit()
    return estoque.fetchall()

The two instructions are very similar and I can not explain why to use one or the other and neither the peformance of each of them, unfortunately.