@@ -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
12051215types 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
12201233Letting 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
12391246Registering 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.
12551263Converting 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
0 commit comments