'Insert if doesn't exist - in python+sqlalchemy
The problem: find an rdbms-independent way to get a value with the specified key from a database using sqlalchemy! If the key doesn't exist, generate a new value and insert it to the table. In a multiuser+multiprocess environment...
I have two ideas: 1st get it, if it returns None, then generate a new and insert, if it is unsuccessful (because of duplicate key) then get it again.
2nd: lock the table, get it, if returns None, then generate a new and insert
The 2nd seems to be better, but I can't find an rdbms-independent solution for locking a table. (session.execute('lock ...') is inappropriate, because it uses sql directly)
The 1st looks... khm... unprofessional. How can I solve this?
Solution 1:[1]
Unfortunately I am not sure what is performance of merge, but regarding to this post (which I highly recommend) the best option would be to use upsert and then just read from the table
from sqlalchemy.dialects.postgresql import insert
insert_stmt = insert(my_table).values(id='id',data='data')
do_nothing_stmt = insert_stmt.on_conflict_do_nothing(index_elements=['id'])
db.query(table).get("id")
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | kosciej16 |