def CheckCreateCollationBadUpper(self): class BadUpperStr(str): def upper(self): return None con = sqlite.connect(":memory:") mycoll = lambda x, y: -((x > y) - (x < y)) con.create_collation(BadUpperStr("mycoll"), mycoll) result = con.execute(""" select x from ( select 'a' as x union select 'b' as x ) order by x collate mycoll """).fetchall() self.assertEqual(result[0][0], 'b') self.assertEqual(result[1][0], 'a')
def setUp(self): self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES) self.cur = self.con.cursor() self.cur.execute( "create table test(i int, s str, f float, b bool, u unicode, foo foo, bin blob, n1 number, n2 number(5), bad bad)" ) # override float, make them always return the same number sqlite.converters["FLOAT"] = lambda x: 47.2 # and implement two custom ones sqlite.converters["BOOL"] = lambda x: bool(int(x)) sqlite.converters["FOO"] = DeclTypesTests.Foo sqlite.converters["BAD"] = DeclTypesTests.BadConform sqlite.converters["WRONG"] = lambda x: "WRONG" sqlite.converters["NUMBER"] = float
def CheckInTransaction(self): # Can't use db from setUp because we want to test initial state. cx = sqlite.connect(":memory:") cu = cx.cursor() self.assertEqual(cx.in_transaction, False) cu.execute( "create table transactiontest(id integer primary key, name text)") self.assertEqual(cx.in_transaction, False) cu.execute("insert into transactiontest(name) values (?)", ("foo", )) self.assertEqual(cx.in_transaction, True) cu.execute("select name from transactiontest where name=?", ["foo"]) row = cu.fetchone() self.assertEqual(cx.in_transaction, True) cx.commit() self.assertEqual(cx.in_transaction, False) cu.execute("select name from transactiontest where name=?", ["foo"]) row = cu.fetchone() self.assertEqual(cx.in_transaction, False)
def setUp(self): self.con = sqlite.connect(":memory:") self.con.create_function("returntext", 0, func_returntext) self.con.create_function("returnunicode", 0, func_returnunicode) self.con.create_function("returnint", 0, func_returnint) self.con.create_function("returnfloat", 0, func_returnfloat) self.con.create_function("returnnull", 0, func_returnnull) self.con.create_function("returnblob", 0, func_returnblob) self.con.create_function("returnlonglong", 0, func_returnlonglong) self.con.create_function("raiseexception", 0, func_raiseexception) self.con.create_function("isstring", 1, func_isstring) self.con.create_function("isint", 1, func_isint) self.con.create_function("isfloat", 1, func_isfloat) self.con.create_function("isnone", 1, func_isnone) self.con.create_function("isblob", 1, func_isblob) self.con.create_function("islonglong", 1, func_islonglong) self.con.create_function("spam", -1, func)
def CheckCollationReturnsLargeInteger(self): def mycoll(x, y): # reverse order return -((x > y) - (x < y)) * 2**32 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() self.assertEqual(result, [('c', ), ('b', ), ('a', )], msg="the expected order was not returned")
def CheckRecursiveCursorUse(self): """ http://bugs.python.org/issue10811 Recursively using a cursor, such as when reusing it from a generator led to segfaults. Now we catch recursive cursor usage and raise a ProgrammingError. """ con = sqlite.connect(":memory:") cur = con.cursor() cur.execute("create table a (bar)") cur.execute("create table b (baz)") def foo(): cur.execute("insert into a (bar) values (?)", (1, )) yield 1 with self.assertRaises(sqlite.ProgrammingError): cur.executemany("insert into b (baz) values (?)", ((i, ) for i in foo()))
def get_messages(config, key): logger.info('Connecting to sql/db.sqlite, reading messages...') conn = sqlite.connect(os.path.join(config['signalDir'], 'sql/db.sqlite')) try: c = conn.cursor() c.execute(f"PRAGMA key=\"x'{key}'\"") for setting, value in config.get('sqlcipher', {}).items(): c.execute(f"PRAGMA {setting}={value}") c.execute("select json from items where id=?", ('number_id', )) number_id = json.loads(c.fetchone()[0]) own_number, device_id = number_id['value'].split('.') logger.info('Own number: %s, device ID: %s', own_number, device_id) cond = ["hasVisualMediaAttachments > 0"] if not config.get('includeExpiringMessages', False): cond.append("expires_at is null") c.execute(f""" select id, json from messages where { ' and '.join(cond) } order by sent_at asc {f'limit {config["maxMessages"]}' if config["maxMessages"] > 0 else ''} """) for row in c: msg = json.loads(row[1]) if 'source' not in msg and msg['type'] == 'outgoing': msg['source'] = own_number yield (row[0], msg) except sqlite.DatabaseError as err: logger.fatal( 'DatabaseError "%s" - please check the database and the sqlcipher parameters!', ' | '.join(err.args)) finally: conn.close()
def CheckUnicodeContent(self): """ Test that the statement can contain unicode literals. """ unicode_value = '\xf6\xe4\xfc\xd6\xc4\xdc\xdf\u20ac' con = sqlite.connect(":memory:") traced_statements = [] def trace(statement): traced_statements.append(statement) con.set_trace_callback(trace) con.execute("create table foo(x)") # Can't execute bound parameters as their values don't appear # in traced statements before SQLite 3.6.21 # (cf. http://www.sqlite.org/draft/releaselog/3_6_21.html) con.execute('insert into foo(x) values ("%s")' % unicode_value) con.commit() self.assertTrue( any(unicode_value in stmt for stmt in traced_statements), "Unicode data %s garbled in trace callback: %s" % (ascii(unicode_value), ', '.join(map(ascii, traced_statements))))
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) 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 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 CheckOpcodeCount(self): """ Test that the opcode argument is respected. """ con = sqlite.connect(":memory:") progress_calls = [] def progress(): progress_calls.append(None) return 0 con.set_progress_handler(progress, 1) curs = con.cursor() curs.execute(""" create table foo (a, b) """) first_count = len(progress_calls) progress_calls = [] con.set_progress_handler(progress, 2) curs.execute(""" create table bar (a, b) """) second_count = len(progress_calls) self.assertGreaterEqual(first_count, second_count)
def setUp(self): self.con = sqlite.connect(":memory:", factory=MyConnection)
def setUp(self): self.con = sqlite.connect(":memory:") self.con.execute("create table test (value text)") self.con.execute("insert into test (value) values (?)", ("a\x00b", ))
def setUp(self): self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES) self.cur = self.con.cursor() self.cur.execute("create table test(d date, ts timestamp)")
def setUp(self): self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES) sqlite.register_converter("bin", BinaryConverterTests.convert)
def setUp(self): self.con = sqlite.connect(":memory:") self.cur = self.con.cursor() self.cur.execute( "create table test(i integer, s varchar, f number, b blob)")
def CheckFailedOpen(self): YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db" with self.assertRaises(sqlite.OperationalError): con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
def CheckClosedCurExecute(self): con = sqlite.connect(":memory:") cur = con.cursor() con.close() with self.assertRaises(sqlite.ProgrammingError): cur.execute("select 4")
def CheckConnectionExecute(self): con = sqlite.connect(":memory:") result = con.execute("select 5").fetchone()[0] self.assertEqual(result, 5, "Basic test of Connection.execute")
def CheckScriptErrorNormal(self): con = sqlite.connect(":memory:") cur = con.cursor() with self.assertRaises(sqlite.OperationalError): cur.executescript( "create table test(sadfsadfdsa); select foo from hurz;")
def CheckScriptSyntaxError(self): con = sqlite.connect(":memory:") cur = con.cursor() with self.assertRaises(sqlite.OperationalError): cur.executescript( "create table test(x); asdf; create table test2(x)")
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, unique_test text unique)") self.cu.execute("insert into test(name) values (?)", ("foo", ))
def CheckSameThreadErrorOnOldVersion(self): with self.assertRaises(sqlite.NotSupportedError) as cm: sqlite.connect(':memory:', check_same_thread=False) self.assertEqual(str(cm.exception), 'shared connections not available')
def CheckClosedCall(self): con = sqlite.connect(":memory:") con.close() with self.assertRaises(sqlite.ProgrammingError): con()
def setUp(self): self.con = sqlite.connect(":memory:")
def CheckConnectionExecutescript(self): con = sqlite.connect(":memory:") con.executescript( "create table test(foo); insert into test(foo) values (5);") result = con.execute("select foo from test").fetchone()[0] self.assertEqual(result, 5, "Basic test of Connection.executescript")
logger = logging.getLogger(__name__) if not sys.platform.startswith('win'): import coloredlogs coloredlogs.install(level='INFO') args = parse_args() dataDir = args.data_dir if not os.access(os.path.dirname(args.db_file), os.X_OK) and not os.access( args.db_file, os.W_OK): print("无法创建或打开数据库文件") exit() elif not os.access(dataDir, os.X_OK): print("数据目录不存在或权限不足") exit() conn = sqlite.connect(args.db_file) while True: try: passwd = getpass.getpass("输入数据库密码:") if passwd != '': conn.execute("PRAGMA KEY = %s" % passwd) conn.execute("CREATE TABLE IF NOT EXISTS msg (\ msg_id TEXT NOT NULL,\ create_time INTEGER NOT NULL,\ msg_type INTEGER NOT NULL,\ content TEXT,\ from_name TEXT,\ from_nickname TEXT,\ to_name TEXT,\ to_nickname TEXT,\ group_name TEXT\
def setUp(self): self.con = sqlite.connect(":memory:") self.cur = self.con.cursor() self.cur.execute("create table test(x foo)")
def setUp(self): self.con = sqlite.connect(":memory:") self.cur = self.con.cursor() self.cur.execute( "create table test(id integer primary key, name text, bin binary, ratio number, ts timestamp)" )
def setUp(self): self.cx = sqlite.connect(":memory:") self.cx.execute( "create table test(id integer primary key, blob_col blob)") self.cx.execute("insert into test(blob_col) values (zeroblob(100))")