Yes. Here is a stored procedure written in Python that implements a "stored
procedure" (that is, it is a procedure and it is indeed stored) that does an
"upsert" operation. You pass it the db connection object, the name of the
table, a dictionary of the PrimaryKey fields, and a dictionary of the data
fields. It does the "upsert" and returns the associated rowid of the record
... and it only needs to be written once. Of course, some source languages are
not so nice and require far more complicated "stored procedures", however, you
only have to design and write them once.
from __future__ import print_function
def upsert(db=None, table=None, pk=None, data=None, debug=False):
if not (db and table and pk):
raise ValueError('must specify db, table, pk')
if not all(pk.values()):
raise ValueError('PrimaryKeys must not be null')
alldata = dict()
alldata.update(pk)
sqlUpdate = ''
if data:
alldata.update(data)
sqlUpdate = ''.join(['UPDATE ',
table,
' SET (',
', '.join(data.keys()),
') = (:',
', :'.join(data.keys()),
') WHERE (',
', '.join(pk.keys()),
') == (:',
', :'.join(pk.keys()),
');'
])
sqlInsert = ''.join(['INSERT OR IGNORE INTO ',
table,
' (',
', '.join(alldata.keys()),
') values (:',
', :'.join(alldata.keys()),
');'
])
sqlSelect = ''.join(['SELECT id ',
'FROM ',
table,
' WHERE (',
', '.join(pk.keys()),
') == (:',
', :'.join(pk.keys()),
');'
])
if debug:
if sqlUpdate:
print(sqlUpdate)
print(sqlInsert)
print(sqlSelect)
cr = db.cursor()
cr.execute('SAVEPOINT %s%s' % ('UpSert', table))
if data:
cr.execute(sqlUpdate, alldata)
cr.execute(sqlInsert, alldata)
id = None
try:
for row in cr.execute(sqlSelect):
id = row[0]
except:
pass
cr.execute('RELEASE %s%s;' % ('UpSert', table))
cr.close()
return id
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux
>Sent: Thursday, 22 March, 2018 13:18
>To: SQLite mailing list
>Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
>followed by INSERT
>
>I think there are a couple main offenders with
>
>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE .... ;
>> COMMIT;
>
>The first is that it is bulky. If this is in the application code
>then it
>has to be repeated for each desired UPSERT and it has to be repeated
>in the
>code of each application that uses the database.
>
>The second is that it seems so inefficient in the case of a new row
>being
>inserted. The row is inserted and then immediately updated. Why do
>both
>operations when only one is needed?
>
>Is it possible to write a stored procedure that checks a result of
>the
>INSERT OR IGNORE and only attempts the UPDATE if the row already
>existed?
>That would at least move the bulky code out of the application and
>into the
>database. Also it seems it would be more efficient.
>
>Thanks.
>
>Peter
>
>
>Peter
>
>
>
>On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt <k.n...@zonnet.nl> wrote:
>
>> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
>> <petermich...@gmail.com> wrote:
>>
>> > You are right that my purpose does seem kind of confusing.
>> >
>> > What I really want is UPSERT to avoid doing an UPDATE, checking
>if the
>> > number of rows affected is zero, then doing an INSERT. The lack
>of UPSERT
>> > leads to a lot more application code using the UPDATE/INSERT
>combination.
>> > UPSERT doesn't exist in SQLite so I was trying to work around
>that by
>> using
>> > INSERT OR REPLACE which is not the same thing. I can see from
>another
>> > recent thread that some others also think that UPSERT would be a
>valuable
>> > addition to SQLite.
>>
>> I fail to see the problem in
>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE .... ;
>> COMMIT;
>> Simple code, no need to test number of affected rows, and pretty
>> fast because the relevant pages will be in cache.
>>
>> Or use an updatable view with an INSTEAD OF INSERT trigger.
>>
>> I did notice that attempts to define a proper UPSERT syntax
>> opened a can of worms by itself because it (also) has to provide
>> two colum lists, one for a full INSERT if the row with that PK
>> doesn't exist, and another one for the columns to be updated
>> when the row already exists. So, I don't see a big advantage in
>> UPSERT.
>>
>> My humble two cents,
>>
>> --
>> Regards,
>> Kees Nuyt
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
