def deleteRelation(data):
'''
解除步骤和ajax的关联关系
:param data:
:return:
'''
ajaxId = data['ajax_id']
stageId = data['stageId']
sql = "delete t_page_stage_relation where ajax_id = %d and stage_id = %d " % (
ajaxId, stageId)
Db.delete(sql)
def updateRelation(data):
"""
更新 cmd_format
:param data:
:return:
"""
cmdForm = data['cmd_format']
ajaxId = data['ajax_id']
stageId = data['stage_id']
sql = "update t_page_stage_relation set cmd_format = '%s' where ajax_id = %d and stage_id = %d " % (
cmdForm, ajaxId, stageId)
Db.update(sql)
def saveRelation(data):
try:
for item in data:
sql = "select id from t_page_stage_relation where page_url = '%s' and stage_id = %d and cmd_format = '%s' " % (
item['page_url'], int(item['stage_id']), item['cmd_format'])
if not Db.fetch_one(sql):
sql = "insert t_page_stage_relation(page_url,user_id,stage_id,cmd_format,create_time) values('%s',%d,%d,'%s',%d)" \
%(item['page_url'], 0,int(item['stage_id']),item['cmd_format'],int(time.time()))
Db.insert(sql)
return True
except Exception as err:
print(err)
return False
class Command:
"""Allows to follow user's path"""
def __init__(self):
self.database = Db()
self.control = Control()
def cli(self):
Display.greeting()
continu = True
while continu:
substitutes = self.database.get_substitute()
self.control.substitution(substitutes)
records_cat = self.database.get_infos_category()
Display.display_categories(records_cat)
choice = self.control.check_input(
"\nChoose the index of one of the categories:",
records_cat,
)
records_prod = self.database.get_infos_product(choice)
records_prod_cleaned = ProductsCleaned().clean(records_prod)
sampling = Display.display_category_product(
choice, records_cat, records_prod_cleaned)
choice_prod = self.control.check_input(
"\nChoose the index of one of the products:",
sampling,
)
records_prod = Display.display_products(choice_prod,
records_prod_cleaned)
choice_subs = self.control.check_input(
"\nChoose the index of a product to substitute: ",
records_prod,
)
selec = [(1, 1), (2, 2)]
choice = self.control.check_input(
"\nDo you want to register this substitute "
"in the database?\n\n1:no\n2:yes\n\nYour answer: ",
selec,
)
if choice == 2:
self.database.update_data(choice_prod, choice_subs)
else:
pass
choice = self.control.check_input(
"\nDo you want to search other products?"
"\n\n1:no\n2:yes\n\nYour answer: ",
selec,
)
if choice == 1:
self.database.cursor.close()
break
def saveLog(data):
print("ssh_log_data", data)
print("json log", json_encode(data['log_data']))
sql = "insert t_log(ajax_id,stage_id, ajax_url, initiator_url, ajax_method,ajax_payload,cmd,log_data,create_time)" \
" values(%d,%d,'%s','%s','%s','%s','%s','%s',%d)"%(data['ajax_id'],data['stage_id'],data['ajax_url'],data['initiator_url'],data['ajax_method'],json_encode(data['ajax_payload']),json_encode(data['cmd_format']),json_encode(data['log_data']),int(time.time()))
return Db.insert(sql)
def scrape(self):
db = Db()
page = requests.get(self.URL)
soup = BeautifulSoup(page.content, 'html.parser')
results = soup.find(class_='container')
questions = results.find_all(class_='card')
for card in questions:
title = card.find('h5').text.strip(' \t\n\r')
rawlocation = card.find('h6').text.strip(' \t\n\r')
# remove (maps) from rawlocation, split on first space
rawlocation = rawlocation.strip('(Maps)')
description = card.find('p',
class_='card-text').text.strip(' \t\n\r')
db.session.add(
InitiativeImport(
category=title,
description=description,
group="demand",
source=self.URL,
location=rawlocation,
))
db.session.commit()
def test_connexion2():
DB_CONF["user"] = "******"
DB_CONF["db"] = "foobar"
with pytest.raises(AssertionError):
assert (Db.__init__(Db) == "1044 (42000): Access denied for user \
'off_user'@'localhost' to database 'foobar'")
def scrape():
db = Db()
# read data
response = requests.get(
'https://api-server-271218.appspot.com/v1/tasks?zipcode=')
result = json.loads(response.content)
# print(result)
questions = result['data']['tasks']
for card in questions:
db.session.add(
InitiativeImport(
name=card['firstName'],
category=card['TaskType']['name'],
description=card['description'],
group="demand",
source=
'https://www.gewoonmensendiemensenwillenhelpen.nl/ik-wil-helpen',
source_id=card['id'],
location=card['zipcode'] + ' ' + card['city'],
frequency=card['when'],
))
db.session.commit()
def addStage(data):
stageName = data['stage_name']
connectStr = data['connect_str']
stageType = int(data['stage_type'])
createTime = int(time.time())
sql = "insert t_stage(stage_name,connect_str,stage_type,create_time) values('%s','%s', %d, %d)" % (stageName,connectStr,stageType,createTime)
print(sql)
return Db.insert(sql)
def scrape(self):
db = Db()
for company in self.zorgheldenautos:
db.session.add(
InitiativeImport(
name=company,
group="zorgheldenauto",
source='https://www.auto.nl/zorgheldenauto',
))
db.session.commit()
def getLog(initiator):
sql = "select log_data, ajax_payload from t_log where initiator_url = '%s' order by id desc limit 10" % (
initiator)
data = Db.fetch_all(sql)
res = []
for item in data:
res.append({
"log_data": json_decode(item['log_data']),
"ajax_payload": json_decode(item['ajax_payload'])
})
return res
def companies(request):
query_string = request.GET['q']
query = ''.join([
"SELECT equilar_id, company FROM companies WHERE lower(company) LIKE '%",
query_string.lower(),
"%' LIMIT 10;"
])
rows = Db.execute(query)
my_id = lambda x: ' - '.join([x[1], str(x[0])])
f = lambda x: {'id': my_id(x), 'text': x[1]}
dicts = map(f, rows) + [{'text': 'Company Not Found', 'id': -1}]
return JsonResponse({'items': dicts})
def saveAjax(data):
initiatorUrl = data['initiator_url']
ajaxUrl = data['ajax_url']
host = data['host']
createTime = int(time.time())
req = getOne(initiatorUrl, ajaxUrl)
if req is None:
sql = "insert t_ajax_request(host, url_format,ajax_url,create_time) values('%s','%s','%s', %d)" % (
host, initiatorUrl, ajaxUrl, createTime)
lastId = Db.insert(sql)
return lastId
else:
return True
def getStageList(initiatorUrl, ajaxUrl):
'''
返回该地址栏下 某个ajax请求相关的步骤列表
:param initiatorUrl: 地址栏
:param ajaxUrl: ajax请求的url
:return:
'''
req = getOne(initiatorUrl, ajaxUrl)
if req:
sql = " select ar.host, ar.url_format, ar.ajax_url, asr.cmd_format, asr.ajax_id, asr.stage_id, st.connect_str, st.stage_name, st.stage_type" \
" from t_ajax_request ar join t_ajax_stage_relation asr on ar.id = asr.ajax_id " \
" join t_stage st on asr.stage_id = st.id " \
" where ar.id = %d" %(req['id'])
return Db.fetch_all(sql)
return None
def getOne(initiatorUrl, ajaxUrl):
'''
通过地址栏和ajax请求的url，返回已有的请求信息
:param initiatorUrl:
:param ajaxUrl:
:return:
'''
sql = "select id, url_format, ajax_url from t_ajax_request where ajax_url = '%s'" % (
ajaxUrl)
urlFormatList = Db.fetch_all(sql)
for item in urlFormatList:
urlFormat = item['url_format']
reObj = re.compile(urlFormat)
if initiatorUrl == urlFormat or (reObj.match(initiatorUrl)
is not None):
return item
return None
def scrape(self):
db = Db()
counter = 1
while counter > 0:
# print(self.URL + str(counter))
page = requests.get(self.URL + str(counter))
soup = BeautifulSoup(page.content, 'html.parser')
results = soup.find_all(class_='postpreview')
if len(results) > 0:
counter += 1
for card in results:
try:
title = card.find(class_='heading3 heading3--semibold'
).text.strip(' \t\n\r')
name = card.find(class_='entity-content-title').text
description = card.find(
class_='paragraph').text.strip(' \t\n\r')
rawtheme = card.find(
class_='postpreview-subtitle').text
link = card.find(class_='postpreview-content')
final_link = link['href']
source_id = final_link.split('/')[-2]
db.session.add(
InitiativeImport(
name=name + " - " + title,
description=description,
group=rawtheme,
source=final_link,
source_id=source_id,
))
except:
print(card)
pass
else:
counter = -1
db.session.commit()
def userList():
sql = "select * from t_user order by id desc"
return Db.fetch_all(sql)
def getUser(id=1):
sql = 'select * from t_user where id = '+ str(id)
return Db.fetch_one(sql)
def signUser(data):
ctime =int(time.time())
sql = "INSERT INTO `t_user` (`username`, `password`,`create_time`) VALUES ('%s', '%s',%d)"%(data['username'], data['password'],ctime)
return Db.insert(sql)
def setUp(self):
self._set_client()
Db.create_all()
def getList(keyword=""):
sql = "select min(id) as id, url_format, min(ajax_url) as ajax_url ,min(host) as host, min(create_time) as create_time from t_ajax_request group by url_format"
res = Db.fetch_all(sql)
return res
"""
from sqlalchemy import exc
from models import Db
Payload.max_decode_packets = 20
load_dotenv()
app = Flask(__name__, static_folder='./build/static')
app.config['SEND_FILE_MAX_AGE_DEFAULT'] = 0
app.config['TEMPLATES_AUTO_RELOAD'] = True
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL')
app.config['SQLALCHEMY_TRACK_MODIFICSTIONS'] = False
cors = CORS(app, resources={r"/*": {"origins": "*"}})
db = Db(app)
clients = []
socketio = SocketIO(
app,
cors_allowed_origins="*",
json=json,
)
@app.route("/LoginorRegister", methods=["GET", "POST"])
def login():
print("Login")
data = json.loads(request.data.decode())
return loginOrRegister(data)
def test_display_products():
database = Db()
display = Display()
rec_prod = [
(
31,
"Ice Tea saveur Pêche",
"d",
82,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
32,
"Ice Tea pêche",
"d",
82,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
33,
"Thé glacé pêche intense",
"d",
79,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
34,
"Thé infusé glacé, Thé noir parfum pêche blanche",
"d",
84,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
35,
"Thé vert infusé glacé saveur Menthe",
"d",
84,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
36,
"Thé noir évasion pêche & saveur hibiscus",
"d",
79,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
37,
"Thé glacé pêche intense",
"d",
79,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
38,
"FROSTIES",
"d",
1569,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
39,
"Sucre glace",
"d",
1674,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
(
40,
"fuze tea pêche intense (thé glacé)",
"d",
79,
"Auchan",
"https://fr-en.openfoodfacts.org/product/7622210601988/yahourt",
),
]
prod_displayed = display.display_products(38, rec_prod)
for prod in prod_displayed:
assert prod[0] != 38
def test_get_infos_product():
database = Db()
rec_prod = database.get_infos_product(2)
for prod in rec_prod:
assert prod[6] == 2
def test_get_infos_category():
DB_CONF["db"] = "off_base"
database = Db()
rec_cat = database.get_infos_category()
assert rec_cat[1][1] == "Yaourts"
def __init__(self):
self.database = Db()
self.control = Control()
def biographies_to_highlight(request):
rows = Db._exec('biographies_to_highlight.sql')
dicts = map(lambda x: dict(zip(['folder', 'director_id', 'director'], x)), rows)
return render(request, 'biographies_to_highlight.html', locals())
def updateUrlFormat(data):
sql = "update t_ajax_request set url_format = '%s' where id = %d" % (
data['url_format'], data['id'])
Db.update(sql)
def test_connexion1():
DB_CONF["user"] = "******"
with pytest.raises(AssertionError):
assert (Db.__init__(Db) == "Something is wrong with your user name\
or password")
"""
Created on Mon Apr 20 14:11:31 2020
@author: jskro
"""
import datetime as dt
import re
from .TreeParser import TreeParser # class for scraping static website
from models import InitiativeImport, Db
# initialize Db
db = Db()
"""
steps
1. get all initiativen from wijamsterdam.nl
2. scrape each initiatief, collect in records
3. insert records into db table
"""
# Step 1
# get all "initiatieven" urls from wijamsterdam
# SCRAPER is defined by: url, schemas, metadata
url = 'https://wijamsterdam.nl/initiatieven'
# schemas: defines fields to be scraped
# schema: fieldname:{xpath,all,cast,transform}
schemas = {
'initiatives': {
'xpath': '//*[@class="tile-list ideas-list"]/div/a[@href]',
'all': True,
'transform': lambda elements: [e.attrib.values() for e in elements]
}
def stageList():
sql = "select * from t_stage order by id desc"
return Db.fetch_all(sql)