Primary Key - Well sort of...

Phuket2

I have looked around to see if this has been asked before, I could not find anything.
A use case that comes up for me with TinyDB is always checking to see if a record has been already added or not, then either insert or update. It would be great to be able to skip that logic if possible. So that either the update or insert methods could be passed a key param that would not require the logic to determine if a new doc should be added or updated.
To me, this would seem to be the most use case for most people (I am guessing). Maybe this would break the API, not sure. Then maybe another method could be added eg. insert_update.

Maybe this is possible already, i have read the docs and I cant see how to do it. So apologies in advance if it is possible.
It may seem trivial, to do the check first yourself and take the appropriate action. But I find I am doing it all the time. For the sort of data that TinyDB is good at dealing with, it seems to me this would be a common users pattern.
Anyway, i think this would significantly make it easier to use TinyDB. I guess there is the question of insert_multiple, I dont see that as being a big issue, at least for me. But sure its still worth considering.

msiemens

That's an interesting use case. Could you provide some sample code of how exactly your exists check looks like?

Cheers!

Phuket2

msiemens . Hi. Look my example below is a little silly, but i think gets the point across. But my normal use case for TinyDB is more like the func add_game_timer2. I am generally adding/updating/reading records that I consider the records to be unique to one of the dicts attrs.
So if I could for example use add_game_timer func, and pass it a key param, it would just seem to me this would be the wider use case. As I mentioned I am guessing at that assertion. But regardless if the key was an optional kwarg or there was a new method, it would not need to impact the use case were you dont care about duplicates.
I am not sure if my example of how I check for duplicates is the moist effecifient way or not. But it seemed to make sense to use the get method as I am assuming I will only either find one or no records, then if found use doc_id to do the update.

Again I understand its still pretty trivial to do the test. Just seems to me that for when you are doing something very quick, this notation is neat and easy, also easy to remember. Also i realise many other ways to code this functionality under the hood. But given backward compatibility, hopefully something like this approch would avoid that.
I also see you may have a problem having a method called insert having the ability to do updates. Maybe that maybe a good reason for a new method(stub at least) with a more appropriate name. On the other hand, you might be ok with it.

Look, I have gone on a little bit here. The reason I have is because I really like TinyDB. I know the intention is not to be a full fledge relational database. But I do not think its not a stretch to think that often users will store records that they consider unquie by on of their dicts attrs.

from tinydb import TinyDB, Query

_tdb = TinyDB('example.dat')


def add_game_timer(timer_dict):
    doc = _tdb.insert(timer_dict)
    print('doc inserted, doc_id', doc)


def add_game_timer2(timer_dict):
    tbl = _tdb.table('Table2')
    q = Query()
    doc = tbl.get(q.name == timer_dict['name'])
    if not doc:
        id = tbl.insert(timer_dict)
        print('doc inserted=', id)
    else:
        tbl.update(timer_dict, doc_ids=[doc.doc_id])
        print('doc ID, updated=', doc.doc_id)


t1 = dict(name='kvk', start=0, finish=10)
t2 = dict(name='ww', start=40, finish=10)
t3 = dict(name='kvk', start=20, finish=100)

add_game_timer(t1)
add_game_timer(t2)
add_game_timer(t3) # will be duplicated

add_game_timer2(t1)
add_game_timer2(t2)
add_game_timer2(t3)  # t1, will be updated

msiemens

Phuket2 I also see you may have a problem having a method called insert having the ability to do updates. Maybe that maybe a good reason for a new method(stub at least) with a more appropriate name.

I think it makes sense to have a method for this. And you're right, my biggest concern is naming it in an intuitive way without creating confusion with existing methods (insert and update). For that reason I'd really prefer having a new method instead of extending the existing ones. With regards to the name, I think we might go with something like update_or_insert (inspired by the Rust Hashmap's hashmap.entry(id).or_insert(entry)). The API might look like this:

db.update_or_insert({'name': 'A'}, where('name') == 'B')

What do you think?

Phuket2

msiemens , too me that looks confusing. But I am sure its from my inexperience.
To me something like the below more readable

doc_id = db.update_or_insert(my_dict, key='name')

I have a feeling I am missing some flexibility in your approach. I am not really qualified to know.

But either way, I think it would be nice to have the one liner to remember when you are just interested in just adding a so called unique record based on a single key/ dict attr.

Maybe your where clause allows a multi-key definition of uniqueness, not sure of your intentions.

But the bottom line I think either way for my general needs would simplify the api.

I will mention this, although I dont think it's possible at this point. But another thing that comes to mind, is that if a table had some properties. Eg, set a tables PK property to 'name', another property PK_collision = 'Update' or raise exception. I know this is going too far. But it does pop into my mind. I understand the whole idea is to keep it simple. Again, i am just trying to think of the most common use cases.

msiemens

Phuket2 Maybe your where clause allows a multi-key definition of uniqueness, not sure of your intentions.

I think we misunderstood each other. where('name') is just an alias for Query().name. You could use any other query here:

db.update_or_insert({'name': 'A'}, Query().name == 'B')

# or

User = Query()
db.update_or_insert(user, User.name == 'foo')

Phuket2 I will mention this, although I dont think it's possible at this point. But another thing that comes to mind, is that if a table had some properties. Eg, set a tables PK property to 'name', another property PK_collision = 'Update' or raise exception. I know this is going too far. But it does pop into my mind.

I think the main issue with introducing something like primary keys is that then people expect TinyDB to be an actual relational database, which it is not. Having primary keys would imply to me that there's a way to cross-reference them with foreign keys, using them for JOIN queries and what have you.

Phuket2

msiemens , sorry I did reply to this a few days ago, but I had a feeling my post did not go through.
But I am clearer on your meaning now, regarding the where. But look I would say I am out of my depth.
But it seems good that you found a existing pattern (Rust Hashmap), meaning its not a totally off the planet idea to have a update record if it exists otherwise insert it.

To me the method naming is not important, but I know at the end of the day it is.

But look my comments just come out of my practical usage of TinyDB. Whilst its super easy to add records if you dont care about duplications. But just seems like you have to do a lot more when you want to add records where one of the dicts attrs is unique which I find for me at least turns out to be my normal use case.

Regarding the 'PrimaryKey' word, I understand this is not a good word in this context. Has connotations beyond uniqueness.

Anyway, I will just leave it in your hands. I will keep checking the updates to see what's new.

Again, I love the ease of use of TinyDB.

alshapton

From a method naming perspective - how about "upsert" - this is commonly used elsewhere in other document-style databases.....

From an implementation perspective, I think that if the upsert finds 0 documents, an insert should be performed. If > 1 document is found then the documents that match the query should be updated - in my simplistic mind, its a wrapper method to the update and (optionally) insert methods..... Thoughts ?

Phuket2

alshapton , upsert, sounds like a nice name. I dont use any other document style DB's. I have never heard of that term before. But sounds befitting , and if its a standard thats very nice.

msiemens

Phuket2 msiemens , sorry I did reply to this a few days ago, but I had a feeling my post did not go through.

I've been quite busy last week as lectures started in university and open-source support sometimes gets lost in my TODO list.

alshapton From a method naming perspective - how about "upsert" - this is commonly used elsewhere in other document-style databases.....

I like that name too. From Wiktionary:

upsert: (computing, databases) To insert rows into a database table if they do not already exist, or update them if they do.
From: https://en.wiktionary.org/wiki/upsert

I'll draft a PR with a upsert method, then we can discuss the API in detail on GitHub.

alshapton

Phuket2 ensure that your local repo is upto date with the Markus' main repo and then do

git checkout upsert

alshapton

msiemens re: "I've been quite busy last week as lectures started in university and open-source support sometimes gets lost in my TODO list."
I'm quite happy to get involved when I can - feel free to forward me anything you can't deal with and I'll attempt to help.

msiemens

BTW, I've implemented this in a PR on GitHub so we can gather feedback from other contributors: https://github.com/msiemens/tinydb/pull/167

Phuket2

msiemens , sorry to be naive, but if I want to try the upsert function, do I do a pull request? Or is cloning the right way. Sorry, github is still mainly a black hole mystery for me.