Best way to find in database

Question

I am implementing a database in Python for animals. Class to create an Animal object:

class Animal:
    def __init__(self, name: str, animal_type: str, birth_date: str, added_date: str, gender: str,
        description=None, image=None) -> None:

        self.id = str(uuid.uuid4())
        self.name = name.capitalize()
        self.animal_type = animal_type.capitalize()
        self.birth_date = birth_date
        self.added_date = added_date
        self.gender = gender
        self.description = description
        self.image = image

On the database table the only UNIQUE key is id. If I want to get a specific animal from the database all properties of that animal should match. Is there a more efficient way to do it?

When I create a new animal a new UUID will be created, so if I want to find or update a specific animal in the database I can't find the animal using the UUID.

not really sure of what is being asked, but from what I understood & if it helps you can use like query in combination with OR. E.g. where name like "%keyword%" or description like "%keyword%" and so on. — arp, Jul 11 '23 at 20:43
So, let me try to clarify this. My problem is, every time that I have to find an animal in the database I need to match all of the properties of that animal like: name =new_name animal_type = new_animal_type birth_date = new_ birth_date .... I need to match everything because I have repeated animals, it's improbably. I was trying to find a better way to do this. — tbotl, Jul 11 '23 at 20:48
If you want to get a specific animal from the database, why wouldn't you know the id? If you know all the other values, why would you need to look anything up at all? Please give a full concrete example of what you're trying to do. Actual executable code demonstrating your use case; [mre] — MatBailie, Jul 11 '23 at 20:55
You could use a composite key as the ID if you want to be able to search for an ID without knowing it prior — Matt, Jul 11 '23 at 20:56
@Matt Can you please give a sample for that composite key. I am not able to use the UUID for searching because it is random. Imagine that the use wants to delete an animal from the db, I would like to have some specific property where i could search for it in the db and delete the animal. But with UUID I am not able to do it — tbotl, Jul 11 '23 at 22:00
@MatBailie Imagine this: ```animal = Animal( name = "Bella", animal_type="Dog", added_date="2022-01-15", birth_date="2022-03-10", gender="Female" ) ``` I have that animal in the db and now I want to delete it. The UUID.was generate when I create de Animal object so I don't know what is the UUID. Also I can't match by name because I can have more animals with the same name.... — tbotl, Jul 11 '23 at 22:04
So how would you differentiate between animals where all the attributes are the same then? Would you not need the ID? — Matt, Jul 11 '23 at 22:25
@tbotl It is not a programming issue, but a database design issue. The database cannot guess which record you want. You need to provide enough data to uniquely identify an animal. Consider another unique index consisting of (name, animal_type, birth_date). If this is enough - enforce uniqueness when adding a new animal. If this is not enough to be unique, you just need more fields, noone can help this. Of course, in some front-end application, you may filter the records one field at a time until you find only one record that matches. — dr_agon, Jul 11 '23 at 22:26
The solution is to keep a record of the generated id value. Every database has a bespoke means to return the generated value, and most ORMs have ways to interface with that. So, which database are you using, and which ORM are you using? — MatBailie, Jul 12 '23 at 02:26
@MatBailie I am using sqlite with python. I am not using a ORM yet — tbotl, Jul 12 '23 at 15:00
Then whatever you're doing to create new rows in the database should probably use this; https://stackoverflow.com/a/60045014/53341 — MatBailie, Jul 12 '23 at 19:29
@tbotl [X-Y problem](https://en.wikipedia.org/wiki/XY_problem)? What problem is the script and database meant to solve (what's the end goal here)? — user4157124, Jul 18 '23 at 12:03

score 0 · Answer 1 · answered Jul 11 '23 at 22:22

You could use a composite key instead of a UUID as your primary key. If you don't have access to the PK and want to know it without querying for it.

CREATE TABLE course_grades (
    quarter_id INTEGER,
    course_id TEXT,
    student_id INTEGER,
    grade INTEGER,
    PRIMARY KEY(quarter_id, course_id, student_id)
);

SELECT *
FROM course_grades
WHERE quarter_id = 1 
  AND course_id = 3
  AND student_id = 5

Best way to find in database

1 Answers1