def databse_conect_create(self):
conn = Database()
# cnn = conn.get_cursor()
try:
sql = """create table if not exists score(
id int(11) NOT NULL PRIMARY KEY AUTHORIZATION ,
course_num VARCHAR(6) not null,
course_code VARCHAR(15) not NULL ,
course_name VARCHAR (30) not null,
course_type VARCHAR(20) not null,
course_score DECIMAL(3,2),
course_evaluation VARCHAR(5),
course_total VARCHAR(5),
course_points DECIMAL(4,2) not NULL ,
PRIMARY key(stu_id,course_code)
)"""
conn.execute(sql)
# conn.get_cursor().close()
# conn.get_database().close()
except Exception as e:
print(e)
finally:
# del conn
# log = Logger('all.log', level='info')
# log.logger.warning('警告已经存在')
# Logger('warning.log', level='warning').logger.warning('warning')
conn.get_cursor().close()
# print(result)
# conn.get_database().close()
# return conn
self.conn = conn.get_database()
def get_phone(self, username):
conn = Database()
try:
check_sql = "select phone from username_phone where username = '******' " % username
con = conn.get_cursor()
rs = con.execute(check_sql)
if rs > 0:
user_phone = con.fetchone()[0]
return user_phone
except Exception as e:
print(e)
print(337)
finally:
conn.get_cursor().close()
conn.get_database().close()
def get_user(self, begin, num): #获得所有用户信息从本地 、、、、、
conn = Database()
sql = "select * from user limit {},{}".format(begin, num)
try:
user = conn.execute(sql)
# user = []
# with open("user.csv") as f:
# reder = csv.reader(f)
# for u in reder:
# user.append(u)
# print(len(user))
# print(user)
if user > 0:
user = conn.get_cursor().fetchall()
# print(user)
# user = [["20154862120","252829",""]]
# user = (('20181110713','cy.20000526','081029'),('20171261649','122619',None),('20181120626','1234567890qq',None))
# user = (('20181120106','xy850850',None),)
return user
except Exception as e:
traceback.print_exc()
print(e)
print(109)
sava_text(e)
finally:
pass
def get_course_name(self, course_code):
conn = Database()
try:
check_sql = "select 课程名称 from au_2019_2020 where 课程代码 = '%s' " % course_code
con = conn.get_cursor()
rs = con.execute(check_sql)
if rs > 0:
course_name = con.fetchone()[0]
return course_name
return ""
except Exception as e:
print(e)
print(320)
finally:
conn.get_cursor().close()
conn.get_database().close()
def get_score_content(self, username):
scor_url = self.url
data = {"projectType": "MAJOR"}
html = self.session.post(scor_url, data=data)
html = etree.HTML(html.content.decode("utf-8"))
# print(etree.tostring(html, encoding="utf-8").decode("utf-8"))
tr_data = html.xpath("//table/tbody[contains(@id,'data')]//tr")
score_data = []
conn = Database()
for tr in tr_data:
if not tr.xpath(".//td"):
continue
semester = "".join(tr.xpath(".//td[1]//text()"))
try:
sql = "select semester_id from semester_year where name='%s'" % semester
rs = conn.execute(sql)
if rs > 0:
semester = conn.get_cursor().fetchone()[0]
except Exception as e:
print(e)
semester = ""
courseid = self.hand_space("".join(tr.xpath(".//td[3]//text()")))
code = "".join(tr.xpath(".//td[2]//text()"))
if "/" in code:
coursecode = re.sub("\W*", "", code) + "/"
else:
coursecode = re.sub("\W*", "", code)
courseevaluation = self.hand_space("".join(
tr.xpath(".//td")[-2].xpath(".//text()")))
coursescore = self.hand_space("".join(
tr.xpath(".//td")[-1].xpath(".//text()")))
score_dic = {
"semester": semester,
"username": username,
"course_id": courseid,
"course_code": coursecode,
"course_evaluation": courseevaluation,
"course_score": coursescore,
}
score_data.append(score_dic) # 成绩列表
return score_data
def get_score_content(self):
con = Database()
state = False
scor_url = "https://jx.sspu.edu.cn/eams/teach/grade/course/person!search.action"
data = {"semesterId": self.__lessionid, "projectType": ""}
# try:
html = self.session.post(scor_url, data=data)
# except Exception as e:
# sava_text(e)
# while True:
# print("进入循环错误")
# try:
# quit_url()
# except Exception as e:
# sava_text(e)
# state = proiexes()
# if state:
# break
# if state:
# html = self.session.post(scor_url, data=data)
html = etree.HTML(html.content.decode("utf-8"))
# print(etree.tostring(html,encoding="utf-8").decode("utf-8"))
# print(self.__username)
tr_data = html.xpath("//table/tbody[contains(@id,'data')]//tr")
score_data = []
con = Database()
for tr in tr_data:
if not tr.xpath(".//td"):
continue
semester = "".join(tr.xpath(".//td[1]//text()"))
try:
sql = "select semester_id from semester_year where name='%s'" % semester
rs = con.execute(sql)
if rs > 0:
semester = con.get_cursor().fetchone()[0]
except Exception as e:
print(e)
print(172)
semester = ""
courseid = self.hand_space("".join(tr.xpath(".//td[3]//text()")))
code = "".join(tr.xpath(".//td[2]//text()"))
# print(code)
if "/" in code:
coursecode = re.sub("\W*", "", code) + "/"
else:
coursecode = re.sub("\W*", "", code)
courseevaluation = self.hand_space("".join(
tr.xpath(".//td")[-2].xpath(".//text()")))
coursescore = self.hand_space("".join(
tr.xpath(".//td")[-1].xpath(".//text()")))
score_dic = {
"semester": semester,
"username": self.__username,
"course_id": courseid,
"course_code": coursecode,
"course_evaluation": courseevaluation,
"course_score": coursescore,
}
score_data.append(score_dic) # 成绩列表
con.get_cursor().close()
con.get_database().close()
return score_data