· 9 years ago · Jan 04, 2017, 07:56 AM
1#-------------------------------------------------------------------------------
2# Name: obtinere_informatii_carti_gen_si_descriere.py
3#Purpose: the script wants to check the rate, genre and description for all the books that are in csv file
4#Functionality:
5# Author: irinap
6#
7# Created: 4/01/2016
8#-------------------------------------------------------------------------------
9#import usefull modules
10import os, re, time
11import urllib, sqlite3, urllib2
12from bs4 import BeautifulSoup
13from HTMLParser import HTMLParser
14
15
16#connect to the database
17conn = sqlite3.connect('carti_good_reads.sqlite')
18cur = conn.cursor()
19cur.execute('''CREATE TABLE IF NOT EXISTS Carti (carte TEXT, autor TEXT, descriere TEXT, punctaj TEXT, gen TEXT)''')
20
21#read all the lines from csv, every line contains author name and book name
22os.chdir("C:\\aplicatii_python")
23fis_citire=open("booklist.csv", "r")
24lista_carti=fis_citire.readlines()
25fis_citire.close()
26
27#prelucrate every line, so based on regex, i obtained the name of the book and the author; i put all of them in a list of books and a list of authors
28lista_carti_prelucrata=[]
29lista_autor=[]
30lista_nume_carte=[]
31for i in range(0,len(lista_carti)):
32 carte=re.findall('^.*?\,.*?\,(.*)\n',lista_carti[i])
33 lista_nume_carte.append(carte[0])
34
35 autor=re.findall('(^.*?\,.*?)\,', lista_carti[i])
36 lista_autor.append(autor[0].replace(",", " "))
37
38 carte_fara_virgula=lista_carti[i].replace(","," ")
39 lista_carti_prelucrata.append(carte_fara_virgula)
40
41no_of_books_with_sleep_timer=0
42#check if the book that i want to search on goodreads already exists in the database; if the book exists, display a message and dont't search the book again on goodreads site
43for i in range(1,len(lista_carti_prelucrata)):
44 cur.execute('SELECT carte FROM Carti WHERE carte= ? and autor=?', (lista_nume_carte[i].decode('utf8', errors='ignore') , lista_autor[i].decode('utf8', errors='ignore')))
45 #check if the book that i want to search on goodreads already exists in the database; if the book exists, display a message and dont't search the book again on goodreads site
46 try:
47 data = cur.fetchone()[0]
48 print ("book is already in the list : "+lista_nume_carte[i] )
49 continue
50 except:
51 no_of_books_with_sleep_timer=no_of_books_with_sleep_timer+1
52 pass
53 #create the url based on the zsite name and on the book and author name
54 url="http://www.goodreads.com/search?utf8=%E2%9C%93&query"+"="+str((lista_carti_prelucrata[i]))
55 #wait 10s after every ten searches
56 if no_of_books_with_sleep_timer%20==0:
57 time.sleep(5)
58 #obtain the page from url
59 fhand = urllib.urlopen(url)
60 pagina_returnata=fhand.read()
61 #obtain rate of the book using regular expressions
62 rate= re.findall(r'([0-9]+.[0-9]+)\savg rating', pagina_returnata, re.MULTILINE)
63 try:
64 #obtain the link for the book; access that link to obtain information like genre and description
65 book_link=re.findall(r'<a class="bookTitle" itemprop="url" href="(.*)"', pagina_returnata)
66 url_book="http://www.goodreads.com"+str(book_link[0])
67 fhand_book = urllib2.urlopen(url_book, None,10)
68 pagina_returnata_book=fhand_book.read()
69 soup=BeautifulSoup(pagina_returnata_book)
70 descr=soup.findAll('div',attrs={'id':'descriptionContainer'})
71 #there are 2 cases for obtaining description: in a freeTextContainer and in a freeText when we can expand the description in the web page
72 try:
73 description=unicode(descr[0].findAll('span',attrs={'id':re.compile("freeText[0-9].*")})[0].get_text())
74 except:
75 description=unicode(descr[0].findAll('span',attrs={'id':re.compile("freeTextContainer[0-9].*")})[0].get_text())
76 #obtain genre
77 tags=soup.findAll('a',attrs={'class':'actionLinkLite bookPageGenreLink'})
78 genre_list=[]
79 for tag in tags:
80 genre_list.append(tag.contents[0])
81 genre=", ".join(genre_list)
82 except:
83 description=""
84 genre=""
85 print "something is wrong for "+str(url)
86 if len(rate)>0:
87 #the following elements are in the database when rate is found
88 cur.execute('''INSERT INTO Carti (carte, autor, descriere, punctaj, gen) VALUES ( ?, ?, ?, ?, ? )''', ( lista_nume_carte[i].decode('utf8', errors='ignore'),lista_autor[i].decode('utf8', errors='ignore'),description.encode('ascii', errors='ignore').decode('utf8', errors='ignore'),rate[0], genre.encode('ascii', errors='ignore').decode('utf8', errors='ignore') ) )
89 else:
90 continue
91 norate=""
92 #if rate is not found, default rate will be an empty string
93 cur.execute('''INSERT INTO Carti (carte, autor, descriere, punctaj) VALUES ( ?, ?, ?, ?, ? )''', ( lista_nume_carte[i].decode('utf8', errors='ignore'),lista_autor[i].decode('utf8', errors='ignore'),description.decode('utf8', errors='ignore'),norate, genre.encode('ascii', errors='ignore').decode('utf8', errors='ignore' ) ))
94 conn.commit()
95
96
97
98#actual issues: how to join many tables 1 book to many genres