def select_where():
print(
"\n### {} ###".format("SELECT * FROM imdb WHERE imdb_rate > 9.0;"))
for movie in ses.query(Movie).filter(Movie.imdb_rate > 9.0):
print("\t", movie)
print("\n### {} ###".format(
"SELECT * FROM imdb WHERE release_date BETWEEN '1990-01-01' AND '2000-01-01';"
))
for movie in ses.query(Movie).filter(
Movie.release_date.between(date(1900, 1, 1), date(2000, 1,
1))):
print("\t", movie)
print("\n### {} ###".format(
"SELECT * FROM imdb WHERE release_date < '1990-01-01' AND imdb_rate > 8.5;"
))
for movie in ses.query(Movie).filter(
and_(Movie.release_date < date(1990, 1, 1),
Movie.imdb_rate > 8.5)):
print("\t", movie)
print("\n### {} ###".format(
"SELECT * FROM imdb WHERE title in ('The Shawshank Redemption', 'The Godfather');"
))
for movie in ses.query(Movie).filter(
Movie.title.in_(["The Shawshank Redemption",
"The Godfather"])):
print("\t", movie)
def update_data():
### === 方法1, 直接对query的结果进行统一的update ===
# ses.query(Movie).filter(Movie.ID == 1).update({"title": "new title"})
### === 方法2 ===
for movie in ses.query(Movie).all(): # 这种方式, 可以突破sql语句的限制, 进行复杂的条件筛选
if movie.release_date < date(1980,1,1): # 1980年之前的电影标题加后缀[old movie]
ses.query(Movie).filter(Movie.ID == movie.ID).update({"title": movie.title + "[old movie]"})
ses.commit()
prt_all()
# update_data()
def update_data():
### === 方法1, 直接对query的结果进行统一的update ===
# ses.query(Movie).filter(Movie.ID == 1).update({"title": "new title"})
### === 方法2 ===
for movie in ses.query(Movie).all(): # 这种方式, 可以突破sql语句的限制, 进行复杂的条件筛选
if movie.release_date < date(1980, 1, 1): # 1980年之前的电影标题加后缀[old movie]
ses.query(Movie).filter(Movie.ID == movie.ID).update(
{"title": movie.title + "[old movie]"})
ses.commit()
prt_all()
# update_data()
def select_where():
print("\n### {} ###".format("SELECT * FROM imdb WHERE imdb_rate > 9.0;"))
for movie in ses.query(Movie).filter(Movie.imdb_rate > 9.0):
print("\t", movie)
print("\n### {} ###".format("SELECT * FROM imdb WHERE release_date BETWEEN '1990-01-01' AND '2000-01-01';"))
for movie in ses.query(Movie).filter(Movie.release_date.between(date(1900,1,1), date(2000,1,1) ) ):
print("\t", movie)
print("\n### {} ###".format("SELECT * FROM imdb WHERE release_date < '1990-01-01' AND imdb_rate > 8.5;"))
for movie in ses.query(Movie).filter(and_(Movie.release_date < date(1990,1,1), Movie.imdb_rate > 8.5)):
print("\t", movie)
print("\n### {} ###".format("SELECT * FROM imdb WHERE title in ('The Shawshank Redemption', 'The Godfather');"))
for movie in ses.query(Movie).filter(Movie.title.in_(["The Shawshank Redemption",
"The Godfather"])):
print("\t", movie)
def prt_all(): # 为了测试方便, 首先我们要学会如何打印表中的所有内容
"""本函数用于打印目前表中的所有内容, 相当于select *;"""
for movie in ses.query(Movie):
print(movie)
def select_limit():
print("\n### {} ###".format("SELECT * FROM imdb LIMIT 3;"))
for movie in ses.query(Movie).limit(3):
print(movie, type(movie))
def select_order_by():
print("\n### {} ###".format("SELECT * FROM imdb ORDER BY imdb_rate DESC, release_date DESC;"))
for movie in ses.query(Movie).order_by(Movie.imdb_rate.desc(), Movie.release_date.desc()):
print(movie)
def select_all():
print("\n### {} ###".format("SELECT * FROM imdb;"))
for movie in ses.query(Movie):
print(movie, type(movie))
def prt_all(): # 为了测试方便, 首先我们要学会如何打印表中的所有内容
"""本函数用于打印目前表中的所有内容, 相当于select *;"""
for movie in ses.query(Movie):
print(movie)
def select_limit():
print("\n### {} ###".format("SELECT * FROM imdb LIMIT 3;"))
for movie in ses.query(Movie).limit(3):
print(movie, type(movie))
def select_order_by():
print("\n### {} ###".format(
"SELECT * FROM imdb ORDER BY imdb_rate DESC, release_date DESC;"))
for movie in ses.query(Movie).order_by(Movie.imdb_rate.desc(),
Movie.release_date.desc()):
print(movie)
def select_all():
print("\n### {} ###".format("SELECT * FROM imdb;"))
for movie in ses.query(Movie):
print(movie, type(movie))