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 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: