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 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 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, e:
         self.assertEqual(e.args[0], "parameter must be callable")

 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 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 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 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 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 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 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 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()