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)