Python Db.execute方法代码示例

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execute [as 别名]
    def testCheckTables(self):
        db_path = "%s/zeronet.db" % config.data_dir
        schema = {
            "db_name": "TestDb",
            "db_file": "%s/zeronet.db" % config.data_dir,
            "map": {
                "data.json": {
                    "to_table": {
                        "test": "test"
                    }
                }
            },
            "tables": {
                "test": {
                    "cols": [
                        ["test_id", "INTEGER"],
                        ["title", "TEXT"],
                    ],
                    "indexes": ["CREATE UNIQUE INDEX test_id ON test(test_id)"],
                    "schema_changed": 1426195822
                }
            }
        }

        if os.path.isfile(db_path):
            os.unlink(db_path)
        db = Db(schema, db_path)
        db.checkTables()
        db.close()

        # Verify tables
        assert os.path.isfile(db_path)
        db = Db(schema, db_path)

        tables = [row["name"] for row in db.execute("SELECT name FROM sqlite_master WHERE type='table'")]
        assert "keyvalue" in tables  # To store simple key -> value
        assert "json" in tables  # Json file path registry
        assert "test" in tables  # The table defined in dbschema.json

        # Verify test table
        cols = [col["name"] for col in db.execute("PRAGMA table_info(test)")]
        assert "test_id" in cols
        assert "title" in cols

        db.close()

        # Cleanup
        os.unlink(db_path)

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execute [as 别名]
    def testQueries(self):
        db_path = "%s/zeronet.db" % config.data_dir
        schema = {
            "db_name": "TestDb",
            "db_file": "%s/zeronet.db" % config.data_dir,
            "map": {
                "data.json": {
                    "to_table": {
                        "test": "test"
                    }
                }
            },
            "tables": {
                "test": {
                    "cols": [
                        ["test_id", "INTEGER"],
                        ["title", "TEXT"],
                    ],
                    "indexes": ["CREATE UNIQUE INDEX test_id ON test(test_id)"],
                    "schema_changed": 1426195822
                }
            }
        }

        if os.path.isfile(db_path):
            os.unlink(db_path)
        db = Db(schema, db_path)
        db.checkTables()

        # Test insert
        for i in range(100):
            db.execute("INSERT INTO test ?", {"test_id": i, "title": "Test #%s" % i})

        assert db.execute("SELECT COUNT(*) AS num FROM test").fetchone()["num"] == 100

        # Test single select
        assert db.execute("SELECT COUNT(*) AS num FROM test WHERE ?", {"test_id": 1}).fetchone()["num"] == 1

        # Test multiple select
        assert db.execute("SELECT COUNT(*) AS num FROM test WHERE ?", {"test_id": [1,2,3]}).fetchone()["num"] == 3
        assert db.execute("SELECT COUNT(*) AS num FROM test WHERE ?", {"test_id": [1,2,3], "title": "Test #2"}).fetchone()["num"] == 1
        assert db.execute("SELECT COUNT(*) AS num FROM test WHERE ?", {"test_id": [1,2,3], "title": ["Test #2", "Test #3", "Test #4"]}).fetchone()["num"] == 2

        # Test named parameter escaping
        assert db.execute("SELECT COUNT(*) AS num FROM test WHERE test_id = :test_id AND title LIKE :titlelike", {"test_id": 1, "titlelike": "Test%"}).fetchone()["num"] == 1

        db.close()

        # Cleanup
        os.unlink(db_path)

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execute [as 别名]
	file_info = data_file.split("_")
	if not file_info[0] in file_map:
		file_map[file_info[0]] = []
	file_map[file_info[0]].append(data_file)

for spec_filename in spec_files:
	with open("specs/" + spec_filename, "rt") as spec_file:
		filename_info = spec_filename.split(".")
		file = csv.reader(spec_file, delimiter=",")
		next(file) # Skip first row
		columns = []
		create_table_sql = "CREATE TABLE " + filename_info[0] + "(id INT NOT NULL AUTO_INCREMENT"
		for column in file:
			create_table_sql += ", {} {}".format(column[0], column[2])
			columns.append({"name": column[0], "width": int(column[1]), "data_type": column[2]})
		create_table_sql += ", PRIMARY KEY (id));"
		db.execute(create_table_sql);
		if filename_info[0] in file_map:
			for data_filename in file_map[filename_info[0]]:
				with open("data/" + data_filename, "rt") as data_file:
					for line in data_file:
						sql = "INSERT INTO " + filename_info[0] + " SET "
						index = 0
						fields = []
						for column in columns:
							fields.append(column["name"] + " = '" + str(function_map[column["data_type"]](line[index:(index + column["width"])])) + "'")
							index += column["width"]
						sql += ", ".join(fields)
						db.insert(sql)

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execute [as 别名]
		with benchmark("Buffered insert x 100 x 100", 1.3):
			cur = db.getCursor()
			cur.execute("BEGIN")
			cur.logging = False
			for u in range(100, 200): # 100 user
				data = {"test": []}
				for i in range(100): # 1000 line of data
					data["test"].append({"test_id": i, "title": "Testdata for %s message %s" % (u, i)})
				json.dump(data, open("%s/test_%s.json" % (config.data_dir, u), "w"))
				db.loadJson("%s/test_%s.json" % (config.data_dir, u), cur=cur)
				os.unlink("%s/test_%s.json" % (config.data_dir, u))
				if u%10 == 0: yield "."
			cur.execute("COMMIT")

		yield " - Total rows in db: %s<br>" % db.execute("SELECT COUNT(*) AS num FROM test").fetchone()[0]

		with benchmark("Indexed query x 1000", 0.25):
			found = 0
			cur = db.getCursor()
			cur.logging = False
			for i in range(1000): # 1000x by test_id
				res = cur.execute("SELECT * FROM test WHERE test_id = %s" % i)
				for row in res:
					found += 1
				if i%100 == 0: yield "."

			assert found == 20000, "Found: %s != 20000" % found


		with benchmark("Not indexed query x 100", 0.6):

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execute [as 别名]
	def rollback(self):
		db = Db(self.properties);
		db.execute("DROP SCHEMA FileStorage;");

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execute [as 别名]
	def migrate(self):
		db = Db(self.properties);
		db.execute("CREATE SCHEMA FileStorage DEFAULT CHARACTER SET 'utf8';");

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execute [as 别名]
    tables['BookLoans'] = "CREATE Table BookLoans (BookId INTEGER, " \
                          "BranchId INTEGER default 0 NOT NULL, " \
                          "CardNum INTEGER default 0 NOT NULL, " \
                          "DateOut DATE, " \
                          "DateIn DATE, " \
                          "FOREIGN KEY(CardNum) REFERENCES Borrower(CardNum) " \
                          "FOREIGN KEY(BranchId) REFERENCES LibraryBranch(BranchId) " \
                          "FOREIGN KEY(BookId) REFERENCES Book(BookId))"

    tables['Borrower'] = "CREATE Table Borrower (CardNum INTEGER, "\
                          "Name VARCHAR(50) default '' NOT NULL, "\
                          "Address VARCHAR(50) default '' NOT NULL, "\
                          "PRIMARY KEY(CardNum))"

    for skript in tables.values():
        db.execute(skript)

    """
    print("How many copies of the book titled The Lost Tribe "
          "are owned by the library branch whose name is 'Sharpstown'")
    comm1 = "SELECT BookCopies.NumCopies FROM BookCopies, LibraryBranch, Book " \
            "WHERE LibraryBranch.BranchId = BookCopies.BranchId " \
            "AND Book.BookId = BookCopies.BookId " \
            "AND LibraryBranch.BranchName = 'Sharpstown' "\
            "AND Book.Title = 'The Lost Tribe'"
    a = db.query(comm1)
    print(a)

    comm2 = "SELECT BookCopies.NumCopies FROM BookCopies "\
            "JOIN LibraryBranch ON USING(BranchId) "\
            "JOIN Book ON USING(BookId) "\