Further integration of the documentation for the sqlite3 module. There's still

quite some content to move over from the pysqlite manual, but it's a start now.

29 files changed, 519 insertions, 0 deletions

diff --git a/Doc/lib/sqlite3/adapter_datetime.py b/Doc/lib/sqlite3/adapter_datetime.py
new file mode 100644
index 0000000..dc41ce8
--- /dev/null
+++ b/Doc/lib/sqlite3/adapter_datetime.py
@@ -0,0 +1,14 @@
+import sqlite3

+import datetime, time

+

+def adapt_datetime(ts):

+    return time.mktime(ts.timetuple())

+

+sqlite3.register_adapter(datetime.datetime, adapt_datetime)

+

+con = sqlite3.connect(":memory:")

+cur = con.cursor()

+

+now = datetime.datetime.now()

+cur.execute("select ?", (now,))

+print cur.fetchone()[0]

diff --git a/Doc/lib/sqlite3/adapter_point_1.py b/Doc/lib/sqlite3/adapter_point_1.py
new file mode 100644
index 0000000..7b0c51e
--- /dev/null
+++ b/Doc/lib/sqlite3/adapter_point_1.py
@@ -0,0 +1,17 @@
+import sqlite3

+

+class Point(object):

+    def __init__(self, x, y):

+        self.x, self.y = x, y

+

+    def __conform__(self, protocol):

+        if protocol is sqlite3.PrepareProtocol:

+            return "%f;%f" % (self.x, self.y)

+

+con = sqlite3.connect(":memory:")

+cur = con.cursor()

+

+p = Point(4.0, -3.2)

+cur.execute("select ?", (p,))

+print cur.fetchone()[0]

+

diff --git a/Doc/lib/sqlite3/adapter_point_2.py b/Doc/lib/sqlite3/adapter_point_2.py
new file mode 100644
index 0000000..3b4ab10
--- /dev/null
+++ b/Doc/lib/sqlite3/adapter_point_2.py
@@ -0,0 +1,18 @@
+import sqlite3

+

+class Point(object):

+    def __init__(self, x, y):

+        self.x, self.y = x, y

+

+def adapt_point(point):

+    return "%f;%f" % (point.x, point.y)

+

+sqlite3.register_adapter(Point, adapt_point)

+

+con = sqlite3.connect(":memory:")

+cur = con.cursor()

+

+p = Point(4.0, -3.2)

+cur.execute("select ?", (p,))

+print cur.fetchone()[0]

+

diff --git a/Doc/lib/sqlite3/collation_reverse.py b/Doc/lib/sqlite3/collation_reverse.py
new file mode 100644
index 0000000..107f49d
--- /dev/null
+++ b/Doc/lib/sqlite3/collation_reverse.py
@@ -0,0 +1,15 @@
+import sqlite3

+

+def collate_reverse(string1, string2):

+    return -cmp(string1, string2)

+

+con = sqlite3.connect(":memory:")

+con.create_collation("reverse", collate_reverse)

+

+cur = con.cursor()

+cur.execute("create table test(x)")

+cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])

+cur.execute("select x from test order by x collate reverse")

+for row in cur:

+    print row

+con.close()

diff --git a/Doc/lib/sqlite3/complete_statement.py b/Doc/lib/sqlite3/complete_statement.py
new file mode 100644
index 0000000..89fc250
--- /dev/null
+++ b/Doc/lib/sqlite3/complete_statement.py
@@ -0,0 +1,30 @@
+# A minimal SQLite shell for experiments
+
+import sqlite3
+
+con = sqlite3.connect(":memory:")
+con.isolation_level = None
+cur = con.cursor()
+
+buffer = ""
+
+print "Enter your SQL commands to execute in sqlite3."
+print "Enter a blank line to exit."
+
+while True:
+    line = raw_input()
+    if line == "":
+        break
+    buffer += line
+    if sqlite3.complete_statement(buffer):
+        try:
+            buffer = buffer.strip()
+            cur.execute(buffer)
+
+            if buffer.lstrip().upper().startswith("SELECT"):
+                print cur.fetchall()
+        except sqlite3.Error, e:
+            print "An error occured:", e.args[0]
+        buffer = ""
+
+con.close()
diff --git a/Doc/lib/sqlite3/connect_db_1.py b/Doc/lib/sqlite3/connect_db_1.py
new file mode 100644
index 0000000..8a1437d
--- /dev/null
+++ b/Doc/lib/sqlite3/connect_db_1.py
@@ -0,0 +1,3 @@
+import sqlite3

+

+con = sqlite3.connect("mydb")

diff --git a/Doc/lib/sqlite3/connect_db_2.py b/Doc/lib/sqlite3/connect_db_2.py
new file mode 100644
index 0000000..303501d
--- /dev/null
+++ b/Doc/lib/sqlite3/connect_db_2.py
@@ -0,0 +1,3 @@
+import sqlite3

+

+con = sqlite3.connect(":memory:")

diff --git a/Doc/lib/sqlite3/converter_point.py b/Doc/lib/sqlite3/converter_point.py
new file mode 100644
index 0000000..eecd1dc3
--- /dev/null
+++ b/Doc/lib/sqlite3/converter_point.py
@@ -0,0 +1,47 @@
+import sqlite3

+

+class Point(object):

+    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 = 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()

diff --git a/Doc/lib/sqlite3/countcursors.py b/Doc/lib/sqlite3/countcursors.py
new file mode 100644
index 0000000..13ba6a6
--- /dev/null
+++ b/Doc/lib/sqlite3/countcursors.py
@@ -0,0 +1,15 @@
+import sqlite3

+

+class CountCursorsConnection(sqlite3.Connection):

+    def __init__(self, *args, **kwargs):

+        sqlite3.Connection.__init__(self, *args, **kwargs)

+        self.numcursors = 0

+

+    def cursor(self, *args, **kwargs):

+        self.numcursors += 1

+        return sqlite3.Connection.cursor(self, *args, **kwargs)

+

+con = sqlite3.connect(":memory:", factory=CountCursorsConnection)

+cur1 = con.cursor()

+cur2 = con.cursor()

+print con.numcursors

diff --git a/Doc/lib/sqlite3/createdb.py b/Doc/lib/sqlite3/createdb.py
new file mode 100644
index 0000000..2fca21f2
--- /dev/null
+++ b/Doc/lib/sqlite3/createdb.py
@@ -0,0 +1,28 @@
+# Not referenced from the documentation, but builds the database file the other

+# code snippets expect.

+

+import sqlite3

+import os

+

+DB_FILE = "mydb"

+

+if os.path.exists(DB_FILE):

+    os.remove(DB_FILE)

+

+con = sqlite3.connect(DB_FILE)

+cur = con.cursor()

+cur.execute("""

+        create table people

+        (

+          name_last      varchar(20),

+          age            integer

+        )

+        """)

+

+cur.execute("insert into people (name_last, age) values ('Yeltsin',   72)")

+cur.execute("insert into people (name_last, age) values ('Putin',     51)")

+

+con.commit()

+

+cur.close()

+con.close()

diff --git a/Doc/lib/sqlite3/execsql_fetchonerow.py b/Doc/lib/sqlite3/execsql_fetchonerow.py
new file mode 100644
index 0000000..51b206d
--- /dev/null
+++ b/Doc/lib/sqlite3/execsql_fetchonerow.py
@@ -0,0 +1,17 @@
+import sqlite3

+

+con = sqlite3.connect("mydb")

+

+cur = con.cursor()

+SELECT = "select name_last, age from people order by age, name_last"

+

+# 1. Iterate over the rows available from the cursor, unpacking the

+# resulting sequences to yield their elements (name_last, age):

+cur.execute(SELECT)

+for (name_last, age) in cur:

+    print '%s is %d years old.' % (name_last, age)

+

+# 2. Equivalently:

+cur.execute(SELECT)

+for row in cur:

+    print '%s is %d years old.' % (row[0], row[1])

diff --git a/Doc/lib/sqlite3/execsql_printall_1.py b/Doc/lib/sqlite3/execsql_printall_1.py
new file mode 100644
index 0000000..b6b2e1e
--- /dev/null
+++ b/Doc/lib/sqlite3/execsql_printall_1.py
@@ -0,0 +1,13 @@
+import sqlite3

+

+# Create a connection to the database file "mydb":

+con = sqlite3.connect("mydb")

+

+# Get a Cursor object that operates in the context of Connection con:

+cur = con.cursor()

+

+# Execute the SELECT statement:

+cur.execute("select * from people order by age")

+

+# Retrieve all rows as a sequence and print that sequence:

+print cur.fetchall()

diff --git a/Doc/lib/sqlite3/execute_1.py b/Doc/lib/sqlite3/execute_1.py
new file mode 100644
index 0000000..a94cf89
--- /dev/null
+++ b/Doc/lib/sqlite3/execute_1.py
@@ -0,0 +1,11 @@
+import sqlite3

+

+con = sqlite3.connect("mydb")

+

+cur = con.cursor()

+

+who = "Yeltsin"

+age = 72

+

+cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))

+print cur.fetchone()

diff --git a/Doc/lib/sqlite3/execute_2.py b/Doc/lib/sqlite3/execute_2.py
new file mode 100644
index 0000000..28318cc
--- /dev/null
+++ b/Doc/lib/sqlite3/execute_2.py
@@ -0,0 +1,13 @@
+import sqlite3

+

+con = sqlite3.connect("mydb")

+

+cur = con.cursor()

+

+who = "Yeltsin"

+age = 72

+

+cur.execute("select name_last, age from people where name_last=:who and age=:age",

+    {"who": who, "age": age})

+print cur.fetchone()

+

diff --git a/Doc/lib/sqlite3/execute_3.py b/Doc/lib/sqlite3/execute_3.py
new file mode 100644
index 0000000..2f02372e
--- /dev/null
+++ b/Doc/lib/sqlite3/execute_3.py
@@ -0,0 +1,14 @@
+import sqlite3

+

+con = sqlite3.connect("mydb")

+

+cur = con.cursor()

+

+who = "Yeltsin"

+age = 72

+

+cur.execute("select name_last, age from people where name_last=:who and age=:age",

+    locals())

+print cur.fetchone()

+

+

diff --git a/Doc/lib/sqlite3/executemany_1.py b/Doc/lib/sqlite3/executemany_1.py
new file mode 100644
index 0000000..c0ab7c1
--- /dev/null
+++ b/Doc/lib/sqlite3/executemany_1.py
@@ -0,0 +1,24 @@
+import sqlite3

+

+class IterChars:

+    def __init__(self):

+        self.count = ord('a')

+

+    def __iter__(self):

+        return self

+

+    def next(self):

+        if self.count > ord('z'):

+            raise StopIteration

+        self.count += 1

+        return (chr(self.count - 1),) # this is a 1-tuple

+

+con = sqlite3.connect(":memory:")

+cur = con.cursor()

+cur.execute("create table characters(c)")

+

+theIter = IterChars()

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

+

+cur.execute("select c from characters")

+print cur.fetchall()

diff --git a/Doc/lib/sqlite3/executemany_2.py b/Doc/lib/sqlite3/executemany_2.py
new file mode 100644
index 0000000..b16f93a
--- /dev/null
+++ b/Doc/lib/sqlite3/executemany_2.py
@@ -0,0 +1,15 @@
+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()

diff --git a/Doc/lib/sqlite3/executescript.py b/Doc/lib/sqlite3/executescript.py
new file mode 100644
index 0000000..2c04066
--- /dev/null
+++ b/Doc/lib/sqlite3/executescript.py
@@ -0,0 +1,24 @@
+import sqlite3

+

+con = sqlite3.connect(":memory:")

+cur = con.cursor()

+cur.executescript("""

+    create table person(

+        firstname,

+        lastname,

+        age

+    );

+

+    create table book(

+        title,

+        author,

+        published

+    );

+

+    insert into book(title, author, published)

+    values (

+        'Dirk Gently''s Holistic Detective Agency

+        'Douglas Adams',

+        1987

+    );

+    """)

diff --git a/Doc/lib/sqlite3/insert_more_people.py b/Doc/lib/sqlite3/insert_more_people.py
new file mode 100644
index 0000000..7daa88b
--- /dev/null
+++ b/Doc/lib/sqlite3/insert_more_people.py
@@ -0,0 +1,17 @@
+import sqlite3

+

+con = sqlite3.connect("mydb")

+

+cur = con.cursor()

+

+newPeople = (

+    ('Lebed'       , 53),

+    ('Zhirinovsky' , 57),

+  )

+

+for person in newPeople:

+    cur.execute("insert into people (name_last, age) values (?, ?)", person)

+

+# The changes will not be saved unless the transaction is committed explicitly:

+con.commit()

+

diff --git a/Doc/lib/sqlite3/md5func.py b/Doc/lib/sqlite3/md5func.py
new file mode 100644
index 0000000..eeb41ea
--- /dev/null
+++ b/Doc/lib/sqlite3/md5func.py
@@ -0,0 +1,11 @@
+import sqlite3

+import md5

+

+def md5sum(t):

+    return md5.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]

diff --git a/Doc/lib/sqlite3/mysumaggr.py b/Doc/lib/sqlite3/mysumaggr.py
new file mode 100644
index 0000000..b398726
--- /dev/null
+++ b/Doc/lib/sqlite3/mysumaggr.py
@@ -0,0 +1,20 @@
+import sqlite3

+

+class MySum:

+    def __init__(self):

+        self.count = 0

+

+    def step(self, value):

+        self.count += value

+

+    def finalize(self):

+        return self.count

+

+con = sqlite3.connect(":memory:")

+con.create_aggregate("mysum", 1, MySum)

+cur = con.cursor()

+cur.execute("create table test(i)")

+cur.execute("insert into test(i) values (1)")

+cur.execute("insert into test(i) values (2)")

+cur.execute("select mysum(i) from test")

+print cur.fetchone()[0]

diff --git a/Doc/lib/sqlite3/parse_colnames.py b/Doc/lib/sqlite3/parse_colnames.py
new file mode 100644
index 0000000..bbb93e9
--- /dev/null
+++ b/Doc/lib/sqlite3/parse_colnames.py
@@ -0,0 +1,8 @@
+import sqlite3

+import datetime

+

+con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)

+cur = con.cursor()

+cur.execute('select ? as "x [timestamp]"', (datetime.datetime.now(),))

+dt = cur.fetchone()[0]

+print dt, type(dt)

diff --git a/Doc/lib/sqlite3/pysqlite_datetime.py b/Doc/lib/sqlite3/pysqlite_datetime.py
new file mode 100644
index 0000000..f9dfa14
--- /dev/null
+++ b/Doc/lib/sqlite3/pysqlite_datetime.py
@@ -0,0 +1,20 @@
+import sqlite3

+import datetime

+

+con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)

+cur = con.cursor()

+cur.execute("create table test(d date, ts timestamp)")

+

+today = datetime.date.today()

+now = datetime.datetime.now()

+

+cur.execute("insert into test(d, ts) values (?, ?)", (today, now))

+cur.execute("select d, ts from test")

+row = cur.fetchone()

+print today, "=>", row[0], type(row[0])

+print now, "=>", row[1], type(row[1])

+

+cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')

+row = cur.fetchone()

+print "current_date", row[0], type(row[0])

+print "current_timestamp", row[1], type(row[1])

diff --git a/Doc/lib/sqlite3/row_factory.py b/Doc/lib/sqlite3/row_factory.py
new file mode 100644
index 0000000..3597459
--- /dev/null
+++ b/Doc/lib/sqlite3/row_factory.py
@@ -0,0 +1,13 @@
+import sqlite3

+

+def dict_factory(cursor, row):

+    d = {}

+    for idx, col in enumerate(cursor.description):

+        d[col[0]] = row[idx]

+    return d

+

+con = sqlite3.connect(":memory:")

+con.row_factory = dict_factory

+cur = con.cursor()

+cur.execute("select 1 as a")

+print cur.fetchone()["a"]

diff --git a/Doc/lib/sqlite3/rowclass.py b/Doc/lib/sqlite3/rowclass.py
new file mode 100644
index 0000000..3fa0b87
--- /dev/null
+++ b/Doc/lib/sqlite3/rowclass.py
@@ -0,0 +1,12 @@
+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"]
diff --git a/Doc/lib/sqlite3/shared_cache.py b/Doc/lib/sqlite3/shared_cache.py
new file mode 100644
index 0000000..bf1d7b4
--- /dev/null
+++ b/Doc/lib/sqlite3/shared_cache.py
@@ -0,0 +1,6 @@
+import sqlite3
+
+# The shared cache is only available in SQLite versions 3.3.3 or later
+# See the SQLite documentaton for details.
+
+sqlite3.enable_shared_cache(True)
diff --git a/Doc/lib/sqlite3/shortcut_methods.py b/Doc/lib/sqlite3/shortcut_methods.py
new file mode 100644
index 0000000..93c9547
--- /dev/null
+++ b/Doc/lib/sqlite3/shortcut_methods.py
@@ -0,0 +1,22 @@
+import sqlite3
+
+persons = [
+    ("Hugo", "Boss"),
+    ("Calvin", "Klein")
+    ]
+
+con = sqlite3.connect(":memory:")
+
+# Create the table
+con.execute("create table person(firstname, lastname)")
+
+# Fill the table
+con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)
+
+# Print the table contents
+for row in con.execute("select firstname, lastname from person"):
+    print row
+
+# Using a dummy WHERE clause to not let SQLite take the shortcut table deletes.
+print "I just deleted", con.execute("delete from person where 1=1").rowcount, "rows"
+
diff --git a/Doc/lib/sqlite3/simple_tableprinter.py b/Doc/lib/sqlite3/simple_tableprinter.py
new file mode 100644
index 0000000..6368668
--- /dev/null
+++ b/Doc/lib/sqlite3/simple_tableprinter.py
@@ -0,0 +1,26 @@
+import sqlite3

+

+FIELD_MAX_WIDTH = 20

+TABLE_NAME = 'people'

+SELECT = 'select * from %s order by age, name_last' % TABLE_NAME

+

+con = sqlite3.connect("mydb")

+

+cur = con.cursor()

+cur.execute(SELECT)

+

+# Print a header.

+for fieldDesc in cur.description:

+    print fieldDesc[0].ljust(FIELD_MAX_WIDTH) ,

+print # Finish the header with a newline.

+print '-' * 78

+

+# For each row, print the value of each field left-justified within

+# the maximum possible width of that field.

+fieldIndices = range(len(cur.description))

+for row in cur:

+    for fieldIndex in fieldIndices:

+        fieldValue = str(row[fieldIndex])

+        print fieldValue.ljust(FIELD_MAX_WIDTH) ,

+

+    print # Finish the row with a newline.

diff --git a/Doc/lib/sqlite3/text_factory.py b/Doc/lib/sqlite3/text_factory.py
new file mode 100644
index 0000000..cf2ae92
--- /dev/null
+++ b/Doc/lib/sqlite3/text_factory.py
@@ -0,0 +1,43 @@
+import sqlite3
+
+con = sqlite3.connect(":memory:")
+cur = con.cursor()
+
+# Create the table
+con.execute("create table person(lastname, firstname)")
+
+AUSTRIA = u"\xd6sterreich"
+
+# by default, rows are returned as Unicode
+cur.execute("select ?", (AUSTRIA,))
+row = cur.fetchone()
+assert row[0] == AUSTRIA
+
+# but we can make pysqlite 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: unicode(x, "utf-8", "ignore")
+cur.execute("select ?", ("this is latin1 and would normally create errors" + u"\xe4\xf6\xfc".encode("latin1"),))
+row = cur.fetchone()
+assert type(row[0]) == unicode
+
+# pysqlite offers a builtin 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]) == unicode
+
+cur.execute("select ?", ("Germany",))
+row = cur.fetchone()
+assert type(row[0]) == str
+