def create_db():
if sqlite.version_info > (2, 0):
if use_custom_types:
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
sqlite.register_converter("text", lambda x: "<%s>" % x)
else:
con = sqlite.connect(":memory:")
if use_dictcursor:
cur = con.cursor(factory=DictCursor)
elif use_rowcursor:
cur = con.cursor(factory=RowCursor)
else:
cur = con.cursor()
else:
if use_tuple:
con = sqlite.connect(":memory:")
con.rowclass = tuple
cur = con.cursor()
else:
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("""
create table test(v text, f float, i integer)
""")
return (con, cur)
def create_db():
if sqlite.version_info > (2, 0):
if use_custom_types:
con = sqlite.connect(":memory:",
detect_types=sqlite.PARSE_DECLTYPES
| sqlite.PARSE_COLNAMES)
sqlite.register_converter("text", lambda x: "<%s>" % x)
else:
con = sqlite.connect(":memory:")
if use_dictcursor:
cur = con.cursor(factory=DictCursor)
elif use_rowcursor:
cur = con.cursor(factory=RowCursor)
else:
cur = con.cursor()
else:
if use_tuple:
con = sqlite.connect(":memory:")
con.rowclass = tuple
cur = con.cursor()
else:
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("""
create table test(v text, f float, i integer)
""")
return (con, cur)
def setUp(self):
try:
os.remove(get_db_path())
except OSError:
pass
self.con1 = sqlite.connect(get_db_path(), timeout=0.1)
self.cur1 = self.con1.cursor()
self.con2 = sqlite.connect(get_db_path(), timeout=0.1)
self.cur2 = self.con2.cursor()
def CheckCreateCollationNotAscii(self):
con = sqlite.connect(":memory:")
try:
con.create_collation("collä", cmp)
self.fail("should have raised a ProgrammingError")
except sqlite.ProgrammingError, e:
pass
def CheckAutoCommit(self):
"""
Verifies that creating a connection in autocommit mode works.
2.5.3 introduced a regression so that these could no longer
be created.
"""
con = sqlite.connect(":memory:", isolation_level=None)
def CheckSetIsolationLevel(self):
"""
See issue 3312.
"""
con = sqlite.connect(":memory:")
self.assertRaises(UnicodeEncodeError, setattr, con,
"isolation_level", u"\xe9")
def CheckCreateCollationNotCallable(self):
con = sqlite.connect(":memory:")
try:
con.create_collation("X", 42)
self.fail("should have raised a TypeError")
except TypeError, e:
self.assertEqual(e.args[0], "parameter must be callable")
def CheckCollationIsUsed(self):
if sqlite.version_info < (3, 2,
1): # old SQLite versions crash on this test
return
def mycoll(x, y):
# reverse order
return -cmp(x, y)
con = sqlite.connect(":memory:")
con.create_collation("mycoll", mycoll)
sql = """
select x from (
select 'a' as x
union
select 'b' as x
union
select 'c' as x
) order by x collate mycoll
"""
result = con.execute(sql).fetchall()
if result[0][0] != "c" or result[1][0] != "b" or result[2][0] != "a":
self.fail("the expected order was not returned")
con.create_collation("mycoll", None)
try:
result = con.execute(sql).fetchall()
self.fail("should have raised an OperationalError")
except sqlite.OperationalError, e:
self.assertEqual(e.args[0].lower(),
"no such collation sequence: mycoll")
def CheckClosed(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.close()
for method_name in ("execute", "executemany", "executescript",
"fetchall", "fetchmany", "fetchone"):
if method_name in ("execute", "executescript"):
params = ("select 4 union select 5", )
elif method_name == "executemany":
params = ("insert into foo(bar) values (?)", [(3, ), (4, )])
else:
params = []
try:
method = getattr(cur, method_name)
method(*params)
self.fail("Should have raised a ProgrammingError: method " +
method_name)
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError: " +
method_name)
def CheckFailedOpen(self):
YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
try:
con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
except sqlite.OperationalError:
return
self.fail("should have raised an OperationalError")
def CheckCreateCollationNotAscii(self):
con = sqlite.connect(":memory:")
try:
con.create_collation("collä", cmp)
self.fail("should have raised a ProgrammingError")
except sqlite.ProgrammingError, e:
pass
def CheckConnectionExecutemany(self):
con = sqlite.connect(":memory:")
con.execute("create table test(foo)")
con.executemany("insert into test(foo) values (?)", [(3,), (4,)])
result = con.execute("select foo from test order by foo").fetchall()
self.assertEqual(result[0][0], 3, "Basic test of Connection.executemany")
self.assertEqual(result[1][0], 4, "Basic test of Connection.executemany")
def CheckCollationIsUsed(self):
if sqlite.version_info < (3, 2, 1): # old SQLite versions crash on this test
return
def mycoll(x, y):
# reverse order
return -cmp(x, y)
con = sqlite.connect(":memory:")
con.create_collation("mycoll", mycoll)
sql = """
select x from (
select 'a' as x
union
select 'b' as x
union
select 'c' as x
) order by x collate mycoll
"""
result = con.execute(sql).fetchall()
if result[0][0] != "c" or result[1][0] != "b" or result[2][0] != "a":
self.fail("the expected order was not returned")
con.create_collation("mycoll", None)
try:
result = con.execute(sql).fetchall()
self.fail("should have raised an OperationalError")
except sqlite.OperationalError, e:
self.assertEqual(e.args[0].lower(), "no such collation sequence: mycoll")
def setUp(self):
self.cx = sqlite.connect(":memory:")
self.cu = self.cx.cursor()
self.cu.execute(
"create table test(id integer primary key, name text, income number)"
)
self.cu.execute("insert into test(name) values (?)", ("foo", ))
def CheckSetIsolationLevel(self):
"""
See issue 3312.
"""
con = sqlite.connect(":memory:")
self.assertRaises(UnicodeEncodeError, setattr, con, "isolation_level",
u"\xe9")
def CheckAutoCommit(self):
"""
Verifies that creating a connection in autocommit mode works.
2.5.3 introduced a regression so that these could no longer
be created.
"""
con = sqlite.connect(":memory:", isolation_level=None)
def CheckCreateCollationNotCallable(self):
con = sqlite.connect(":memory:")
try:
con.create_collation("X", 42)
self.fail("should have raised a TypeError")
except TypeError, e:
self.assertEqual(e.args[0], "parameter must be callable")
def CheckFailedOpen(self):
YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
try:
con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
except sqlite.OperationalError:
return
self.fail("should have raised an OperationalError")
def read_modify_write():
# Open connection and create example schema and data.
# In reality, open a database file instead of an in-memory database.
con = sqlite4.connect(":memory:")
cur = con.cursor()
cur.executescript("""
create table test(id integer primary key, data);
insert into test(data) values ('foo');
insert into test(data) values ('bar');
insert into test(data) values ('baz');
""")
# The read part. There are two ways for fetching data using pysqlite.
# 1. "Lazy-reading"
# cur.execute("select ...")
# for row in cur:
# ...
#
# Advantage: Low memory consumption, good for large resultsets, data is
# fetched on demand.
# Disadvantage: Database locked as long as you iterate over cursor.
#
# 2. "Eager reading"
# cur.fetchone() to fetch one row
# cur.fetchall() to fetch all rows
# Advantage: Locks cleared ASAP.
# Disadvantage: fetchall() may build large lists.
cur.execute("select id, data from test where id=?", (2,))
row = cur.fetchone()
# Stupid way to modify the data column.
lst = list(row)
lst[1] = lst[1] + " & more"
# This is the suggested recipe to modify data using pysqlite. We use
# pysqlite's proprietary API to use the connection object as a context
# manager. This is equivalent to the following code:
#
# try:
# cur.execute("...")
# except:
# con.rollback()
# raise
# finally:
# con.commit()
#
# This makes sure locks are cleared - either by commiting or rolling back
# the transaction.
#
# If the rollback happens because of concurrency issues, you just have to
# try again until it succeeds. Much more likely is that the rollback and
# the raised exception happen because of other reasons, though (constraint
# violation, etc.) - don't forget to roll back on errors.
#
# Or use this recipe. It's useful and gets everything done in two lines of
# code.
with con:
cur.execute("update test set data=? where id=?", (lst[1], lst[0]))
def setUp(self):
self.con = sqlite.connect(":memory:")
try:
del sqlite.adapters[int]
except:
pass
sqlite.register_adapter(int, ObjectAdaptationTests.cast)
self.cur = self.con.cursor()
def setUp(self):
self.con = sqlite.connect(":memory:")
try:
del sqlite.adapters[int]
except:
pass
sqlite.register_adapter(int, ObjectAdaptationTests.cast)
self.cur = self.con.cursor()
def CheckScriptErrorNormal(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
raised = False
try:
cur.executescript("create table test(sadfsadfdsa); select foo from hurz;")
except sqlite.OperationalError:
raised = True
self.assertEqual(raised, True, "should have raised an exception")
def CheckScriptSyntaxError(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
raised = False
try:
cur.executescript("create table test(x); asdf; create table test2(x)")
except sqlite.OperationalError:
raised = True
self.assertEqual(raised, True, "should have raised an exception")
def CheckPragmaSchemaVersion(self):
# This still crashed pysqlite <= 2.2.1
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
try:
cur = self.con.cursor()
cur.execute("pragma schema_version")
finally:
cur.close()
con.close()
def CheckConnectionExecutemany(self):
con = sqlite.connect(":memory:")
con.execute("create table test(foo)")
con.executemany("insert into test(foo) values (?)", [(3, ), (4, )])
result = con.execute("select foo from test order by foo").fetchall()
self.assertEqual(result[0][0], 3,
"Basic test of Connection.executemany")
self.assertEqual(result[1][0], 4,
"Basic test of Connection.executemany")
def setUp(self):
self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
self.cur = self.con.cursor()
self.cur.execute("create table test(x foo)")
sqlite.converters["FOO"] = lambda x: "[%s]" % x
sqlite.converters["BAR"] = lambda x: "<%s>" % x
sqlite.converters["EXC"] = lambda x: 5 // 0
sqlite.converters["B1B1"] = lambda x: "MARKER"
def CheckPragmaSchemaVersion(self):
# This still crashed pysqlite <= 2.2.1
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
try:
cur = self.con.cursor()
cur.execute("pragma schema_version")
finally:
cur.close()
con.close()
def getcon():
#con = sqlite.connect("db", isolation_level=None, timeout=5.0)
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table test(i, s)")
for i in range(10):
cur.execute("insert into test(i, s) values (?, 'asfd')", (i, ))
con.commit()
cur.close()
return con
def CheckClosedCall(self):
con = sqlite.connect(":memory:")
con.close()
try:
con()
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckScriptSyntaxError(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
raised = False
try:
cur.executescript(
"create table test(x); asdf; create table test2(x)")
except sqlite.OperationalError:
raised = True
self.assertEqual(raised, True, "should have raised an exception")
def CheckScriptErrorNormal(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
raised = False
try:
cur.executescript(
"create table test(sadfsadfdsa); select foo from hurz;")
except sqlite.OperationalError:
raised = True
self.assertEqual(raised, True, "should have raised an exception")
def setUp(self):
self.con = sqlite.connect(":memory:",
detect_types=sqlite.PARSE_COLNAMES)
self.cur = self.con.cursor()
self.cur.execute("create table test(x foo)")
sqlite.converters["FOO"] = lambda x: "[%s]" % x
sqlite.converters["BAR"] = lambda x: "<%s>" % x
sqlite.converters["EXC"] = lambda x: 5 // 0
sqlite.converters["B1B1"] = lambda x: "MARKER"
def CheckClosedCall(self):
con = sqlite.connect(":memory:")
con.close()
try:
con()
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def getcon():
#con = sqlite.connect("db", isolation_level=None, timeout=5.0)
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table test(i, s)")
for i in range(10):
cur.execute("insert into test(i, s) values (?, 'asfd')", (i,))
con.commit()
cur.close()
return con
def CheckClosedSetProgressCallback(self):
con = sqlite.connect(":memory:")
con.close()
def progress(): pass
try:
con.set_progress_handler(progress, 100)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckUnicodeConnect(self):
"""
With pysqlite 2.4.0 you needed to use a string or a APSW connection
object for opening database connections.
Formerly, both bytestrings and unicode strings used to work.
Let's make sure unicode strings work in the future.
"""
con = sqlite.connect(u":memory:")
con.close()
def CheckClosedCurExecute(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
con.close()
try:
cur.execute("select 4")
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckUnicodeConnect(self):
"""
With pysqlite 2.4.0 you needed to use a string or a APSW connection
object for opening database connections.
Formerly, both bytestrings and unicode strings used to work.
Let's make sure unicode strings work in the future.
"""
con = sqlite.connect(u":memory:")
con.close()
def CheckClosedCreateFunction(self):
con = sqlite.connect(":memory:")
con.close()
def f(x): return 17
try:
con.create_function("foo", 1, f)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def test():
con = sqlite4.connect(":memory:")
cur = con.cursor(EagerCursor)
cur.execute("create table test(foo)")
cur.executemany("insert into test(foo) values (?)", [(3,), (4,), (5,)])
cur.execute("select * from test")
print cur.fetchone()
print cur.fetchone()
print cur.fetchone()
print cur.fetchone()
print cur.fetchone()
def test():
con = sqlite4.connect(":memory:")
cur = con.cursor(EagerCursor)
cur.execute("create table test(foo)")
cur.executemany("insert into test(foo) values (?)", [(3, ), (4, ), (5, )])
cur.execute("select * from test")
print cur.fetchone()
print cur.fetchone()
print cur.fetchone()
print cur.fetchone()
print cur.fetchone()
def CheckClosedCurExecute(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
con.close()
try:
cur.execute("select 4")
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckScriptStringSql(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.executescript("""
-- bla bla
/* a stupid comment */
create table a(i);
insert into a(i) values (5);
""")
cur.execute("select i from a")
res = cur.fetchone()[0]
self.assertEqual(res, 5)
def CheckClosedSetAuthorizer(self):
con = sqlite.connect(":memory:")
con.close()
def authorizer(*args):
return sqlite.DENY
try:
con.set_authorizer(authorizer)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckScriptStringSql(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.executescript("""
-- bla bla
/* a stupid comment */
create table a(i);
insert into a(i) values (5);
""")
cur.execute("select i from a")
res = cur.fetchone()[0]
self.assertEqual(res, 5)
def CheckPragmaAutocommit(self):
"""
Verifies that running a PRAGMA statement that does an autocommit does
work. This did not work in 2.5.3/2.5.4.
"""
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table foo(bar)")
cur.execute("insert into foo(bar) values (5)")
cur.execute("pragma page_size")
row = cur.fetchone()
def CheckPragmaAutocommit(self):
"""
Verifies that running a PRAGMA statement that does an autocommit does
work. This did not work in 2.5.3/2.5.4.
"""
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table foo(bar)")
cur.execute("insert into foo(bar) values (5)")
cur.execute("pragma page_size")
row = cur.fetchone()
def CheckStatementFinalizationOnCloseDb(self):
# pysqlite versions <= 2.3.3 only finalized statements in the statement
# cache when closing the database. statements that were still
# referenced in cursors weren't closed an could provoke "
# "OperationalError: Unable to close due to unfinalised statements".
con = sqlite.connect(":memory:")
cursors = []
# default statement cache size is 100
for i in range(105):
cur = con.cursor()
cursors.append(cur)
cur.execute("select 1 x union select " + str(i))
con.close()
def CheckScriptStringUnicode(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.executescript(u"""
create table a(i);
insert into a(i) values (5);
select i from a;
delete from a;
insert into a(i) values (6);
""")
cur.execute("select i from a")
res = cur.fetchone()[0]
self.assertEqual(res, 6)
def CheckStatementFinalizationOnCloseDb(self):
# pysqlite versions <= 2.3.3 only finalized statements in the statement
# cache when closing the database. statements that were still
# referenced in cursors weren't closed an could provoke "
# "OperationalError: Unable to close due to unfinalised statements".
con = sqlite.connect(":memory:")
cursors = []
# default statement cache size is 100
for i in range(105):
cur = con.cursor()
cursors.append(cur)
cur.execute("select 1 x union select " + str(i))
con.close()
def create_db():
con = sqlite.connect(":memory:")
if use_autocommit:
if use_pysqlite4:
con.isolation_level = None
else:
con.autocommit = True
cur = con.cursor()
cur.execute("""
create table test(v text, f float, i integer)
""")
cur.close()
return con
def CheckClearHandler(self):
"""
Test that setting the progress handler to None clears the previously set handler.
"""
con = sqlite.connect(":memory:")
action = 0
def progress():
action = 1
return 0
con.set_progress_handler(progress, 1)
con.set_progress_handler(None, 1)
con.execute("select 1 union select 2 union select 3").fetchall()
self.assertEqual(action, 0, "progress handler was not cleared")
def CheckCollationRegisterTwice(self):
"""
Register two different collation functions under the same name.
Verify that the last one is actually used.
"""
con = sqlite.connect(":memory:")
con.create_collation("mycoll", cmp)
con.create_collation("mycoll", lambda x, y: -cmp(x, y))
result = con.execute("""
select x from (select 'a' as x union select 'b' as x) order by x collate mycoll
""").fetchall()
if result[0][0] != 'b' or result[1][0] != 'a':
self.fail("wrong collation function is used")
def CheckCollationRegisterTwice(self):
"""
Register two different collation functions under the same name.
Verify that the last one is actually used.
"""
con = sqlite.connect(":memory:")
con.create_collation("mycoll", cmp)
con.create_collation("mycoll", lambda x, y: -cmp(x, y))
result = con.execute("""
select x from (select 'a' as x union select 'b' as x) order by x collate mycoll
""").fetchall()
if result[0][0] != 'b' or result[1][0] != 'a':
self.fail("wrong collation function is used")
def CheckScriptStringUnicode(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.executescript(u"""
create table a(i);
insert into a(i) values (5);
select i from a;
delete from a;
insert into a(i) values (6);
""")
cur.execute("select i from a")
res = cur.fetchone()[0]
self.assertEqual(res, 6)
def create_db():
con = sqlite.connect(":memory:")
if use_autocommit:
if use_pysqlite4:
con.isolation_level = None
else:
con.autocommit = True
cur = con.cursor()
cur.execute("""
create table test(v text, f float, i integer)
""")
cur.close()
return con
def setUp(self):
self.con = sqlite.connect(":memory:")
self.con.executescript("""
create table t1 (c1, c2);
create table t2 (c1, c2);
insert into t1 (c1, c2) values (1, 2);
insert into t2 (c1, c2) values (4, 5);
""")
# For our security test:
self.con.execute("select c2 from t2")
self.con.set_authorizer(authorizer_cb)
def CheckClosedSetAuthorizer(self):
con = sqlite.connect(":memory:")
con.close()
def authorizer(*args):
return sqlite.DENY
try:
con.set_authorizer(authorizer)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckClosedSetProgressCallback(self):
con = sqlite.connect(":memory:")
con.close()
def progress():
pass
try:
con.set_progress_handler(progress, 100)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
