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): self.db = get_db_path() self.con1 = sqlite.connect(self.db, timeout=0.1) self.cur1 = self.con1.cursor() self.con2 = sqlite.connect(self.db, timeout=0.1) self.cur2 = self.con2.cursor()
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 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 CheckCreateCollationNotCallable(self): con = sqlite.connect(":memory:") try: con.create_collation("X", 42) self.fail("should have raised a TypeError") except TypeError as e: self.assertEqual(e.args[0], "parameter must be callable")
def CheckConvertTimestampMicrosecondPadding(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 CheckConvertTimestampMicrosecondPadding(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 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 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 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 CheckCollationIsUsed(self): def mycoll(x, y): # reverse order return -((x > y) - (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 as e: self.assertEqual(e.args[0].lower(), "no such collation sequence: mycoll")
def CheckCreateCollationNotAscii(self): con = sqlite.connect(":memory:") try: con.create_collation("collä", lambda x, y: (x > y) - (x < y)) self.fail("should have raised a ProgrammingError") except sqlite.ProgrammingError as e: pass
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 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("mysum", 1, AggrSum)
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 = sqlite3.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 read_modify_write(): # Open connection and create example schema and data. # In reality, open a database file instead of an in-memory database. con = sqlite3.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 CheckNullCharacter(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 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 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 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 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 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 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 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 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 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 test(): con = sqlite3.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 test(): con = sqlite3.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 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 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 CheckClearTraceCallback(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 CheckTraceCallbackUsed(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 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 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", lambda x, y: (x > y) - (x < y)) con.create_collation("mycoll", lambda x, y: -((x > y) - (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 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))") # 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["WRONG"] = lambda x: "WRONG" sqlite.converters["NUMBER"] = float
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 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_pysqlightning: 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 on_created(self, event): conn = sqlite.connect('mydb') c = conn.cursor() c.execute('''insert or ignore into file values ((?), '2', '3', 4, 5)''', (event.src_path[len(path):], )) conn.commit() print '--------' c.execute('select * from file') for row in c: print row c.close() print '--------' print event.src_path