The code examples for the sqlite3 library were in some cases non-functional.

With the help of Petri Lehtinen from core-mentorship, the following fixes are suggested.

NOTE: Last issue is not resolved yet, but suggestions have been made. Could you please review and decide what to do. The remaining issues have suggested fixes in the patch.

-------------------------------------------------------

Connection.create_function(name, num_params, func)

    Creates a user-defined function that you can later use from within SQL statements under the function name name. num_params is the number of parameters the function accepts, and func is a Python callable that is called as the SQL function.

    The function can return any of the types supported by SQLite: bytes, str, int, float and None.

    Example:

    import sqlite3
    import hashlib

    def md5sum(t):
        return hashlib.md5(t).hexdigest()

    con = sqlite3.connect(":memory:")
    con.create_function("md5", 1, md5sum)
    cur = con.cursor()
    cur.execute("select md5(?)", ("foo",))
    print(cur.fetchone()[0])

This script raises error:

Traceback (most recent call last):
  File "sqlexample.py", line 12, in <module>
    cur.execute("select md5(?)", ("foo",))
sqlite3.OperationalError: user-defined function raised exception

When md5sum is then run separately, the following traceback is given

>>> md5sum(("foo",))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "sqlexample.py", line 7, in md5sum
    return hashlib.md5(t).hexdigest()
TypeError: object supporting the buffer API required


Suggested fix:

Change ("foo") to (b"foo")

--------------------------------------------------------

Connection.text_factory¶

    Using this attribute you can control what objects are returned for the TEXT data type. By default, this attribute is set to str and the sqlite3 module will return Unicode objects for TEXT. If you want to return bytestrings instead, you can set it to bytes.

    For efficiency reasons, there’s also a way to return str objects only for non-ASCII data, and bytes otherwise. To activate it, set this attribute to sqlite3.OptimizedUnicode.

    You can also set it to any other callable that accepts a single bytestring parameter and returns the resulting object.

    See the following example code for illustration:

    import sqlite3

    con = sqlite3.connect(":memory:")
    cur = con.cursor()

    # Create the table
    con.execute("create table person(lastname, firstname)")

    AUSTRIA = "\xd6sterreich"

    # by default, rows are returned as Unicode
    cur.execute("select ?", (AUSTRIA,))
    row = cur.fetchone()
    assert row[0] == AUSTRIA

    # but we can make sqlite3 always return bytestrings ...
    con.text_factory = str
    cur.execute("select ?", (AUSTRIA,))
    row = cur.fetchone()
    assert type(row[0]) == str
    # the bytestrings will be encoded in UTF-8, unless you stored garbage in the
    # database ...
    assert row[0] == AUSTRIA.encode("utf-8")

    # we can also implement a custom text_factory ...
    # here we implement one that will ignore Unicode characters that cannot be
    # decoded from UTF-8
    con.text_factory = lambda x: str(x, "utf-8", "ignore")
    cur.execute("select ?", ("this is latin1 and would normally create errors" +
                             "\xe4\xf6\xfc".encode("latin1"),))
    row = cur.fetchone()
    assert type(row[0]) == str

    # sqlite3 offers a built-in optimized text_factory that will return bytestring
    # objects, if the data is in ASCII only, and otherwise return unicode objects
    con.text_factory = sqlite3.OptimizedUnicode
    cur.execute("select ?", (AUSTRIA,))
    row = cur.fetchone()
    assert type(row[0]) == str

    cur.execute("select ?", ("Germany",))
    row = cur.fetchone()
    assert type(row[0]) == str

The code example returns the following error traceback

Traceback (most recent call last):
  File "sqlexample.py", line 23, in <module>
    assert row[0] == AUSTRIA.encode("utf-8")
AssertionError

Suggested fixes:
- #Create table... -> removed as not used
- all "assert type ... str" changed to "assert type ... bytes"
- # we can also implement... code block removed
- add ":meth:`[parameters]` needs to be a bytes type otherwise a TypeError will be raised." to the doc

-----------------------------------------------------------------------------
Cursor.executemany(sql, seq_of_parameters)

    Executes an SQL command against all parameter sequences or mappings found in the sequence sql. The sqlite3 module also allows using an iterator yielding parameters instead of a sequence.

    Here’s a shorter example using a generator:

    import sqlite3

    def char_generator():
        import string
        for c in string.letters[:26]:
            yield (c,)

    con = sqlite3.connect(":memory:")
    cur = con.cursor()
    cur.execute("create table characters(c)")

    cur.executemany("insert into characters(c) values (?)", char_generator())

    cur.execute("select c from characters")
    print(cur.fetchall())

Traceback (most recent call last):
  File "sqlexample.py", line 12, in <module>
    cur.executemany("insert into characters(c) values (?)", char_generator())
  File "sqlexample.py", line 5, in char_generator
    for c in string.letters[:26]:
AttributeError: 'module' object has no attribute 'letters'

suggested fixes
- import string outside function
- string.letters changed to string.ascii_letters_lowercase

-------------------------------------------------------------------------------

11.6.5.3. 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.

Let’s go back to the 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 Point object from it.

Note

Converter functions always get called with a string, no matter under which data type you sent the value to SQLite.

def convert_point(s):
    x, y = map(float, s.split(";"))
    return Point(x, y)

Now you need to make the 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
    * Explicitly via the column name

Both ways are described in section Module functions and constants, in the entries for the constants PARSE_DECLTYPES and PARSE_COLNAMES.

The following example illustrates both approaches.

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

def convert_point(s):
    x, y = list(map(float, s.split(";")))
    return Point(x, y)

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

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.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
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print("with column names:", cur.fetchone()[0])
cur.close()
con.close()

The given code gives the following error:

Traceback (most recent call last):
  File "sqlexample.py", line 32, in <module>
    cur.execute("select p from test")
  File "sqlexample.py", line 14, in convert_point
    x, y = list(map(float, s.split(";")))
TypeError: Type str doesn't support the buffer API

suggested fixes:

def adapt_point(point):
   return ("%f;%f" % (point.x, point.y)).encode('ascii')

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

------------------------------------------------------------------------------

11.6.7.2. Accessing columns by name instead of by index¶

One useful feature of the sqlite3 module is the built-in sqlite3.Row class designed to be used as a row factory.

Rows wrapped with this class can be accessed both by index (like tuples) and case-insensitively by name:

import sqlite3

con = sqlite3.connect("mydb")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute("select name_last, age from people")
for row in cur:
    assert row[0] == row["name_last"]
    assert row["name_last"] == row["nAmE_lAsT"]
    assert row[1] == row["age"]
    assert row[1] == row["AgE"]

Gives following error:

Traceback (most recent call last):
  File "sqlexample.py", line 7, in <module>
    cur.execute("select name_last, age from people")
sqlite3.OperationalError: no such table: people

"Same error in 11.6.3 Cursor.execute() description"

Suggested fixes:
- None yet. I feel these should be standalone examples out of the box. the sqlite3 includes have a "createdb.py" file which would create the tablem but it is not referenced in the documentary. I do not know the reasoning behind this, but I would like to have standalone examples in these cases.