Proper way to store GUID in sqlite

I am looking to store a GUID in my SQLite database using C # and python clients.

Creating a database and inserting a string, saving 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() 

Everything works perfectly. Using the default Python __str__ view for UUIDs 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 due to the use of SQLite Expert . It seems that SQLite Expert is pleased with the declaration of the GUID data type:

enter image description here

But if I edit the line:

enter image description here

it seems like it is changing the data type! My SELECT with a crop of None , and if I SELECT * , I see that this 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 the disk, you can see that the GUID is stored in binary format after SQLite Expert touched it:

Before - GUID is ASCII text:

enter image description here

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

enter image description here


So, what is the “right” way to store GUIDs in SQLite, specifically using Python? Later I will have C # code interacting with this, and also want me to do things “correctly”.

+4
source share
2 answers

You can substantially add support for GUID types for sqlite3 in Python. You can register conversion functions:

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

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 pass the uuid.UUID objects directly to execute() . The lambda u: buffer(u.bytes) adapter lambda u: buffer(u.bytes) tells sqlite3 how to convert them to buffer (which converts to X'ABCD....' blob in SQLite.
  • fectchone() returns uuid.UUID objects directly. The lambda u: buffer(u.bytes) tells sqlite3 how to create them from an array of bytes when it encounters a declared GUID type.
  • These GUIDs are stored as 16-byte binary drops, in little-endian order.
  • I can successfully open / edit databases using SQLite Expert (in the default configuration).
+6
source

Your GUID is a string. Declare it as VARCHAR or CHAR, and it will correctly select the affinity of the text. Since the GUID is not a type, the field has no type affinity and therefore will have the NONE data type.

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

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

Support talks about the mapping system and how to change the display, as well as the default tool for using Blob.

+3
source

Source: https://habr.com/ru/post/1502344/


All Articles