From 51032d827b297e4ea0cd529d57d73cd44e0c3905 Mon Sep 17 00:00:00 2001 From: Kali Kaneko Date: Thu, 30 May 2013 04:35:35 +0900 Subject: cleanup docs --- doc/includes/sqlite3/adapter_datetime.py | 14 - doc/includes/sqlite3/adapter_point_1.py | 16 - doc/includes/sqlite3/adapter_point_2.py | 17 - doc/includes/sqlite3/apsw_example.py | 12 - doc/includes/sqlite3/authorizer.py | 26 - doc/includes/sqlite3/collation_reverse.py | 15 - doc/includes/sqlite3/complete_statement.py | 30 - doc/includes/sqlite3/connect_db_1.py | 3 - doc/includes/sqlite3/connect_db_2.py | 3 - doc/includes/sqlite3/converter_point.py | 47 -- doc/includes/sqlite3/countcursors.py | 15 - doc/includes/sqlite3/createdb.py | 28 - doc/includes/sqlite3/ctx_manager.py | 19 - doc/includes/sqlite3/execsql_fetchonerow.py | 17 - doc/includes/sqlite3/execsql_printall_1.py | 13 - doc/includes/sqlite3/execute_1.py | 11 - doc/includes/sqlite3/execute_2.py | 12 - doc/includes/sqlite3/execute_3.py | 12 - doc/includes/sqlite3/executemany_1.py | 24 - doc/includes/sqlite3/executemany_2.py | 15 - doc/includes/sqlite3/executescript.py | 24 - doc/includes/sqlite3/insert_more_people.py | 16 - doc/includes/sqlite3/load_extension.py | 28 - doc/includes/sqlite3/md5func.py | 11 - doc/includes/sqlite3/mysumaggr.py | 20 - doc/includes/sqlite3/parse_colnames.py | 8 - doc/includes/sqlite3/progress.py | 29 - doc/includes/sqlite3/pysqlite_datetime.py | 20 - doc/includes/sqlite3/row_factory.py | 13 - doc/includes/sqlite3/rowclass.py | 12 - doc/includes/sqlite3/shared_cache.py | 6 - doc/includes/sqlite3/shortcut_methods.py | 21 - doc/includes/sqlite3/simple_tableprinter.py | 26 - doc/includes/sqlite3/text_factory.py | 42 -- doc/install-source.txt | 147 ----- doc/sphinx/conf.py | 8 +- doc/sphinx/index.rst | 4 +- doc/sphinx/sqlcipher.rst | 887 ++++++++++++++++++++++++++++ doc/sphinx/sqlite3.rst | 886 --------------------------- 39 files changed, 893 insertions(+), 1664 deletions(-) delete mode 100644 doc/includes/sqlite3/adapter_datetime.py delete mode 100644 doc/includes/sqlite3/adapter_point_1.py delete mode 100644 doc/includes/sqlite3/adapter_point_2.py delete mode 100644 doc/includes/sqlite3/apsw_example.py delete mode 100644 doc/includes/sqlite3/authorizer.py delete mode 100644 doc/includes/sqlite3/collation_reverse.py delete mode 100644 doc/includes/sqlite3/complete_statement.py delete mode 100644 doc/includes/sqlite3/connect_db_1.py delete mode 100644 doc/includes/sqlite3/connect_db_2.py delete mode 100644 doc/includes/sqlite3/converter_point.py delete mode 100644 doc/includes/sqlite3/countcursors.py delete mode 100644 doc/includes/sqlite3/createdb.py delete mode 100644 doc/includes/sqlite3/ctx_manager.py delete mode 100644 doc/includes/sqlite3/execsql_fetchonerow.py delete mode 100644 doc/includes/sqlite3/execsql_printall_1.py delete mode 100644 doc/includes/sqlite3/execute_1.py delete mode 100644 doc/includes/sqlite3/execute_2.py delete mode 100644 doc/includes/sqlite3/execute_3.py delete mode 100644 doc/includes/sqlite3/executemany_1.py delete mode 100644 doc/includes/sqlite3/executemany_2.py delete mode 100644 doc/includes/sqlite3/executescript.py delete mode 100644 doc/includes/sqlite3/insert_more_people.py delete mode 100644 doc/includes/sqlite3/load_extension.py delete mode 100644 doc/includes/sqlite3/md5func.py delete mode 100644 doc/includes/sqlite3/mysumaggr.py delete mode 100644 doc/includes/sqlite3/parse_colnames.py delete mode 100644 doc/includes/sqlite3/progress.py delete mode 100644 doc/includes/sqlite3/pysqlite_datetime.py delete mode 100644 doc/includes/sqlite3/row_factory.py delete mode 100644 doc/includes/sqlite3/rowclass.py delete mode 100644 doc/includes/sqlite3/shared_cache.py delete mode 100644 doc/includes/sqlite3/shortcut_methods.py delete mode 100644 doc/includes/sqlite3/simple_tableprinter.py delete mode 100644 doc/includes/sqlite3/text_factory.py delete mode 100644 doc/install-source.txt create mode 100644 doc/sphinx/sqlcipher.rst delete mode 100644 doc/sphinx/sqlite3.rst diff --git a/doc/includes/sqlite3/adapter_datetime.py b/doc/includes/sqlite3/adapter_datetime.py deleted file mode 100644 index 5a43b02..0000000 --- a/doc/includes/sqlite3/adapter_datetime.py +++ /dev/null @@ -1,14 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/adapter_point_1.py b/doc/includes/sqlite3/adapter_point_1.py deleted file mode 100644 index d9acb8d..0000000 --- a/doc/includes/sqlite3/adapter_point_1.py +++ /dev/null @@ -1,16 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/adapter_point_2.py b/doc/includes/sqlite3/adapter_point_2.py deleted file mode 100644 index 6ec58a8..0000000 --- a/doc/includes/sqlite3/adapter_point_2.py +++ /dev/null @@ -1,17 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/apsw_example.py b/doc/includes/sqlite3/apsw_example.py deleted file mode 100644 index bdca0c9..0000000 --- a/doc/includes/sqlite3/apsw_example.py +++ /dev/null @@ -1,12 +0,0 @@ -from pysqlite2 import dbapi2 as sqlite3 -import apsw - -apsw_con = apsw.Connection(":memory:") -apsw_con.createscalarfunction("times_two", lambda x: 2*x, 1) - -# Create pysqlite connection from APSW connection -con = sqlite3.connect(apsw_con) -result = con.execute("select times_two(15)").fetchone()[0] -assert result == 30 -con.close() - diff --git a/doc/includes/sqlite3/authorizer.py b/doc/includes/sqlite3/authorizer.py deleted file mode 100644 index 0176c6c..0000000 --- a/doc/includes/sqlite3/authorizer.py +++ /dev/null @@ -1,26 +0,0 @@ -from pysqlite2 import dbapi2 as sqlite3 - -def authorizer_callback(action, arg1, arg2, dbname, source): - if action != sqlite3.SQLITE_SELECT: - return sqlite3.SQLITE_DENY - if arg1 == "private_table": - return sqlite3.SQLITE_DENY - return sqlite3.SQLITE_OK - -con = sqlite3.connect(":memory:") -con.executescript(""" - create table public_table(c1, c2); - create table private_table(c1, c2); - """) -con.set_authorizer(authorizer_callback) - -try: - con.execute("select * from private_table") -except sqlite3.DatabaseError, e: - print "SELECT FROM private_table =>", e.args[0] # access ... prohibited - -try: - con.execute("insert into public_table(c1, c2) values (1, 2)") -except sqlite3.DatabaseError, e: - print "DML command =>", e.args[0] # access ... prohibited - diff --git a/doc/includes/sqlite3/collation_reverse.py b/doc/includes/sqlite3/collation_reverse.py deleted file mode 100644 index 100fac9..0000000 --- a/doc/includes/sqlite3/collation_reverse.py +++ /dev/null @@ -1,15 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/complete_statement.py b/doc/includes/sqlite3/complete_statement.py deleted file mode 100644 index 2bb49d4..0000000 --- a/doc/includes/sqlite3/complete_statement.py +++ /dev/null @@ -1,30 +0,0 @@ -# A minimal SQLite shell for experiments - -from pysqlite2 import dbapi2 as sqlite3 - -con = sqlite3.connect(":memory:") -con.isolation_level = None -cur = con.cursor() - -buffer = "" - -print "Enter your SQL commands to execute in SQLite." -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 occurred:", e.args[0] - buffer = "" - -con.close() diff --git a/doc/includes/sqlite3/connect_db_1.py b/doc/includes/sqlite3/connect_db_1.py deleted file mode 100644 index 360bf21..0000000 --- a/doc/includes/sqlite3/connect_db_1.py +++ /dev/null @@ -1,3 +0,0 @@ -from pysqlite2 import dbapi2 as sqlite3 - -con = sqlite3.connect("mydb") diff --git a/doc/includes/sqlite3/connect_db_2.py b/doc/includes/sqlite3/connect_db_2.py deleted file mode 100644 index 6899843..0000000 --- a/doc/includes/sqlite3/connect_db_2.py +++ /dev/null @@ -1,3 +0,0 @@ -from pysqlite2 import dbapi2 as sqlite3 - -con = sqlite3.connect(":memory:") diff --git a/doc/includes/sqlite3/converter_point.py b/doc/includes/sqlite3/converter_point.py deleted file mode 100644 index 4ba0df5..0000000 --- a/doc/includes/sqlite3/converter_point.py +++ /dev/null @@ -1,47 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/countcursors.py b/doc/includes/sqlite3/countcursors.py deleted file mode 100644 index 9ba7614..0000000 --- a/doc/includes/sqlite3/countcursors.py +++ /dev/null @@ -1,15 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/createdb.py b/doc/includes/sqlite3/createdb.py deleted file mode 100644 index 28e9514..0000000 --- a/doc/includes/sqlite3/createdb.py +++ /dev/null @@ -1,28 +0,0 @@ -# Not referenced from the documentation, but builds the database file the other -# code snippets expect. - -from pysqlite2 import dbapi2 as 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/includes/sqlite3/ctx_manager.py b/doc/includes/sqlite3/ctx_manager.py deleted file mode 100644 index 2821e8f..0000000 --- a/doc/includes/sqlite3/ctx_manager.py +++ /dev/null @@ -1,19 +0,0 @@ -from __future__ import with_statement -from pysqlite2 import dbapi2 as sqlite3 - -con = sqlite3.connect(":memory:") -con.execute("create table person (id integer primary key, firstname varchar unique)") - -# Successful, con.commit() is called automatically afterwards -with con: - con.execute("insert into person(firstname) values (?)", ("Joe",)) - -# con.rollback() is called after the with block finishes with an exception, the -# exception is still raised and must be catched -try: - with con: - con.execute("insert into person(firstname) values (?)", ("Joe",)) -except sqlite3.IntegrityError: - print "couldn't add Joe twice" - - diff --git a/doc/includes/sqlite3/execsql_fetchonerow.py b/doc/includes/sqlite3/execsql_fetchonerow.py deleted file mode 100644 index e3aa578..0000000 --- a/doc/includes/sqlite3/execsql_fetchonerow.py +++ /dev/null @@ -1,17 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/execsql_printall_1.py b/doc/includes/sqlite3/execsql_printall_1.py deleted file mode 100644 index 62e48bd..0000000 --- a/doc/includes/sqlite3/execsql_printall_1.py +++ /dev/null @@ -1,13 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/execute_1.py b/doc/includes/sqlite3/execute_1.py deleted file mode 100644 index 70967ea..0000000 --- a/doc/includes/sqlite3/execute_1.py +++ /dev/null @@ -1,11 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/execute_2.py b/doc/includes/sqlite3/execute_2.py deleted file mode 100644 index 416b116..0000000 --- a/doc/includes/sqlite3/execute_2.py +++ /dev/null @@ -1,12 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/execute_3.py b/doc/includes/sqlite3/execute_3.py deleted file mode 100644 index 868be99..0000000 --- a/doc/includes/sqlite3/execute_3.py +++ /dev/null @@ -1,12 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/executemany_1.py b/doc/includes/sqlite3/executemany_1.py deleted file mode 100644 index b076389..0000000 --- a/doc/includes/sqlite3/executemany_1.py +++ /dev/null @@ -1,24 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/executemany_2.py b/doc/includes/sqlite3/executemany_2.py deleted file mode 100644 index 9913909..0000000 --- a/doc/includes/sqlite3/executemany_2.py +++ /dev/null @@ -1,15 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/executescript.py b/doc/includes/sqlite3/executescript.py deleted file mode 100644 index 57c2613..0000000 --- a/doc/includes/sqlite3/executescript.py +++ /dev/null @@ -1,24 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/insert_more_people.py b/doc/includes/sqlite3/insert_more_people.py deleted file mode 100644 index 40600dc..0000000 --- a/doc/includes/sqlite3/insert_more_people.py +++ /dev/null @@ -1,16 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/load_extension.py b/doc/includes/sqlite3/load_extension.py deleted file mode 100644 index d8df90f..0000000 --- a/doc/includes/sqlite3/load_extension.py +++ /dev/null @@ -1,28 +0,0 @@ -from pysqlite2 import dbapi2 as sqlite3 - -con = sqlite3.connect(":memory:") - -# enable extension loading -con.enable_load_extension(True) - -# Load the fulltext search extension -con.execute("select load_extension('./fts3.so')") - -# alternatively you can load the extension using an API call: -# con.load_extension("./fts3.so") - -# disable extension laoding again -con.enable_load_extension(False) - -# example from SQLite wiki -con.execute("create virtual table recipe using fts3(name, ingredients)") -con.executescript(""" - insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes'); - insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery'); - insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour'); - insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter'); - """) -for row in con.execute("select rowid, name, ingredients from recipe where name match 'pie'"): - print row - - diff --git a/doc/includes/sqlite3/md5func.py b/doc/includes/sqlite3/md5func.py deleted file mode 100644 index 5b8b983..0000000 --- a/doc/includes/sqlite3/md5func.py +++ /dev/null @@ -1,11 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/mysumaggr.py b/doc/includes/sqlite3/mysumaggr.py deleted file mode 100644 index 4fbcad5..0000000 --- a/doc/includes/sqlite3/mysumaggr.py +++ /dev/null @@ -1,20 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/parse_colnames.py b/doc/includes/sqlite3/parse_colnames.py deleted file mode 100644 index 702fa8d..0000000 --- a/doc/includes/sqlite3/parse_colnames.py +++ /dev/null @@ -1,8 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/progress.py b/doc/includes/sqlite3/progress.py deleted file mode 100644 index b30941d..0000000 --- a/doc/includes/sqlite3/progress.py +++ /dev/null @@ -1,29 +0,0 @@ -from pysqlite2 import dbapi2 as sqlite3 - -def progress(): - print "Query still executing. Please wait ..." - -con = sqlite3.connect(":memory:") -con.execute("create table test(x)") - -# Let's create some data -con.executemany("insert into test(x) values (?)", [(x,) for x in xrange(300)]) - -# A progress handler, executed every 10 million opcodes -con.set_progress_handler(progress, 10000000) - -# A particularly long-running query -killer_stament = """ - select count(*) from ( - select t1.x from test t1, test t2, test t3 - ) - """ - -con.execute(killer_stament) -print "-" * 50 - -# Clear the progress handler -con.set_progress_handler(None, 0) - -con.execute(killer_stament) - diff --git a/doc/includes/sqlite3/pysqlite_datetime.py b/doc/includes/sqlite3/pysqlite_datetime.py deleted file mode 100644 index 9075b46..0000000 --- a/doc/includes/sqlite3/pysqlite_datetime.py +++ /dev/null @@ -1,20 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/row_factory.py b/doc/includes/sqlite3/row_factory.py deleted file mode 100644 index bfbc64d..0000000 --- a/doc/includes/sqlite3/row_factory.py +++ /dev/null @@ -1,13 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/rowclass.py b/doc/includes/sqlite3/rowclass.py deleted file mode 100644 index e210ef2..0000000 --- a/doc/includes/sqlite3/rowclass.py +++ /dev/null @@ -1,12 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/shared_cache.py b/doc/includes/sqlite3/shared_cache.py deleted file mode 100644 index 98adf78..0000000 --- a/doc/includes/sqlite3/shared_cache.py +++ /dev/null @@ -1,6 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/shortcut_methods.py b/doc/includes/sqlite3/shortcut_methods.py deleted file mode 100644 index fcfc631..0000000 --- a/doc/includes/sqlite3/shortcut_methods.py +++ /dev/null @@ -1,21 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/simple_tableprinter.py b/doc/includes/sqlite3/simple_tableprinter.py deleted file mode 100644 index 2237dc5..0000000 --- a/doc/includes/sqlite3/simple_tableprinter.py +++ /dev/null @@ -1,26 +0,0 @@ -from pysqlite2 import dbapi2 as 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/includes/sqlite3/text_factory.py b/doc/includes/sqlite3/text_factory.py deleted file mode 100644 index cb38d52..0000000 --- a/doc/includes/sqlite3/text_factory.py +++ /dev/null @@ -1,42 +0,0 @@ -from pysqlite2 import dbapi2 as 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 diff --git a/doc/install-source.txt b/doc/install-source.txt deleted file mode 100644 index 90a3ce7..0000000 --- a/doc/install-source.txt +++ /dev/null @@ -1,147 +0,0 @@ -------------------------------------------------- -pysqlite installation guide - source distribution -------------------------------------------------- - -\(c\) 2005 Gerhard Häring - -Note: For Windows users, it is recommended that you use the win32 binary - distribution of pysqlite! - -Steps: - - - `Step 1: Satisfy the dependencies`_ - - `Step 2: Compile pysqlite`_ - - `Step 3: Install pysqlite`_ - - `Step 4: Test your pysqlite installation`_ - -Step 1: Satisfy The Dependencies -================================ - -pysqlite requires a valid combination of the dependencies in the list below. - -Detailed instructions on how to install each dependency are beyond the scope of -this document; consult the dependency distributor for installation -instructions. - -Dependencies: - - 1. Operating System and C Compiler - one of: - - * Linux/FreeBSD/NetBSD/OpenBSD and GCC - - * Other POSIX-compliant operating system and a C compiler - - 2. SQLite: - - * SQLite version 3.0.8 or later (as of pysqlite 2.2.0). This means we need - the SQLite library installed - either statically or dynamically linked - - and the SQLite header files. On Linux, the chances are very high that - your distribution offers packages for SQLite 3. Be sure to verify the - package is recent enough (version 3.0.8 or higher) and that you're - installing the development package as well, which will be need for - building pysqlite. On Debian and derivatives, the package to look for is - called libsqlite3-dev. - - 3. Python: - - * Python 2.3 or later - -Step 2: Compile pysqlite -======================== - -Once you have successfully installed the dependencies, you may proceed with the -installation of pysqlite itself. - -pysqlite has full support for the distutils_, the standard facility for Python -package distribution and installation. Full instructions for using the -distutils are available in `this section of the Python documentation`_, but you -can skip them unless you have an otherwise insoluble problem. - -Open a command prompt, change to the directory where you decompressed the -pysqlite source distribution, and type:: - - python setup.py build - -The installation script, setup.py, will attempt to automatically detect the -information needed by the C compiler; then it will invoke the distutils to -perform the actual compilation. If you installed automatic distributions of the -dependencies that place themselves in standard locations (on UNIX-style -operating systems), the compilation should proceed without incident. - -Otherwise you will have to customize the build process. That's what the file -*setup.cfg* is meant for. It's contains a few lines that you can customize so -your C compiler will find the library and header files and you can also do a -few other tweaks, like build pysqlite in debug mode. - -After you've customized *setup.cfg* appropriately, try invoking ``python -setup.py build`` again. - -If setup.py raises no errors and its output concludes with something like -"Creating library...", then you are ready to proceed to the next step. - -If you receive an error message from the compiler, then try to look at the -first error it reports. Other errors will most likely be aftereffects from the -first error (like not finding the sqlite3.h header file). - - -Step 3: Install pysqlite -======================== - -During this step, the setup script moves the *pysqlite2* package (including the -newly compiled C extension) to the standard package directory of your Python -installation so that Python will be able to import pysqlite2.dbapi2 and -pysqlite2.test. - -In addition to the Python code and shared library files actually used by the -Python interpreter, the setup script typically installs some supporting files, -such as documentation. Depending on your system configuration, these supporting -files may be placed in the same directory or a different directory from the -files used by the Python interpreter. - -Run the following command:: - - python setup.py install - -The setup script will install pysqlite, listing each file it installs. - -Errors during this step are rare because compilation (the finicky part of this -process) has already taken place; installation is really just a matter of -copying files. However, there will be file system permission errors if the -Python installation directory is not writable by the user running the setup -script. If you encounter such an error, try one of the following: - -- Log in as a user who has the required file system permissions and repeat the - installation step. -- Manually copy the directory build/lib.platform-pyver/pysqlite2 (which - contains the Python modules and compiled library files created during the - compilation step) to a directory in your PYTHONPATH. This approach will not - install the supporting files, but they are for the benefit of the programmer - rather than the Python interpreter anyway. - -Step 4: Test Your pysqlite Installation -======================================= - -Switch to a directory other than the temporary directory into which you -decompressed the source distribution (to avoid conflict between the copy of -pysqlite in that directory and the copy placed under the standard Python -site-packages directory), then run the pysqlite test suite by starting a Python -interpreter for the Python version you installed pysqlite for and entering the -following:: - - >>> from pysqlite2 import test - >>> test.test() - ..................................................................................................... - ---------------------------------------------------------------------- - Ran 101 tests in 0.182s - -If the test suite runs without any errors, you are finished. - -You should not encounter any errors at this stage since you have already -completed the compilation and installation steps successfully. If you do, -please report them to the `pysqlite bug tracker`_ or the `pysqlite mailing -list`_. - -.. _distutils: http://www.python.org/sigs/distutils-sig/ -.. _this section of the Python documentation: http://www.python.org/doc/current/inst/inst.html -.. _pysqlite bug tracker: http://pysqlite.googlecode.com/ -.. _pysqlite mailing list: http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite diff --git a/doc/sphinx/conf.py b/doc/sphinx/conf.py index 6528513..8aee750 100644 --- a/doc/sphinx/conf.py +++ b/doc/sphinx/conf.py @@ -33,8 +33,8 @@ source_suffix = '.rst' master_doc = 'index' # General substitutions. -project = 'pysqlite' -copyright = u'2008-2009, Gerhard Häring' +project = 'pysqlcipher' +copyright = u'2008-2009, Gerhard Häring; 2013, Kali Kaneko' # The default replacements for |version| and |release|, also used in various # other places throughout the built documents. @@ -42,7 +42,7 @@ copyright = u'2008-2009, Gerhard Häring' # The short X.Y version. version = '2.6' # The full version, including alpha/beta/rc tags. -release = '2.6.0' +release = '2.6.0.dev1' # There are two options for replacing |today|: either, you set today to some # non-false value, then it is used: @@ -106,7 +106,7 @@ html_last_updated_fmt = '%b %d, %Y' #html_copy_source = True # Output file base name for HTML help builder. -htmlhelp_basename = 'pysqlitedoc' +htmlhelp_basename = 'pysqlcipherdoc' # Options for LaTeX output diff --git a/doc/sphinx/index.rst b/doc/sphinx/index.rst index 522f986..b48000d 100644 --- a/doc/sphinx/index.rst +++ b/doc/sphinx/index.rst @@ -2,8 +2,8 @@ You can adapt this file completely to your liking, but it should at least contain the root `toctree` directive. -Welcome to pysqlite's documentation! -==================================== +Welcome to pysqlcipher's documentation! +======================================= Contents: diff --git a/doc/sphinx/sqlcipher.rst b/doc/sphinx/sqlcipher.rst new file mode 100644 index 0000000..7785aeb --- /dev/null +++ b/doc/sphinx/sqlcipher.rst @@ -0,0 +1,887 @@ +:mod:`sqlcipher` --- DB-API 2.0 interface for SQCipher databases +============================================================== + +.. module:: sqlcipher + :synopsis: A DB-API 2.0 implementation using SQCipher 3.x. +.. sectionauthor:: Gerhard Häring +.. sectionauthor:: Kali Kaneko + +.. note:: This documentation has to be adapted to the use of SQLCipher + +SQLite is a C library that provides a lightweight disk-based database that +doesn't require a separate server process and allows accessing the database +using a nonstandard variant of the SQL query language. Some applications can use +SQLite for internal data storage. It's also possible to prototype an +application using SQLite and then port the code to a larger database such as +PostgreSQL or Oracle. + +pysqlite was written by Gerhard Häring and provides a SQL interface compliant +with the DB-API 2.0 specification described by :pep:`249`. + +To use the module, you must first create a :class:`Connection` object that +represents the database. Here the data will be stored in the +:file:`/tmp/example` file:: + + +You can also supply the special name ``:memory:`` to create a database in RAM. + +Once you have a :class:`Connection`, you can create a :class:`Cursor` object +and call its :meth:`~Cursor.execute` method to perform SQL commands:: + + c = conn.cursor() + + # Create table + c.execute('''create table stocks + (date text, trans text, symbol text, + qty real, price real)''') + + # Insert a row of data + c.execute("""insert into stocks + values ('2006-01-05','BUY','RHAT',100,35.14)""") + + # Save (commit) the changes + conn.commit() + + # We can also close the cursor if we are done with it + c.close() + +Usually your SQL operations will need to use values from Python variables. You +shouldn't assemble your query using Python's string operations because doing so +is insecure; it makes your program vulnerable to an SQL injection attack. + +Instead, use the DB-API's parameter substitution. Put ``?`` as a placeholder +wherever you want to use a value, and then provide a tuple of values as the +second argument to the cursor's :meth:`~Cursor.execute` method. (Other database +modules may use a different placeholder, such as ``%s`` or ``:1``.) For +example:: + + # Never do this -- insecure! + symbol = 'IBM' + c.execute("... where symbol = '%s'" % symbol) + + # Do this instead + t = (symbol,) + c.execute('select * from stocks where symbol=?', t) + + # Larger example + for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), + ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), + ('2006-04-06', 'SELL', 'IBM', 500, 53.00), + ]: + c.execute('insert into stocks values (?,?,?,?,?)', t) + +To retrieve data after executing a SELECT statement, you can either treat the +cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to +retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the +matching rows. + +This example uses the iterator form:: + + >>> c = conn.cursor() + >>> c.execute('select * from stocks order by price') + >>> for row in c: + ... print row + ... + (u'2006-01-05', u'BUY', u'RHAT', 100, 35.14) + (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0) + (u'2006-04-06', u'SELL', u'IBM', 500, 53.0) + (u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0) + >>> + + +.. seealso:: + + http://code.google.com/p/pysqlite/ + The pysqlite web page -- sqlite3 is developed externally under the name + "pysqlite". + + http://www.sqlite.org + The SQLite web page; the documentation describes the syntax and the + available data types for the supported SQL dialect. + + :pep:`249` - Database API Specification 2.0 + PEP written by Marc-André Lemburg. + + +.. _sqlite3-module-contents: + +Module functions and constants +------------------------------ + + +.. data:: PARSE_DECLTYPES + + This constant is meant to be used with the *detect_types* parameter of the + :func:`connect` function. + + Setting it makes the :mod:`sqlite3` module parse the declared type for each + column it returns. It will parse out the first word of the declared type, + i. e. for "integer primary key", it will parse out "integer", or for + "number(10)" it will parse out "number". Then for that column, it will look + into the converters dictionary and use the converter function registered for + that type there. + + +.. data:: PARSE_COLNAMES + + This constant is meant to be used with the *detect_types* parameter of the + :func:`connect` function. + + Setting this makes the SQLite interface parse the column name for each column it + returns. It will look for a string formed [mytype] in there, and then decide + that 'mytype' is the type of the column. It will try to find an entry of + 'mytype' in the converters dictionary and then use the converter function found + there to return the value. The column name found in :attr:`Cursor.description` + is only the first word of the column name, i. e. if you use something like + ``'as "x [datetime]"'`` in your SQL, then we will parse out everything until the + first blank for the column name: the column name would simply be "x". + + +.. function:: connect(database[, timeout, isolation_level, detect_types, factory]) + + Opens a connection to the SQLite database file *database*. You can use + ``":memory:"`` to open a database connection to a database that resides in RAM + instead of on disk. + + When a database is accessed by multiple connections, and one of the processes + modifies the database, the SQLite database is locked until that transaction is + committed. The *timeout* parameter specifies how long the connection should wait + for the lock to go away until raising an exception. The default for the timeout + parameter is 5.0 (five seconds). + + For the *isolation_level* parameter, please see the + :attr:`Connection.isolation_level` property of :class:`Connection` objects. + + SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If + you want to use other types you must add support for them yourself. The + *detect_types* parameter and the using custom **converters** registered with the + module-level :func:`register_converter` function allow you to easily do that. + + *detect_types* defaults to 0 (i. e. off, no type detection), you can set it to + any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn + type detection on. + + By default, the :mod:`sqlite3` module uses its :class:`Connection` class for the + connect call. You can, however, subclass the :class:`Connection` class and make + :func:`connect` use your class instead by providing your class for the *factory* + parameter. + + Consult the section :ref:`sqlite3-types` of this manual for details. + + The :mod:`sqlite3` module internally uses a statement cache to avoid SQL parsing + overhead. If you want to explicitly set the number of statements that are cached + for the connection, you can set the *cached_statements* parameter. The currently + implemented default is to cache 100 statements. + + +.. function:: register_converter(typename, callable) + + Registers a callable to convert a bytestring from the database into a custom + Python type. The callable will be invoked for all database values that are of + the type *typename*. Confer the parameter *detect_types* of the :func:`connect` + function for how the type detection works. Note that the case of *typename* and + the name of the type in your query must match! + + +.. function:: register_adapter(type, callable) + + Registers a callable to convert the custom Python type *type* into one of + SQLite's supported types. The callable *callable* accepts as single parameter + the Python value, and must return a value of the following types: int, long, + float, str (UTF-8 encoded), unicode or buffer. + + +.. function:: complete_statement(sql) + + Returns :const:`True` if the string *sql* contains one or more complete SQL + statements terminated by semicolons. It does not verify that the SQL is + syntactically correct, only that there are no unclosed string literals and the + statement is terminated by a semicolon. + + This can be used to build a shell for SQLite, as in the following example: + + + .. literalinclude:: ../includes/sqlite3/complete_statement.py + + +.. function:: enable_callback_tracebacks(flag) + + By default you will not get any tracebacks in user-defined functions, + aggregates, converters, authorizer callbacks etc. If you want to debug them, you + can call this function with *flag* as True. Afterwards, you will get tracebacks + from callbacks on ``sys.stderr``. Use :const:`False` to disable the feature + again. + + +.. _sqlite3-connection-objects: + +Connection Objects +------------------ + +.. class:: Connection + + A SQLite database connection has the following attributes and methods: + +.. attribute:: Connection.isolation_level + + Get or set the current isolation level. :const:`None` for autocommit mode or + one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section + :ref:`sqlite3-controlling-transactions` for a more detailed explanation. + + +.. method:: Connection.cursor([cursorClass]) + + The cursor method accepts a single optional parameter *cursorClass*. If + supplied, this must be a custom cursor class that extends + :class:`sqlite3.Cursor`. + + +.. method:: Connection.commit() + + This method commits the current transaction. If you don't call this method, + anything you did since the last call to ``commit()`` is not visible from from + other database connections. If you wonder why you don't see the data you've + written to the database, please check you didn't forget to call this method. + +.. method:: Connection.rollback() + + This method rolls back any changes to the database since the last call to + :meth:`commit`. + +.. method:: Connection.close() + + This closes the database connection. Note that this does not automatically + call :meth:`commit`. If you just close your database connection without + calling :meth:`commit` first, your changes will be lost! + +.. method:: Connection.execute(sql, [parameters]) + + This is a nonstandard shortcut that creates an intermediate cursor object by + calling the cursor method, then calls the cursor's + :meth:`execute` method with the parameters given. + + +.. method:: Connection.executemany(sql, [parameters]) + + This is a nonstandard shortcut that creates an intermediate cursor object by + calling the cursor method, then calls the cursor's + :meth:`executemany` method with the parameters given. + +.. method:: Connection.executescript(sql_script) + + This is a nonstandard shortcut that creates an intermediate cursor object by + calling the cursor method, then calls the cursor's + :meth:`executescript` method with the parameters + given. + + +.. method:: 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: unicode, str, int, + long, float, buffer and None. + + Example: + + .. literalinclude:: ../includes/sqlite3/md5func.py + + +.. method:: Connection.create_aggregate(name, num_params, aggregate_class) + + Creates a user-defined aggregate function. + + The aggregate class must implement a ``step`` method, which accepts the number + of parameters *num_params*, and a ``finalize`` method which will return the + final result of the aggregate. + + The ``finalize`` method can return any of the types supported by SQLite: + unicode, str, int, long, float, buffer and None. + + Example: + + .. literalinclude:: ../includes/sqlite3/mysumaggr.py + + +.. method:: Connection.create_collation(name, callable) + + Creates a collation with the specified *name* and *callable*. The callable will + be passed two string arguments. It should return -1 if the first is ordered + lower than the second, 0 if they are ordered equal and 1 if the first is ordered + higher than the second. Note that this controls sorting (ORDER BY in SQL) so + your comparisons don't affect other SQL operations. + + Note that the callable will get its parameters as Python bytestrings, which will + normally be encoded in UTF-8. + + The following example shows a custom collation that sorts "the wrong way": + + .. literalinclude:: ../includes/sqlite3/collation_reverse.py + + To remove a collation, call ``create_collation`` with None as callable:: + + con.create_collation("reverse", None) + + +.. method:: Connection.interrupt() + + You can call this method from a different thread to abort any queries that might + be executing on the connection. The query will then abort and the caller will + get an exception. + + +.. method:: Connection.set_authorizer(authorizer_callback) + + This routine registers a callback. The callback is invoked for each attempt to + access a column of a table in the database. The callback should return + :const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL + statement should be aborted with an error and :const:`SQLITE_IGNORE` if the + column should be treated as a NULL value. These constants are available in the + :mod:`sqlite3` module. + + The first argument to the callback signifies what kind of operation is to be + authorized. The second and third argument will be arguments or :const:`None` + depending on the first argument. The 4th argument is the name of the database + ("main", "temp", etc.) if applicable. The 5th argument is the name of the + inner-most trigger or view that is responsible for the access attempt or + :const:`None` if this access attempt is directly from input SQL code. + + Please consult the SQLite documentation about the possible values for the first + argument and the meaning of the second and third argument depending on the first + one. All necessary constants are available in the :mod:`sqlite3` module. + + +.. method:: Connection.set_progress_handler(handler, n) + + This routine registers a callback. The callback is invoked for every *n* + instructions of the SQLite virtual machine. This is useful if you want to + get called from SQLite during long-running operations, for example to update + a GUI. + + If you want to clear any previously installed progress handler, call the + method with :const:`None` for *handler*. + + +.. method:: Connection.enable_load_extension(enabled) + + This routine allows/disallows the SQLite engine to load SQLite extensions + from shared libraries. SQLite extensions can define new functions, + aggregates or whole new virtual table implementations. One well-known + extension is the fulltext-search extension distributed with SQLite. + + .. literalinclude:: ../includes/sqlite3/load_extension.py + +.. method:: Connection.load_extension(path) + + This routine loads a SQLite extension from a shared library. You have to + enable extension loading with ``enable_load_extension`` before you can use + this routine. + +.. attribute:: Connection.row_factory + + You can change this attribute to a callable that accepts the cursor and the + original row as a tuple and will return the real result row. This way, you can + implement more advanced ways of returning results, such as returning an object + that can also access columns by name. + + Example: + + .. literalinclude:: ../includes/sqlite3/row_factory.py + + If returning a tuple doesn't suffice and you want name-based access to + columns, you should consider setting :attr:`row_factory` to the + highly-optimized :class:`sqlite3.Row` type. :class:`Row` provides both + index-based and case-insensitive name-based access to columns with almost no + memory overhead. It will probably be better than your own custom + dictionary-based approach or even a db_row based solution. + + .. XXX what's a db_row-based solution? + + +.. attribute:: 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 :class:`unicode` and the + :mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to + return bytestrings instead, you can set it to :class:`str`. + + For efficiency reasons, there's also a way to return Unicode objects only for + non-ASCII data, and bytestrings otherwise. To activate it, set this attribute to + :const:`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: + + .. literalinclude:: ../includes/sqlite3/text_factory.py + + +.. attribute:: Connection.total_changes + + Returns the total number of database rows that have been modified, inserted, or + deleted since the database connection was opened. + + +.. attribute:: Connection.iterdump + + Returns an iterator to dump the database in an SQL text format. Useful when + saving an in-memory database for later restoration. This function provides + the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3` + shell. + + Example:: + + # Convert file existing_db.db to SQL dump file dump.sql + import sqlite3, os + + con = sqlite3.connect('existing_db.db') + full_dump = os.linesep.join([line for line in con.iterdump()]) + f = open('dump.sql', 'w') + f.writelines(full_dump) + f.close() + + +.. _sqlite3-cursor-objects: + +Cursor Objects +-------------- + +A :class:`Cursor` instance has the following attributes and methods: + + A SQLite database cursor has the following attributes and methods: + +.. method:: Cursor.execute(sql, [parameters]) + + Executes an SQL statement. The SQL statement may be parametrized (i. e. + placeholders instead of SQL literals). The :mod:`sqlite3` module supports two + kinds of placeholders: question marks (qmark style) and named placeholders + (named style). + + This example shows how to use parameters with qmark style: + + .. literalinclude:: ../includes/sqlite3/execute_1.py + + This example shows how to use the named style: + + .. literalinclude:: ../includes/sqlite3/execute_2.py + + :meth:`execute` will only execute a single SQL statement. If you try to execute + more than one statement with it, it will raise a Warning. Use + :meth:`executescript` if you want to execute multiple SQL statements with one + call. + + +.. method:: Cursor.executemany(sql, seq_of_parameters) + + Executes an SQL command against all parameter sequences or mappings found in + the sequence *sql*. The :mod:`sqlite3` module also allows using an + :term:`iterator` yielding parameters instead of a sequence. + + .. literalinclude:: ../includes/sqlite3/executemany_1.py + + Here's a shorter example using a :term:`generator`: + + .. literalinclude:: ../includes/sqlite3/executemany_2.py + + +.. method:: Cursor.executescript(sql_script) + + This is a nonstandard convenience method for executing multiple SQL statements + at once. It issues a ``COMMIT`` statement first, then executes the SQL script it + gets as a parameter. + + *sql_script* can be a bytestring or a Unicode string. + + Example: + + .. literalinclude:: ../includes/sqlite3/executescript.py + + +.. method:: Cursor.fetchone() + + Fetches the next row of a query result set, returning a single sequence, + or :const:`None` when no more data is available. + + +.. method:: Cursor.fetchmany([size=cursor.arraysize]) + + Fetches the next set of rows of a query result, returning a list. An empty + list is returned when no more rows are available. + + The number of rows to fetch per call is specified by the *size* parameter. + If it is not given, the cursor's arraysize determines the number of rows + to be fetched. The method should try to fetch as many rows as indicated by + the size parameter. If this is not possible due to the specified number of + rows not being available, fewer rows may be returned. + + Note there are performance considerations involved with the *size* parameter. + For optimal performance, it is usually best to use the arraysize attribute. + If the *size* parameter is used, then it is best for it to retain the same + value from one :meth:`fetchmany` call to the next. + +.. method:: Cursor.fetchall() + + Fetches all (remaining) rows of a query result, returning a list. Note that + the cursor's arraysize attribute can affect the performance of this operation. + An empty list is returned when no rows are available. + + +.. attribute:: Cursor.rowcount + + Although the :class:`Cursor` class of the :mod:`sqlite3` module implements this + attribute, the database engine's own support for the determination of "rows + affected"/"rows selected" is quirky. + + For ``DELETE`` statements, SQLite reports :attr:`rowcount` as 0 if you make a + ``DELETE FROM table`` without any condition. + + For :meth:`executemany` statements, the number of modifications are summed up + into :attr:`rowcount`. + + As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in + case no ``executeXX()`` has been performed on the cursor or the rowcount of the + last operation is not determinable by the interface". + + This includes ``SELECT`` statements because we cannot determine the number of + rows a query produced until all rows were fetched. + +.. attribute:: Cursor.lastrowid + + This read-only attribute provides the rowid of the last modified row. It is + only set if you issued a ``INSERT`` statement using the :meth:`execute` + method. For operations other than ``INSERT`` or when :meth:`executemany` is + called, :attr:`lastrowid` is set to :const:`None`. + +.. attribute:: Cursor.description + + This read-only attribute provides the column names of the last query. To + remain compatible with the Python DB API, it returns a 7-tuple for each + column where the last six items of each tuple are :const:`None`. + + It is set for ``SELECT`` statements without any matching rows as well. + +.. _sqlite3-row-objects: + +Row Objects +----------- + +.. class:: Row + + A :class:`Row` instance serves as a highly optimized + :attr:`~Connection.row_factory` for :class:`Connection` objects. + It tries to mimic a tuple in most of its features. + + It supports mapping access by column name and index, iteration, + representation, equality testing and :func:`len`. + + If two :class:`Row` objects have exactly the same columns and their + members are equal, they compare equal. + + .. versionchanged:: 2.6 + Added iteration and equality (hashability). + + .. method:: keys + + This method returns a tuple of column names. Immediately after a query, + it is the first member of each tuple in :attr:`Cursor.description`. + + .. versionadded:: 2.6 + +Let's assume we initialize a table as in the example given above:: + + conn = sqlite3.connect(":memory:") + c = conn.cursor() + c.execute('''create table stocks + (date text, trans text, symbol text, + qty real, price real)''') + c.execute("""insert into stocks + values ('2006-01-05','BUY','RHAT',100,35.14)""") + conn.commit() + c.close() + +Now we plug :class:`Row` in:: + + >>> conn.row_factory = sqlite3.Row + >>> c = conn.cursor() + >>> c.execute('select * from stocks') + + >>> r = c.fetchone() + >>> type(r) + + >>> r + (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14) + >>> len(r) + 5 + >>> r[2] + u'RHAT' + >>> r.keys() + ['date', 'trans', 'symbol', 'qty', 'price'] + >>> r['qty'] + 100.0 + >>> for member in r: print member + ... + 2006-01-05 + BUY + RHAT + 100.0 + 35.14 + + +.. _sqlite3-types: + +SQLite and Python types +----------------------- + + +Introduction +^^^^^^^^^^^^ + +SQLite natively supports the following types: ``NULL``, ``INTEGER``, +``REAL``, ``TEXT``, ``BLOB``. + +The following Python types can thus be sent to SQLite without any problem: + ++-----------------------------+-------------+ +| Python type | SQLite type | ++=============================+=============+ +| :const:`None` | ``NULL`` | ++-----------------------------+-------------+ +| :class:`int` | ``INTEGER`` | ++-----------------------------+-------------+ +| :class:`long` | ``INTEGER`` | ++-----------------------------+-------------+ +| :class:`float` | ``REAL`` | ++-----------------------------+-------------+ +| :class:`str` (UTF8-encoded) | ``TEXT`` | ++-----------------------------+-------------+ +| :class:`unicode` | ``TEXT`` | ++-----------------------------+-------------+ +| :class:`buffer` | ``BLOB`` | ++-----------------------------+-------------+ + +This is how SQLite types are converted to Python types by default: + ++-------------+----------------------------------------------+ +| SQLite type | Python type | ++=============+==============================================+ +| ``NULL`` | :const:`None` | ++-------------+----------------------------------------------+ +| ``INTEGER`` | :class:`int` or :class:`long`, | +| | depending on size | ++-------------+----------------------------------------------+ +| ``REAL`` | :class:`float` | ++-------------+----------------------------------------------+ +| ``TEXT`` | depends on :attr:`~Connection.text_factory`, | +| | :class:`unicode` by default | ++-------------+----------------------------------------------+ +| ``BLOB`` | :class:`buffer` | ++-------------+----------------------------------------------+ + +The type system of the :mod:`sqlite3` module is extensible in two ways: you can +store additional Python types in a SQLite database via object adaptation, and +you can let the :mod:`sqlite3` module convert SQLite types to different Python +types via converters. + + +Using adapters to store additional Python types in SQLite databases +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +As described before, SQLite supports only a limited set of types natively. To +use other Python types with SQLite, you must **adapt** them to one of the +sqlite3 module's supported types for SQLite: one of NoneType, int, long, float, +str, unicode, buffer. + +The :mod:`sqlite3` module uses Python object adaptation, as described in +:pep:`246` for this. The protocol to use is :class:`PrepareProtocol`. + +There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python +type to one of the supported ones. + + +Letting your object adapt itself +"""""""""""""""""""""""""""""""" + +This is a good approach if you write the class yourself. Let's suppose you have +a class like this:: + + class Point(object): + def __init__(self, x, y): + self.x, self.y = x, y + +Now you want to store the point in a single SQLite column. First you'll have to +choose one of the supported types first to be used for representing the point. +Let's just use str and separate the coordinates using a semicolon. Then you need +to give your class a method ``__conform__(self, protocol)`` which must return +the converted value. The parameter *protocol* will be :class:`PrepareProtocol`. + +.. literalinclude:: ../includes/sqlite3/adapter_point_1.py + + +Registering an adapter callable +""""""""""""""""""""""""""""""" + +The other possibility is to create a function that converts the type to the +string representation and register the function with :meth:`register_adapter`. + +.. note:: + + The type/class to adapt must be a :term:`new-style class`, i. e. it must have + :class:`object` as one of its bases. + +.. literalinclude:: ../includes/sqlite3/adapter_point_2.py + +The :mod:`sqlite3` module has two default adapters for Python's built-in +:class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose +we want to store :class:`datetime.datetime` objects not in ISO representation, +but as a Unix timestamp. + +.. literalinclude:: ../includes/sqlite3/adapter_datetime.py + + +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 :class:`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 :class:`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 :mod:`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 :ref:`sqlite3-module-contents`, in the entries +for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`. + +The following example illustrates both approaches. + +.. literalinclude:: ../includes/sqlite3/converter_point.py + + +Default adapters and converters +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +There are default adapters for the date and datetime types in the datetime +module. They will be sent as ISO dates/ISO timestamps to SQLite. + +The default converters are registered under the name "date" for +:class:`datetime.date` and under the name "timestamp" for +:class:`datetime.datetime`. + +This way, you can use date/timestamps from Python without any additional +fiddling in most cases. The format of the adapters is also compatible with the +experimental SQLite date/time functions. + +The following example demonstrates this. + +.. literalinclude:: ../includes/sqlite3/pysqlite_datetime.py + + +.. _sqlite3-controlling-transactions: + +Controlling Transactions +------------------------ + +By default, the :mod:`sqlite3` module opens transactions implicitly before a +Data Modification Language (DML) statement (i.e. +``INSERT``/``UPDATE``/``DELETE``/``REPLACE``), and commits transactions +implicitly before a non-DML, non-query statement (i. e. +anything other than ``SELECT`` or the aforementioned). + +So if you are within a transaction and issue a command like ``CREATE TABLE +...``, ``VACUUM``, ``PRAGMA``, the :mod:`sqlite3` module will commit implicitly +before executing that command. There are two reasons for doing that. The first +is that some of these commands don't work within transactions. The other reason +is that pysqlite needs to keep track of the transaction state (if a transaction +is active or not). + +You can control which kind of ``BEGIN`` statements sqlite3 implicitly executes +(or none at all) via the *isolation_level* parameter to the :func:`connect` +call, or via the :attr:`isolation_level` property of connections. + +If you want **autocommit mode**, then set :attr:`isolation_level` to None. + +Otherwise leave it at its default, which will result in a plain "BEGIN" +statement, or set it to one of SQLite's supported isolation levels: "DEFERRED", +"IMMEDIATE" or "EXCLUSIVE". + + + +Using :mod:`sqlite3` efficiently +-------------------------------- + + +Using shortcut methods +^^^^^^^^^^^^^^^^^^^^^^ + +Using the nonstandard :meth:`execute`, :meth:`executemany` and +:meth:`executescript` methods of the :class:`Connection` object, your code can +be written more concisely because you don't have to create the (often +superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor` +objects are created implicitly and these shortcut methods return the cursor +objects. This way, you can execute a ``SELECT`` statement and iterate over it +directly using only a single call on the :class:`Connection` object. + +.. literalinclude:: ../includes/sqlite3/shortcut_methods.py + + +Accessing columns by name instead of by index +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +One useful feature of the :mod:`sqlite3` module is the built-in +:class:`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: + +.. literalinclude:: ../includes/sqlite3/rowclass.py + + +Using the connection as a context manager +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +With Python 2.5 or higher, connection objects can be used as context managers +that automatically commit or rollback transactions. In the event of an +exception, the transaction is rolled back; otherwise, the transaction is +committed: + +.. literalinclude:: ../includes/sqlite3/ctx_manager.py + + +Common issues +------------- + +Multithreading +^^^^^^^^^^^^^^ + +Older SQLite versions had issues with sharing connections between threads. +That's why the Python module disallows sharing connections and cursors between +threads. If you still try to do so, you will get an exception at runtime. + +The only exception is calling the :meth:`~Connection.interrupt` method, which +only makes sense to call from a different thread. + diff --git a/doc/sphinx/sqlite3.rst b/doc/sphinx/sqlite3.rst deleted file mode 100644 index a0d5de8..0000000 --- a/doc/sphinx/sqlite3.rst +++ /dev/null @@ -1,886 +0,0 @@ -:mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases -============================================================ - -.. module:: sqlite3 - :synopsis: A DB-API 2.0 implementation using SQLite 3.x. -.. sectionauthor:: Gerhard Häring - - -SQLite is a C library that provides a lightweight disk-based database that -doesn't require a separate server process and allows accessing the database -using a nonstandard variant of the SQL query language. Some applications can use -SQLite for internal data storage. It's also possible to prototype an -application using SQLite and then port the code to a larger database such as -PostgreSQL or Oracle. - -pysqlite was written by Gerhard Häring and provides a SQL interface compliant -with the DB-API 2.0 specification described by :pep:`249`. - -To use the module, you must first create a :class:`Connection` object that -represents the database. Here the data will be stored in the -:file:`/tmp/example` file:: - - conn = sqlite3.connect('/tmp/example') - -You can also supply the special name ``:memory:`` to create a database in RAM. - -Once you have a :class:`Connection`, you can create a :class:`Cursor` object -and call its :meth:`~Cursor.execute` method to perform SQL commands:: - - c = conn.cursor() - - # Create table - c.execute('''create table stocks - (date text, trans text, symbol text, - qty real, price real)''') - - # Insert a row of data - c.execute("""insert into stocks - values ('2006-01-05','BUY','RHAT',100,35.14)""") - - # Save (commit) the changes - conn.commit() - - # We can also close the cursor if we are done with it - c.close() - -Usually your SQL operations will need to use values from Python variables. You -shouldn't assemble your query using Python's string operations because doing so -is insecure; it makes your program vulnerable to an SQL injection attack. - -Instead, use the DB-API's parameter substitution. Put ``?`` as a placeholder -wherever you want to use a value, and then provide a tuple of values as the -second argument to the cursor's :meth:`~Cursor.execute` method. (Other database -modules may use a different placeholder, such as ``%s`` or ``:1``.) For -example:: - - # Never do this -- insecure! - symbol = 'IBM' - c.execute("... where symbol = '%s'" % symbol) - - # Do this instead - t = (symbol,) - c.execute('select * from stocks where symbol=?', t) - - # Larger example - for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), - ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), - ('2006-04-06', 'SELL', 'IBM', 500, 53.00), - ]: - c.execute('insert into stocks values (?,?,?,?,?)', t) - -To retrieve data after executing a SELECT statement, you can either treat the -cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to -retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the -matching rows. - -This example uses the iterator form:: - - >>> c = conn.cursor() - >>> c.execute('select * from stocks order by price') - >>> for row in c: - ... print row - ... - (u'2006-01-05', u'BUY', u'RHAT', 100, 35.14) - (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0) - (u'2006-04-06', u'SELL', u'IBM', 500, 53.0) - (u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0) - >>> - - -.. seealso:: - - http://code.google.com/p/pysqlite/ - The pysqlite web page -- sqlite3 is developed externally under the name - "pysqlite". - - http://www.sqlite.org - The SQLite web page; the documentation describes the syntax and the - available data types for the supported SQL dialect. - - :pep:`249` - Database API Specification 2.0 - PEP written by Marc-André Lemburg. - - -.. _sqlite3-module-contents: - -Module functions and constants ------------------------------- - - -.. data:: PARSE_DECLTYPES - - This constant is meant to be used with the *detect_types* parameter of the - :func:`connect` function. - - Setting it makes the :mod:`sqlite3` module parse the declared type for each - column it returns. It will parse out the first word of the declared type, - i. e. for "integer primary key", it will parse out "integer", or for - "number(10)" it will parse out "number". Then for that column, it will look - into the converters dictionary and use the converter function registered for - that type there. - - -.. data:: PARSE_COLNAMES - - This constant is meant to be used with the *detect_types* parameter of the - :func:`connect` function. - - Setting this makes the SQLite interface parse the column name for each column it - returns. It will look for a string formed [mytype] in there, and then decide - that 'mytype' is the type of the column. It will try to find an entry of - 'mytype' in the converters dictionary and then use the converter function found - there to return the value. The column name found in :attr:`Cursor.description` - is only the first word of the column name, i. e. if you use something like - ``'as "x [datetime]"'`` in your SQL, then we will parse out everything until the - first blank for the column name: the column name would simply be "x". - - -.. function:: connect(database[, timeout, isolation_level, detect_types, factory]) - - Opens a connection to the SQLite database file *database*. You can use - ``":memory:"`` to open a database connection to a database that resides in RAM - instead of on disk. - - When a database is accessed by multiple connections, and one of the processes - modifies the database, the SQLite database is locked until that transaction is - committed. The *timeout* parameter specifies how long the connection should wait - for the lock to go away until raising an exception. The default for the timeout - parameter is 5.0 (five seconds). - - For the *isolation_level* parameter, please see the - :attr:`Connection.isolation_level` property of :class:`Connection` objects. - - SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If - you want to use other types you must add support for them yourself. The - *detect_types* parameter and the using custom **converters** registered with the - module-level :func:`register_converter` function allow you to easily do that. - - *detect_types* defaults to 0 (i. e. off, no type detection), you can set it to - any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn - type detection on. - - By default, the :mod:`sqlite3` module uses its :class:`Connection` class for the - connect call. You can, however, subclass the :class:`Connection` class and make - :func:`connect` use your class instead by providing your class for the *factory* - parameter. - - Consult the section :ref:`sqlite3-types` of this manual for details. - - The :mod:`sqlite3` module internally uses a statement cache to avoid SQL parsing - overhead. If you want to explicitly set the number of statements that are cached - for the connection, you can set the *cached_statements* parameter. The currently - implemented default is to cache 100 statements. - - -.. function:: register_converter(typename, callable) - - Registers a callable to convert a bytestring from the database into a custom - Python type. The callable will be invoked for all database values that are of - the type *typename*. Confer the parameter *detect_types* of the :func:`connect` - function for how the type detection works. Note that the case of *typename* and - the name of the type in your query must match! - - -.. function:: register_adapter(type, callable) - - Registers a callable to convert the custom Python type *type* into one of - SQLite's supported types. The callable *callable* accepts as single parameter - the Python value, and must return a value of the following types: int, long, - float, str (UTF-8 encoded), unicode or buffer. - - -.. function:: complete_statement(sql) - - Returns :const:`True` if the string *sql* contains one or more complete SQL - statements terminated by semicolons. It does not verify that the SQL is - syntactically correct, only that there are no unclosed string literals and the - statement is terminated by a semicolon. - - This can be used to build a shell for SQLite, as in the following example: - - - .. literalinclude:: ../includes/sqlite3/complete_statement.py - - -.. function:: enable_callback_tracebacks(flag) - - By default you will not get any tracebacks in user-defined functions, - aggregates, converters, authorizer callbacks etc. If you want to debug them, you - can call this function with *flag* as True. Afterwards, you will get tracebacks - from callbacks on ``sys.stderr``. Use :const:`False` to disable the feature - again. - - -.. _sqlite3-connection-objects: - -Connection Objects ------------------- - -.. class:: Connection - - A SQLite database connection has the following attributes and methods: - -.. attribute:: Connection.isolation_level - - Get or set the current isolation level. :const:`None` for autocommit mode or - one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section - :ref:`sqlite3-controlling-transactions` for a more detailed explanation. - - -.. method:: Connection.cursor([cursorClass]) - - The cursor method accepts a single optional parameter *cursorClass*. If - supplied, this must be a custom cursor class that extends - :class:`sqlite3.Cursor`. - - -.. method:: Connection.commit() - - This method commits the current transaction. If you don't call this method, - anything you did since the last call to ``commit()`` is not visible from from - other database connections. If you wonder why you don't see the data you've - written to the database, please check you didn't forget to call this method. - -.. method:: Connection.rollback() - - This method rolls back any changes to the database since the last call to - :meth:`commit`. - -.. method:: Connection.close() - - This closes the database connection. Note that this does not automatically - call :meth:`commit`. If you just close your database connection without - calling :meth:`commit` first, your changes will be lost! - -.. method:: Connection.execute(sql, [parameters]) - - This is a nonstandard shortcut that creates an intermediate cursor object by - calling the cursor method, then calls the cursor's - :meth:`execute` method with the parameters given. - - -.. method:: Connection.executemany(sql, [parameters]) - - This is a nonstandard shortcut that creates an intermediate cursor object by - calling the cursor method, then calls the cursor's - :meth:`executemany` method with the parameters given. - -.. method:: Connection.executescript(sql_script) - - This is a nonstandard shortcut that creates an intermediate cursor object by - calling the cursor method, then calls the cursor's - :meth:`executescript` method with the parameters - given. - - -.. method:: 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: unicode, str, int, - long, float, buffer and None. - - Example: - - .. literalinclude:: ../includes/sqlite3/md5func.py - - -.. method:: Connection.create_aggregate(name, num_params, aggregate_class) - - Creates a user-defined aggregate function. - - The aggregate class must implement a ``step`` method, which accepts the number - of parameters *num_params*, and a ``finalize`` method which will return the - final result of the aggregate. - - The ``finalize`` method can return any of the types supported by SQLite: - unicode, str, int, long, float, buffer and None. - - Example: - - .. literalinclude:: ../includes/sqlite3/mysumaggr.py - - -.. method:: Connection.create_collation(name, callable) - - Creates a collation with the specified *name* and *callable*. The callable will - be passed two string arguments. It should return -1 if the first is ordered - lower than the second, 0 if they are ordered equal and 1 if the first is ordered - higher than the second. Note that this controls sorting (ORDER BY in SQL) so - your comparisons don't affect other SQL operations. - - Note that the callable will get its parameters as Python bytestrings, which will - normally be encoded in UTF-8. - - The following example shows a custom collation that sorts "the wrong way": - - .. literalinclude:: ../includes/sqlite3/collation_reverse.py - - To remove a collation, call ``create_collation`` with None as callable:: - - con.create_collation("reverse", None) - - -.. method:: Connection.interrupt() - - You can call this method from a different thread to abort any queries that might - be executing on the connection. The query will then abort and the caller will - get an exception. - - -.. method:: Connection.set_authorizer(authorizer_callback) - - This routine registers a callback. The callback is invoked for each attempt to - access a column of a table in the database. The callback should return - :const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL - statement should be aborted with an error and :const:`SQLITE_IGNORE` if the - column should be treated as a NULL value. These constants are available in the - :mod:`sqlite3` module. - - The first argument to the callback signifies what kind of operation is to be - authorized. The second and third argument will be arguments or :const:`None` - depending on the first argument. The 4th argument is the name of the database - ("main", "temp", etc.) if applicable. The 5th argument is the name of the - inner-most trigger or view that is responsible for the access attempt or - :const:`None` if this access attempt is directly from input SQL code. - - Please consult the SQLite documentation about the possible values for the first - argument and the meaning of the second and third argument depending on the first - one. All necessary constants are available in the :mod:`sqlite3` module. - - -.. method:: Connection.set_progress_handler(handler, n) - - This routine registers a callback. The callback is invoked for every *n* - instructions of the SQLite virtual machine. This is useful if you want to - get called from SQLite during long-running operations, for example to update - a GUI. - - If you want to clear any previously installed progress handler, call the - method with :const:`None` for *handler*. - - -.. method:: Connection.enable_load_extension(enabled) - - This routine allows/disallows the SQLite engine to load SQLite extensions - from shared libraries. SQLite extensions can define new functions, - aggregates or whole new virtual table implementations. One well-known - extension is the fulltext-search extension distributed with SQLite. - - .. literalinclude:: ../includes/sqlite3/load_extension.py - -.. method:: Connection.load_extension(path) - - This routine loads a SQLite extension from a shared library. You have to - enable extension loading with ``enable_load_extension`` before you can use - this routine. - -.. attribute:: Connection.row_factory - - You can change this attribute to a callable that accepts the cursor and the - original row as a tuple and will return the real result row. This way, you can - implement more advanced ways of returning results, such as returning an object - that can also access columns by name. - - Example: - - .. literalinclude:: ../includes/sqlite3/row_factory.py - - If returning a tuple doesn't suffice and you want name-based access to - columns, you should consider setting :attr:`row_factory` to the - highly-optimized :class:`sqlite3.Row` type. :class:`Row` provides both - index-based and case-insensitive name-based access to columns with almost no - memory overhead. It will probably be better than your own custom - dictionary-based approach or even a db_row based solution. - - .. XXX what's a db_row-based solution? - - -.. attribute:: 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 :class:`unicode` and the - :mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to - return bytestrings instead, you can set it to :class:`str`. - - For efficiency reasons, there's also a way to return Unicode objects only for - non-ASCII data, and bytestrings otherwise. To activate it, set this attribute to - :const:`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: - - .. literalinclude:: ../includes/sqlite3/text_factory.py - - -.. attribute:: Connection.total_changes - - Returns the total number of database rows that have been modified, inserted, or - deleted since the database connection was opened. - - -.. attribute:: Connection.iterdump - - Returns an iterator to dump the database in an SQL text format. Useful when - saving an in-memory database for later restoration. This function provides - the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3` - shell. - - Example:: - - # Convert file existing_db.db to SQL dump file dump.sql - import sqlite3, os - - con = sqlite3.connect('existing_db.db') - full_dump = os.linesep.join([line for line in con.iterdump()]) - f = open('dump.sql', 'w') - f.writelines(full_dump) - f.close() - - -.. _sqlite3-cursor-objects: - -Cursor Objects --------------- - -A :class:`Cursor` instance has the following attributes and methods: - - A SQLite database cursor has the following attributes and methods: - -.. method:: Cursor.execute(sql, [parameters]) - - Executes an SQL statement. The SQL statement may be parametrized (i. e. - placeholders instead of SQL literals). The :mod:`sqlite3` module supports two - kinds of placeholders: question marks (qmark style) and named placeholders - (named style). - - This example shows how to use parameters with qmark style: - - .. literalinclude:: ../includes/sqlite3/execute_1.py - - This example shows how to use the named style: - - .. literalinclude:: ../includes/sqlite3/execute_2.py - - :meth:`execute` will only execute a single SQL statement. If you try to execute - more than one statement with it, it will raise a Warning. Use - :meth:`executescript` if you want to execute multiple SQL statements with one - call. - - -.. method:: Cursor.executemany(sql, seq_of_parameters) - - Executes an SQL command against all parameter sequences or mappings found in - the sequence *sql*. The :mod:`sqlite3` module also allows using an - :term:`iterator` yielding parameters instead of a sequence. - - .. literalinclude:: ../includes/sqlite3/executemany_1.py - - Here's a shorter example using a :term:`generator`: - - .. literalinclude:: ../includes/sqlite3/executemany_2.py - - -.. method:: Cursor.executescript(sql_script) - - This is a nonstandard convenience method for executing multiple SQL statements - at once. It issues a ``COMMIT`` statement first, then executes the SQL script it - gets as a parameter. - - *sql_script* can be a bytestring or a Unicode string. - - Example: - - .. literalinclude:: ../includes/sqlite3/executescript.py - - -.. method:: Cursor.fetchone() - - Fetches the next row of a query result set, returning a single sequence, - or :const:`None` when no more data is available. - - -.. method:: Cursor.fetchmany([size=cursor.arraysize]) - - Fetches the next set of rows of a query result, returning a list. An empty - list is returned when no more rows are available. - - The number of rows to fetch per call is specified by the *size* parameter. - If it is not given, the cursor's arraysize determines the number of rows - to be fetched. The method should try to fetch as many rows as indicated by - the size parameter. If this is not possible due to the specified number of - rows not being available, fewer rows may be returned. - - Note there are performance considerations involved with the *size* parameter. - For optimal performance, it is usually best to use the arraysize attribute. - If the *size* parameter is used, then it is best for it to retain the same - value from one :meth:`fetchmany` call to the next. - -.. method:: Cursor.fetchall() - - Fetches all (remaining) rows of a query result, returning a list. Note that - the cursor's arraysize attribute can affect the performance of this operation. - An empty list is returned when no rows are available. - - -.. attribute:: Cursor.rowcount - - Although the :class:`Cursor` class of the :mod:`sqlite3` module implements this - attribute, the database engine's own support for the determination of "rows - affected"/"rows selected" is quirky. - - For ``DELETE`` statements, SQLite reports :attr:`rowcount` as 0 if you make a - ``DELETE FROM table`` without any condition. - - For :meth:`executemany` statements, the number of modifications are summed up - into :attr:`rowcount`. - - As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in - case no ``executeXX()`` has been performed on the cursor or the rowcount of the - last operation is not determinable by the interface". - - This includes ``SELECT`` statements because we cannot determine the number of - rows a query produced until all rows were fetched. - -.. attribute:: Cursor.lastrowid - - This read-only attribute provides the rowid of the last modified row. It is - only set if you issued a ``INSERT`` statement using the :meth:`execute` - method. For operations other than ``INSERT`` or when :meth:`executemany` is - called, :attr:`lastrowid` is set to :const:`None`. - -.. attribute:: Cursor.description - - This read-only attribute provides the column names of the last query. To - remain compatible with the Python DB API, it returns a 7-tuple for each - column where the last six items of each tuple are :const:`None`. - - It is set for ``SELECT`` statements without any matching rows as well. - -.. _sqlite3-row-objects: - -Row Objects ------------ - -.. class:: Row - - A :class:`Row` instance serves as a highly optimized - :attr:`~Connection.row_factory` for :class:`Connection` objects. - It tries to mimic a tuple in most of its features. - - It supports mapping access by column name and index, iteration, - representation, equality testing and :func:`len`. - - If two :class:`Row` objects have exactly the same columns and their - members are equal, they compare equal. - - .. versionchanged:: 2.6 - Added iteration and equality (hashability). - - .. method:: keys - - This method returns a tuple of column names. Immediately after a query, - it is the first member of each tuple in :attr:`Cursor.description`. - - .. versionadded:: 2.6 - -Let's assume we initialize a table as in the example given above:: - - conn = sqlite3.connect(":memory:") - c = conn.cursor() - c.execute('''create table stocks - (date text, trans text, symbol text, - qty real, price real)''') - c.execute("""insert into stocks - values ('2006-01-05','BUY','RHAT',100,35.14)""") - conn.commit() - c.close() - -Now we plug :class:`Row` in:: - - >>> conn.row_factory = sqlite3.Row - >>> c = conn.cursor() - >>> c.execute('select * from stocks') - - >>> r = c.fetchone() - >>> type(r) - - >>> r - (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14) - >>> len(r) - 5 - >>> r[2] - u'RHAT' - >>> r.keys() - ['date', 'trans', 'symbol', 'qty', 'price'] - >>> r['qty'] - 100.0 - >>> for member in r: print member - ... - 2006-01-05 - BUY - RHAT - 100.0 - 35.14 - - -.. _sqlite3-types: - -SQLite and Python types ------------------------ - - -Introduction -^^^^^^^^^^^^ - -SQLite natively supports the following types: ``NULL``, ``INTEGER``, -``REAL``, ``TEXT``, ``BLOB``. - -The following Python types can thus be sent to SQLite without any problem: - -+-----------------------------+-------------+ -| Python type | SQLite type | -+=============================+=============+ -| :const:`None` | ``NULL`` | -+-----------------------------+-------------+ -| :class:`int` | ``INTEGER`` | -+-----------------------------+-------------+ -| :class:`long` | ``INTEGER`` | -+-----------------------------+-------------+ -| :class:`float` | ``REAL`` | -+-----------------------------+-------------+ -| :class:`str` (UTF8-encoded) | ``TEXT`` | -+-----------------------------+-------------+ -| :class:`unicode` | ``TEXT`` | -+-----------------------------+-------------+ -| :class:`buffer` | ``BLOB`` | -+-----------------------------+-------------+ - -This is how SQLite types are converted to Python types by default: - -+-------------+----------------------------------------------+ -| SQLite type | Python type | -+=============+==============================================+ -| ``NULL`` | :const:`None` | -+-------------+----------------------------------------------+ -| ``INTEGER`` | :class:`int` or :class:`long`, | -| | depending on size | -+-------------+----------------------------------------------+ -| ``REAL`` | :class:`float` | -+-------------+----------------------------------------------+ -| ``TEXT`` | depends on :attr:`~Connection.text_factory`, | -| | :class:`unicode` by default | -+-------------+----------------------------------------------+ -| ``BLOB`` | :class:`buffer` | -+-------------+----------------------------------------------+ - -The type system of the :mod:`sqlite3` module is extensible in two ways: you can -store additional Python types in a SQLite database via object adaptation, and -you can let the :mod:`sqlite3` module convert SQLite types to different Python -types via converters. - - -Using adapters to store additional Python types in SQLite databases -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -As described before, SQLite supports only a limited set of types natively. To -use other Python types with SQLite, you must **adapt** them to one of the -sqlite3 module's supported types for SQLite: one of NoneType, int, long, float, -str, unicode, buffer. - -The :mod:`sqlite3` module uses Python object adaptation, as described in -:pep:`246` for this. The protocol to use is :class:`PrepareProtocol`. - -There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python -type to one of the supported ones. - - -Letting your object adapt itself -"""""""""""""""""""""""""""""""" - -This is a good approach if you write the class yourself. Let's suppose you have -a class like this:: - - class Point(object): - def __init__(self, x, y): - self.x, self.y = x, y - -Now you want to store the point in a single SQLite column. First you'll have to -choose one of the supported types first to be used for representing the point. -Let's just use str and separate the coordinates using a semicolon. Then you need -to give your class a method ``__conform__(self, protocol)`` which must return -the converted value. The parameter *protocol* will be :class:`PrepareProtocol`. - -.. literalinclude:: ../includes/sqlite3/adapter_point_1.py - - -Registering an adapter callable -""""""""""""""""""""""""""""""" - -The other possibility is to create a function that converts the type to the -string representation and register the function with :meth:`register_adapter`. - -.. note:: - - The type/class to adapt must be a :term:`new-style class`, i. e. it must have - :class:`object` as one of its bases. - -.. literalinclude:: ../includes/sqlite3/adapter_point_2.py - -The :mod:`sqlite3` module has two default adapters for Python's built-in -:class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose -we want to store :class:`datetime.datetime` objects not in ISO representation, -but as a Unix timestamp. - -.. literalinclude:: ../includes/sqlite3/adapter_datetime.py - - -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 :class:`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 :class:`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 :mod:`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 :ref:`sqlite3-module-contents`, in the entries -for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`. - -The following example illustrates both approaches. - -.. literalinclude:: ../includes/sqlite3/converter_point.py - - -Default adapters and converters -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -There are default adapters for the date and datetime types in the datetime -module. They will be sent as ISO dates/ISO timestamps to SQLite. - -The default converters are registered under the name "date" for -:class:`datetime.date` and under the name "timestamp" for -:class:`datetime.datetime`. - -This way, you can use date/timestamps from Python without any additional -fiddling in most cases. The format of the adapters is also compatible with the -experimental SQLite date/time functions. - -The following example demonstrates this. - -.. literalinclude:: ../includes/sqlite3/pysqlite_datetime.py - - -.. _sqlite3-controlling-transactions: - -Controlling Transactions ------------------------- - -By default, the :mod:`sqlite3` module opens transactions implicitly before a -Data Modification Language (DML) statement (i.e. -``INSERT``/``UPDATE``/``DELETE``/``REPLACE``), and commits transactions -implicitly before a non-DML, non-query statement (i. e. -anything other than ``SELECT`` or the aforementioned). - -So if you are within a transaction and issue a command like ``CREATE TABLE -...``, ``VACUUM``, ``PRAGMA``, the :mod:`sqlite3` module will commit implicitly -before executing that command. There are two reasons for doing that. The first -is that some of these commands don't work within transactions. The other reason -is that pysqlite needs to keep track of the transaction state (if a transaction -is active or not). - -You can control which kind of ``BEGIN`` statements sqlite3 implicitly executes -(or none at all) via the *isolation_level* parameter to the :func:`connect` -call, or via the :attr:`isolation_level` property of connections. - -If you want **autocommit mode**, then set :attr:`isolation_level` to None. - -Otherwise leave it at its default, which will result in a plain "BEGIN" -statement, or set it to one of SQLite's supported isolation levels: "DEFERRED", -"IMMEDIATE" or "EXCLUSIVE". - - - -Using :mod:`sqlite3` efficiently --------------------------------- - - -Using shortcut methods -^^^^^^^^^^^^^^^^^^^^^^ - -Using the nonstandard :meth:`execute`, :meth:`executemany` and -:meth:`executescript` methods of the :class:`Connection` object, your code can -be written more concisely because you don't have to create the (often -superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor` -objects are created implicitly and these shortcut methods return the cursor -objects. This way, you can execute a ``SELECT`` statement and iterate over it -directly using only a single call on the :class:`Connection` object. - -.. literalinclude:: ../includes/sqlite3/shortcut_methods.py - - -Accessing columns by name instead of by index -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -One useful feature of the :mod:`sqlite3` module is the built-in -:class:`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: - -.. literalinclude:: ../includes/sqlite3/rowclass.py - - -Using the connection as a context manager -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -With Python 2.5 or higher, connection objects can be used as context managers -that automatically commit or rollback transactions. In the event of an -exception, the transaction is rolled back; otherwise, the transaction is -committed: - -.. literalinclude:: ../includes/sqlite3/ctx_manager.py - - -Common issues -------------- - -Multithreading -^^^^^^^^^^^^^^ - -Older SQLite versions had issues with sharing connections between threads. -That's why the Python module disallows sharing connections and cursors between -threads. If you still try to do so, you will get an exception at runtime. - -The only exception is calling the :meth:`~Connection.interrupt` method, which -only makes sense to call from a different thread. - -- cgit v1.2.3