Python dbapi2.connect函数代码示例

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 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 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 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 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 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ä", lambda x, y: (x > y) - (x < y))
         self.fail("should have raised a ProgrammingError")
     except sqlite.ProgrammingError as e:
         pass

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