In [1]:

    

from bs4 import BeautifulSoup
import urllib.request
import re
import mysql.connector
import os
import string


    

In [2]:

    

#connect to mysql server
cnx = mysql.connector.connect(host='localhost', user='ctolson', password='ilaYOU5!', database='sephora_cosmetics')
cursor = cnx.cursor()

#mysql insert functions
add_ingredient = ("INSERT INTO Product_Ingredient "
                    "(product_id, ingredient_name) "
                    "VALUES (%s, %s)")
add_review = ("INSERT INTO Reviews "
                    "(product_id, review, reviewer) "
                    "VALUES (%s, %s, %s)")
add_product = ("INSERT INTO Product "
                       "(name, brand, type, url) "
                       "VALUES (%s, %s, %s, %s)")


    

In [3]:

    

search_topics = ['blush','conditioner-hair','mascara','foundation-makeup', 'lipstick']     

for search in search_topics:
    print(search)
    #scrape by search 
    link_main = 'http://www.sephora.com/'
    term = search+'?pageSize=-1'
    req = urllib.request.Request(link_main+term, headers={'User-Agent': 'Firefox'})
    webpage = urllib.request.urlopen(req).read()
    soup_web = BeautifulSoup(webpage, 'html.parser')
    
    #parse product urls
    product_url = soup_web.find_all('script', {'type': 'application/ld+json'})[1]
    product_url = str(product_url)
    product_url = re.findall("(url\":\")(.*?)(\"},{\")", product_url)
    product_url = [x[1] for x in product_url]

    #get product information
    for i in range(0, len(product_url)):
        print(i)
        try:   
            #scrap product page
            req_pg = urllib.request.Request(product_url[i], headers={'User-Agent': 'Chrome'})
            page = urllib.request.urlopen(req_pg).read()
            soup = BeautifulSoup(page, 'html.parser')
    
            #parse ingredients
            ingredients = soup.find_all('script', {'type': 'text/json'})[4]
            ingredients = str(ingredients)
            ingredients = re.sub("(,\"is_new\":true)", "", ingredients)
            ingredients = ingredients.split("ingredients\":")[1]
            ingredients = ingredients.split('","')[0]
            if ingredients == '"':
                continue
            cleanr = re.compile('<.*?>')
            ingredients = re.sub(cleanr, '', ingredients)
            ingredients = ingredients.split(",")
            ingredients = [re.sub(r'[^\w\s]','',x) for x in ingredients]
            ingredients = [re.sub(' r n ','',x) for x in ingredients]
                
            #parse brand name
            brand = soup.find_all('script', {'type': 'text/json'})[4]
            brand = str(brand)
            brand = brand.split("brand_name\":")[1]
            brand = brand.split("},\"sku_number")[0]
            brand = re.split(',', brand)
            brand = str(brand)
            brand = brand[3:-3]
            brand = brand.split('"}')[0] 

            #parse product name
            name = soup.find_all('script', {'type': 'text/json'})[4]
            name = str(name)
            name = name.split("display_name\":")[1]
            name = name.split(",\"variation_type")[0]
            name = re.split(',', name)
            name = str(name)
            name = name[3:-3]
                          
            #parse review page
            keywords = soup.find_all('meta')
            keywords = str(keywords)
            keywords = keywords.split('//reviews.sephora.com/static/')[1]
            keywords = keywords.split('/')[0]
            keywords2 = product_url[i].split('-')
            keywords2 = keywords2[len(keywords2)-1]
            link = 'http://reviews.sephora.com/'+keywords+'/'+keywords2+'/reviews.htm?format=embedded&amp;page=1m&amp;'
            req_pg2 = urllib.request.Request(link, headers={'User-Agent': 'Chrome'})
            page2 = urllib.request.urlopen(req_pg2).read()
            soup2 = BeautifulSoup(page2, 'html.parser')
            rg = soup2.find_all('span', {'class': 'BVRRNumber'})
            if rg == []:
                continue
            
            #insert product into mysql
            try:
                #product table
                data_product = (name, brand, search, str(product_url[i]))
                cursor.execute(add_product, data_product)
                product_id = cursor.lastrowid
                    
                #product-ingredient table
                for ingredient_name in ingredients:
                    data_ingredient = (product_id, ingredient_name)
                    cursor.execute(add_ingredient, data_ingredient)

            except:
                #close mysql server
                cnx.close()
                #connect to mysql server
                cnx = mysql.connector.connect(host='152.19.68.141', user='ctolson', password='ilaYOU5!', database='sephora_cosmetics')
                cursor = cnx.cursor()
                #continue
                continue
                
            rg = str(rg).split("class=\"BVRRNumber\">")[1]
            rg = rg.split("</span")[0]
            rg = re.sub(",", "", rg)
            rg = int(int(rg)/5)
        
            #parse reviews
            for num in range(1,rg):
                link = 'http://reviews.sephora.com/'+keywords+'/'+keywords2+'/reviews.htm?format=embedded&page='+str(num)+'&scrollToTop=true'
                req_pg3 = urllib.request.Request(link, headers={'User-Agent': 'Chrome'})
                page3 = urllib.request.urlopen(req_pg3).read()
                soup3 = BeautifulSoup(page3, 'html.parser')
                text = soup3.find_all('div', {'class': 'BVRRReviewTextContainer'})
                user = soup3.find_all('span', {'class': 'BVRRNickname'})
                for z in range(0,len(text)):
                    review = text[z].find_all('span', {'class': 'BVRRReviewText'})
                    review = str(review).split("class=\"BVRRReviewText\">")
                    review = re.sub("(</span>)", "", str(review))
                    review = re.sub("(<span)", "", str(review))
                    reviewer = str(user[z]).split("itemprop=\"author\">")[1]
                    reviewer = reviewer.split("</span")[0]
                        
                    #insert reviews into mysql       
                    try:
                        data_review = (product_id, review, reviewer)
                        cursor.execute(add_review, data_review)
                    except:
                        pass
            
            #commit entry    
            cnx.commit()
            print('commit')
        
        except:
            #close mysql server
            cnx.close()
            #connect to mysql server
            cnx = mysql.connector.connect(host='152.19.68.141', user='ctolson', password='ilaYOU5!', database='sephora_cosmetics')
            cursor = cnx.cursor()
            #continue
            continue


    

    




blush
0
1
2
3
4
5
commit
6
commit
7
8
9
10
11
12
13
commit
14
15
16
17
18
commit
19
20
21
commit
22
commit
23
commit
24
commit
25
26
commit
27
28
29
30
31
32
commit
33
commit
34
commit
35
commit
36
commit
37
commit
38
39
commit
40
41
42
commit
43
44
commit
45
commit
46
47
commit
48
49
50
51
commit
52
53
54
commit
55
56
57
commit
58
59
commit
60
commit
61
commit
62
commit
63
64
65
66
67
68
commit
69
commit
70
71
72
commit
73
74
commit
75
76
commit
77
commit
78
79
commit
80
81
commit
82
83
commit
84
85
commit
86
commit
87
commit
88
89
90
91
commit
92
commit
93
94
95
commit
96
commit
97
98
99
commit
100
101
102
commit
103
104
commit
105
commit
106
107
108
commit
109
110
commit
111
112
commit
113
commit
114
115
commit
116
commit
117
commit
118
119
120
commit
121
122
commit
123
commit
124
125
126
127
128
commit
129
130
131
commit
132
commit
133
commit
134
135
136
commit
137
commit
138
commit
conditioner-hair
0
commit
1
commit
2
commit
3
commit
4
commit
5
commit
6
commit
7
commit
8
commit
9
commit
10
commit
11
commit
12
commit
13
commit
14
commit
15
commit
16
commit
17
18
commit
19
commit
20
commit
21
22
commit
23
commit
24
commit
25
commit
26
27
commit
28
commit
29
commit
30
commit
31
commit
32
commit
33
34
commit
35
commit
36
commit
37
commit
38
39
commit
40
commit
41
commit
42
commit
43
commit
44
commit
45
commit
46
commit
47
48
commit
49
commit
50
commit
51
52
commit
53
commit
54
commit
55
56
commit
57
commit
58
commit
59
commit
60
commit
61
commit
62
commit
63
commit
64
65
66
67
commit
68
commit
69
commit
70
commit
71
commit
72
commit
73
commit
74
commit
75
commit
76
commit
77
commit
78
commit
79
commit
80
commit
81
commit
82
commit
83
commit
84
commit
85
commit
86
commit
87
commit
88
commit
89
commit
90
commit
91
commit
92
commit
93
commit
94
commit
95
commit
96
commit
97
commit
98
commit
99
commit
100
commit
101
commit
102
commit
103
commit
104
commit
105
commit
106
commit
107
commit
108
commit
109
commit
110
commit
111
112
commit
113
commit
114
commit
115
commit
116
commit
117
commit
118
119
commit
120
121
commit
122
commit
123
commit
124
commit
125
commit
126
commit
127
commit
128
129
commit
130
commit
131
commit
132
commit
133
commit
134
135
commit
136
137
commit
138
commit
139
commit
140
commit
141
commit
142
commit
143
commit
144
commit
145
commit
146
commit
147
commit
148
149
commit
150
151
commit
152
commit
153
commit
154
commit
155
156
commit
157
158
commit
159
commit
160
commit
161
commit
162
commit
163
commit
164
commit
165
commit
166
commit
167
commit
168
commit
169
commit
170
commit
171
commit
172
commit
173
commit
174
commit
175
commit
176
commit
177
commit
178
commit
179
commit
180
commit
181
commit
182
mascara
0
1
2
commit
3
commit
4
5
6
7
commit
8
commit
9
commit
10
commit
11
12
commit
13
14
15
commit
16
commit
17
commit
18
19
20
commit
21
commit
22
23
24
commit
25
commit
26
27
28
commit
29
30
commit
31
commit
32
33
commit
34
35
36
37
commit
38
commit
39
commit
40
41
commit
42
commit
43
commit
44
commit
45
commit
46
commit
47
commit
48
commit
49
commit
50
51
52
commit
53
54
commit
55
commit
56
57
commit
58
commit
59
commit
60
61
62
commit
63
commit
64
65
66
commit
67
68
commit
69
commit
70
71
commit
72
73
commit
74
75
commit
76
commit
77
78
79
80
commit
81
82
commit
83
84
85
86
commit
87
commit
88
commit
89
commit
90
commit
91
92
commit
93
94
95
96
97
98
commit
99
100
101
102
commit
103
104
commit
105
commit
106
107
commit
108
commit
109
110
111
commit
112
113
114
commit
115
commit
116
commit
117
commit
118
119
commit
120
commit
121
122
commit
123
commit
124
commit
125
126
127
commit
128
129
commit
130
131
commit
132
commit
133
commit
134
135
commit
136
commit
137
138
commit
139
commit
140
141
142
143
144
145
146
commit
147
commit
148
149
commit
150
151
152
commit
153
commit
154
155
156
commit
157
commit
158
159
commit
160
161
commit
162
commit
163
164
165
commit
166
commit
167
commit
168
commit
169
170
commit
171
commit
foundation-makeup
0
1
commit
2
commit
3
commit
4
commit
5
commit
6
commit
7
commit
8
9
commit
10
11
commit
12
13
commit
14
commit
15
commit
16
17
commit
18
commit
19
commit
20
commit
21
commit
22
commit
23
24
commit
25
commit
26
commit
27
commit
28
29
30
commit
31
commit
32
33
commit
34
35
commit
36
commit
37
commit
38
39
commit
40
41
commit
42
commit
43
commit
44
45
46
commit
47
commit
48
commit
49
50
commit
51
commit
52
commit
53
commit
54
commit
55
commit
56
commit
57
commit
58
commit
59
60
61
commit
62
commit
63
commit
64
commit
65
commit
66
commit
67
commit
68
commit
69
70
71
72
73
74
commit
75
commit
76
commit
77
commit
78
commit
79
commit
80
81
commit
82
commit
83
commit
84
85
commit
86
commit
87
commit
88
89
90
91
92
commit
93
94
commit
95
commit
96
97
commit
98
99
100
101
commit
102
commit
103
commit
104
105
commit
106
commit
107
108
commit
109
110
commit
111
112
commit
113
commit
114
commit
115
116
commit
117
118
119
commit
120
commit
121
122
commit
123
commit
124
125
126
commit
127
128
129
130
commit
131
commit
132
133
134
commit
135
commit
136
commit
137
commit
138
139
commit
140
commit
141
commit
142
143
commit
144
145
commit
146
147
commit
148
149
150
commit
151
152
commit
153
154
commit
155
commit
156
157
commit
158
159
commit
160
161
162
commit
163
commit
164
165
commit
166
commit
167
168
commit
169
170
commit
171
172
commit
173
commit
174
commit
175
commit
176
commit
177
commit
178
commit
179
180
181
182
commit
183
184
commit
185
186
187
commit
188
commit
189
190
commit
191
192
193
194
195
196
commit
197
commit
198
commit
199
commit
200
commit
201
commit
202
203
commit
204
205
commit
206
commit
207
208
commit
209
commit
210
211
commit
212
commit
213
commit
214
commit
215
commit
216
217
218
219
220
commit
221
commit
222
223
commit
224
commit
225
commit
226
commit
227
commit
228
commit
229
230
commit
231
232
233
commit
234
235
commit
236
commit
237
commit
238
239
240
241
242
243
commit
244
245
246
commit
247
248
249
250
251
252
commit
253
254
255
256
commit
257
commit
258
259
260
261
lipstick
0
1
2
commit
3
commit
4
commit
5
commit
6
7
commit
8
9
commit
10
11
commit
12
13
14
15
16
17
18
19
20
commit
21
22
23
24
commit
25
commit
26
27
28
29
30
commit
31
32
commit
33
34
commit
35
36
37
commit
38
commit
39
commit
40
commit
41
commit
42
43
commit
44
45
commit
46
47
48
commit
49
commit
50
51
52
commit
53
54
commit
55
commit
56
57
commit
58
59
60
61
commit
62
commit
63
64
65
66
67
68
69
70
71
72
73
commit
74
commit
75
commit
76
commit
77
commit
78
commit
79
commit
80
81
82
commit
83
commit
84
85
86
commit
87
commit
88
89
90
commit
91
92
commit
93
94
commit
95
96
commit
97
commit
98
99
100
commit
101
commit
102
103
104
105
106
107
108
commit
109
commit
110
111
commit
112
commit
113
commit
114
115
commit
116
commit
117
commit
118
119
commit
120
121
commit
122
123
commit
124
commit
125
126
127
128
commit
129
130
commit
131
132
133
134
135
commit
136
137
138
commit
139
140
commit
141
142
143
144
145
commit
146
commit
147
commit
148
commit
149
commit
150
commit
151
152
commit
153
154
commit
155
156
commit
157
commit
158
159
commit
160
161
162
163
164
commit
165
166
commit
167
commit
168
commit
169
170
commit
171
commit
172
173
174
175
176
commit
177
commit
178
179
180
181
182
commit
183
184
185
186
commit
187
188
commit
189
190
191
192
commit
193
194
commit
195
commit
196
commit
197
198
commit
199
200
commit
201
202
203
204
205
206
207
commit
208
209
commit
210
211
commit
212
commit
213
commit
214
215
commit
216
217
218
219
220
commit
221
222
commit
223
224
commit
225
226
commit
227
commit
228
229
commit
230
commit
231
232
233
234
commit
235
236
237
238
commit
239
240
241
242
commit
243
commit
244
commit
245
commit
246
247
248
249
250
251
252

In [4]:

    

#close mysql server
cnx.close()


    

In [ ]: