New submission from Nebelhom <[email protected]>:
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.
----------
assignee: docs@python
components: Documentation
files: sqlite_code_update.patch
keywords: patch
messages: 148448
nosy: Nebelhom, docs@python
priority: normal
severity: normal
status: open
title: sqlite3 code adjustments
versions: Python 3.3
Added file: http://bugs.python.org/file23793/sqlite_code_update.patch
_______________________________________
Python tracker <[email protected]>
<http://bugs.python.org/issue13491>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
