def get_user(self, begin, num): #获得所有用户信息从本地 、、、、、 conn = Database() sql = "select * from user limit {},{}".format(begin, num) try: user = conn.execute(sql) # user = [] # with open("user.csv") as f: # reder = csv.reader(f) # for u in reder: # user.append(u) # print(len(user)) # print(user) if user > 0: user = conn.get_cursor().fetchall() # print(user) # user = [["20154862120","252829",""]] # user = (('20181110713','cy.20000526','081029'),('20171261649','122619',None),('20181120626','1234567890qq',None)) # user = (('20181120106','xy850850',None),) return user except Exception as e: traceback.print_exc() print(e) print(109) sava_text(e) finally: pass
def find(user_product): """ used for finding substitute from selected product """ user_product.categories = Category.load_from_id(user_product.id) i = 1 substitute = list() cur = Database.createCursor() cur.execute(""" SELECT product_id FROM categories_products INNER JOIN categories ON categories_products.category_id = categories.id INNER JOIN products ON categories_products.product_id = products.id WHERE categories.name = %s AND products.nutriscore = 'a' limit 5""", (user_product.categories[0], )) for elt in cur.fetchall(): substituts_with_id = dict() substituts_with_id['id'] = i substituts_with_id['product'] = Product.get(elt[0]) substitute.append(substituts_with_id) i += 1 cur.close() return substitute
def filter(): global filterStart if request.method == "POST": __filterBY__ = request.form['filter'] __modu__ = request.form['modules'] __summary__ = {} __modules__ = __modu__.split(',') for item in __modules__: __summary__[item] = [] db = Database("database/vfeed.db") __conn__ = db.Establish_connection() if __modules__ is not None: for item in __modules__: if (item != " " and __conn__): query = "select cveid,date_published,date_modified,summary from nvd_db where summary like '%{0}%' order by {1} desc limit {2},10".format( item, __filterBY__, filterStart) # query = "select cveid,date_published,date_modified,summary from nvd_db where summary like '% microsoft%' order by cveid limit 10" cur = __conn__.execute(query) rows = cur.fetchall() for row in rows: __summary__[item].append(row) __conn__.close() return render_template('public/home.html', __summary__=__summary__, error=0) return render_template('public/test.html', filter=__filterBY__, modules=__modules__)
def play(self): """ main method """ Database.connect() if Database().is_empty(): Category.importing() Store.importing() Product.importing() user = input("""1 - Quel aliment souhaitez-vous remplacer ? 2 - Retrouver mes aliments substitués. 3 - Quitter :""") try: user = int(user) assert 0 < user <= 3 except ValueError: return self.play() except AssertionError: return self.play() if user == 1: self.find_substitute() Database.disconnect() elif user == 2: products = Substitute.load() for product in products: print(Product.get(product[0]).display()) self.play() elif user == 3: print('You\'re leaving the programm') Database.disconnect() exit() else: print('Choice has to be 1, 2 or 3') return self.play()
def databse_conect_create(self): conn = Database() # cnn = conn.get_cursor() try: sql = """create table if not exists score( id int(11) NOT NULL PRIMARY KEY AUTHORIZATION , course_num VARCHAR(6) not null, course_code VARCHAR(15) not NULL , course_name VARCHAR (30) not null, course_type VARCHAR(20) not null, course_score DECIMAL(3,2), course_evaluation VARCHAR(5), course_total VARCHAR(5), course_points DECIMAL(4,2) not NULL , PRIMARY key(stu_id,course_code) )""" conn.execute(sql) # conn.get_cursor().close() # conn.get_database().close() except Exception as e: print(e) finally: # del conn # log = Logger('all.log', level='info') # log.logger.warning('警告已经存在') # Logger('warning.log', level='warning').logger.warning('warning') conn.get_cursor().close() # print(result) # conn.get_database().close() # return conn self.conn = conn.get_database()
def process_next(): global start start = start + 10 if request.method == "POST": __summary__ = {} __cveid__ = {} __modules__ = [] __modules__ = request.form['next'].split(',') __pages__ = False pages = 0 if __modules__ is not None: db = Database("database/vfeed.db") __conn__ = db.Establish_connection() if __conn__: # Got a database connection for item in __modules__: # check every module in module list __summary__[item] = [ ] # save this module to the summary dictionary with empty list __cveid__[item] = [] for item in __modules__: if (item != ""): total_rows_query = "select count(*) from nvd_db where summary like '%{0}%'".format( item) cur = __conn__.execute(total_rows_query) total_rows = cur.fetchone( ) # returns a tuple total_pages = total_rows[0] // 10 if total_pages - start > 10: pages = pages + int(total_pages) if start > pages: start = 0 else: __pages__ = True query = "select cveid,date_published,date_modified,summary from nvd_db where summary like '%{0}%' limit {1},10 ".format( item, start) cur = __conn__.execute(query) rows = cur.fetchall() for row in rows: __summary__[item].append( [row[0], row[1], row[2], row[3]]) __conn__.close() return render_template('public/home.html', __summary__=__summary__, error=0, pages=__pages__) else: return render_template('500.html', error="Error connecting Database", summary=0) else: return render_template("401.html", error="No modules found", summary=0) else: return render_template("401.html", error="Get Method Not Supported")
def get_score_content(self, username): scor_url = self.url data = {"projectType": "MAJOR"} html = self.session.post(scor_url, data=data) html = etree.HTML(html.content.decode("utf-8")) # print(etree.tostring(html, encoding="utf-8").decode("utf-8")) tr_data = html.xpath("//table/tbody[contains(@id,'data')]//tr") score_data = [] conn = Database() for tr in tr_data: if not tr.xpath(".//td"): continue semester = "".join(tr.xpath(".//td[1]//text()")) try: sql = "select semester_id from semester_year where name='%s'" % semester rs = conn.execute(sql) if rs > 0: semester = conn.get_cursor().fetchone()[0] except Exception as e: print(e) semester = "" courseid = self.hand_space("".join(tr.xpath(".//td[3]//text()"))) code = "".join(tr.xpath(".//td[2]//text()")) if "/" in code: coursecode = re.sub("\W*", "", code) + "/" else: coursecode = re.sub("\W*", "", code) courseevaluation = self.hand_space("".join( tr.xpath(".//td")[-2].xpath(".//text()"))) coursescore = self.hand_space("".join( tr.xpath(".//td")[-1].xpath(".//text()"))) score_dic = { "semester": semester, "username": username, "course_id": courseid, "course_code": coursecode, "course_evaluation": courseevaluation, "course_score": coursescore, } score_data.append(score_dic) # 成绩列表 return score_data
def load(): """ used to load categories on a dict """ categories = Database().load('categories') i = 1 categories_dict = dict() for category in categories: categories_dict[i] = Category(category[0], category[1], category[2], category[3], category[4]) i += 1 return categories_dict
def save(self): """ method for inserting data into mysql database """ cur = Database.createCursor() sql = """ INSERT INTO categories (name, count, url, url_id) VALUES (%s, %s, %s, %s)""" val = (self.name, self.count, self.url, self.url_id) cur.execute(sql, val) Database.databaseConnection.commit() cur.close() print(cur.rowcount, "record inserted.")
def save(self): """ Used for inserting stores on database """ cur = Database.createCursor() sql = """ INSERT INTO stores (name) VALUES (%s) """ val = (self.name, ) cur.execute(sql, val) Database.databaseConnection.commit() cur.close() print(cur.rowcount, "record inserted.")
def save(self): """ insert substitute in db """ cur = Database.createCursor() sql = """ INSERT INTO substitutes_products ( substitute_id, product_id) VALUES (%s, %s) """ val = (self.id, self.product_id) cur.execute(sql, val) Database.databaseConnection.commit() cur.close()
def get(product_id): """ get product from a product id """ cur = Database.createCursor() cur.execute( """ SELECT * FROM products WHERE id = %s """, (product_id, )) product = cur.fetchone() products = Product(product[0], product[6], product[1], product[2], product[3], product[5], product[4], None, None) cur.close() return products
def load_from_id(product_id): """ load stores from product id """ cur = Database.createCursor() cur.execute( """ SELECT name FROM stores INNER JOIN stores_products ON stores.id = stores_products.store_id WHERE product_id = %s""", (product_id, )) stores = [''.join(x) for x in cur.fetchall()] cur.close() return stores
def load_from_id(product_id): """ find categories from a product id """ cur = Database.createCursor() cur.execute( """ SELECT name FROM categories INNER JOIN categories_products ON categories.id = categories_products.category_id WHERE product_id = %s""", (product_id, )) cur.close() categories = [''.join(x) for x in cur.fetchall()] cur.close() return categories
def response(): __summary__ = {} __cveid__ = {} __pages__ = False if request.method == "POST": __modules__ = request.form['data'].split(',') if __modules__ is not None: db = Database("database/vfeed.db") __conn__ = db.Establish_connection() if __conn__: # Got a database connection for item in __modules__: # check every module in module list if item is not None: # and item not in excludes: __summary__[item] = [ ] # save this module to the summary dictionary with empty list __cveid__[item] = [] for item in __modules__: if (item != ""): total_rows_query = "select count(*) from nvd_db where summary like '%{0}%'".format( item) cur = __conn__.execute(total_rows_query) total_rows = cur.fetchone() total_pages = total_rows[0] // 10 if (total_pages > 10): __pages__ = True query = "select cveid,date_published,date_modified,summary from nvd_db where summary like '%{0}%' limit 0,10".format( item) cur = __conn__.execute(query) rows = cur.fetchall() if rows: for row in rows: __summary__[item].append(row) __conn__.close() return render_template('public/home.html', __summary__=__summary__, pages=__pages__, error=0) else: flash("Error connecting database", "success") return redirect(url_for('public.home')) else: return render_template("401.html", error="No modules found", summary=0) flash("Get Method not supported", "success") # else: return redirect(url_for('public.home'))
def get_phone(self, username): conn = Database() try: check_sql = "select phone from username_phone where username = '******' " % username con = conn.get_cursor() rs = con.execute(check_sql) if rs > 0: user_phone = con.fetchone()[0] return user_phone except Exception as e: print(e) print(337) finally: conn.get_cursor().close() conn.get_database().close()
def process_ajax(): global start start = start + 10 module = request.args.get('a') if request.method == "GET": __summary__ = {} __summary__[module] = [] db = Database("database/vfeed.db") __conn__ = db.Establish_connection() if __conn__: query = "select cveid,date_published,date_modified,summary from nvd_db where summary like '%{0}%' limit {1},10".format( module, start) cur = __conn__.execute(query) rows = cur.fetchall() __conn__.close() for row in rows: __summary__[module].append(row) return jsonify(result=__summary__)
def importing(): """ used for products importing on database """ categories = Database().load('categories') for category in categories: products = Data(category[3] + '.json').load() for product in products: if 'stores' in product.keys() \ and 'nutriscore_grade' in product.keys() \ and 'ingredients_text_fr' in product.keys() \ and 'image_url' in product.keys(): Product(None, product['brands'], product['product_name'], product['image_url'], product['url'], product['ingredients_text_fr'], product['nutriscore_grade'], product['stores_tags'], product['categories_tags']).save()
def save(self): """ Used for inserting products on database """ cur = Database.createCursor() sql = """ INSERT INTO products ( brand, name, image, url, description, nutriscore) VALUES (%s, %s, %s, %s, %s, %s) """ val = (self.brands, self.name, self.image, self.url, self.description, self.nutriscore) cur.execute(sql, val) last_product_id = cur.lastrowid Database.databaseConnection.commit() if len(self.stores) > 0 and len(self.categories) > 0: for elt in self.stores: cur.execute( """ select id from stores where name = %s """, (str(elt), )) if cur.rowcount: id = cur.fetchone()[0] sql = """ INSERT INTO stores_products (store_id, product_id) VALUES (%s, %s)""" cur.execute(sql, (int(id), int(last_product_id))) Database.databaseConnection.commit() cur.close() for elt in self.categories: cur.execute( """ select id from categories where url_id = %s """, (str(elt), )) if cur.rowcount: id = cur.fetchone()[0] sql = """ INSERT INTO categories_products (category_id, product_id) VALUES (%s, %s) """ cur.execute(sql, (int(id), int(last_product_id))) Database.databaseConnection.commit() cur.close() cur.close()
def get_course_name(self, course_code): conn = Database() try: check_sql = "select 课程名称 from au_2019_2020 where 课程代码 = '%s' " % course_code con = conn.get_cursor() rs = con.execute(check_sql) if rs > 0: course_name = con.fetchone()[0] return course_name return "" except Exception as e: print(e) print(320) finally: conn.get_cursor().close() conn.get_database().close()
def process(): if request.method == "POST": module = request.form['module'] check = request.form['check'] __pages__ = False if module.lower() not in exc_py.python_excludes(): __summary__ = {} __summary__[module] = [] db = Database("database/vfeed.db") __conn__ = db.Establish_connection() if __conn__: total_rows_query = "select count(*) from nvd_db where summary like '%{0}%'".format( module) cur = __conn__.execute(total_rows_query) total_rows = cur.fetchone() total_pages = total_rows[0] // 10 if (total_pages > 10): __pages__ = True query = "select cveid,date_published,date_modified,summary from nvd_db where {0} like '%{1}%' limit 0,10".format( check, module) cur = __conn__.execute(query) rows = cur.fetchall() __conn__.close() for row in rows: __summary__[module].append(row) return render_template("public/home.html", __summary__=__summary__, error=0, pages=__pages__) else: flash("Database Error", "success") return redirect(url_for('public.home')) # return render_template("500.html",summary=0,error="Database Error") else: flash("No Vulnerability found", "success") return redirect(url_for('public.home')) # return render_template("401.html",summary=0,error="No Vulnerability found") else: flash("GET method not supported") return redirect(url_for('public.home'))
def load(category): """ load products from a specific categories """ products_with_id = list() i = 1 cur = Database.createCursor() cur.execute( """SELECT product_id FROM categories_products WHERE category_id = %s""", (category, )) product_id = cur.fetchall() for id in product_id: products = dict() products['id'] = i products['product'] = Product.get(id[0]) i += 1 products_with_id.append(products) cur.close() return products_with_id
def get_score_content(self): con = Database() state = False scor_url = "https://jx.sspu.edu.cn/eams/teach/grade/course/person!search.action" data = {"semesterId": self.__lessionid, "projectType": ""} # try: html = self.session.post(scor_url, data=data) # except Exception as e: # sava_text(e) # while True: # print("进入循环错误") # try: # quit_url() # except Exception as e: # sava_text(e) # state = proiexes() # if state: # break # if state: # html = self.session.post(scor_url, data=data) html = etree.HTML(html.content.decode("utf-8")) # print(etree.tostring(html,encoding="utf-8").decode("utf-8")) # print(self.__username) tr_data = html.xpath("//table/tbody[contains(@id,'data')]//tr") score_data = [] con = Database() for tr in tr_data: if not tr.xpath(".//td"): continue semester = "".join(tr.xpath(".//td[1]//text()")) try: sql = "select semester_id from semester_year where name='%s'" % semester rs = con.execute(sql) if rs > 0: semester = con.get_cursor().fetchone()[0] except Exception as e: print(e) print(172) semester = "" courseid = self.hand_space("".join(tr.xpath(".//td[3]//text()"))) code = "".join(tr.xpath(".//td[2]//text()")) # print(code) if "/" in code: coursecode = re.sub("\W*", "", code) + "/" else: coursecode = re.sub("\W*", "", code) courseevaluation = self.hand_space("".join( tr.xpath(".//td")[-2].xpath(".//text()"))) coursescore = self.hand_space("".join( tr.xpath(".//td")[-1].xpath(".//text()"))) score_dic = { "semester": semester, "username": self.__username, "course_id": courseid, "course_code": coursecode, "course_evaluation": courseevaluation, "course_score": coursescore, } score_data.append(score_dic) # 成绩列表 con.get_cursor().close() con.get_database().close() return score_data
import requests import os import json from datetime import datetime from connect import Database fires_table = Database(collection_name="fires") def updateFires(): print("Update fires: Starting run at", datetime.now()) url = "https://www.fire.ca.gov/umbraco/Api/IncidentApi/GetIncidents" r = requests.get(url, allow_redirects=True) fire_data = json.loads(r.content) for fire in fire_data['ListIncidents']: try: for county in fire['Counties']: item = {} item['county'] = county item['uuid'] = fire['UniqueId'] item['url'] = 'https://www.fire.ca.gov/' + fire['CanonicalUrl'] item['name'] = fire['Name'] item['description'] = fire['SearchDescription'] item['status'] = fire['Status'] item['final'] = fire['Final'] item['updated'] = fire['Updated']
import requests import os import json from static.constant import geo_dict from connect import Database import time from decimal import localcontext from decimal import Inexact from decimal import Rounded from datetime import datetime from datetime import tzinfo import pytz import requests weather_table = Database(collection_name="weather") def get_weather_data(lat, lon): payload = { 'latitude': lat, 'longitude': lon, 'stateCode': 'CA', 'maxDistance': '50' } url = "https://airnowgovapi.com/weather/get" data = requests.post(url, data=payload) if data.status_code == 500: return None else: return data.json()
def database_connect(self): conn = Database() self.conn = conn.get_database()
def load(): """ load all entries of subsitutes table """ return Database.load('substitutes_products')
from connect import Database dbms = Database("sqlite", dbname='testdb.sqlite') #dbms._get_data('USERS1') #dbms._get_data('USERS2') #dbms.ensure_column_exists("USERS2", "session_id", "INTEGER") #dbms.ensure_column_exists("USERS1", "session_id", "INTEGER") #dbms.set_cell_value("USERS1", "session_id", 13, "id", 1) #dbms.set_cell_value("USERS2", "session_id", 13, "id", 5)