Python Db.Db类代码示例

 def __init__(self, path):
     Db.__init__(self, {"db_name": "ContentDb", "tables": {}}, path)
     self.foreign_keys = True
     self.schema = self.getSchema()
     self.checkTables()
     self.site_ids = {}
     self.sites = {}

class LldpCtl(lldpy.Watcher):
	""" A custom class to hook into llpdctl utility. """
	def __init__(self, interface):
		super(LldpCtl, self).__init__()
		self.location = socket.gethostname().upper()
		self.interface = interface
		self.db = Db()
		self.is_alive = True

	def on_add(self, local, remote):
		if local.interface_name == self.interface:
			self.location = self.db.get_topology(remote.chassis_name)[-1][3]
			self.location = self.location.upper()

	def on_delete(self, local, remote):
		if local.interface_name == self.interface:
			self.location = socket.gethostname()
			self.location = self.location.upper()

	def on_update(self, local, remote):
		if local.interface_name == self.interface:
			self.location = self.db.get_topology(remote.chassis_name)[-1][3]
			self.location = self.location.upper()

	def get_location(self):
		return self.location

    def testDb(self):
        from Db import Db
        for db_path in [os.path.abspath("%s/test/zeronet.db" % config.data_dir), "%s/test/zeronet.db" % config.data_dir]:
            print "Creating db using %s..." % db_path,
            schema = {
                "db_name": "TestDb",
                "db_file": "%s/test/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("%s/test/zeronet.db" % config.data_dir):
                os.unlink("%s/test/zeronet.db" % config.data_dir)
            db = Db(schema, "%s/test/zeronet.db" % config.data_dir)
            db.checkTables()
            db.close()

            # Cleanup
            os.unlink("%s/test/zeronet.db" % config.data_dir)
            os.rmdir("%s/test/" % config.data_dir)

def test():
	p = Prudence('ad','bb','localhost')
	user = p.get_raw_user()
	User = p.get_user()
	c = p.get_companies()
	d = Db('localhost')
	c1 =d.getCompanies()
	print type(c)
	print type(c[0])
	self.assertEqual(type(c),type(c1),'i tipi esterni no')
	self.assertEqual(type(c[0]),type(c1[0]),'i tipi interni no')

 def __init__(self, path):
     Db.__init__(self, {"db_name": "ContentDb", "tables": {}}, path)
     self.foreign_keys = True
     try:
         self.schema = self.getSchema()
         self.checkTables()
     except Exception, err:
         self.log.error("Error loading content.db: %s, rebuilding..." % Debug.formatException(err))
         self.close()
         os.unlink(path)  # Remove and try again
         self.schema = self.getSchema()
         self.checkTables()

    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)

def db(request):
    db_path = "%s/zeronet.db" % config.data_dir
    schema = {
        "db_name": "TestDb",
        "db_file": "%s/zeronet.db" % config.data_dir,
        "maps": {
            "data.json": {
                "to_table": [
                    "test",
                    {"node": "test", "table": "test_importfilter", "import_cols": ["test_id", "title"]}
                ]
            }
        },
        "tables": {
            "test": {
                "cols": [
                    ["test_id", "INTEGER"],
                    ["title", "TEXT"],
                    ["json_id", "INTEGER REFERENCES json (json_id)"]
                ],
                "indexes": ["CREATE UNIQUE INDEX test_id ON test(test_id)"],
                "schema_changed": 1426195822
            },
            "test_importfilter": {
                "cols": [
                    ["test_id", "INTEGER"],
                    ["title", "TEXT"],
                    ["json_id", "INTEGER REFERENCES json (json_id)"]
                ],
                "indexes": ["CREATE UNIQUE INDEX test_importfilter_id ON test_importfilter(test_id)"],
                "schema_changed": 1426195822
            }
        }
    }

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

    def stop():
        db.close()
        os.unlink(db_path)

    request.addfinalizer(stop)
    return db

	def openDb(self, check=True):
		schema = self.loadJson("dbschema.json")
		db_path = self.getPath(schema["db_file"])
		if check:
			if not os.path.isfile(db_path) or os.path.getsize(db_path) == 0: # Not exits or null
				self.rebuildDb()
		self.db = Db(schema, db_path)
		if check and not self.db_checked: 
			changed_tables = self.db.checkTables()
			if changed_tables: self.rebuildDb(delete_db=False) # Todo only update the changed table datas

	def test(self, spec_filename):
		function_map = {
			"TEXT": str,
			"INTEGER": int,
			"BOOLEAN": int,
			"DATE": str
		}

		properties = Properties.getProperties()
		db = Db(properties)

		data_files = os.listdir("data")

		file_map = {}
		for data_file in data_files:
			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)

		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 = []
			for column in file:
				columns.append({"name": column[0], "width": int(column[1]), "data_type": column[2]})
			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 = "SELECT count(*) as total FROM {} WHERE ".format(filename_info[0])
							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 += " AND ".join(fields)
							row = db.select(sql)[0]
							if row["total"]	!= 1:
								raise Exception("Line: {} was not inserted".format(line.strip()))

class App:
    user = None
    cfg = None
    db = None

    def __init__(self, cfg):
        self.cfg = cfg
        self.db = Db()
        if self.db:
            self.db.connect(cfg.getDbPath())
    
    def routing(self):
        return { 
            '/echo(/.*)?': self.echo
            }

    def echo(self, vars, params):
        params[u'path_vars'] = vars
        return self.to_json_response(params)

    def to_json_response(self, data):
        s = json.dumps(data, indent=2)
        return ('text/json', s)

	def __init__(self, interface):
		super(LldpCtl, self).__init__()
		self.location = socket.gethostname().upper()
		self.interface = interface
		self.db = Db()
		self.is_alive = True

import sqlite3
from Db import Db

if __name__ == '__main__':
    db = Db('classes.db')
    db.execue_script('classes.sql')

    print("Find all students who took a class in California "
          "from an instructor not in the student's major department and "
          "got a score over 80. Return the student name, university, and score.")
    comm1 = "SELECT DISTINCT Student.name, Class.univ, Took.score " \
            "FROM Student, Class, Took, Instructor " \
            "WHERE Student.studID = Took.studID " \
            "AND Instructor.instID = Took.instID " \
            "AND Class.classID = Took.classID " \
            "AND Student.major != Instructor.dept " \
            "AND Took.score >= 80 " \
            "AND Class.region = 'CA'"
    a = db.query(comm1)
    print(a)

    comm1 = "SELECT DISTINCT Student.name, Class.univ, Took.score FROM Student "\
            "JOIN Took ON Student.studID = Took.studID "\
            "JOIN Instructor ON Instructor.instID = Took.instID "\
            "JOIN Class ON Class.classID = Took.classID "\
            "WHERE Student.major != Instructor.dept "\
            "AND Took.score >= 80 "\
            "AND Class.region = 'CA'"
    a = db.query(comm1)
    print(a)

import sqlite3
from Db import Db

if __name__ == '__main__':
    db = Db('social.db')
    db.execue_script('social.sql')

    print("1 Find the names of all students who are friends with someone named Gabriel.")
    comm1 = "SELECT Highschooler.name FROM Highschooler " \
            "WHERE Highschooler.ID IN " \
            "(SELECT Friend.ID2 FROM Highschooler, Friend " \
            "WHERE Highschooler.ID = Friend.ID1 " \
            "AND Highschooler.name = 'Gabriel')"
    a = db.query(comm1)
    print(a)

    print("2. For every student who likes someone 2 or more "
          "grades younger than themselves, \n"
          "return that student's name and grade, \n"
          "and the name and grade of the student they like.")
    comm1 = "SELECT (SELECT name FROM HighSchooler, Likes "\
            "WHERE HighSchooler.ID = L.ID1), "\
            "(SELECT grade from HighSchooler, Likes "\
            "WHERE HighSchooler.ID = L.ID1), "\
            "name, grade "\
            "FROM HighSchooler H, Likes L "\
            "WHERE H.ID = L.ID2 and H.grade + 2 <= "\
            "(SELECT grade from HighSchooler  "\
            "WHERE HighSchooler.ID=L.ID1)"
    a = db.query(comm1)
    print(a)

	def __init__(self):
		self._r = redis.Redis(host='127.0.0.1',port=6379)
		self._db = Db.getinstance()

import sqlite3
from Db import Db

if __name__ == '__main__':
    db = Db('rating.db')
    db.execue_script('rating.sql')

    comm1 = "SELECT Movie.title FROM Movie WHERE director = 'Steven Spielberg'"
    a = db.query(comm1)
    print(a)

    print("2. Find all years that have a movie that received a rating of 4 or 5 "
          "and sort them in increasing order.")
    comm1 = "SELECT DISTINCT Movie.year FROM Movie, Rating " \
            "WHERE Movie.mID = Rating.mID " \
            "AND Rating.stars >= 4 " \
            "ORDER BY Movie.year ASC"
    a = db.query(comm1)
    print(a)

    print("3. Find the titles of all movies that have no ratings.")
    comm1 = "SELECT Movie.title FROM Movie " \
            "WHERE NOT EXISTS "\
            "(SELECT * FROM Rating "\
            "WHERE Movie.mID = Rating.mID)"
    a = db.query(comm1)
    print(a)

    print("4. Some reviewers didn't provide a date with their rating. "
          "Find the names of all reviewers who have "
          "ratings with a NULL value for the date.")