While going through the documentation of Python's sqlite3
module, I noticed that
it's quite API-driven, where different parts of the module are explained in a prescriptive
manner. I, however, learn better from examples, recipes, and narratives. Although a few
good recipes already exist in the docs, I thought I'd also enlist some of the examples
I tried out while grokking them.
Executing individual statements¶
To execute individual statements, you'll need to use the cursor_obj.execute(statement)
primitive.
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
with conn:
c.execute(
"""
create table if not exists
stat (id integer primary key, cat text, score real);
"""
)
c.execute("""insert into stat (cat, score) values ('a', 1.0);""")
c.execute("""insert into stat (cat, score) values ('b', 2.0);""")
result = c.execute("""select * from stat;""").fetchall()
print(result)
[(1, 'a', 1.0), (2, 'b', 2.0)]
Executing batch statements¶
You can bundle up multiple statements and execute them in a single go with the
cursor_obj.executemany(template_statement, (data, ...))
API.
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
with conn:
c.execute(
"""
create table if not exists
stat (id integer primary key, cat text, score real);
"""
)
# Data needs to be passed as an iterable of tuples.
data = (
("a", 1.0),
("b", 2.0),
("c", 3.0),
)
c.executemany("insert into stat (cat, score) values (?, ?);", data)
result = c.execute("""select * from stat;""").fetchall()
print(result)
[(1, 'a', 1.0), (2, 'b', 2.0), (3, 'c', 3.0)]
Applying user-defined callbacks¶
You can define and apply arbitrary Python callbacks to different data points in an SQLite table. There are two types of callbacks that you can apply:
- Scalar function: A scalar function returns one value per invocation; in most cases, you can think of this as returning one value per row.
- Aggregate function: In contrast, an aggregate function returns one value per group of rows.
Applying user-defined scalar functions¶
In the following example, I've created a table called users
with two text type
columns—username
and password
. Here, we define a transformation scalar function named
sha256
that applies sha256 hashing to all the elements of the password
column. The
function is then registered via the connection_obj.create_function(func_name, narg, func)
API.
# src.py
import sqlite3
import hashlib
conn = sqlite3.connect(":memory:")
c = conn.cursor()
def sha256(t: str) -> str:
return hashlib.sha256(
t.encode("utf-8"),
usedforsecurity=True,
).hexdigest()
# Register the scalar function.
conn.create_function("sha256", 1, sha256)
with conn:
c.execute(
"""
create table if not exists users (
username text,
password text
);
"""
)
c.execute(
"insert into users values (?, sha256(?));",
("admin", "password"),
)
c.execute(
"insert into users values (?, sha256(?));",
("user", "otherpass"),
)
result = c.execute("select * from users;").fetchall()
print(result)
[
(
'admin',
'5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8'),
(
'user',
'0da86a02c6944c679c5a7f06418bfde6bddb445de708639a3131af3682b34108'
)
]
Applying user-defined aggregate functions¶
Aggregate functions are defined as classes and then registered with the
connection_obj.create_aggregate(func_name, narg, aggregate_class)
API. In the example
below, I've created a table called series
with a single integer type column val
. To
define an aggregate function, we'll need to write a class with two methods—step
and
finalize
where step
will return the value of an intermediary progression step and
finalize
will return the final result. Below, you can see that the aggregate function
returns a single value in the output.
# src.py
import sqlite3
import hashlib
conn = sqlite3.connect(":memory:")
c = conn.cursor()
class Mult:
def __init__(self):
self._result = 1
def step(self, value):
self._result *= value
def finalize(self):
return self._result
# Register the aggregate class.
conn.create_aggregate("mult", 1, Mult)
with conn:
c.execute(
"""
create table if not exists series (
val integer
);
"""
)
c.execute("insert into series (val) values (?);", (2,))
c.execute("insert into series (val) values (?);", (3,))
result = c.execute("select mult(val) from series;").fetchall()
print(result)
[(6,)]
Printing traceback when a user-defined callback raises an error¶
By default, sqlite3
will suppress the traceback of any error raised from an user-defined
function. However, you can turn on the traceback option as follows:
# src
import sqlite3
sqlite3.enable_callback_tracebacks(True)
...
Transforming types¶
Conventionally, Python sqlite3
documentation uses the term adaptation to refer to
the transformation that changes Python types to SQLite types and conversion to refer to
the change in the reverse direction.
Adapting Python types to SQLite types¶
To transform Python types to native SQLite types, you'll need to define a transformation
callback that'll carry out the task. Then the callback will need to be registered with the
sqlite3.register_adapter(type, adapter_callback)
API.
Here, I've created an in-memory table called colors
with a single text type column name
that refers to the name of the color. Then I register the lambda color: color.value
anonymous function that serializes an enum value to a text value. This allows me to pass
an enum member directly into the cursor_obj.execute
method.
# src.py
import enum
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
class Color(enum.Enum):
RED = "red"
GREEN = "green"
BLUE = "blue"
# Register an adapter to transform a Python type to an SQLite type.
sqlite3.register_adapter(Color, lambda color: color.value)
with conn:
c.execute(
"""
create table if not exists colors (
name integer
);
"""
)
c.execute("insert into colors (name) values (?);", (Color.RED,))
c.execute("insert into colors (name) values (?);", (Color.GREEN,))
result = c.execute("select name from colors;").fetchall()
print(result)
[('red',), ('green',)]
Converting SQLite types to Python types¶
Converting SQLite types to Python types works similarly to the previous section. Here, as
well, I've created the same colors
table with a single name
column as before. But this
time, I want to insert string values into the name
column and get back native enum
objects from that field while performing a get query.
To do so, I've registered a converter function with the
sqlite3.register_converter("sqlite_type_as_a_string", converter_callback)
API. Another
point to keep in mind is that you'll have to set
detect_type=sqlite3.PARSE_DECLTYPES
in the sqlite3.connection
method for the adaptation
to work. Notice the output of the last select ...
statement and you'll see that we're
getting enum objects in the returned list.
# src.py
import enum
import sqlite3
class Color(enum.Enum):
RED = "red"
GREEN = "green"
BLUE = "blue"
color_map = {v.value: v for v in Color.__members__.values()}
# Register a convert to convert text to Color enum
sqlite3.register_converter(
"text",
lambda v: color_map[v.decode("utf-8")],
)
conn = sqlite3.connect(
":memory:",
detect_types=sqlite3.PARSE_DECLTYPES, # Parse declaration types.
)
c = conn.cursor()
with conn:
c.execute(
"""
create table if not exists colors (
name text
);
"""
)
c.execute("insert into colors (name) values (?);", ("red",))
c.execute("insert into colors (name) values (?);", ("green",))
c.execute("insert into colors (name) values (?);", ("blue",))
result = c.execute("select name from colors;").fetchall()
print(result)
[
(<Color.RED: 'red'>,),
(<Color.GREEN: 'green'>,),
(<Color.BLUE: 'blue'>,)
]
Using the default adapters and converters¶
The sqlite3
module also employs some default adapters and converters that you can take
advantage of without defining and registering custom transformers. For example,
SQLite doesn't have any special types to represent a date or timestamp. However, Python
sqlite3
allows you to annotate a column with a special type and it'll automatically
convert the values of the column to a compatible type of Python object while returning the
result of a get query.
Here, I've created a table called timekeeper
with two columns—d
and dt
where d
expects a date and dt
expects a timestamp. So, in the table creation DDL statement,
we annotate the columns with date
and timestamp
types respectively. We've also turned
on column type parsing by setting
detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
in the sqlite3.connect
method.
Finally, notice how we're inserting datetime.date
and datetime.datetime
objects
directly into the table. Also, this time, the final select ...
statement looks a bit
different. We're specifying the expected type in the select ...
statement and it's
returning native Python objects in the returned list.
# src.py
import datetime
import sqlite3
import zoneinfo
conn = sqlite3.connect(
":memory:",
detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES,
)
with conn:
c = conn.cursor()
c.execute(
"""
create table if not exists
timekeeper (id integer primary key, d date, dt timestamp);"""
)
tz = zoneinfo.ZoneInfo("America/New_York")
dt = datetime.datetime.now(tz)
d = dt.date()
c.execute(
"insert into timekeeper (d, dt) values (?, ?);",
((d, dt)),
)
result = c.execute(
"""
select
d as "d [date]", dt as "dt [timestamp]"
from timekeeper;"""
).fetchall()
print(result)
[
(
datetime.date(2022, 9, 5),
datetime.datetime(2022, 9, 5, 14, 59, 52, 867917)
)
]
Implementing authorization control¶
Sometimes you need control over what operations are allowed to be run on an SQLite database
and what aren't. The connection_obj.set_authorizer(auth_callback)
allows you to implement
authorization control. Here, auth_callback
takes in 5 arguments. From the docs:
The 1st argument to the callback signifies what kind of operation is to be authorized. The 2nd and 3rd arguments will be arguments or None depending on the 1st argument. The 4th argument is the name of the database (“main”, “temp”, etc.) if applicable. The 5th argument is the name of the inner-most trigger or view that is responsible for the access attempt or None if this access attempt is directly from input SQL code. Please consult the SQLite documentation about the possible values for the first argument and the meaning of the second and third arguments depending on the first one.
You can find the list of all the supported actions here. In the following example,
I'm disallowing create table, create index, drop table, and drop index actions. To deny an
action, the auth_callback
will have to return sqlite3.SQLITE_DENY
and that'll raise
an sqlite3.DatabaseError
exception whenever a user tries to execute any of the restricted
actions. Returning sqlite3.SQLITE_OK
from the callback ensures that unfiltered actions
can still pass through the guardrail without incurring any errors.
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
def authorizer(action, arg1, arg2, dbname, trigger):
# Print the params.
print(action, arg1, arg2, dbname, trigger)
# Disallow these actions.
if action in (
sqlite3.SQLITE_CREATE_TABLE,
sqlite3.SQLITE_CREATE_INDEX,
sqlite3.SQLITE_DROP_TABLE,
sqlite3.SQLITE_DROP_INDEX,
):
return sqlite3.SQLITE_DENY
# Let everything else pass through.
return sqlite3.SQLITE_OK
c = conn.cursor()
with conn:
c.execute(
"""
create table if not exists colors (
name text
);
"""
)
# After creating the table, let's make sure users can't perform
# certain DDL operations.
conn.set_authorizer(authorizer)
c.execute("insert into colors (name) values (?);", ("red",))
# This will fail because the authorizer will deny the operation.
c.execute("drop table colors;")
18 colors None main None
22 BEGIN None None None
9 sqlite_master None main None
11 colors None main None
22 ROLLBACK None None None
Traceback (most recent call last):
File "/home/rednafi/canvas/personal/reflections/src.py", line 45, in <module>
c.execute("drop table colors;")
sqlite3.DatabaseError: not authorized
Changing the representation of a row¶
The sqlite3
module allows you to change the representation of a database row to your
liking. By default, the result of a query comes out as a list of tuples where each tuple
represents a single row. However, you can change the representation of database rows in
such a way that the result might come out as a list of dictionaries or a list of custom
objects.
Via an arbitrary container object as the row factory¶
You can attach a callback to the connection_obj.row_factory
attribute to change how you
want to display the rows in a result list. The factory callback takes in two arguments—
cursor
and row
where cursor
is a tuple containing some metadata related to a single
table record and row
is the default representation of a single database row as a tuple.
In the following snippet, just like before, I'm creating the same colors
table with two
columns—name
and hex
. Here, the row_factory
function is the factory callback that
converts the default row representation from a tuple to a dictionary. We're then
registering the row_factory
function with the connection_obj.row_factory = row_factory
assignment statement. Afterward, the sqlite3
module calls this statement on each record
and transforms the representation of the rows. When you run the snippet, you'll see that
the result comes out as a list of dictionaries instead of a list of tuples.
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
# Using a dictionary to represent a row.
def row_factory(cursor, row):
# cursor.description:
# (name, type_code, display_size,
# internal_size, precision, scale, null_ok)
# row: (value, value, ...)
return {
col[0]: row[idx]
for idx, col in enumerate(
cursor.description,
)
}
conn.row_factory = row_factory
c = conn.cursor()
with conn:
c.execute(
"""
create table if not exists colors (
name text,
hex text
);
"""
)
c.execute(
"insert into colors (name, hex) values (?, ?);",
("red", "#ff0000"),
)
c.execute(
"insert into colors (name, hex) values (?, ?);",
("green", "#00ff00"),
)
c.execute(
"insert into colors (name, hex) values (?, ?);",
("blue", "#0000ff"),
)
result = c.execute("select * from colors;").fetchall()
print(result)
[
{'name': 'red', 'hex': '#ff0000'},
{'name': 'green', 'hex': '#00ff00'},
{'name': 'blue', 'hex': '#0000ff'}
]
Via a specialized Row object as the row factory¶
Instead of rolling with your own custom row factory, you can also take advantage of the
highly optimized sqlite3.Row
object. From the docs:
A
Row
instance serves as a highly optimizedrow_factory
for Connection objects. It supports iteration, equality testing, len(), and mapping access by column name and index. Two row objects compare equal if have equal columns and equal members.
In the following example, I've reused the script from the previous section and just
replaced the custom row factory callback with sqlite3.Row
. In the output, you'll see that
the Row
object not only allows us to access the value of a column by row[column_name]
syntax but also let us convert the representation of the final result.
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
# Registering a highly optimized 'Row' object as the
# default row_factory. Row is a map-like object that
# allows you to access column values by name.
conn.row_factory = sqlite3.Row
c = conn.cursor()
with conn:
c.execute(
"""
create table if not exists colors (
name text,
hex text
);
"""
)
c.executemany(
"insert into colors (name, hex) values (?, ?);",
(
("red", "#ff0000"),
("green", "#00ff00"),
("blue", "#0000ff"),
),
)
result = c.execute("select * from colors;").fetchall()
# Access the values of a row by column name.
for row in result:
print(row["name"], row["hex"])
# Convert the result to a list of dicts.
result_dict = [dict(row) for row in result]
print(result_dict)
red #ff0000
green #00ff00
blue #0000ff
[
{'name': 'red', 'hex': '#ff0000'},
{'name': 'green', 'hex': '#00ff00'},
{'name': 'blue', 'hex': '#0000ff'}
]
Via text factory¶
If you need to apply a common transformation callback to multiple text columns, the
sqlite3
module has a shortcut to do so. You can certainly write an ordinary row factory
that'll only transform the text columns but the connection_obj.text_factory
attribute
enables you to do that in a more elegant fashion. You can set
connection_obj.text_factory = row_factory
and that'll selectively apply the row_factory
callback only to the text columns. In
the following example, I'm applying an anonymous function to the text columns to translate
the color names to English.
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
# Apply factory only to text fields.
color_map = {"το κόκκινο": "red", "সবুজ": "green"}
# Translate all the text fields.
conn.text_factory = lambda x: color_map.get(x.decode("utf-8"), x)
with conn:
c.execute("create table if not exists colors (name text);")
c.execute("insert into colors (name) values (?);", ("το κόκκινο",))
c.execute("insert into colors (name) values (?);", ("সবুজ",))
result = c.execute("select * from colors;").fetchall()
print(result)
[('red',), ('green',)]
Creating custom collation¶
Collation defines how the string values in a text column are compared. It also dictates how
the data in the column will be ordered when you perform any kind of sort operation. A
collation callback can be registered with the
connection_obj.create_collation(name, collation_callback)
syntax where the name
denotes
the name of the collation rule and the collation_callback
determines how the string
comparison should be done. The callback accepts two string values as arguments and returns—
- 1 if the first is ordered higher than the second
- -1 if the first is ordered lower than the second
- 0 if they are ordered equal
Then you can use the collation rules with an order by clause as follows:
select * from table_name order by column_name collate collation_name
Here's a full example of a collation callback in action:
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
def reverse_collate(a, b):
return 1 if a < b else -1 if a > b else 0
# Register the collation function.
conn.create_collation("reverse", reverse_collate)
with conn:
c.execute("create table if not exists colors (name text);")
c.executemany(
"insert into colors (name) values (?);",
(("το κόκκινο",), ("সবুজ",)),
)
result = c.execute(
"""select * from colors
order by name collate reverse;"""
).fetchall()
print(result)
[('সবুজ',), ('το κόκκινο',)]
Registering trace callbacks to introspect running SQL statements¶
During debugging, I often find it helpful to be able to trace all the SQL statements
running under a certain connection. This becomes even more useful in a multiprocessing
environment where each process opens a new connection to the DB and runs its own sets
of SQL queries. We can leverage the connection_obj.set_trace_callback
method to trace
the statements. The set_trace_callback
method accepts a callable that takes a single
argument and sqlite3
module passes the currently running statement to the callback
every time it invokes it. Notice how the output prints all the statements executed by
SQLite behind the scene. This also reveals that cursor_obj.executemany
wraps up multiple
statements in a transaction and runs them in an atomic manner.
# src.py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
# Print all the statements executed.
def introspect(s):
print(s)
# Register the trace function.
conn.set_trace_callback(introspect)
with conn:
c.execute("create table if not exists colors (name text);")
c.executemany(
"insert into colors (name) values (?);",
(("red",), ("green",), ("blue",)),
)
result = c.execute("""select * from colors""").fetchall()
print(result)
create table if not exists colors (name text);
BEGIN
insert into colors (name) values (?);
insert into colors (name) values (?);
insert into colors (name) values (?);
select * from colors
[('red',), ('green',), ('blue',)]
COMMIT
Backing up a database¶
There are a few ways you can back up your database file via Python sqlite3
.
Dumping the database iteratively¶
The following snippet creates a table, inserts some data into it, and then, iteratively
fetches the database content via the connection_obj.iterdump()
API. Afterward, the
returned content is written to another database file using the file.write
primitive.
For demonstration purposes, I'm using an in-memory DB and backing that up in another
NamedTemporaryFile
. This will work the same way with an on-disk DB and on-disk backup
file as well. One advantage of this approach is that your data is not loaded into memory at
once, rather it's streamed iteratively from the main DB to the backup DB.
# src.py
import sqlite3
import tempfile
from contextlib import ExitStack
conn = sqlite3.connect(":memory:")
with ExitStack() as stack:
conn = stack.enter_context(conn)
dst_file = stack.enter_context(tempfile.NamedTemporaryFile())
c = conn.cursor()
c.execute("create table if not exists colors (name text);")
c.executemany(
"insert into colors (name) values (?);",
(("red",), ("green",), ("blue",)),
)
for line in conn.iterdump():
dst_file.write(line.encode("utf-8") + b"\n")
dst_file.seek(0)
print(dst_file.read().decode("utf-8"))
BEGIN TRANSACTION;
CREATE TABLE colors (name text);
INSERT INTO "colors" VALUES('red');
INSERT INTO "colors" VALUES('green');
INSERT INTO "colors" VALUES('blue');
COMMIT;
Copying an on-disk database to another¶
This example shows another approach that you can adopt to create a second copy of your
on-disk DB. First, it connects to the source DB and then creates another connection to an
empty backup DB. Afterward, the source data is backed up to the destination DB with the
connection_obj_source.backup(connection_obj_destination)
API.
The .backup
method takes in three values—a connection object that points to the
destination DB, the number of pages to copy in a single pass, and a callback to introspect
the progress. You can set the value of the progress
parameter to -1
if you want to
load the entire source database into memory and copy everything to the destination in a
single pass. Also, in this example, the progress
hook just prints the progress of the
copied pages.
# src.py
import sqlite3
from contextlib import ExitStack
conn_src = sqlite3.connect("src.db")
conn_dst = sqlite3.connect("dst.db")
# Hook that indicates backup progress.
def progress(status, remaining, total):
print(f"Copied {total-remaining} of {total} pages...")
with ExitStack() as stack:
conn_src = stack.enter_context(conn_src)
conn_dst = stack.enter_context(conn_dst)
cursor_src = conn_src.cursor()
cursor_src.execute(
"""
create table if not exists colors (
name text,
hex text
);
"""
)
cursor_src.executemany(
"insert into colors (name, hex) values (?, ?);",
(
("red", "#ff0000"),
("green", "#00ff00"),
("blue", "#0000ff"),
),
)
# Must commit before backup.
conn_src.commit()
# Copy a to b. The 'pages' parameter determines how many DB pages
# to copy in a single iteration. Set to -1 to load everything into
# memory at once and do it in a single iteration.
conn_src.backup(conn_dst, pages=1, progress=progress)
# Ensure that the backup is complete.
result = conn_dst.execute("select count(*) from colors;").fetchone()
print(f"Number of rows in dst: {result[0]}")
Copied 1 of 2 pages...
Copied 2 of 2 pages...
Number of rows in dst: 3
Loading an on-disk database into the memory¶
The connection_obj.backup
API also lets you load your existing database into memory.
This is helpful when the DB you're working with is small and you want to leverage the extra
performance benefits that come with an in-memory DB. The workflow is almost exactly the
same as before and the only difference is that the destination connection object points to
an in-memory DB instead of an on-disk one.
# src.py
import sqlite3
conn_src = sqlite3.connect("src.db")
conn_dst = sqlite3.connect(":memory:")
with conn_src:
cursor_src = conn_src.cursor()
cursor_src.execute(
"""
create table if not exists colors (
name text,
hex text
);
"""
)
cursor_src.executemany(
"insert into colors (name, hex) values (?, ?);",
(
("red", "#ff0000"),
("green", "#00ff00"),
("blue", "#0000ff"),
),
)
# Must commit before backup.
conn_src.commit()
# Copy a to memory.
conn_src.backup(conn_dst)
# Ensure that the backup is complete.
result = conn_dst.execute("select count(*) from colors;").fetchone()
print(f"Number of rows in dst: {result[0]}")
Number of rows in dst: 3
Copying an in-memory database to an on-disk file¶
You can also dump your in-memory DB into the disk. Just point the source connection object to the in-memory DB and the destination connection to the on-disk DB file.
# src.py
import sqlite3
conn_src = sqlite3.connect(":memory:")
conn_dst = sqlite3.connect("dst.db")
with conn_src:
cursor_src = conn_src.cursor()
cursor_src.execute(
"""
create table if not exists colors (
name text,
hex text
);
"""
)
cursor_src.executemany(
"insert into colors (name, hex) values (?, ?);",
(
("red", "#ff0000"),
("green", "#00ff00"),
("blue", "#0000ff"),
),
)
# Must commit before backup.
conn_src.commit()
# Copy a to an on-disk file.
conn_src.backup(conn_dst)
# Ensure that the backup is complete.
result = conn_dst.execute("select count(*) from colors;").fetchone()
print(f"Number of rows in dst: {result[0]}")
Number of rows in dst: 3
Implementing a full text search engine¶
This is not exactly a feature that's specific to the sqlite3
API. However, I wanted to
showcase how effortless it is to leverage SQLite's native features via the Python API.
The following example creates a virtual table and implements a full-text search engine
that allows us to fuzzy search the colors in the colors
table by their names or hex
values.
# src.py
import sqlite3
import uuid
conn = sqlite3.connect(":memory:")
# Get the search result as a Python dict.
conn.row_factory = lambda cursor, row: {
col[0]: row[idx] for idx, col in enumerate(cursor.description)
}
with conn:
c = conn.cursor()
# Unindexed ensures that uuid field doesn't appear in the ft5 index.
c.execute(
"""
create virtual table
if not exists colors using fts5(uuid unindexed, name, hex);"""
)
get_uuid = lambda: str(uuid.uuid4()) # noqa: E731
color_data = (
(get_uuid(), "red", "#ff0000"),
(get_uuid(), "green", "#00ff00"),
(get_uuid(), "blue", "#0000ff"),
(get_uuid(), "yellow", "#ffff00"),
(get_uuid(), "cyan", "#00ffff"),
(get_uuid(), "magenta", "#ff00ff"),
(get_uuid(), "black", "#000000"),
(get_uuid(), "white", "#ffffff"),
)
c.executemany("insert into colors values (?, ?, ?);", color_data)
result = c.execute(
"""select * from colors where name match
'cyan OR red NOT magenta';"""
).fetchall()
print(result)
[
{
'uuid': 'c5f6e5ea-124b-44fe-afad-69aad565541e',
'name': 'red',
'hex': '#ff0000'
},
{
'uuid': '0dedbb75-6e1d-4a7f-85f7-f0ed0ae4d162',
'name': 'cyan',
'hex': '#00ffff'
}
]