def get_all_notice():
connection = dbconnector.connect()
cursor = connection.cursor()
result = cursor.execute('SELECT * FROM notification')
return result
def get_all_notice():
connection = dbconnector.connect()
cursor = connection.cursor()
result = cursor.execute('SELECT * FROM notification')
return result
def get_n_recent_notice(limit=1):
connection = dbconnector.connect()
cursor = connection.cursor()
result = cursor.execute('''SELECT * FROM notification ORDER BY updatetime DESC LIMIT ?''', (limit,))
return result
def get_recent_notice():
connection = dbconnector.connect()
cursor = connection.cursor()
result = cursor.execute(
'''SELECT * FROM notification ORDER BY updatetime DESC LIMIT 1''')
return result
def batchinsert():
try:
con = connect()
query = 'insert into authors(name,gender,ratings) values(%s,%s,%s)'
cursor = con.cursor()
cursor.executemany(query, authorlist)
finally:
if con:
con.commit()
con.close()
def viewpermanentauthors():
try:
con = connect()
except Exception:
print('Unable to get the permanent authors. Try again later')
else:
cursor = con.cursor()
query = 'select * from authors where gender = "m"'
cursor.execute(query)
for row in cursor:
print('Name: {0}\nGender: {1}\nRatings: {2}'.format(
row[1], row[2], row[3]))
def drop_table():
conn = dbconnector.connect()
cursor = conn.cursor()
json_data = {}
try:
cursor.execute('''DROP TABLE notification''')
json_data['status'] = "sucess"
except sqlite3.OperationalError as e:
json_data['status'] = "error"
json_data['error'] = str(e)
finally:
conn.commit()
json_data = json.dumps(json_data)
return json_data
def drop_table():
conn = dbconnector.connect()
cursor = conn.cursor()
json_data = {}
try:
cursor.execute('''DROP TABLE notification''')
json_data['status'] = "sucess"
except sqlite3.OperationalError as e:
json_data['status'] = "error"
json_data['error'] = str(e)
finally:
conn.commit()
json_data = json.dumps(json_data)
return json_data
def deleteauthor():
authorid = int(
input('enter id of the author whose data you want to update: '))
try:
con = connect()
except Exception:
print('Unable to get the permanent authors. Try again later')
else:
cursor = con.cursor()
query = 'delete from authors where id = %s'
cursor.execute(query, (authorid, ))
finally:
if con:
con.commit()
con.close()
def create_table():
conn = dbconnector.connect()
cursor = conn.cursor()
json_data = {}
try:
cursor.execute('''CREATE TABLE notification
(heading text, subheading text, url text, updatetime timestamp UNIQUE)''')
json_data['status'] = "sucess"
except sqlite3.OperationalError as e:
json_data['status'] = "error"
json_data['error'] = str(e)
finally:
conn.commit()
json_data = json.dumps(json_data)
return json_data
def create_table():
conn = dbconnector.connect()
cursor = conn.cursor()
json_data = {}
try:
cursor.execute('''CREATE TABLE notification
(heading text, subheading text, url text, updatetime timestamp UNIQUE)'''
)
json_data['status'] = "sucess"
except sqlite3.OperationalError as e:
json_data['status'] = "error"
json_data['error'] = str(e)
finally:
conn.commit()
json_data = json.dumps(json_data)
return json_data
def updatepermanentauthordata():
'''authorid = int(input('enter id of the author whose data you want to update: '))
name = input('enter author name: ')
gender = input('enter gender: ')
ratings = int(input('enter ratings: '))'''
with open('authors.json', mode='r') as fp:
data = json.load(fp)
datalist = [(d['name'], d['gender'], d['ratings'], d['id']) for d in data]
try:
con = connect()
except Exception:
print('Unable to get the permanent authors. Try again later')
else:
cursor = con.cursor()
query = 'update authors set name=%s, gender=%s, ratings=%s where id = %s'
cursor.executemany(query, datalist)
finally:
if con:
con.commit()
con.close()
def scrap():
new_notice = 0
new_notice_list = []
# Creating the database connection
conn = dbconnector.connect()
# Creating the cursor object
c = conn.cursor()
# The root link for the notice page where er get the irritating marquee
notice_root_link = 'http://www.skf.edu.in/notice/'
# Finak endpoint for the notice page
notice_link_endpoint = notice_root_link + 'notice.aspx'
# Response object after making a GET request to the endpoint
response_object = requests.get(notice_link_endpoint)
# Parsing the complete html out of the response object
notice_html = response_object.text
# Creating soup object for parsing the HTML later
soup = BeautifulSoup(notice_html, 'html.parser')
# Selector for notice anchor tags
notice_list = soup.select('#divSkill a')
# Sl no. is only for printing, they are not saved in the DB
sl_no = 1
for notice in notice_list:
print(str(sl_no), end=': ')
# Selecting the previous sibling which gives the text withing the ugly
# background coloured text, which is saved as heading
notice_heading_object = notice.previous_sibling.previous_sibling
notice_heading = notice_heading_object.b.span.text
# escaping some html escape sequence
notice_heading = notice_heading.replace(u'\xa0', u' ')
print(notice_heading, end=': ')
# Taking the notice subheading text
notice_sub_heading = notice.b.text
print(notice_sub_heading)
# Taking the URL for each of the notices
notice_link = notice_root_link + notice['href']
print('URL: ' + notice_link)
# Sending a HEAD request to the above got URL in order to get the
# LAST MODIFIED header information which will act as a primary key
# in the database
notice_request = requests.head(notice_link)
last_modified = notice_request.headers['Last-Modified']
print('Last Modified: ' + last_modified)
# Parsing the datatime in the format recognizable for the SQLite
time = datetime.datetime.strptime(last_modified, "%a, %d %b %Y %H:%M:%S %Z")
# Try inserting the data in the table
# TO_DO: If the insertion takes place then it will shout out the data to a
# WebHook for messenger which will send off the data to all the subscribers
# of the page
try:
values = (notice_heading, notice_sub_heading, notice_link, time)
c.execute("INSERT INTO notification VALUES(?, ?, ?, ?)", values)
new_notice = new_notice + 1
notice_details = {
"heading": values[0],
"sub_heading": values[1],
"url": values[2],
"update_time": str(values[3])
}
new_notice_list.append(notice_details)
except sqlite3.IntegrityError as e:
print('Record Already Exists')
sl_no += 1
# Commiting the changes in the database so that the change is persistent
# in the database
conn.commit()
json_data = {
"status": "success",
"new_items": len(new_notice_list),
"data": new_notice_list
}
json_data = json.dumps(json_data)
return json_data
from db.dbconnect import connect
name = input('enter name: ')
gender = input('enter gender: ')
noofsubjects = int(input('enter no. of subjects taught: '))
subjects = []
for i in range(1, noofsubjects + 1):
subject = input('enter subject: ')
subjects.append(subject)
try:
con = connect()
except Exception:
print('Unable to connect to the db')
else:
cursor = con.cursor()
query1 = 'insert into professors(name,gender) values(%s, %s)'
cursor.execute(query1, (name, gender))
con.commit()
professorid = cursor.lastrowid
data = []
for subject in subjects:
data.append((subject, professorid))
query2 = 'insert into professor_subjects(subject, professor_id) values (%s, %s)'
cursor.executemany(query2, data)
finally:
