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 test_OpenUri(self):
if sqlite.sqlite_version_info < (3, 7, 7):
with self.assertRaises(sqlite.NotSupportedError):
sqlite.connect(':memory:', uri=True)
return
self.addCleanup(unlink, TESTFN)
with sqlite.connect(TESTFN) as cx:
cx.execute('create table test(id integer)')
with sqlite.connect('file:' + TESTFN, uri=True) as cx:
cx.execute('insert into test(id) values(0)')
with sqlite.connect('file:' + TESTFN + '?mode=ro', uri=True) as cx:
with self.assertRaises(sqlite.OperationalError):
cx.execute('insert into test(id) values(1)')
def setUp(self):
self.con = sqlite.connect(":memory:")
cur = self.con.cursor()
cur.execute("""
create table test(
t text,
i integer,
f float,
n,
b blob
)
""")
cur.execute("insert into test(t, i, f, n, b) values (?, ?, ?, ?, ?)", (
"foo",
5,
3.14,
None,
memoryview(b"blob"),
))
self.con.create_aggregate("nostep", 1, AggrNoStep)
self.con.create_aggregate("nofinalize", 1, AggrNoFinalize)
self.con.create_aggregate("excInit", 1, AggrExceptionInInit)
self.con.create_aggregate("excStep", 1, AggrExceptionInStep)
self.con.create_aggregate("excFinalize", 1, AggrExceptionInFinalize)
self.con.create_aggregate("checkType", 2, AggrCheckType)
self.con.create_aggregate("checkTypes", -1, AggrCheckTypes)
self.con.create_aggregate("mysum", 1, AggrSum)
def test_CursorExecutescriptAsBytes(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
with self.assertRaises(ValueError) as cm:
cur.executescript(
b"create table test(foo); insert into test(foo) values (5);")
self.assertEqual(str(cm.exception), 'script argument must be unicode.')
def test_CommitCursorReset(self):
"""
Connection.commit() did reset cursors, which made sqlite3
to return rows multiple times when fetched from cursors
after commit. See issues 10513 and 23129 for details.
"""
con = sqlite.connect(":memory:")
con.executescript("""
create table t(c);
create table t2(c);
insert into t values(0);
insert into t values(1);
insert into t values(2);
""")
self.assertEqual(con.isolation_level, "")
counter = 0
for i, row in enumerate(con.execute("select c from t")):
with self.subTest(i=i, row=row):
con.execute("insert into t2(c) values (?)", (i, ))
con.commit()
if counter == 0:
self.assertEqual(row[0], 0)
elif counter == 1:
self.assertEqual(row[0], 1)
elif counter == 2:
self.assertEqual(row[0], 2)
counter += 1
self.assertEqual(counter, 3, "should have returned exactly three rows")
def test_modifying_progress(self):
journal = []
def progress(status, remaining, total):
if not journal:
self.cx.execute('INSERT INTO foo (key) VALUES (?)',
(remaining + 1000, ))
self.cx.commit()
journal.append(remaining)
self.verify_backup(self.cx)
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, pages=1, progress=progress)
self.verify_backup(bck)
result = bck.execute("SELECT key FROM foo"
" WHERE key >= 1000"
" ORDER BY key").fetchall()
self.assertEqual(result[0][0], 1001)
self.assertEqual(len(journal), 3)
self.assertEqual(journal[0], 1)
self.assertEqual(journal[1], 1)
self.assertEqual(journal[2], 0)
def test_ConvertTimestampMicrosecondPadding(self):
"""
http://bugs.python.org/issue14720
The microsecond parsing of convert_timestamp() should pad with zeros,
since the microsecond string "456" actually represents "456000".
"""
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("CREATE TABLE t (x TIMESTAMP)")
# Microseconds should be 456000
cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")
# Microseconds should be truncated to 123456
cur.execute(
"INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")
cur.execute("SELECT * FROM t")
values = [x[0] for x in cur.fetchall()]
self.assertEqual(values, [
datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
])
def test_AutoCommit(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 test_SetIsolationLevel(self):
# See issue 27881.
class CustomStr(str):
def upper(self):
return None
def __del__(self):
con.isolation_level = ""
con = sqlite.connect(":memory:")
con.isolation_level = None
for level in "", "DEFERRED", "IMMEDIATE", "EXCLUSIVE":
with self.subTest(level=level):
con.isolation_level = level
con.isolation_level = level.lower()
con.isolation_level = level.capitalize()
con.isolation_level = CustomStr(level)
# setting isolation_level failure should not alter previous state
con.isolation_level = None
con.isolation_level = "DEFERRED"
pairs = [
(1, TypeError),
(b'', TypeError),
("abc", ValueError),
("IMMEDIATE\0EXCLUSIVE", ValueError),
("\xe9", ValueError),
]
for value, exc in pairs:
with self.subTest(level=value):
with self.assertRaises(exc):
con.isolation_level = value
self.assertEqual(con.isolation_level, "DEFERRED")
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, unique_name TEXT UNIQUE
);
""")
def test_NullCharacter(self):
# Issue #21147
con = sqlite.connect(":memory:")
self.assertRaises(ValueError, con, "\0select 1")
self.assertRaises(ValueError, con, "select 1\0")
cur = con.cursor()
self.assertRaises(ValueError, cur.execute, " \0select 2")
self.assertRaises(ValueError, cur.execute, "select 2\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 test_TraceCallbackContent(self):
# set_trace_callback() shouldn't produce duplicate content (bpo-26187)
traced_statements = []
def trace(statement):
traced_statements.append(statement)
queries = ["create table foo(x)",
"insert into foo(x) values(1)"]
self.addCleanup(unlink, TESTFN)
con1 = sqlite.connect(TESTFN, isolation_level=None)
con2 = sqlite.connect(TESTFN)
con1.set_trace_callback(trace)
cur = con1.cursor()
cur.execute(queries[0])
con2.execute("create table bar(x)")
cur.execute(queries[1])
self.assertEqual(traced_statements, queries)
def test_bad_target_in_transaction(self):
bck = sqlite.connect(':memory:')
bck.execute('CREATE TABLE bar (key INTEGER)')
bck.executemany('INSERT INTO bar (key) VALUES (?)', [(3, ), (4, )])
with self.assertRaises(sqlite.OperationalError) as cm:
self.cx.backup(bck)
if sqlite.sqlite_version_info < (3, 8, 8):
self.assertEqual(str(cm.exception), 'target is in transaction')
def test_failing_progress(self):
def progress(status, remaining, total):
raise SystemError('nearly out of space')
with self.assertRaises(SystemError) as err:
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, progress=progress)
self.assertEqual(str(err.exception), 'nearly out of space')
def test_ConnectionExecutemany(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 test_ClosedCreateFunction(self):
con = sqlite.connect(":memory:")
con.close()
def f(x):
return 17
with self.assertRaises(sqlite.ProgrammingError):
con.create_function("foo", 1, f)
def test_ClosedSetAuthorizer(self):
con = sqlite.connect(":memory:")
con.close()
def authorizer(*args):
return sqlite.DENY
with self.assertRaises(sqlite.ProgrammingError):
con.set_authorizer(authorizer)
def test_ClosedSetProgressCallback(self):
con = sqlite.connect(":memory:")
con.close()
def progress():
pass
with self.assertRaises(sqlite.ProgrammingError):
con.set_progress_handler(progress, 100)
def test_PragmaSchemaVersion(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 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.decode("ascii")
sqlite.converters["BAR"] = lambda x: "<%s>" % x.decode("ascii")
sqlite.converters["EXC"] = lambda x: 5 / 0
sqlite.converters["B1B1"] = lambda x: "MARKER"
def test_database_source_name(self):
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, name='main')
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, name='temp')
with self.assertRaises(sqlite.OperationalError) as cm:
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, name='non-existing')
self.assertIn(
str(cm.exception),
['SQL logic error', 'SQL logic error or missing database'])
self.cx.execute("ATTACH DATABASE ':memory:' AS attached_db")
self.cx.execute('CREATE TABLE attached_db.foo (key INTEGER)')
self.cx.executemany('INSERT INTO attached_db.foo (key) VALUES (?)',
[(3, ), (4, )])
self.cx.commit()
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, name='attached_db')
self.verify_backup(bck)
def test_DeregisterCollation(self):
"""
Register a collation, then deregister it. Make sure an error is raised if we try
to use it.
"""
con = sqlite.connect(":memory:")
con.create_collation("mycoll", lambda x, y: (x > y) - (x < y))
con.create_collation("mycoll", None)
with self.assertRaises(sqlite.OperationalError) as cm:
con.execute("select 'a' as x union select 'b' as x order by x collate mycoll")
self.assertEqual(str(cm.exception), 'no such collation sequence: mycoll')
def test_TraceCallbackUsed(self):
"""
Test that the trace callback is invoked once it is set.
"""
con = sqlite.connect(":memory:")
traced_statements = []
def trace(statement):
traced_statements.append(statement)
con.set_trace_callback(trace)
con.execute("create table foo(a, b)")
self.assertTrue(traced_statements)
self.assertTrue(any("create table foo" in stmt for stmt in traced_statements))
def test_ClearTraceCallback(self):
"""
Test that setting the trace callback to None clears the previously set callback.
"""
con = sqlite.connect(":memory:")
traced_statements = []
def trace(statement):
traced_statements.append(statement)
con.set_trace_callback(trace)
con.set_trace_callback(None)
con.execute("create table foo(a, b)")
self.assertFalse(traced_statements, "trace callback was not cleared")
def test_progress_all_pages_at_once_2(self):
journal = []
def progress(status, remaining, total):
journal.append(remaining)
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, pages=-1, progress=progress)
self.verify_backup(bck)
self.assertEqual(len(journal), 1)
self.assertEqual(journal[0], 0)
def test_OpenWithPathLikeObject(self):
""" Checks that we can successfully connect to a database using an object that
is PathLike, i.e. has __fspath__(). """
self.addCleanup(unlink, TESTFN)
class Path:
def __fspath__(self):
return TESTFN
path = Path()
with sqlite.connect(path) as cx:
cx.execute('create table test(id integer)')
def test_ScriptStringSql(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 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(self.authorizer_cb)
def test_progress(self):
journal = []
def progress(status, remaining, total):
journal.append(status)
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, pages=1, progress=progress)
self.verify_backup(bck)
self.assertEqual(len(journal), 2)
self.assertEqual(journal[0], sqlite.SQLITE_OK)
self.assertEqual(journal[1], sqlite.SQLITE_DONE)