python · erlend-aasland · Jun 25, 2022 · May 23, 2022 · May 23, 2022 · May 23, 2022
@@ -5,40 +5,33 @@ 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;%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")
 print("with declared types:", cur.fetchone()[0])
 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')

@@ -199,31 +199,39 @@ Module functions and constants
 
 .. data:: PARSE_DECLTYPES
 
-   This constant is meant to be used with the *detect_types* parameter of the
-   :func:`connect` function.
+   Use this flag with the *detect_types* keyword of :meth:`connect` to enable
+   parsing of declared types for each column it return.
+   The types are declared when the database table is created.
+   :mod:`sqlite3` will look up a converter function using the first word of the
+   declared type as the converter dictionary key.
+   The following SQL code results in the following lookups:
 
-   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 paired with :const:`PARSE_COLNAMES` using the ``|``
+   operator.
 
 
 .. data:: PARSE_COLNAMES
 
-   This constant is meant to be used with the *detect_types* parameter of the
-   :func:`connect` function.
+   Use this flag with the *detect_types* keyword of :meth:`connect` to enable
+   parsing of column names in queries.
+   :mod:`sqlite3` will look for strings containing brackets, and will look up a
+   converter function using the word inside the brackets as the converter
+   dictionary key.
+
+   .. code-block:: sql
+
+      SELECT p as "p [point]" FROM test;  ! will look up converter "point"
 
-   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".
+   This flag may be paired with :const:`PARSE_DECLTYPES` using the ``|``
+   operator.
 
 
 .. function:: connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])
@@ -250,11 +258,12 @@ Module functions and constants
    *detect_types* parameter and the 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 of :const:`PARSE_DECLTYPES` and
+   :const:`PARSE_COLNAMES` to enable type detection.
+   Types cannot be detected for generated fields (for example ``max(data)``),
+   even when *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 +318,23 @@ Module functions and constants
       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 callable *converter* to convert SQLite type name *typename* into a
+   Python type. The converter is invoked for all SQLite values of type
+   *typename*. Confer the parameter *detect_types* of
+   :meth:`Connection.connect` regarding how type detection works.
 
+   Note: *typename* and the name of the type in your query are matched in a
+   case-insensitive manner.
 
-.. function:: register_adapter(type, callable)
 
-   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.
+.. function:: register_adapter(type, adapter)
+
+   Register callable *adapter* to adapt Python type *type* into an SQLite type.
+   The adapter is called with a Python object as its sole argument,
+   and must return a valid SQLite type:
+   :class:`int`, :class:`float`, :class:`str`, :class:`bytes`, or :const:`None`.
 
 
 .. function:: complete_statement(sql)
@@ -1205,42 +1216,40 @@ 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.
+To store custom Python types in SQLite databases, **adapt** them one of the
+basic types supported by SQLite:
+:class:`int`, :class:`float`, :class:`str`, :class:`bytes`, or :const:`None`.
 
-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 function.
+The latter will take precedence above the former. For a library that exports a
+custom type, it may make sense to let that type be able to adapt itself. As an
+application developer, it may make more sense to take control, and register
+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 ``Point`` class that represents a pair of coordinates,
+``x`` and ``y``, in a Cartesian coordinate system.
+We want to store the coordinate pair as a text string in the database,
+using a semicolon to separate the coordinates.
+We implement this by adding a ``__conform__(self, protocol)`` method which
+returns the adapted value. *protocol* will be :class:`PrepareProtocol`.
 
 .. literalinclude:: ../includes/sqlite3/adapter_point_1.py
 
 
 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`.
+Continuing the above example, let's rewrite it using an adapter function.
+We use :meth:`register_adapter` to add an adapter function that takes a Python
+type as its argument, and returns an SQLite compatible type.
 
 .. literalinclude:: ../includes/sqlite3/adapter_point_2.py
 
@@ -1255,39 +1264,33 @@ but as a Unix timestamp.
 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.
+To be able to convert SQLite value to custom Python types, we use _converters_.
 
-Enter converters.
-
-Let's go back to the :class:`Point` class. We stored the x and y coordinates
-separated via semicolons as strings in SQLite.
-
-First, we'll define a converter function that accepts the string as a parameter
-and constructs a :class:`Point` object from it.
+Let's revisit the ``Point`` class example from above;
+the coordinate pair is stored in the database as a semicolon separated string.
+We define a converter that accept a string, and return a ``Point`` object.
 
 .. 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 **always** are passed a :class:`bytes` object,
+   no matter the underlying SQLite data type.
 
 ::
 
    def convert_point(s):
        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 :mod:`sqlite3` when it should convert a given SQLite value.
+This is done when connecting to a database, using the *detect_types* keyword of
+:meth:`connect`. We've got 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``
 
-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 approach:
 
 .. literalinclude:: ../includes/sqlite3/converter_point.py
 
@@ -1321,6 +1324,47 @@ 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.
+
+.. testcode::
+
+   import sqlite3
+
+   # Timezone naive datetime adapters and converters.
+   def adapt_date(val):
+       return val.isoformat()
+
+   def adapt_datetime(val):
+       return val.isoformat(" ")
+
+   def convert_date(val):
+       return datetime.date(*map(int, val.split(b"-")))
+
+   def convert_timestamp(val):
+       datepart, timepart = val.split(b" ")
+       year, month, day = map(int, datepart.split(b"-"))
+       timepart_full = timepart.split(b".")
+       hours, minutes, seconds = map(int, timepart_full[0].split(b":"))
+       if len(timepart_full) == 2:
+           microseconds = int('{:0<6.6}'.format(timepart_full[1].decode()))
+       else:
+           microseconds = 0
+
+       val = datetime.datetime(year, month, day, hours, minutes, seconds, microseconds)
+       return val
+
+   sqlite3.register_adapter(datetime.date, adapt_date)
+   sqlite3.register_adapter(datetime.datetime, adapt_datetime)
+   sqlite3.register_converter("date", convert_date)
+   sqlite3.register_converter("timestamp", convert_timestamp)
+
+
 .. _sqlite3-controlling-transactions:
 
 Controlling Transactions

@@ -108,16 +108,16 @@ pysqlite_complete_statement_impl(PyObject *module, const char *statement)
 _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 types to SQLite types.
 [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=f96c4fb2beba002b]*/
 {
     int rc;
 
@@ -142,17 +142,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 types to Python types.
 [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=138f93f0063cb031]*/
 {
     PyObject* name = NULL;
     PyObject* retval = NULL;