Python Db.execue_script方法代码示例

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execue_script [as 别名]
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.")

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execue_script [as 别名]
import sqlite3
from Db import Db

if __name__ == '__main__':
    db = Db('test.db')
    db.execue_script('xjadralci.sql')
    comm = "SELECT ime, rating FROM Jadralec j WHERE rating%2=0"
    k = db.query(comm)
    print(k)

    comm = "SELECT c1.ime, c1.cid FROM Coln c1, Coln c2 WHERE c1.ime = c2.ime AND c1.cid != c2.cid"
    k = db.query(comm)
    print(k)

    comm = "SELECT c.ime, c.dolzina, j.starost FROM Coln c " \
           "JOIN Rezervacija r USING(cid) " \
           "JOIN Jadralec j USING(jid) " \
           "WHERE c.dolzina > 35 " \
           "AND j.starost > 35"
    k = db.query(comm)
    print(k)

    comm = "SELECT c.ime, c.dolzina, j.starost FROM Coln c, Rezervacija r, Jadralec j " \
           "WHERE c.cid = r.cid " \
           "AND r.jid = j.jid " \
           "AND c.dolzina > 35 "\
           "AND j.starost > 35"
    k = db.query(comm)
    print(k)

    comm = "SELECT DISTINCT j.ime, r.dan FROM Jadralec j LEFT JOIN Rezervacija r"

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execue_script [as 别名]
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)

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execue_script [as 别名]
import sqlite3
from Db import Db

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

    print("Print the names and ages of each employee who works in both "
          "Sports and Travel department.")
    comm1 = "SELECT Emp.ename, Emp.age FROM Emp, Works w1, Works w2, Dept d1, Dept d2 "\
            "WHERE Emp.eid = w1.eid AND w1.did = d1.did AND d1.dname = 'Travels' AND " \
            "Emp.eid = w2.eid AND w2.did = d2.did AND d2.dname = 'Sports'"
    a = db.query(comm1)
    print(a)

    comm1 = "SELECT Emp.ename, Emp.age FROM Emp, Dept, Works "\
            "WHERE Works.eid = Emp.eid "\
            "AND Works.did = Dept.did " \
            "AND Dept.dname = 'Travels' " \
            "AND Emp.eid IN "\
            "(SELECT Emp.eid FROM Emp, Dept, Works "\
            "WHERE Works.eid = Emp.eid "\
            "AND Works.did = Dept.did " \
            "AND Dept.dname = 'Sports')"
    a = db.query(comm1)
    print(a)

    print("For each department with more than 1 full employees "
          "(where the part-time add up to at least "
          "that many fulltime employees),\n"

# 需要导入模块: from Db import Db [as 别名]
# 或者: from Db.Db import execue_script [as 别名]
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)