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)