def extract_data():
# load id data
file = open("movie.txt", "r")
id_list = file.readlines()
for i in range(len(id_list)):
id_list[i] = id_list[i].strip("\n")
# setup
logger = Logger("file").getLogger()
with MyDb("localhost", "root", "", 3306) as db, DoubanAPI() as D:
# create database "movie_info", comment this block if database already created
try:
create_db(db)
except Exception as e:
logger.error(e)
raise Exception("Database creation failed!")
# connect to "movie_info"
db.connect_to_db("movie_info")
# begin extraction and insertion
for id in id_list:
time.sleep(3) # simulate user behaviour
# extraction
try:
D.search(id)
logger.info("Search success for id: " + str(id))
except Exception as e: # error caused by ip ban
logger.error(e)
raise Exception("Ip banned!")
try:
info = d.info()
except Exception as e: # error caused by ip ban
logger.error(e)
raise Exception("Ip banned!")
# insert into entity 'movie'
query = "INSERT INTO `movie` VALUES (%s, %s, %s, %s, %s)"
param = (id, ",".join(info["上映日期"]), info["rating"],
info["rating_count"], ",".join(info["rating_weight"]))
try:
db.execute(query, param)
except Exception as e: # error caused by duplicate id in txt
logger.error(e)
logger.info("Failure in id: " + str(id))
continue
# insert into relations
query = "INSERT INTO `director_movie` VALUES (%s, %s)"
for d in info["director_id"]:
try:
param = (d, id)
db.execute(query, param)
except Exception as e:
logger.error(e)
continue
query = "INSERT INTO `actor_movie` VALUES (%s, %s)"
for a in info["actor_id"]:
try:
param = (a, id)
db.execute(query, param)
except Exception as e:
logger.error(e)
continue
query = "INSERT INTO `writer_movie` VALUES (%s, %s)"
for w in info["screenwriter_id"]:
try:
param = (w, id)
db.execute(query, param)
except Exception as e:
logger.error(e)
continue
query = "INSERT INTO `actor_movie` VALUES (%s, %s)"
for a in info["actor_id"]:
try:
param = (a, id)
db.execute(query, param)
except Exception as e:
logger.error(e)
continue
query = "INSERT INTO `genre_movie` VALUES (%s, %s)"
for g in info["类型"]:
try:
param = (g, id)
db.execute(query, param)
except Exception as e:
logger.error(e)
continue
query = "INSERT INTO `region_movie` VALUES (%s, %s)"
for r in info["制片国家/地区"]:
try:
param = (r, id)
db.execute(query, param)
except Exception as e:
logger.error(e)
continue
query = "INSERT INTO `language_movie` VALUES (%s, %s)"
for l in info["语言"]:
try:
param = (l, id)
db.execute(query, param)
except Exception as e:
logger.error(e)
continue
# if success
logger.info("Success in id: " + str(id))
logger.info("Finish insertion")
time.sleep(3000) # wait until insertion finish successfully
filter_data(db)
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from logger_class import Logger
logger_object = Logger("var/log/class_logger.log")
logger_object.error("This is a error message")
def region_info(db):
header = ("region", "movie_count", "avg_rating")
query = "SELECT region, COUNT(movie_ID) AS movie_count, AVG(rating) AS avg_rating FROM `region_movie` JOIN movie " \
"WHERE movie_ID = ID GROUP BY region "
db.execute_to_csv(query, None, "./data/region_avg.csv", header)
header = ("movie_id", "region_rating")
query = "SELECT ID, AVG(avg_r) AS avg_rating FROM movie JOIN region_movie JOIN (SELECT region, COUNT(movie_ID) AS " \
"movie_count, AVG(rating) AS avg_r FROM `region_movie` JOIN movie WHERE movie_ID = ID GROUP BY region) AS " \
"T WHERE ID = region_movie.movie_ID AND region_movie.region = T.region GROUP BY ID "
db.execute_to_csv(query, None, "./data/region_pred_avg.csv", header)
query = "SELECT ID, rating FROM `movie` WHERE ID NOT IN (SELECT movie_ID FROM region_movie)"
db.execute_to_csv(query, None, "./data/region_empty.csv", header)
if __name__ == '__main__':
logger = Logger("file").getLogger()
make_dir()
with MyDb("localhost", "root", "", 3306, "movie_info") as db:
filter_data(db)
try:
get_movie(db)
actor_info(db)
director_info(db)
writer_info(db)
genre_info(db)
language_info(db)
region_info(db)
except Exception as e:
logger.error(e)