[3.11] gh-90016: Reword sqlite3 adapter/converter docs (GH-93095) (#94272)
Also add adapters and converter recipes.
Co-authored-by: CAM Gerlach <CAM.Gerlach@Gerlach.CAM>
Co-authored-by: Alex Waygood <Alex.Waygood@Gmail.com.
(cherry picked from commit bd3c1c187e0e4fde5aec6835d180e9eddde8ceb6)
diff --git a/Doc/includes/sqlite3/adapter_datetime.py b/Doc/includes/sqlite3/adapter_datetime.py
deleted file mode 100644
index d5221d8..0000000
--- a/Doc/includes/sqlite3/adapter_datetime.py
+++ /dev/null
@@ -1,17 +0,0 @@
-import sqlite3
-import datetime
-import time
-
-def adapt_datetime(ts):
- return time.mktime(ts.timetuple())
-
-sqlite3.register_adapter(datetime.datetime, adapt_datetime)
-
-con = sqlite3.connect(":memory:")
-cur = con.cursor()
-
-now = datetime.datetime.now()
-cur.execute("select ?", (now,))
-print(cur.fetchone()[0])
-
-con.close()
diff --git a/Doc/includes/sqlite3/converter_point.py b/Doc/includes/sqlite3/converter_point.py
index 5df828e..147807a 100644
--- a/Doc/includes/sqlite3/converter_point.py
+++ b/Doc/includes/sqlite3/converter_point.py
@@ -5,28 +5,23 @@ def __init__(self, x, y):
self.x, self.y = x, y
def __repr__(self):
- return "(%f;%f)" % (self.x, self.y)
+ return f"Point({self.x}, {self.y})"
def adapt_point(point):
- return ("%f;%f" % (point.x, point.y)).encode('ascii')
+ return f"{point.x};{point.y}".encode("utf-8")
def convert_point(s):
x, y = list(map(float, s.split(b";")))
return Point(x, y)
-# Register the adapter
+# Register the adapter and converter
sqlite3.register_adapter(Point, adapt_point)
-
-# Register the converter
sqlite3.register_converter("point", convert_point)
+# 1) Parse using declared types
p = Point(4.0, -3.2)
-
-#########################
-# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
-cur = con.cursor()
-cur.execute("create table test(p point)")
+cur = con.execute("create table test(p point)")
cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
@@ -34,11 +29,9 @@ def convert_point(s):
cur.close()
con.close()
-#######################
-# 1) Using column names
+# 2) Parse using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
-cur = con.cursor()
-cur.execute("create table test(p)")
+cur = con.execute("create table test(p)")
cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index 4bac8fb..2bdac76 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -199,31 +199,41 @@
.. data:: PARSE_DECLTYPES
- This constant is meant to be used with the *detect_types* parameter of the
- :func:`connect` function.
+ Pass this flag value to the *detect_types* parameter of
+ :func:`connect` to look up a converter function using
+ the declared types for each column.
+ The types are declared when the database table is created.
+ ``sqlite3`` will look up a converter function using the first word of the
+ declared type as the converter dictionary key.
+ For example:
- Setting it makes the :mod:`sqlite3` module parse the declared type for each
- column it returns. It will parse out the first word of the declared type,
- i. e. for "integer primary key", it will parse out "integer", or for
- "number(10)" it will parse out "number". Then for that column, it will look
- into the converters dictionary and use the converter function registered for
- that type there.
+
+ .. code-block:: sql
+
+ CREATE TABLE test(
+ i integer primary key, ! will look up a converter named "integer"
+ p point, ! will look up a converter named "point"
+ n number(10) ! will look up a converter named "number"
+ )
+
+ This flag may be combined with :const:`PARSE_COLNAMES` using the ``|``
+ (bitwise or) operator.
.. data:: PARSE_COLNAMES
- This constant is meant to be used with the *detect_types* parameter of the
- :func:`connect` function.
+ Pass this flag value to the *detect_types* parameter of
+ :func:`connect` to look up a converter function by
+ using the type name, parsed from the query column name,
+ as the converter dictionary key.
+ The type name must be wrapped in square brackets (``[]``).
- Setting this makes the SQLite interface parse the column name for each column it
- returns. It will look for a string formed [mytype] in there, and then decide
- that 'mytype' is the type of the column. It will try to find an entry of
- 'mytype' in the converters dictionary and then use the converter function found
- there to return the value. The column name found in :attr:`Cursor.description`
- does not include the type, i. e. if you use something like
- ``'as "Expiration date [datetime]"'`` in your SQL, then we will parse out
- everything until the first ``'['`` for the column name and strip
- the preceding space: the column name would simply be "Expiration date".
+ .. code-block:: sql
+
+ SELECT p as "p [point]" FROM test; ! will look up converter "point"
+
+ This flag may be combined with :const:`PARSE_DECLTYPES` using the ``|``
+ (bitwise or) operator.
.. function:: connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])
@@ -247,14 +257,17 @@
SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If
you want to use other types you must add support for them yourself. The
- *detect_types* parameter and the using custom **converters** registered with the
+ *detect_types* parameter and using custom **converters** registered with the
module-level :func:`register_converter` function allow you to easily do that.
- *detect_types* defaults to 0 (i. e. off, no type detection), you can set it to
- any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn
- type detection on. Due to SQLite behaviour, types can't be detected for generated
- fields (for example ``max(data)``), even when *detect_types* parameter is set. In
- such case, the returned type is :class:`str`.
+ *detect_types* defaults to 0 (type detection disabled).
+ Set it to any combination (using ``|``, bitwise or) of
+ :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`
+ to enable type detection.
+ Column names takes precedence over declared types if both flags are set.
+ Types cannot be detected for generated fields (for example ``max(data)``),
+ even when the *detect_types* parameter is set.
+ In such cases, the returned type is :class:`str`.
By default, *check_same_thread* is :const:`True` and only the creating thread may
use the connection. If set :const:`False`, the returned connection may be shared
@@ -309,21 +322,27 @@
Added the ``sqlite3.connect/handle`` auditing event.
-.. function:: register_converter(typename, callable)
+.. function:: register_converter(typename, converter)
- Registers a callable to convert a bytestring from the database into a custom
- Python type. The callable will be invoked for all database values that are of
- the type *typename*. Confer the parameter *detect_types* of the :func:`connect`
- function for how the type detection works. Note that *typename* and the name of
- the type in your query are matched in case-insensitive manner.
+ Register the *converter* callable to convert SQLite objects of type
+ *typename* into a Python object of a specific type.
+ The converter is invoked for all SQLite values of type *typename*;
+ it is passed a :class:`bytes` object and should return an object of the
+ desired Python type.
+ Consult the parameter *detect_types* of
+ :func:`connect` for information regarding how type detection works.
+
+ Note: *typename* and the name of the type in your query are matched
+ case-insensitively.
-.. function:: register_adapter(type, callable)
+.. function:: register_adapter(type, adapter)
- Registers a callable to convert the custom Python type *type* into one of
- SQLite's supported types. The callable *callable* accepts as single parameter
- the Python value, and must return a value of the following types: int,
- float, str or bytes.
+ Register an *adapter* callable to adapt the Python type *type* into an
+ SQLite type.
+ The adapter is called with a Python object of type *type* as its sole
+ argument, and must return a value of a
+ :ref:`type that SQLite natively understands<sqlite3-types>`.
.. function:: complete_statement(statement)
@@ -1236,33 +1255,32 @@
types via converters.
-Using adapters to store additional Python types in SQLite databases
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+Using adapters to store custom Python types in SQLite databases
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-As described before, SQLite supports only a limited set of types natively. To
-use other Python types with SQLite, you must **adapt** them to one of the
-sqlite3 module's supported types for SQLite: one of NoneType, int, float,
-str, bytes.
+SQLite supports only a limited set of data types natively.
+To store custom Python types in SQLite databases, *adapt* them to one of the
+:ref:`Python types SQLite natively understands<sqlite3-types>`.
-There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python
-type to one of the supported ones.
+There are two ways to adapt Python objects to SQLite types:
+letting your object adapt itself, or using an *adapter callable*.
+The latter will take precedence above the former.
+For a library that exports a custom type,
+it may make sense to enable that type to adapt itself.
+As an application developer, it may make more sense to take direct control by
+registering custom adapter functions.
Letting your object adapt itself
""""""""""""""""""""""""""""""""
-This is a good approach if you write the class yourself. Let's suppose you have
-a class like this::
-
- class Point:
- def __init__(self, x, y):
- self.x, self.y = x, y
-
-Now you want to store the point in a single SQLite column. First you'll have to
-choose one of the supported types to be used for representing the point.
-Let's just use str and separate the coordinates using a semicolon. Then you need
-to give your class a method ``__conform__(self, protocol)`` which must return
-the converted value. The parameter *protocol* will be :class:`PrepareProtocol`.
+Suppose we have a ``Point`` class that represents a pair of coordinates,
+``x`` and ``y``, in a Cartesian coordinate system.
+The coordinate pair will be stored as a text string in the database,
+using a semicolon to separate the coordinates.
+This can be implemented by adding a ``__conform__(self, protocol)``
+method which returns the adapted value.
+The object passed to *protocol* will be of type :class:`PrepareProtocol`.
.. literalinclude:: ../includes/sqlite3/adapter_point_1.py
@@ -1270,26 +1288,20 @@
Registering an adapter callable
"""""""""""""""""""""""""""""""
-The other possibility is to create a function that converts the type to the
-string representation and register the function with :meth:`register_adapter`.
+The other possibility is to create a function that converts the Python object
+to an SQLite-compatible type.
+This function can then be registered using :func:`register_adapter`.
.. literalinclude:: ../includes/sqlite3/adapter_point_2.py
-The :mod:`sqlite3` module has two default adapters for Python's built-in
-:class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose
-we want to store :class:`datetime.datetime` objects not in ISO representation,
-but as a Unix timestamp.
-
-.. literalinclude:: ../includes/sqlite3/adapter_datetime.py
-
Converting SQLite values to custom Python types
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-Writing an adapter lets you send custom Python types to SQLite. But to make it
-really useful we need to make the Python to SQLite to Python roundtrip work.
-
-Enter converters.
+Writing an adapter lets you convert *from* custom Python types *to* SQLite
+values.
+To be able to convert *from* SQLite values *to* custom Python types,
+we use *converters*.
Let's go back to the :class:`Point` class. We stored the x and y coordinates
separated via semicolons as strings in SQLite.
@@ -1299,8 +1311,8 @@
.. note::
- Converter functions **always** get called with a :class:`bytes` object, no
- matter under which data type you sent the value to SQLite.
+ Converter functions are **always** passed a :class:`bytes` object,
+ no matter the underlying SQLite data type.
::
@@ -1308,17 +1320,17 @@
x, y = map(float, s.split(b";"))
return Point(x, y)
-Now you need to make the :mod:`sqlite3` module know that what you select from
-the database is actually a point. There are two ways of doing this:
+We now need to tell ``sqlite3`` when it should convert a given SQLite value.
+This is done when connecting to a database, using the *detect_types* parameter
+of :func:`connect`. There are three options:
-* Implicitly via the declared type
+* Implicit: set *detect_types* to :const:`PARSE_DECLTYPES`
+* Explicit: set *detect_types* to :const:`PARSE_COLNAMES`
+* Both: set *detect_types* to
+ ``sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES``.
+ Colum names take precedence over declared types.
-* Explicitly via the column name
-
-Both ways are described in section :ref:`sqlite3-module-contents`, in the entries
-for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`.
-
-The following example illustrates both approaches.
+The following example illustrates the implicit and explicit approaches:
.. literalinclude:: ../includes/sqlite3/converter_point.py
@@ -1352,6 +1364,52 @@
offsets in timestamps, either leave converters disabled, or register an
offset-aware converter with :func:`register_converter`.
+
+.. _sqlite3-adapter-converter-recipes:
+
+Adapter and Converter Recipes
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+This section shows recipes for common adapters and converters.
+
+.. code-block::
+
+ import datetime
+ import sqlite3
+
+ def adapt_date_iso(val):
+ """Adapt datetime.date to ISO 8601 date."""
+ return val.isoformat()
+
+ def adapt_datetime_iso(val):
+ """Adapt datetime.datetime to timezone-naive ISO 8601 date."""
+ return val.isoformat()
+
+ def adapt_datetime_epoch(val)
+ """Adapt datetime.datetime to Unix timestamp."""
+ return int(val.timestamp())
+
+ sqlite3.register_adapter(datetime.date, adapt_date_iso)
+ sqlite3.register_adapter(datetime.datetime, adapt_datetime_iso)
+ sqlite3.register_adapter(datetime.datetime, adapt_datetime_epoch)
+
+ def convert_date(val):
+ """Convert ISO 8601 date to datetime.date object."""
+ return datetime.date.fromisoformat(val)
+
+ def convert_datetime(val):
+ """Convert ISO 8601 datetime to datetime.datetime object."""
+ return datetime.datetime.fromisoformat(val)
+
+ def convert_timestamp(val):
+ """Convert Unix epoch timestamp to datetime.datetime object."""
+ return datetime.datetime.fromtimestamp(val)
+
+ sqlite3.register_converter("date", convert_date)
+ sqlite3.register_converter("datetime", convert_datetime)
+ sqlite3.register_converter("timestamp", convert_timestamp)
+
+
.. _sqlite3-controlling-transactions:
Controlling Transactions
diff --git a/Modules/_sqlite/clinic/module.c.h b/Modules/_sqlite/clinic/module.c.h
index b088f4b..a5a900e 100644
--- a/Modules/_sqlite/clinic/module.c.h
+++ b/Modules/_sqlite/clinic/module.c.h
@@ -197,10 +197,10 @@
}
PyDoc_STRVAR(pysqlite_register_adapter__doc__,
-"register_adapter($module, type, caster, /)\n"
+"register_adapter($module, type, adapter, /)\n"
"--\n"
"\n"
-"Registers an adapter with sqlite3\'s adapter registry.");
+"Register a function to adapt Python objects to SQLite values.");
#define PYSQLITE_REGISTER_ADAPTER_METHODDEF \
{"register_adapter", _PyCFunction_CAST(pysqlite_register_adapter), METH_FASTCALL, pysqlite_register_adapter__doc__},
@@ -228,10 +228,10 @@
}
PyDoc_STRVAR(pysqlite_register_converter__doc__,
-"register_converter($module, name, converter, /)\n"
+"register_converter($module, typename, converter, /)\n"
"--\n"
"\n"
-"Registers a converter with sqlite3.");
+"Register a function to convert SQLite values to Python objects.");
#define PYSQLITE_REGISTER_CONVERTER_METHODDEF \
{"register_converter", _PyCFunction_CAST(pysqlite_register_converter), METH_FASTCALL, pysqlite_register_converter__doc__},
@@ -332,4 +332,4 @@
exit:
return return_value;
}
-/*[clinic end generated code: output=10c4f942dc9f0c79 input=a9049054013a1b77]*/
+/*[clinic end generated code: output=fe62f28efd8a5c63 input=a9049054013a1b77]*/
diff --git a/Modules/_sqlite/module.c b/Modules/_sqlite/module.c
index 3f69427..8c64e41 100644
--- a/Modules/_sqlite/module.c
+++ b/Modules/_sqlite/module.c
@@ -138,16 +138,16 @@ pysqlite_enable_shared_cache_impl(PyObject *module, int do_enable)
_sqlite3.register_adapter as pysqlite_register_adapter
type: object(type='PyTypeObject *')
- caster: object
+ adapter as caster: object
/
-Registers an adapter with sqlite3's adapter registry.
+Register a function to adapt Python objects to SQLite values.
[clinic start generated code]*/
static PyObject *
pysqlite_register_adapter_impl(PyObject *module, PyTypeObject *type,
PyObject *caster)
-/*[clinic end generated code: output=a287e8db18e8af23 input=b4bd87afcadc535d]*/
+/*[clinic end generated code: output=a287e8db18e8af23 input=29a5e0f213030242]*/
{
int rc;
@@ -172,17 +172,17 @@ pysqlite_register_adapter_impl(PyObject *module, PyTypeObject *type,
/*[clinic input]
_sqlite3.register_converter as pysqlite_register_converter
- name as orig_name: unicode
+ typename as orig_name: unicode
converter as callable: object
/
-Registers a converter with sqlite3.
+Register a function to convert SQLite values to Python objects.
[clinic start generated code]*/
static PyObject *
pysqlite_register_converter_impl(PyObject *module, PyObject *orig_name,
PyObject *callable)
-/*[clinic end generated code: output=a2f2bfeed7230062 input=90f645419425d6c4]*/
+/*[clinic end generated code: output=a2f2bfeed7230062 input=159a444971b40378]*/
{
PyObject* name = NULL;
PyObject* retval = NULL;
