[Python-checkins] [3.10] gh-90016: Reword sqlite3 adapter/converter docs (GH-93095) (#94273)
erlend-aasland
webhook-mailer at python.org
Sat Jun 25 16:55:42 EDT 2022
https://github.com/python/cpython/commit/deb4d0457ff368141a8939de6b06edc04621e831
commit: deb4d0457ff368141a8939de6b06edc04621e831
branch: 3.10
author: Erlend Egeberg Aasland <erlend.aasland at protonmail.com>
committer: erlend-aasland <erlend.aasland at protonmail.com>
date: 2022-06-25T22:55:38+02:00
summary:
[3.10] gh-90016: Reword sqlite3 adapter/converter docs (GH-93095) (#94273)
Also add adapters and converter recipes.
Co-authored-by: CAM Gerlach <CAM.Gerlach at Gerlach.CAM>
Co-authored-by: Alex Waygood <Alex.Waygood at Gmail.com.
(cherry picked from commit bd3c1c187e0e4fde5aec6835d180e9eddde8ceb6)
files:
D Doc/includes/sqlite3/adapter_datetime.py
M Doc/includes/sqlite3/converter_point.py
M Doc/library/sqlite3.rst
M Modules/_sqlite/clinic/module.c.h
M Modules/_sqlite/module.c
diff --git a/Doc/includes/sqlite3/adapter_datetime.py b/Doc/includes/sqlite3/adapter_datetime.py
deleted file mode 100644
index d5221d80c35c8..0000000000000
--- 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 5df828e336124..147807a2225ff 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 2fb2b62c6d25a..816b8825216c2 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -181,31 +181,41 @@ Module functions and constants
.. 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 (``[]``).
+
+ .. code-block:: sql
- 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".
+ 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])
@@ -229,14 +239,17 @@ Module functions and constants
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
@@ -291,21 +304,27 @@ Module functions and constants
Added the ``sqlite3.connect/handle`` auditing event.
-.. function:: register_converter(typename, callable)
+.. function:: register_converter(typename, converter)
+
+ 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.
- 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.
+ 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)
@@ -1004,33 +1023,32 @@ you can let the :mod:`sqlite3` module convert SQLite types to different Python
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
@@ -1038,26 +1056,20 @@ the converted value. The parameter *protocol* will be :class:`PrepareProtocol`.
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.
@@ -1067,8 +1079,8 @@ and constructs a :class:`Point` object from it.
.. 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.
::
@@ -1076,17 +1088,17 @@ and constructs a :class:`Point` object from it.
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:
-
-* Implicitly via the declared type
+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:
-* Explicitly via the column name
+* 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.
-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
@@ -1120,6 +1132,52 @@ timestamp converter.
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 2118cb7c42429..c634760426beb 100644
--- a/Modules/_sqlite/clinic/module.c.h
+++ b/Modules/_sqlite/clinic/module.c.h
@@ -87,10 +87,10 @@ pysqlite_enable_shared_cache(PyObject *module, PyObject *const *args, Py_ssize_t
}
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)(void(*)(void))pysqlite_register_adapter, METH_FASTCALL, pysqlite_register_adapter__doc__},
@@ -118,10 +118,10 @@ pysqlite_register_adapter(PyObject *module, PyObject *const *args, Py_ssize_t na
}
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)(void(*)(void))pysqlite_register_converter, METH_FASTCALL, pysqlite_register_converter__doc__},
@@ -222,4 +222,4 @@ pysqlite_adapt(PyObject *module, PyObject *const *args, Py_ssize_t nargs)
exit:
return return_value;
}
-/*[clinic end generated code: output=6939849a4371122d input=a9049054013a1b77]*/
+/*[clinic end generated code: output=ad3685282fedde73 input=a9049054013a1b77]*/
diff --git a/Modules/_sqlite/module.c b/Modules/_sqlite/module.c
index 8cff4e224d57c..3759098b0c3aa 100644
--- a/Modules/_sqlite/module.c
+++ b/Modules/_sqlite/module.c
@@ -152,16 +152,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;
@@ -182,17 +182,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;
More information about the Python-checkins
mailing list