Most efficient method of storing UUID v4 to a SQLite table

Python Help
1

I’m generating and then needing to store a uuid4 in a SQLite table (it will not serve as PK). I’d like to ensure that it’s stored as efficiently as possible because there are close to a million records and this can in future grow significantly.

I defined the column item_uuid as a blob in SQLite and will be generating and storing the uuid via a Python script.

My code snippet (which works) is as follows:

# generate a uuid v4 value and run an update query to store it
uuidval = str(uuid.uuid4())
dbcursor.execute('''UPDATE table SET item_uuid = (?) WHERE rowid = (?);''', (uuidval, record[0]))

I was hoping I could store it without converting to string, but SQLite rejects inserting uuidval if not converted to a string first.

Am I missing a trick or have I gone about this correctly?

2

I don’t think sqlite has a ‘uuid-type’. Though a more efficient way is to save it as a BLOB of 16 bytes.

You can get those bytes via .bytes

In [5]: uuid.uuid4()
Out[5]: UUID('b42afb4e-652a-4e7e-a7d3-0f33475610b4')

In [6]: u = uuid.uuid4()

In [7]: u.bytes
Out[7]: b'\xe2\x8d\xcc\x1bk\xddN\xa4\xa08y\x8fI\xa3\xa1\xc2'

In [8]: len(u.bytes)
Out[8]: 16

In [9]: len(str(u))
Out[9]: 36

So you gain some efficiency, but may lose out on readability since it may be harder (for a human) to read the bytes output than the string output.

Another reason that bytes can be better than saving the string here is that a capitalized representation of a string uuid won’t match a non-capitalized version. Though once you go to bytes, they’re the same.

4 Likes