def select_platform_course_name(platform_name, course_name):
if platform_name == None or course_name == None:
return None
if course_name == "":
return None
sql = "SELECT DISTINCT * FROM COURSE WHERE platform_name = %s and (course_name like %s or course_name like %s or course_name like %s or course_name= %s);"
courses = []
conn = set_conect()
cursor = conn.cursor()
try:
cursor.execute(sql,
(platform_name, '%' + course_name + '%',
'%' + course_name, course_name + '%', course_name))
conn.commit()
result = cursor.fetchall()
for row in result:
c = Course(course_name=row[1],
course_type=row[2],
course_url=row[3],
platform_name=row[4])
c.id = row[0]
courses.append(c)
except:
print("select_platform_name error")
return None
finally:
cursor.close()
conn.close()
return courses
def select_course_type_accuracy(course_type):
if course_type == None:
return None
if course_type == "":
return []
sql = "SELECT DISTINCT * FROM COURSE WHERE course_type= %s;"
courses = []
conn = set_conect()
cursor = conn.cursor()
try:
cursor.execute(sql, (course_type))
result = cursor.fetchall()
conn.commit()
for row in result:
c = Course(course_name=row[1],
course_type=row[2],
course_url=row[3],
platform_name=row[4])
c.id = row[0]
courses.append(c)
except:
print("select_course_type_accuracy error")
return None
finally:
cursor.close()
conn.close()
return courses
def test_deleteid3(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
db.drop_course_table()
db.init_course_table()
db.insert_courses_table(s)
# print(db.select_all_course()[0].id)
self.assertFalse(db.delete_course_table2(None))
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
db.drop_course_table()
db.init_course_table()
db.insert_courses_table(s)
# print(db.select_all_course()[0].id)
self.assertFalse(db.delete_course_table2(None))
def test_selectplatformcoursename2(self):
a = Course(course_name="物理", course_type="sss", course_url="4399", platform_name="s")
b = Course(course_name="化学", course_type="sss", course_url="4399d", platform_name="rt")
c=Course(course_name="高数", course_type="sss", course_url="4399s", platform_name="rt")
s = [a, b,c]
db.insert_courses_table(s)
self.assertEqual(db.select_platform_course_name("rt"," "),[])
def test_selecttypeaccuracy3(self):
a = Course(course_name="物理", course_type="sss", course_url="4399", platform_name="s")
b = Course(course_name="化学", course_type="sss", course_url="4399d", platform_name="rt")
c = Course(course_name="高数", course_type="sss", course_url="4399s", platform_name="rt")
s = [a, b, c]
db.insert_courses_table(s)
self.assertEqual(db.select_course_type_accuracy(None),None)
def test_selectplatformname3(self):
a = Course(course_name="这是一个测试", course_type="ss", course_url="4399", platform_name="f")
b = Course(course_name="这是一个测试", course_type="sss", course_url="4399d", platform_name="rt")
c=Course(course_name="这是s测试", course_type="325s", course_url="439d", platform_name="y")
s = [a, b]
db.insert_courses_table(s)
self.assertEqual(db.select_platform_name(c.platform_name),[])
def test_selectcoursetype2(self):
a = Course(course_name="这是一个测试", course_type="sss", course_url="4399", platform_name="")
b = Course(course_name="这是一个测试", course_type="sss", course_url="4399d", platform_name="rt")
s = [a, b]
db.insert_courses_table(s)
self.assertEqual(db.select_course_type("ss")[0].course_type,a.course_type)
self.assertEqual(db.select_course_type(a.course_type)[1].course_type,a.course_type)
def test_delete3(self):
a = Course(course_name="这是一个测试", course_type="ss", course_url="4399", platform_name="")
b = Course(course_name="这是一个测试", course_type="sss", course_url="4399d", platform_name="rt")
s = [a, b]
db.insert_courses_table(s)
c = Course(course_name="测试", course_type="ssds", course_url="sjw", platform_name="t")
self.assertTrue(db.delete_course_table(c))
def test_update1(self):
a = Course(course_name="这是一个测试", course_type="ss", course_url="4399", platform_name="")
b = Course(course_name="这是一个测试", course_type="sss", course_url="4399d", platform_name="rt")
s = [a, b]
c = Course(course_name="这个测试", course_type="sss", course_url="4399", platform_name="r")
db.drop_course_table()
db.init_course_table()
db.insert_courses_table(s)
self.assertTrue(db.update_course_table(1, c))
def test_course(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
db.insert_courses_table(s)
def test_insertmany6(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399d",
platform_name="")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
self.assertTrue(db.insert_courses_table(s))
def test_delete4(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
db.insert_courses_table(s)
self.assertFalse(db.delete_course_table(None))
def test_selectplatformname4(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
db.insert_courses_table(s)
self.assertEqual(db.select_platform_name(None), None)
def test_selectall1(self):
a = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399",
platform_name="s")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
db.insert_courses_table(s)
self.assertEqual(db.select_all_course()[0].course_name, a.course_name)
def test_update2(self):
a = Course(course_name="这是一个测试", course_type="ss", course_url="4399", platform_name="")
b = Course(course_name="这是一个测试", course_type="sss", course_url="4399d", platform_name="rt")
s = [a, b]
c = Course(course_name="这个测试", course_type="sss", course_url="4399", platform_name="r")
db.drop_course_table()
db.init_course_table()
db.insert_courses_table(s)
self.assertTrue(db.update_course_table(3, c))
#print(db.select_all_course()[3].course_name)
self.assertFalse(db.update_course_table(None, None))
self.assertFalse(db.update_course_table(1, None))
self.assertFalse(db.update_course_table(None, c))
def getInfoFromYoutebeForPython():
# 构造浏览器对象
broswer = Chrome()
url = "https://www.youtube.com/results?search_query=python"
# 滚动页面方法
js = "var q =document.documentElement.scrollTop=1000"
js2 = 'window.scrollTo(0, document.body.scrollHeight)'
broswer.get(url)
# print(broswer.page_source)
# 滚动页面参数。range值越大，滚得越多
for i in range(3):
broswer.execute_script(js2)
time.sleep(5)
# 单个视频类型
xpath = '//*[@id="video-title"]'
# 列表清单的视频类型path
xpathList = '//*[@id="content"]/a'
# 获取html中视频信息
contentV = broswer.find_elements_by_xpath(xpath)
contentVList = broswer.find_elements_by_xpath(xpathList)
# print(content[0].get_attribute("href"))
CourseItem = []
for item in contentVList:
# print(item.get_attribute("href"))
CourseNameL = item.find_element_by_id("video-title").get_attribute(
"title")
CourseURLL = item.get_attribute("href")
#获取视频列表的href
course = Course(CourseNameL, "Youtube", "program", CourseURLL)
CourseItem.append(course)
# print(CourseNameL,CourseURLL) #check
for item2 in contentV:
CourseName = item2.get_attribute("title")
CourseURL = item2.get_attribute("href")
if CourseURL != None:
course = Course(CourseName, "Youtube", "program", CourseURL)
CourseItem.append(course)
# print(CourseName,CourseURL) #check
return CourseItem
# print(getInfoFromYoutebeForPython()) #check
def test_insert6(self):
a = Course(course_name="这是一个测试这是一个测试一个测试",
course_type="ss",
course_url="4399",
platform_name="sss")
self.assertTrue(db.insert_course_table(a))
def getInfoFromImmocForPython():
mylist = []
uu = 0
num = 3
url = [
"https://www.imooc.com/course/list?c=python" +
"&page={}".format(str(i)) for i in range(1, num)
] #爬取网页从第一页到num-1页
#爬虫基本配置
while uu <= num - 2:
req2 = requests.get(url[uu])
soup2 = BeautifulSoup(req2.text, "html.parser")
cardContainer = soup2.select(".course-card-container")
#记录
uu = uu + 1
#记录
for i in cardContainer:
courseName = i.select("h3")[0].text
courseURL = "https://www.imooc.com" + i.select("a")[0][
"href"] #获得每个课程的后缀，需要自行添加网页前缀，如https://www.imooc.com/learn/1240
course = Course(course_name=courseName,
course_type="Python",
course_url=courseURL,
platform_name="IMOOC")
mylist.append(course)
return mylist
def test_insert2(self):
a = Course(course_name="",
course_type="",
course_url="",
platform_name="")
self.assertTrue(db.insert_course_table(a))
def test_delete5(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
db.delete_all_course_table()
self.assertTrue(db.delete_course_table(a))
def aiqiyi_Python():
option = ChromeOptions()
option.add_argument("--headless")
browser = Chrome(options=option)
browser.get(
'https://so.iqiyi.com/so/q_Python?source=input&sr=1088234053533')
page = 1
mylist = []
while page <= 30:
i = 1
while i <= 10:
xpath = '//*[@id="__layout"]/div/div[1]/div/div/div[2]/div[2]/div[1]/div[2]/div[1]/div[2]/div[2]/div/ul/li[%s]/div/div[1]/a' % i
c = browser.find_element_by_xpath(xpath)
course = Course(course_name=c.get_attribute('title'),
course_type='Python',
course_url=c.get_attribute('href'),
platform_name='爱奇艺')
mylist.append(course)
i = i + 1
but = browser.find_element_by_xpath(
'//*[@id="__layout"]/div/div[1]/div/div/div[2]/div[2]/div[1]/div[2]/div[1]/div[2]/div[3]/div/a'
)
but.click()
page = page + 1
return mylist
def test_insert9(self):
a = Course(course_name="hh",
course_type="ss",
course_url="4399",
platform_name="sss")
db.insert_course_table(a)
self.assertTrue(db.insert_course_table(a))
def test_insertmany5(self):
a = Course(course_name="这一个测试",
course_type="ss",
course_url="4399",
platform_name="")
b = None
s = [a, b]
self.assertTrue(db.insert_courses_table(s))
def test_insert7(self):
a = Course(
course_name="这ddd一个测试这s是一个测试一个测试",
course_type=
"s是sssssssssssssssssssssssssssssssssssssssssssssssssssssddddddddddddddddddddddddddddddddddddddddddds",
course_url="4399",
platform_name="sss")
self.assertFalse(db.insert_course_table(a))
def test_insert4(self):
a = Course(
course_name="aaasssss",
course_type=
"saaaaaaaaa555sssssssssssssssss55555555555555555sssssssssssssssssssssssssssssssssssss",
course_url="4399",
platform_name="sss")
self.assertFalse(db.insert_course_table(a))
def select_all_course():
sql = "SELECT DISTINCT * FROM COURSE ORDER BY course_type;"
courses = []
conn = set_conect()
cursor = conn.cursor()
try:
cursor.execute(sql)
conn.commit()
result = cursor.fetchall()
for row in result:
c = Course(course_name=str(row[1]),
course_type=str(row[2]),
course_url=str(row[3]),
platform_name=str(row[4]))
c.id = row[0]
courses.append(c)
except:
print("select_all_course error")
return None
finally:
cursor.close()
conn.close()
return courses
def worm_bilibili_PS():#爬取bilibili的python关键字的视频并返回标题和链接组成的数据字典
i=2
url_0='https://search.bilibili.com/video?keyword=PS%E6%95%99%E7%A8%8B'
resp_0=requests.get(url_0)
html=resp_0.text
while i<=50:
url='https://search.bilibili.com/video?keyword=PS%E6%95%99%E7%A8%8B'+'&page=%s'%i
i=i+1
time.sleep(0.5)
resp=requests.get(url)
html = html+resp.text
soup=BeautifulSoup(html,'html.parser')
context=soup.find_all("a",attrs={"class":"title"})
mylist=[]
for i in context:
value_href=i.get('href')
value_title=i.get('title')
course = Course(course_name=value_title,course_type="PS",course_url=value_href,platform_name="bilibili")
mylist.append(course)
return mylist
def test_insertmany4(self):
a = Course(course_name="这s一个测试", course_type="ssdfsjklkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkks", course_url="4399", platform_name="")
b = Course(course_name="这是一个测试", course_type="sss", course_url="4399d", platform_name="rt")
s = [a, b]
self.assertFalse(db.insert_courses_table(s))
def test_selectplatformname5(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
self.assertEqual(db.select_platform_name(a), None)
def test_course(self):
a = Course(course_name="这是一个测试",
course_type="ss",
course_url="4399",
platform_name="")
b = Course(course_name="这是一个测试",
course_type="sss",
course_url="4399d",
platform_name="rt")
s = [a, b]
db.insert_courses_table(s)
#c=COurse(course_name=)
self.assertTrue(
db.insert_course_table(
Course(course_name="s",
course_type="ss",
course_url="757",
platform_name="1);DROP ALL TABLE--")))
self.assertEqual(db.select_all_course()[0].course_name, a.course_name)
db.delete_all_course_table()
a = Course(course_name="TABLE",
course_type="ss",
course_url="4399",
platform_name="1);DROP ALL TABLE--")
b = Course(course_name="ALL",
course_type="sss",
course_url="4399d",
platform_name="1);DROP ALL TABLE--")
s = [a, b]
self.assertTrue(db.insert_courses_table(s))
self.assertEqual(db.select_all_course()[0].course_name, a.course_name)
self.assertTrue(db.delete_course_table(b))
self.assertEqual(db.select_all_course()[0].course_name, a.course_name)
db.drop_course_table()
db.init_course_table()
a = Course(course_name="TABLE",
course_type="ss",
course_url="4399",
platform_name="1);DROP TABLE if exists COURSE;--")
b = Course(course_name="ALL",
course_type="sss",
course_url="4399d",
platform_name="1);DROP TABLE if exists COURSE;--")
s = [a, b]
db.insert_courses_table(s)
self.assertFalse(db.delete_course_table2("1);DROP ALL TABLE--"))
self.assertEqual(db.select_all_course()[0].course_name, a.course_name)
self.assertTrue(
db.update_course_table(
1,
Course(course_name="TABLE",
course_type="ss",
course_url="4399",
platform_name="1);DROP TABLE if exists COURSE;--")))
self.assertEqual(
db.select_course_name("5);DROP TABLE if exists COURSE;--"), [])
self.assertEqual(
db.select_course_type("5);DROP TABLE if exists COURSE;--"), [])
self.assertEqual(
db.select_platform_name("5);DROP TABLE if exists COURSE;--"), [])
self.assertEqual(
db.select_course_type_accuracy(
"5);DROP TABLE if exists COURSE;--"), [])
self.assertEqual(
db.select_platform_course_name(
"s", "5);DROP TABLE if exists COURSE;--"), [])