New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Fixes for sqlite3 doc #57700
Comments
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)
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¶
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
# 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:
----------------------------------------------------------------------------- Cursor.executemany(sql, seq_of_parameters)
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.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
------------------------------------------------------------------------------- 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:
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,)) #######################
# 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,)) 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:
|
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 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 Thanks. Johannes P.S. Any suggestions where to look in the doc next until your packaging On Mon, Dec 5, 2011 at 4:50 PM, Éric Araujo <report@bugs.python.org> wrote:
|
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:
|
Ah, and I added a working example of a custom text_factory function to Doc/includes/sqlite3/text_factory.py, too. |
|
No. There's no mention of createdb.py anywhere.
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': New changeset ba5b337ecc27 by Petri Lehtinen in branch 'default': |
É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': New changeset 5f492397ccb8 by Petri Lehtinen in branch '3.2': New changeset 82032c64dd89 by Petri Lehtinen in branch 'default': |
All patches applied, thanks everybody! |
Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.
Show more details
GitHub fields:
bugs.python.org fields:
The text was updated successfully, but these errors were encountered: