AtHfuf7i

1from bs4 import BeautifulSoup
2import urllib
3import sqlite3
4# import smptlib
5# https://docs.python.org/3.4/library/email-examples.html
6
7
8site = urllib.request.urlopen('https://ukjobs.uky.edu/all_jobs.atom')
9soup = BeautifulSoup(site, "lxml")
10
11jobs = []
12for job in soup.find_all('entry'):
13    d = {
14        'id': job.id.string,
15        'updated': job.updated.string,
16        'title': job.title.string,
17        'content': job.content.string,
18        'emailed': str("no"),
19        }
20    jobs.append(d)
21
22connection = sqlite3.connect('jobs.db')
23cursor = connection.cursor()
24cursor.execute('''
25    CREATE TABLE IF NOT EXISTS jobstable(
26    id STRING PRIMARY KEY,
27    updated TEXT,
28    title TEXT,
29    content TEXT,
30    emailed TEXT
31    )
32    ''')
33
34#
35# Need an if statement here to check if id is in table
36# Use .row_factory from
37# https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory
38# to create a dict, then look at the id
39# if id not in dict then execute many below?
40# or use .row_factory = sqlite3.row?
41#
42
43cursor.executemany('''
44                   INSERT INTO jobstable
45                       (id, updated, title, content, emailed)
46                       VALUES (:id, :updated, :title, :content, :emailed);
47                       ''',
48                   jobs)
49
50cursor.execute(
51               '''
52               UPDATE jobstable
53               SET emailed = 'yes'
54               WHERE title LIKE '%?%';
55               '''
56               )
57
58cursor.execute(
59               '''
60               UPDATE jobstable
61               SET emailed = 'yes'
62               WHERE title LIKE '%nurse%';
63               '''
64               )
65
66cursor.execute(
67               '''
68               UPDATE jobstable
69               SET emailed = 'yes'
70               WHERE title LIKE '%Police Officer%';
71               '''
72               )
73
74connection.commit()
75connection.close()