Skip to content

Commit 598e26a

Browse files
pythongh-90016: Reword sqlite3 adapter/converter docs
1 parent e5d8dbd commit 598e26a

4 files changed

Lines changed: 114 additions & 119 deletions

File tree

‎Doc/includes/sqlite3/converter_point.py‎

Lines changed: 7 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -5,40 +5,33 @@ def __init__(self, x, y):
55
self.x, self.y = x, y
66

77
def __repr__(self):
8-
return "(%f;%f)" % (self.x, self.y)
8+
return f"Point({self.x}, {self.y})"
99

1010
def adapt_point(point):
11-
return ("%f;%f" % (point.x, point.y)).encode('ascii')
11+
return ("%f;%f" % (point.x, point.y)).encode("utf-8")
1212

1313
def convert_point(s):
1414
x, y = list(map(float, s.split(b";")))
1515
return Point(x, y)
1616

17-
# Register the adapter
17+
# Register the adapter and converter
1818
sqlite3.register_adapter(Point, adapt_point)
19-
20-
# Register the converter
2119
sqlite3.register_converter("point", convert_point)
2220

21+
# 1) Parse using declared types
2322
p = Point(4.0, -3.2)
24-
25-
#########################
26-
# 1) Using declared types
2723
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
28-
cur = con.cursor()
29-
cur.execute("create table test(p point)")
24+
cur = con.execute("create table test(p point)")
3025

3126
cur.execute("insert into test(p) values (?)", (p,))
3227
cur.execute("select p from test")
3328
print("with declared types:", cur.fetchone()[0])
3429
cur.close()
3530
con.close()
3631

37-
#######################
38-
# 1) Using column names
32+
# 2) Parse using column names
3933
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
40-
cur = con.cursor()
41-
cur.execute("create table test(p)")
34+
cur = con.execute("create table test(p)")
4235

4336
cur.execute("insert into test(p) values (?)", (p,))
4437
cur.execute('select p as "p [point]" from test')

‎Doc/library/sqlite3.rst‎

Lines changed: 78 additions & 76 deletions
Original file line numberDiff line numberDiff line change
@@ -199,31 +199,37 @@ Module functions and constants
199199

200200
.. data:: PARSE_DECLTYPES
201201

202-
This constant is meant to be used with the *detect_types* parameter of the
203-
:func:`connect` function.
202+
Use this flag with the *detect_types* keyword of :meth:`connect` to enable
203+
parsing of declared types for each column it return.
204+
The types are declared when the database table is created.
205+
:mod:`sqlite3` will look up a converter function using the first word of the
206+
declared type as the converter dictionary key.
207+
The following SQL code results in the following lookups:
204208

205-
Setting it makes the :mod:`sqlite3` module parse the declared type for each
206-
column it returns. It will parse out the first word of the declared type,
207-
i. e. for "integer primary key", it will parse out "integer", or for
208-
"number(10)" it will parse out "number". Then for that column, it will look
209-
into the converters dictionary and use the converter function registered for
210-
that type there.
209+
.. code-block:: sql
210+
CREATE TABLE test(
211+
i integer primary key, ! will look up a converter named "integer"
212+
p point, ! will look up a converter named "point"
213+
n number(10) ! will look up a converter named "number"
214+
)
215+
216+
This flag may be paired with :const:`PARSE_COLNAMES` using the ``|``
217+
operator.
211218

212219

213220
.. data:: PARSE_COLNAMES
214221

215-
This constant is meant to be used with the *detect_types* parameter of the
216-
:func:`connect` function.
222+
Use this flag with the *detect_types* keyword of :meth:`connect` to enable
223+
parsing of column names in queries.
224+
:mod:`sqlite3` will look for strings containing brackets, and will look up a
225+
converter function using the word inside the brackets as the converter
226+
dictionary key.
227+
228+
.. code-block:: sql
229+
SELECT p as "p [point]" FROM test; ! will look up converter "point"
217230
218-
Setting this makes the SQLite interface parse the column name for each column it
219-
returns. It will look for a string formed [mytype] in there, and then decide
220-
that 'mytype' is the type of the column. It will try to find an entry of
221-
'mytype' in the converters dictionary and then use the converter function found
222-
there to return the value. The column name found in :attr:`Cursor.description`
223-
does not include the type, i. e. if you use something like
224-
``'as "Expiration date [datetime]"'`` in your SQL, then we will parse out
225-
everything until the first ``'['`` for the column name and strip
226-
the preceding space: the column name would simply be "Expiration date".
231+
This flag may be paired with :const:`PARSE_DECLTYPES` using the ``|``
232+
operator.
227233

228234

229235
.. function:: connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])
@@ -250,11 +256,12 @@ Module functions and constants
250256
*detect_types* parameter and the using custom **converters** registered with the
251257
module-level :func:`register_converter` function allow you to easily do that.
252258

253-
*detect_types* defaults to 0 (i. e. off, no type detection), you can set it to
254-
any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn
255-
type detection on. Due to SQLite behaviour, types can't be detected for generated
256-
fields (for example ``max(data)``), even when *detect_types* parameter is set. In
257-
such case, the returned type is :class:`str`.
259+
*detect_types* defaults to 0 (type detection disabled).
260+
Set it to any combination of :const:`PARSE_DECLTYPES` and
261+
:const:`PARSE_COLNAMES` to enable type detection.
262+
Types cannot be detected for generated fields (for example ``max(data)``),
263+
even when *detect_types* parameter is set. In such cases, the returned type
264+
is :class:`str`.
258265

259266
By default, *check_same_thread* is :const:`True` and only the creating thread may
260267
use the connection. If set :const:`False`, the returned connection may be shared
@@ -309,21 +316,24 @@ Module functions and constants
309316
Added the ``sqlite3.connect/handle`` auditing event.
310317

311318

312-
.. function:: register_converter(typename, callable)
319+
.. function:: register_converter(type, callable)
313320

314-
Registers a callable to convert a bytestring from the database into a custom
315-
Python type. The callable will be invoked for all database values that are of
316-
the type *typename*. Confer the parameter *detect_types* of the :func:`connect`
317-
function for how the type detection works. Note that *typename* and the name of
318-
the type in your query are matched in case-insensitive manner.
321+
Register *callable* to convert SQLite *type* into a Python type.
322+
*callable* is invoked for all values of type *typename*.
323+
Confer the parameter *detect_types* of :meth:`Connection.connect`
324+
regarding how type detection works.
325+
326+
.. note::
327+
*type* and the name of the type in your query are matched in a
328+
case-insensitive manner.
319329

320330

321331
.. function:: register_adapter(type, callable)
322332

323-
Registers a callable to convert the custom Python type *type* into one of
324-
SQLite's supported types. The callable *callable* accepts as single parameter
325-
the Python value, and must return a value of the following types: int,
326-
float, str or bytes.
333+
Register *callable* to adapt Python *type* into an SQLite type.
334+
*callable* is called with the Python value and must return a valid SQLite
335+
type:
336+
:class:`int`, :class:`float`, :class:`str`, :class:`bytes`, or :const:`None`.
327337

328338

329339
.. function:: complete_statement(sql)
@@ -1205,42 +1215,40 @@ you can let the :mod:`sqlite3` module convert SQLite types to different Python
12051215
types via converters.
12061216

12071217

1208-
Using adapters to store additional Python types in SQLite databases
1209-
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1218+
Using adapters to store custom Python types in SQLite databases
1219+
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
12101220

1211-
As described before, SQLite supports only a limited set of types natively. To
1212-
use other Python types with SQLite, you must **adapt** them to one of the
1213-
sqlite3 module's supported types for SQLite: one of NoneType, int, float,
1214-
str, bytes.
1221+
To store custom Python types in SQLite databases, **adapt** them one of the
1222+
basic types supported by SQLite:
1223+
:class:`int`, :class:`float`, :class:`str`, :class:`bytes`, or :const:`None`.
12151224

1216-
There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python
1217-
type to one of the supported ones.
1225+
There are two ways to adapt Python objects to SQLite types:
1226+
letting your object adapt itself, or using an adapter function.
1227+
The latter will take precedence above the former. For a library that exports a
1228+
custom type, it may make sense to let that type be able to adapt itself. As an
1229+
application developer, it may make more sense to take control, and register
1230+
custom adapter functions.
12181231

12191232

12201233
Letting your object adapt itself
12211234
""""""""""""""""""""""""""""""""
12221235

1223-
This is a good approach if you write the class yourself. Let's suppose you have
1224-
a class like this::
1225-
1226-
class Point:
1227-
def __init__(self, x, y):
1228-
self.x, self.y = x, y
1229-
1230-
Now you want to store the point in a single SQLite column. First you'll have to
1231-
choose one of the supported types to be used for representing the point.
1232-
Let's just use str and separate the coordinates using a semicolon. Then you need
1233-
to give your class a method ``__conform__(self, protocol)`` which must return
1234-
the converted value. The parameter *protocol* will be :class:`PrepareProtocol`.
1236+
Suppose we have `Point` class that represents a pair of coordinates,
1237+
``x`` and ``y``, in a Cartesian coordinate system.
1238+
We want to store the coordinate pair as a text string in the database,
1239+
using a semicolon to separate the coordinates.
1240+
We implement this by adding a ``__conform__(self, protocol)`` method which
1241+
returns the adapted value. *protocol* will be :class:`PrepareProtocol`.
12351242

12361243
.. literalinclude:: ../includes/sqlite3/adapter_point_1.py
12371244

12381245

12391246
Registering an adapter callable
12401247
"""""""""""""""""""""""""""""""
12411248

1242-
The other possibility is to create a function that converts the type to the
1243-
string representation and register the function with :meth:`register_adapter`.
1249+
Continuing the above example, let's rewrite it using an adapter function.
1250+
We use :meth:`register_adapter` to add an adapter function that takes a Python
1251+
type as its argument, and returns an SQLite compatible type.
12441252

12451253
.. literalinclude:: ../includes/sqlite3/adapter_point_2.py
12461254

@@ -1255,39 +1263,33 @@ but as a Unix timestamp.
12551263
Converting SQLite values to custom Python types
12561264
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
12571265

1258-
Writing an adapter lets you send custom Python types to SQLite. But to make it
1259-
really useful we need to make the Python to SQLite to Python roundtrip work.
1260-
1261-
Enter converters.
1262-
1263-
Let's go back to the :class:`Point` class. We stored the x and y coordinates
1264-
separated via semicolons as strings in SQLite.
1266+
To be able to convert SQLite value to custom Python types, we use _converters_.
12651267

1266-
First, we'll define a converter function that accepts the string as a parameter
1267-
and constructs a :class:`Point` object from it.
1268+
Let's revisit the ``Point`` class example from above;
1269+
the coordinate pair is stored in the database as a semicolon separated string.
1270+
We define a converter that accept a string, and return a ``Point`` object.
12681271

12691272
.. note::
12701273

1271-
Converter functions **always** get called with a :class:`bytes` object, no
1272-
matter under which data type you sent the value to SQLite.
1274+
Converter functions **always** are passed a :class:`bytes` object,
1275+
no matter the underlying SQLite data type.
12731276

12741277
::
12751278

12761279
def convert_point(s):
12771280
x, y = map(float, s.split(b";"))
12781281
return Point(x, y)
12791282

1280-
Now you need to make the :mod:`sqlite3` module know that what you select from
1281-
the database is actually a point. There are two ways of doing this:
1282-
1283-
* Implicitly via the declared type
1284-
1285-
* Explicitly via the column name
1283+
We now need to tell :mod:`sqlite3` when it should convert a given SQLite value.
1284+
This is done when connecting to a database, using the *detect_types* keyword of
1285+
:meth:`connect`. We've got three options:
12861286

1287-
Both ways are described in section :ref:`sqlite3-module-contents`, in the entries
1288-
for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`.
1287+
* Implicit: set *detect_types* to :const:`PARSE_DECLTYPES`
1288+
* Explicit: set *detect_types* to :const:`PARSE_COLNAMES`
1289+
* Both: set *detect_types* to
1290+
``sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES``
12891291

1290-
The following example illustrates both approaches.
1292+
The following example illustrates the implicit and explicit approach:
12911293

12921294
.. literalinclude:: ../includes/sqlite3/converter_point.py
12931295

‎Modules/_sqlite/clinic/module.c.h‎

Lines changed: 16 additions & 16 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)