在sqlite中存储GUID的正确方法 [英] Proper way to store GUID in sqlite

问题描述

我希望使用 C# 和 python 客户端将 GUID 存储在我的 SQLite 数据库中.

创建数据库，并插入一行，将 GUID 存储为字符串:

conn = sqlite3.connect(filename)c = conn.cursor()# 创建表.是的，我知道 GUID 不是真正的 SQLite 数据类型.c.execute('CREATE TABLE test (guid GUID PRIMARY KEY, name text)')u = uuid.uuid4()打印你t = (str(u), 'foo')c.execute('INSERT INTO test VALUES (?,?)', t)conn.commit()conn.close()

获取:

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

这一切都完美无缺.使用 UUID 的默认 Python __str__ 表示效果很好.

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

<小时>

我的疑虑源于使用 : 将 SQLite 类型转换为 Python 类型

register_adapter(): 将 Python 类型转换为 SQLite 类型

要使用这些，您需要为 connect().

示例:

导入 sqlite3导入 uuidsqlite3.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)')数据 = (uuid.uuid4(), 'foo')打印 '输入数据:', 数据c.execute('INSERT INTO test VALUES (?,?)', data)c.execute('SELECT * FROM test')打印 '结果数据:', c.fetchone()

输出:

输入数据:(UUID('59cc2646-8666-4fb3-9f57-fe76e22603c0'), 'foo')结果数据:(UUID('59cc2646-8666-4fb3-9f57-fe76e22603c0'), u'foo')

结果:

• 我正在传递 uuid.UUID 对象直接到 execute().适配器 lambda u: buffer(u.bytes) 告诉 sqlite3 如何将它们转换为 buffer(转换为 X'ABCD....' SQLite 中的 blob.
• fectchone() 直接返回 uuid.UUID 对象.转换器 lambda u: buffer(u.bytes) 告诉 sqlite3 在遇到声明的 GUID 类型时如何从字节数组创建它们.
• 这些 GUID 以小端顺序存储为 16 字节二进制 blob.
• 我可以使用 SQLite Expert(在默认配置下)成功打开/编辑数据库.

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.

解决方案

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).

这篇关于在sqlite中存储GUID的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！