Python: insert UUID value in Sqlite3

Question

I am trying to insert the UUID value into sqlite3 database as

INSERT INTO user (user_id,username, email) VALUES (uuid.UUID('7005d0e0-f25b-45f9-897d-bae151fddaff'),'user', '[email protected]')

And I am storing the UUID value in the database as a custom data type 'GUID'. For this conversion, I am using sqlite3 converter as

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

The problem with this query, the query execution gets failed as:

sqlite3.OperationalError: near "(": syntax error

Am I missing anything? I want to store the UUID value into the database.

Answer 1

You are putting Python code in the SQL expression, and you registered a handler to convert SQLite data to a Python type.

Register an adapter for the type, then insert the UID as a SQL parameter. Or pass in a literal byte sequence in the SQL query.

An adapter is as simple as:

sqlite3.register_adapter(uuid.UUID, lambda u: u.bytes_le)

and then you can use your instance as a query parameter:

cursor.execute('''
        INSERT INTO user (user_id, username, email) 
        VALUES (?, ?, ?)
    ''', (
        uuid.UUID('7005d0e0-f25b-45f9-897d-bae151fddaff'), 'user',
        '[email protected]'
    ))

To go the other direction, getting a uuid.UUID() instance from a SELECT query, you'd either have to use the type name in the register_converter() call in the column definition of the table schema, or use the type name in the column name of the select; you need to tell sqlite3 what variants you plan use in the connect() call (it can be both).

Demo:

>>> import uuid, sqlite3
>>> conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
>>> sqlite3.register_adapter(uuid.UUID, lambda u: u.bytes_le)
>>> sqlite3.register_converter('GUID', lambda b: uuid.UUID(bytes_le=b))
>>> conn.execute('CREATE TABLE user (user_id GUID, username TEXT, email TEXT)')
<sqlite3.Cursor object at 0x1074065e0>
>>> with conn:
...     cursor = conn.cursor()
...     cursor.execute('''
...             INSERT INTO user (user_id, username, email)
...             VALUES (?, ?, ?)
...         ''', (
...             uuid.UUID('7005d0e0-f25b-45f9-897d-bae151fddaff'), 'user',
...             '[email protected]'
...         ))
...
<sqlite3.Cursor object at 0x107406570>
>>> cursor = conn.cursor()
>>> for row in cursor.execute('SELECT * FROM user'):
...     print(row)
...
(UUID('7005d0e0-f25b-45f9-897d-bae151fddaff'), 'user', '[email protected]')

In the above demo, passing in a uuid.UUID() instance to cursor.execute() triggers the adapter registered with register_adapter, producing a bytestring to insert into the database column.

And when iterating over rows from a SELECT, the sqlite3 library mapped the GUID type from the user_id column in the table schema and used the register_converter() registration of the same name to produce a uuid.UUID() instance from the bytes stored.

Answer 2

uuid.UUID is not an SQL function, so you cannot use it in the SQL query.

You have to use a parameter so that Python can convert it:

cur.execute("INSERT ... VALUES (?,?,?)",
            (uuid.UUID('7005d0e0-f25b-45f9-897d-bae151fddaff'), 'user', '[email protected]'))