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.

It is very helpful that you review the docs.  Some obvious fixes were made when moving to Python 3 (print, etc.) but apparently the examples were not run.  Sphinx can let us run the code blocks in reST files as doctests, but it is currently not done because the docs are built with a Python 2 version.

I have reviewed your patch on our code review tool; I did not check your message (a patch is much easier :).

> I feel these should be standalone examples out of the box. the sqlite3 includes have a
> "createdb.py" file which would create the tables but it is not referenced in the
> documenta[tion]. I do not know the reasoning behind this, but I would like to have standalone
> examples in these cases.

I think the examples do not stand alone because their author wanted to create and populate a database with many entries, to demonstrate querying, and it was easier to write one script once than to either clutter the examples with long table creation code or having examples with so few rows that it would not be realistic/interesting.  To help people wanting to run the examples in the docs, we could explain that createdb.py needs to be run first.

The createdb script and other sqlite3 doc examples were added when sqlite3 was added in Python 2.5; I’m adding the module author and then-doc lead (hi Gerhard and Fred), maybe they can shed more light on this.

The updated patch looks good, I’ll commit it soon.

Hi,

thanks for your help and support, mate.

Unfortunately, no word about the createdb.py business (the last bullet
point on the list) and whether it should be referred to in the doc. Should
we just drop this from the adjustments?

Thanks.

Johannes

P.S. Any suggestions where to look in the doc next until your packaging
patch is ready for reviewing?

On Mon, Dec 5, 2011 at 4:50 PM, Éric Araujo <report@bugs.python.org> wrote:

>
> Éric Araujo <merwok@netwok.org> added the comment:
>
> The updated patch looks good, I’ll commit it soon.
>
> ----------
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue13491>
> _______________________________________
>

> Unfortunately, no word about the createdb.py business (the last bullet point on the list)
> and whether it should be referred to in the doc.
If you make the whitespace more standard, use the tempfile module (to remove the os.remove line) and use with statements, the file is actually very short (so my earlier hypothesis about createdb was wrong :).

I think we could inline it (with a literalinclude directive) or link to it (with :source:`Doc/include/sqlite3/createdb.py`).  Add something like “If you want to run these examples, run this code to create and populate the initial database” and we’re set.

Attaching an updated patch with the following changes:

- Updated to apply on current default branch

- No examples included in the documentation now require createdb.py to be run. There were only three examples that required it, but these were refactored a bit. In the Doc/includes/sqlite3 directory there are still some scripts that require the createdb.py, so I didn't nuke the file.

- I also removed the strange "The arguments to :meth:`executescript` must be :func:`bytes` objects" sentence from the patch. I assume it was a mistake, as it's wrong.

Ah, and I added a working example of a custom text_factory function to Doc/includes/sqlite3/text_factory.py, too.

> No examples included in the documentation now require createdb.py to be run.
Cool!  These fixes should make it to 2.7 too; I can propose a patch if you don’t want to backport yourself.

> In the Doc/includes/sqlite3 directory there are still some scripts that require the
> createdb.py, so I didn't nuke the file.
Do these scripts (or a README file) explain about createdb.py?

> I also removed the strange "The arguments to :meth:`executescript` must be :func:`bytes`
> objects" sentence from the patch. I assume it was a mistake, as it's wrong.
Why not change it so that it’s correct?  (Maybe looking at the file history would show where the mistake comes from.)

> Ah, and I added a working example of a custom text_factory function
Nice.

> > In the Doc/includes/sqlite3 directory there are still some scripts that require the
> > createdb.py, so I didn't nuke the file.
>
> Do these scripts (or a README file) explain about createdb.py?

No. There's no mention of createdb.py anywhere.

> > I also removed the strange "The arguments to :meth:`executescript` must be :func:`bytes`
> > objects" sentence from the patch. I assume it was a mistake, as it's wrong.
>
> Why not change it so that it’s correct?  (Maybe looking at the file history would
> show where the mistake comes from.)

It's just not in the history, it was only in OP's patch. And it's wrong, as executescript() takes an str argument, not bytes.

Having slept over this, I think execute_1.py and execute_2.py should be merged as one file, as after my patch, execute_2.py would not be runnable by itself.

This would also be a good chance to look at all the examples and have them cleaned up. That could also be a separate patch.

I think you can commit your patch, with our without merging execute_[12].py, as you think best.  Then you can do other patches (with or without pre-commit review) to fix or clean up the examples.

New changeset 9eb77d455be1 by Petri Lehtinen in branch '3.2':
Issue #13491: Fix many errors in sqlite3 documentation
http://hg.python.org/cpython/rev/9eb77d455be1

New changeset ba5b337ecc27 by Petri Lehtinen in branch 'default':
Merge branch '3.2'
http://hg.python.org/cpython/rev/ba5b337ecc27

Éric: You can make a patch for 2.7 if you want to, I left the issue open.

Here’s a patch for 2.7.  I haven’t changed the text_factory example.

The second patch is for 3.2 and contains a few additional changes that I did in my first patch.

Both patches look good to me. The text_factory example is OK on 2.7 because the OptimizedUnicode flag works correctly there.

New changeset d2cf730de195 by Petri Lehtinen in branch '2.7':
sqlite3: Port relevant documentation changes from 3.2
http://hg.python.org/cpython/rev/d2cf730de195

New changeset 5f492397ccb8 by Petri Lehtinen in branch '3.2':
sqlite3: Port documentation changes from the 2.7 branch
http://hg.python.org/cpython/rev/5f492397ccb8

New changeset 82032c64dd89 by Petri Lehtinen in branch 'default':
Merge branch '3.2'
http://hg.python.org/cpython/rev/82032c64dd89

All patches applied, thanks everybody!