Proper way to store GUID in sqlite

Question

I'm looking to store GUIDs in my SQLite database, using C# and python clients.

Creating a database, and inserting a row, storing the GUID as a string:

conn = sqlite3.connect(filename)

c = conn.cursor()

# Create the table. Yes, I know GUID isn't a real SQLite datatype.
c.execute('CREATE TABLE test (guid GUID PRIMARY KEY, name text)')

u = uuid.uuid4()
print u
t = (str(u), 'foo')
c.execute('INSERT INTO test VALUES (?,?)', t)

conn.commit()
conn.close()

Fetching:

# ...
c.execute('SELECT * FROM test WHERE guid = "c1332103-6031-4ff7-b610-f8f3b940fa66"')
print c.fetchone()

This all works perfectly. Using the default Python __str__ representation of the UUID works well.

C:\Users\Jonathon>makedb.py test.db
c1332103-6031-4ff7-b610-f8f3b940fa66

C:\Users\Jonathon>opendb.py test.db
(u'c1332103-6031-4ff7-b610-f8f3b940fa66', u'foo')

---

My doubts arise from using SQLite Expert. It appears that SQLite Expert is happy with my GUID datatype declaration:

But, if I edit a row:

it seems that it changes the datatype! My SELECT from before yields None, and if I SELECT *, I see that it is no longer a simple unicode string:

C:\Users\Jonathon>opendb.py test.db
(<read-write buffer ptr 0x02239520, size 16 at 0x02239500>, u'foo')

---

Looking at the data on disk, you can see that the GUID is stored in binary, after SQLite Expert touches it:

Before - the GUID is ASCII text:

After - the previous data is garbage, and a binary version of the GUID exists:

---

So what is the "correct" way to store GUIDs in SQLite, specifically using Python? Later, I will have C# code interacting with this as well, and want to make sure I'm going things the "right" way.

Answer 1

Your GUID is a string. Declare it as a VARCHAR or CHAR and it will pick up the text affinity correctly. As GUID is not a type the field has no type affinity, and thus will have the datatype of NONE.

This is described here: http://www.sqlite.org/datatype3.html

There is also a brief discussion of someone trying to use a Guid within SQLite Expert on their support forum here: http://sqliteexpert.com/forum/YaBB.pl?num=1368018774/0

Support talks about the mapping system and how to change a mapping, as well as the tool defaulting to the use of a Blob.

Answer 2

One can essentially add support for GUID datatypes to sqlite3 in Python. You can register conversion functions:

• register_converter(): convert SQLite types to Python types
• register_adapter(): convert Python types to SQLite types

To use these, you need to pass an argument for the detect_types parameter of connect().

Example:

import sqlite3
import uuid

sqlite3.register_converter('GUID', lambda b: uuid.UUID(bytes_le=b))
sqlite3.register_adapter(uuid.UUID, lambda u: buffer(u.bytes_le))

conn = sqlite3.connect('test.db', detect_types=sqlite3.PARSE_DECLTYPES)

c = conn.cursor()
c.execute('CREATE TABLE test (guid GUID PRIMARY KEY, name TEXT)')

data = (uuid.uuid4(), 'foo')
print 'Input Data:', data
c.execute('INSERT INTO test VALUES (?,?)', data)

c.execute('SELECT * FROM test')
print 'Result Data:', c.fetchone()

Output:

Input Data: (UUID('59cc2646-8666-4fb3-9f57-fe76e22603c0'), 'foo')
Result Data: (UUID('59cc2646-8666-4fb3-9f57-fe76e22603c0'), u'foo')

Results:

• I am passing uuid.UUID objects directly to execute(). The adapter lambda u: buffer(u.bytes) tells sqlite3 how to convert those to a buffer (which translates to an X'ABCD....' blob in SQLite.
• fectchone() is returning uuid.UUID objects directly. The converter lambda u: buffer(u.bytes) tells sqlite3 how to create those from a byte array when it encounters a declared type of GUID.
• These GUIDs are being stored as 16-byte binary blobs, in little-endian order.
• I can successfully open/edit the databases using SQLite Expert (in its default configuration).