def handle_get_all_missions(): try: database.db_connect() missions = mission.get_missions() result = {"missions": missions} except Exception, e: print e result = {"missions": None}
def handle_get_all_achievements(): try: database.db_connect() achieves = achievements.get_all_achievements() result = {"achievements": achieves} except Exception, e: print e result = {"achievements": None}
def handle_get_all_player_achievements(): if 'username' not in session: result = {'achievements': None, 'BadRequest': True} else: player = session['username'] try: database.db_connect() achieves = achievements.get_player_achievements(player) result = {"achievements": achieves} except Exception, e: print e result = {"achievements": None} finally:
def handle_add_player_achievement(): if 'username' not in session or 'achievement' not in data: result = {'result': False, 'BadRequest':True} else: player = session['username'] achievement = data['achievement'] try: database.db_connect() achievements.add_player_achievement(player, achievement) result = {"result": True} except Exception, e: print e result = {"result": False} finally:
def handle_use_recipe(): data = request.json if 'username' not in session: result = {'recipemade' :None, 'BadRequest':True} else: try: database.db_connect() charid = data['charid'] rid = data['recipe'] success = recipe.exec_recipe(rid, charid, character.SHOP) result = { 'result' : success } except Exception, e: print e result = {"result": None} finally:
def handle_create_character(): data = request.json if 'username' not in session or 'charname' not in data: result = False else: username = session['username'] charname = data['charname'] try: database.db_connect() result = character.create_character(username, charname) except Exception, e: print "Error in /character/create:", e result = False finally:
def handle_get_achievement_description(): data = request.json if 'achievementName' not in data: result = {"description": None, "BadRequest": True} else: achievementName = data['achievementName'] try: database.db_connect() description = achievements.get_achievement_description(achievementName) result = {"description": description} except Exception, e: print e result = {"description": None} finally:
def _foreign_keys(self): with db_connect().cursor() as cursor: cursor.execute("""SELECT id FROM re_cities WHERE city=%(city)s""", self.prepared_data) ct = cursor.fetchone() self.prepared_data["city_id"] = ct.get("id") cursor.connection.close()
def handle_login_request(): data = request.json if data == None or 'user' not in data or 'password' not in data: result = {'result': False} else: name = data['user'] password = data['password'] password_hash = hash_password(name, password) try: database.db_connect() loginPlayer = player.get_player(name, password_hash) except Exception, e: print e loginPlayer = None finally:
def change_password(): json = request.get_json() if not json or "old_password" not in json or "new_password" not in json or "confirm_password" not in json \ or json["old_password"] == "" or json["new_password"] == "" or json["confirm_password"] == "": return jsonify({"error": "Incorrect request json"}), 400 if json["new_password"] != json["confirm_password"]: return jsonify({'error': 'Passwords do not match.'}), 400 user_ident = get_jwt_identity() with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM users WHERE id=%s", user_ident["user_id"]) user = cursor.fetchone() if user is None: cursor.connection.close() return Response(status=404) if not bcrypt.checkpw(bytes(json["old_password"], 'utf-8'), bytes(user["password"], 'utf-8')): cursor.connection.close() return jsonify({'error': 'Incorrect password'}), 403 pw_hash = bcrypt.hashpw(bytes(json["new_password"], 'utf-8'), bcrypt.gensalt()) cursor.execute("UPDATE users SET password=%s WHERE id=%s", (pw_hash, user_ident["user_id"])) cursor.connection.commit() cursor.connection.close() return "", 200
def delete_re_query(user_id, query_id): with db_connect().cursor() as cursor: cursor.execute("DELETE FROM re_queries WHERE id=%s AND user_id=%s", (query_id, user_id)) cursor.connection.commit() cursor.connection.close() return True
def send_push_notifications(self, msg): with db.db_connect().cursor() as cursor: cursor.execute( "SELECT * FROM push_notification_auth WHERE user_id=%s", (self.car_query["car_query"]["user_id"])) auth_list = cursor.fetchall() for auth in auth_list: print("SENDING PUSH NOTIFICATION") auth_json = auth["auth_json"] try: pywebpush.webpush( json.loads(auth_json), data=json.dumps({ "title": "Pasikeitė paieškos rezultatai", "body": f"{self.car_query['make_model']['make']} {self.car_query['make_model']['model_name']}", "href": f"/users/{self.car_query['car_query']['user_id']}/messages" }), vapid_private_key='./vapid_private.pem', vapid_claims={"sub": "mailto:[email protected]"}) except pywebpush.WebPushException as err: print("Web push failed:") print(err) if "expired" in err.message: cursor.execute( "DELETE FROM `push_notification_auth` WHERE id=%s", auth["id"]) cursor.connection.commit() cursor.connection.close()
def handle_get_equipped_character_equipment(): data = request.json if 'charid' not in data: result = {"equipment": None, "BadRequest": True} else: charid = data['charid'] try: database.db_connect() eq = equipment.get_equipment(charid) result = { "equipment" : [] } for entry in eq: result['equipment'].append( {"name":entry[0], "slot":entry[1]} ); except Exception, e: print "Error in /character/equipped:", e result = {"equipment": None} finally:
def handle_new_account(): data = request.json if data == None or 'user' not in data or 'password' not in data: result = {'result': False} else: name = data['user'] password = data['password'] password_hash = hash_password(name, password) try: database.db_connect() player.create_player(name, password_hash) session['username'] = name result = {'result': True} except Exception, e: result = {'result': False} finally:
def handle_add_character_inventory(): data = request.json if 'itemid' not in data or 'quantity' not in data or 'charid' not in data: result = {"result": False, "BadRequest": True } else: charid = data['charid'] # TODO: Make this character name? itemid = data['itemid'] quantity = data['quantity'] try: database.db_connect() inv = inventory.add_item(charid, itemid, quantity) result = {"result": True} except Exception, e: print e result = {"result": False} finally:
def handle_get_characters(): data = request.json if 'username' not in session: result = {'characters':None, 'BadRequest':True} else: username = session['username'] try: database.db_connect() characters = character.get_characters(username) result = {'characters': characters} except: result = {'characters': None} finally: database.db_close() return jsonify(result)
def handle_get_character_inventory(): data = request.json if 'charid' not in data: result = {"inventory": None, "BadRequest": True } else: charid = data['charid'] # TODO: Make this character name? try: database.db_connect() inv = inventory.get_inventory(charid) result = { "inventory" : [] } for entry in inv: result['inventory'].append( {"name":entry[0], "quantity":entry[1]} ) except Exception, e: print e result = {"inventory": None} finally:
def handle_get_items(): try: database.db_connect() items = item.get_items() result = { "items" : [] } for itemInList in items: iname = itemInList[_ITEM_NAME_SPOT] idesc = itemInList[_ITEM_DESC_SPOT] itype = itemInList[_ITEM_TYPE_SPOT] attrs = getJSONItemAttrs(iname) slots = getJSONItemSlots(iname) result['items'].append({"name":iname, "type":itype, "desc":idesc, "attributes":attrs, "slots":slots}) except Exception, e: #print "Error in /item/getAll:", e result = { "items": None }
def handle_get_reward(): data = request.json if 'rewardid' not in data: result = {"rewardexp": None, "rewarditems": None, "BadRequest": True} else: rewardid = data['rewardid'] try: database.db_connect() rewardItems = reward.get_reward_items(rewardid) rewardExp = reward.get_reward_exp(rewardid) result = {"rewardexp": rewardExp, "rewarditems": rewardItems} except Exception, e: print e result = {"rewardexp": None, "rewarditems": None, "BadRequest": True} finally:
def handle_get_character_gold(): #TODO: Fix DRY violation (Why was this copied and pasted then 1 line changed...) data = request.json if 'charid' not in data: result = {"inventory": None, "BadRequest": True } else: charid = data['charid'] # TODO: Make this character name? try: database.db_connect() inv = inventory.get_inventory(charid) result = { "inventory" : [] } for entry in inv: if entry[0] in ['Gold']: result['inventory'].append( {"name":entry[0], "quantity":entry[1]} ) except Exception, e: print e result = {"inventory": None} finally:
def update_re_query(re_query): with db_connect().cursor() as cursor: cursor.execute( """UPDATE `re_queries` SET `city_id`=%(city_id)s, `house_type_id`=%(house_type_id)s, `type_id`=%(type_id)s, `category_id`=%(category_id)s, `search_term`=%(search_term)s, `price_from`=%(price_from)s, `price_to`=%(price_to)s, `area_from`=%(area_from)s, `area_to`=%(area_to)s, `rooms_from`=%(rooms_from)s, `rooms_to`=%(rooms_to)s, `year_from`=%(year_from)s, `year_to`=%(year_to)s, user_id=%(user_id)s, sites=%(sites)s, was_scraped=0 WHERE id=%(id)s""", re_query) cursor.connection.commit() cursor.connection.close() return cursor.lastrowid
def insert_re_query(re_query): with db_connect().cursor() as cursor: print(re_query) cursor.execute( """INSERT INTO `re_queries`(`city_id`, `house_type_id`, `type_id`, `category_id`, `search_term`, `price_from`, `price_to`, `area_from`, `area_to`, `rooms_from`, `rooms_to`, `year_from`, `year_to`, user_id, sites) VALUES (%(city_id)s, %(house_type_id)s, %(type_id)s, %(category_id)s, %(search_term)s, %(price_from)s, %(price_to)s, %(area_from)s, %(area_to)s, %(rooms_from)s, %(rooms_to)s, %(year_from)s, %(year_to)s, %(user_id)s, %(sites)s)""", re_query) cursor.connection.commit() cursor.connection.close() return cursor.lastrowid
def car_list(): conn = db_connect() car_ads = None with conn.cursor() as cursor: cursor.execute( """SELECT *, makes.make as make_name, models.model_name as model_name, body_styles.name as body_type_name, fuel_types.fuel_name as fuel_name FROM car_ads JOIN makes ON car_ads.make=makes.id JOIN models ON car_ads.model=models.id JOIN body_styles ON car_ads.body_type=body_styles.id JOIN fuel_types ON car_ads.fuel_type=fuel_types.id""") car_ads = cursor.fetchall() conn.close() return jsonify(car_ads)
def get_re_ad(re_ad_id): with db_connect().cursor() as cursor: cursor.execute( """SELECT re_ads.*, re_cities.city AS city FROM `re_ads` JOIN re_cities ON re_cities.id=re_ads.city_id WHERE re_ads.id=%s AND re_ads.deleted=0 """, re_ad_id) re_ad = cursor.fetchone() cursor.execute("SELECT * FROM re_ad_pictures WHERE re_ad_id=%s", re_ad["id"]) re_ad["pictures"] = [x["picture_href"] for x in cursor.fetchall()] cursor.connection.close() return re_ad
def ban_or_unban_user(user_id): user = get_jwt_identity() if user["group"] != "admin": return jsonify({"error": "Only admin can access this endpoint"}), 403 data = request.get_json() print(data) if not data or "banned" not in data or (data['banned'] != 0 and data['banned'] != 1): return "", 400 with db_connect().cursor() as cursor: rows_affected = cursor.execute( "UPDATE users SET banned=%s WHERE id=%s AND user_group!='admin'", (data['banned'], user_id)) cursor.connection.commit() cursor.connection.close() if rows_affected == 0: return "", 404 return "", 200
def login_auth(): if not request.is_json: return jsonify({"error": "Missing JSON in request"}), 400 email = request.json.get('email', None) password = request.json.get('password', None) if not email: return jsonify({"error": "Missing email parameter"}), 400 if not password: return jsonify({"error": "Missing password parameter"}), 400 with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM users WHERE email=%s", email) user = cursor.fetchone() print(user) if user is None: cursor.connection.close() return jsonify({"error": "Bad email or password"}), 401 if user['banned'] == 1: return jsonify({"error": "This user is banned"}), 401 if bcrypt.checkpw(bytes(password, 'utf-8'), bytes(user["password"], 'utf-8')): access_token = create_access_token( identity={ "user_id": user["id"], "group": user["user_group"], "email": user["email"] }) refresh_token = create_refresh_token( identity={ "user_id": user["id"], "group": user["user_group"], "email": user["email"] }) cursor.connection.close() #return jsonify(access_token=access_token, refresh_token=refresh_token), 200 return jsonify(access_token=access_token), 200 return jsonify({"error": "Bad email or password"}), 401
def get_user_re_queries(user_id): with db_connect().cursor() as cursor: cursor.execute( """SELECT re_queries.id as id, `user_id`, `city_id`, `house_type_id`, `type_id`, `category_id`, `search_term`, `price_from`, `price_to`, `area_from`, `area_to`, `rooms_from`, `rooms_to`, `year_from`, `year_to`, `sites`, re_cities.city as city, re_cities.domo_id as domo_city_id, re_cities.skelbiu_id as skelbiu_city_id, re_house_type.name as house_type_name, re_house_type.skelbiu_id as skelbiu_house_type_id, re_house_type.domo_id as domo_house_type_id, re_types.name as type_name, re_types.skelbiu_id as skelbiu_type_id, re_types.domo_id as domo_type_id, re_categories.name as category_name, re_categories.domo_id as domo_category_id, re_categories.skelbiu_id as skelbiu_category_id, scrape_interval, last_scraped, was_scraped, currently_scraping FROM `re_queries` LEFT JOIN re_cities ON city_id=re_cities.id LEFT JOIN re_house_type ON house_type_id=re_house_type.id LEFT JOIN re_types ON type_id=re_types.id LEFT JOIN re_categories ON category_id=re_categories.id WHERE re_queries.user_id=%s""", user_id) queries = cursor.fetchall() cursor.connection.close() return queries
def register_user(): userjson = request.get_json() if not userjson: return "", 400 if "email" not in userjson or userjson["email"] is None: return jsonify({"error": "email error"}), 400 em = userjson["email"].split('@') if " " in userjson["email"] or len( em) <= 1 or em[0] == userjson["email"] or len(em[1]) == 0: return jsonify({ "error": "Invalid email. Email can not contain spaces and must contain one '@'." }), 400 if "password" not in userjson or \ "confirm_password" not in userjson or \ userjson["password"] is None or \ userjson["confirm_password"] is None or \ userjson["password"] != userjson["confirm_password"] or \ len(userjson["password"]) == 0: return jsonify({"error": "password error"}), 400 with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM users WHERE email=%s", userjson["email"]) if cursor.fetchone(): cursor.connection.close() return jsonify( {"error": "Account with this email already registered."}), 409 hashed_pw = bcrypt.hashpw(bytes(userjson["password"], 'utf-8'), bcrypt.gensalt()) cursor.execute( "INSERT INTO `users`(`email`, `password`, `user_group`) VALUES (%s, %s, %s)", (userjson["email"], hashed_pw, 'regular')) cursor.connection.commit() cursor.connection.close() return Response(status=200)
def get_fuel_types(): with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM fuel_types") fuel_types = cursor.fetchall() cursor.connection.close() return jsonify(fuel_types)
def get_body_styles(): with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM body_styles") body_styles = cursor.fetchall() cursor.connection.close() return jsonify(body_styles)
def get_re_cities(): with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM `re_cities`") cities = cursor.fetchall() cursor.connection.close() return cities
def get_re_house_types(): with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM `re_house_type`") data = cursor.fetchall() cursor.connection.close() return data
def mark_re_ads_as_deleted(ad_ids: list): with db_connect().cursor() as cursor: cursor.executemany("UPDATE `re_ads` SET deleted=1 WHERE re_ads.id=%s", ad_ids) cursor.connection.commit() cursor.connection.close()
def insert_ad(self): self._site() self._foreign_keys() with db_connect().cursor() as cursor: cursor.execute( f"SELECT id FROM re_ads WHERE {self.prepared_data['key_column']}=%(key_value)s", self.prepared_data) ad_in_db = cursor.fetchone() if ad_in_db: self.prepared_data["id"] = ad_in_db["id"] cursor.execute( f"""UPDATE re_ads SET `city_id`=%(city_id)s, `title`=%(title)s, `village`=%(village)s, `installation`=%(installation)s, `type`=%(type)s, `house_type`=%(house_type)s, `year`=%(year)s, `site_area`=%(site_area)s, `heating`=%(heating)s, `area`=%(area)s, `street`=%(street)s, `description`=%(description)s, `floor`=%(floor)s, `neighborhood`=%(neighborhood)s, `energy_class`=%(energy_class)s, `floor_count`=%(floor_count)s, `features`=%(features)s, `price`=%(price)s, `price_per_area`=%(price_per_area)s, `phone`=%(phone)s, {self.prepared_data['key_column']}=%(key_value)s, `room_count`=%(room_count)s, href=%(href)s, when_scraped=unix_timestamp(), year_reconstructed=%(year_reconstructed)s, water=%(water)s, sewage=%(sewage)s, gas=%(gas)s WHERE id=%(id)s""", self.prepared_data) try: cursor.execute( "INSERT INTO re_query_ad_fk (query_id, re_ad_id) VALUES (%(query_id)s, %(id)s)", self.prepared_data) except pymysql.IntegrityError as err: print("RE ad already existed: ") print(err) try: cursor.executemany( "INSERT INTO `re_ad_pictures`(`re_ad_id`, `picture_href`) VALUES (%s, %s)", [(self.prepared_data["id"], p_href) for p_href in self.prepared_data["pictures"]]) except pymysql.IntegrityError as err: print("Picture already exists: ") print(err) else: cursor.execute( f"""INSERT INTO `re_ads`(`city_id`, `title`, `village`, `installation`, `type`, `house_type`, `year`, `site_area`, `heating`, `area`, `street`, `description`, `floor`, `neighborhood`, `energy_class`, `floor_count`, `features`, `price`, `price_per_area`, `phone`, {self.prepared_data['key_column']}, `room_count`, href, when_scraped, year_reconstructed, water, sewage, gas) VALUES (%(city_id)s, %(title)s, %(village)s, %(installation)s, %(type)s, %(house_type)s, %(year)s, %(site_area)s, %(heating)s, %(area)s, %(street)s, %(description)s, %(floor)s, %(neighborhood)s, %(energy_class)s, %(floor_count)s, %(features)s, %(price)s, %(price_per_area)s, %(phone)s, %(key_value)s, %(room_count)s, %(href)s, unix_timestamp(), %(year_reconstructed)s, %(water)s, %(sewage)s, %(gas)s)""", self.prepared_data) self.prepared_data["id"] = cursor.lastrowid cursor.execute( "INSERT INTO re_query_ad_fk (query_id, re_ad_id) VALUES (%(query_id)s, %(id)s)", self.prepared_data) cursor.executemany( "INSERT INTO `re_ad_pictures`(`re_ad_id`, `picture_href`) VALUES (%s, %s)", [(self.prepared_data["id"], p_href) for p_href in self.prepared_data["pictures"]]) cursor.connection.commit() cursor.connection.close() return self.extract_relevant_attributes()
def re_scraper_loop(self): while True: lowest = math.inf self.current_query = None for (query_id, query) in self.re_queries.items(): if query["next_scrape"] < lowest: lowest = query["next_scrape"] self.current_query = query current_timestamp = time.time() time_to_wait = lowest - current_timestamp if time_to_wait > 0: print("SLEEPING TIME:") print(time_to_wait) # time.sleep(time_to_wait) with self.cv: if time_to_wait == math.inf: if self.cv.wait(): print("Sleep got interrupted: new re query added or deleted.") else: print("Sleep ended, continue scraping") else: if self.cv.wait(time_to_wait): print("Sleep got interrupted: new re query added or deleted.") else: print("Sleep ended, continue scraping") else: print("NO NEED TO WAIT") if self.current_query is None or time.time() < self.current_query["next_scrape"]: continue # print("SCRAPER TURNED OFF") # time.sleep(99999999) # get old ads old_ads = get_query_re_ads(self.current_query["id"]) # SCRAPE HERE, more threads? maybe with proxy q = Queue() self.current_query["currently_scraping"] = True with db_connect().cursor() as cursor: cursor.execute("UPDATE re_queries SET currently_scraping=1 WHERE id=%s", self.current_query["id"]) cursor.connection.commit() cursor.connection.close() requests.post(f'{SERVER_NAME}:{SERVER_PORT}/started_scraping_re_query/{self.current_query["user_id"]}/{self.current_query["id"]}', json={'secret':SECRET}, verify=False) p = Process(target=self.scrape, args=(q,)) p.start() scraped_ads = q.get(True) p.join() self.current_query["currently_scraping"] = False with db_connect().cursor() as cursor: cursor.execute("UPDATE re_queries SET currently_scraping=0 WHERE id=%s", self.current_query["id"]) cursor.connection.commit() cursor.connection.close() requests.post(f'{SERVER_NAME}:{SERVER_PORT}/done_scraping_re_query/{self.current_query["user_id"]}/{self.current_query["id"]}', json={'secret':SECRET}, verify=False) print("JOINED") print("TIME after Q.get()") print(time.time()) print("SCRAPED ITEMS:") # get new ads # check differences and notify ttt = time.time() notif = ReNotifier(old_ads, scraped_ads, self.current_query) print("NOTIFIER TOOK TIME:") print(time.time() - ttt) # update last scraped self.current_query["last_scraped"] = int(time.time()) self.current_query["was_scraped"] = 1 with db_connect().cursor() as cursor: cursor.execute("UPDATE re_queries SET last_scraped=%(last_scraped)s, was_scraped=1 WHERE id=%(id)s", self.current_query) cursor.connection.commit() cursor.connection.close() self.current_query["next_scrape"] = time.time() + self.current_query["scrape_interval"] if "scrape_interval" in self.current_query and \ self.current_query["scrape_interval"] is not None else time.time() + SCRAPE_INTERVAL
import sys sys.path.insert(0,'..') from database.database import db_connect from pymysql.cursors import DictCursor count = 0 affected = 0 with open("autob_cities_v2.txt", "r") as f: lines = f.readlines() conn = db_connect() with conn.cursor() as cursor: cursor: DictCursor for line in lines: words = line.split(" ", 1) prev = affected affected += cursor.execute("UPDATE `cities` SET `autob_id`=%s WHERE city=%s", (words[0].strip(), words[1].strip())) if prev == affected: print("couldn't find this city: " + words[1].strip()) count += 1 print("total rows:") print(count) print("affected rows:") print(affected) conn.commit() conn.close()
def get_cities(): with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM cities") cities = cursor.fetchall() cursor.connection.close() return jsonify(cities)
from pywebpush import webpush import json import database.database as db with db.db_connect().cursor() as cursor: cursor.execute("SELECT * FROM push_notification_auth WHERE user_id=1") auth = cursor.fetchone() auth_json = auth["auth_json"] webpush(json.loads(auth_json), data=json.dumps({ "title": "My title", "body": "My blooolody", "href": "/register" }), vapid_private_key='./vapid_private.pem', vapid_claims={"sub": "mailto:[email protected]"}, verbose=True)
def insert_auto_ad(self): """Inserts car ad into database and returns inserted car ad""" if "autog_id" in self.prepared_params and self.prepared_params["autog_id"] is not None: self.prepared_params["key_column"] = "autog_id" self.prepared_params["key_value"] = self.prepared_params["autog_id"] elif "autop_id" in self.prepared_params and self.prepared_params["autop_id"] is not None: self.prepared_params["key_column"] = "autop_id" self.prepared_params["key_value"] = self.prepared_params["autop_id"] elif "autob_id" in self.prepared_params and self.prepared_params["autob_id"] is not None: self.prepared_params["key_column"] = "autob_id" self.prepared_params["key_value"] = self.prepared_params["autob_id"] with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM car_ads WHERE "+self.prepared_params["key_column"]+"=%(key_value)s", self.prepared_params) ad_exists = cursor.fetchone() self.auto_foreign_keys(cursor) if ad_exists: cursor.execute(f"""UPDATE `car_ads` SET make=%(make)s, model=%(model)s, year=%(year)s, engine=%(engine)s, fuel_type=%(fuel_type)s, body_type=%(body_type)s, color=%(color)s, gearbox=%(gearbox)s, driven_wheels=%(driven_wheels)s, damage=%(damage)s, steering_column=%(steering_column)s, door_count=%(door_count)s, cylinder_count=%(cylinder_count)s, gear_count=%(gear_count)s, seat_count=%(seat_count)s, ts_to=DATE(%(ts_to)s), weight=%(weight)s, wheels=%(wheels)s, fuel_urban=%(fuel_urban)s, fuel_overland=%(fuel_overland)s, fuel_overall=%(fuel_overall)s, features=%(features)s, comments=%(comments)s, """+self.prepared_params["key_column"]+f"""=%(key_value)s, price=%(price)s, export_price=%(export_price)s, vin_code=%(vin_code)s, href=%(href)s, mileage=%(mileage)s, location=%(location)s, when_scraped=unix_timestamp(), phone=%(phone)s, deleted=0, first_reg_country=%(first_reg_country)s, el_range=%(el_range)s WHERE {self.prepared_params["key_column"]}=%(key_value)s""", self.prepared_params) self.prepared_params["id"] = ad_exists["id"] try: cursor.execute("INSERT INTO query_car_fk (query_id, car_id) VALUES (%(query_id)s, %(id)s)", self.prepared_params) except pymysql.IntegrityError as err: print("Car ad already existed: ") print(err) try: cursor.executemany("INSERT INTO `car_pictures`(`car_id`, `picture_href`) VALUES (%s, %s)", [(self.prepared_params["id"], p_href) for p_href in self.prepared_params["picture_href"]]) except pymysql.IntegrityError as err: print("Picture already exists: ") print(err) else: cursor.execute("""INSERT INTO `car_ads`(`make`, `model`, `year`, `engine`, `fuel_type`, `body_type`, `color`, `gearbox`, `driven_wheels`, `damage`, `steering_column`, `door_count`, `cylinder_count`, `gear_count`, `seat_count`, `ts_to`, `weight`, `wheels`, `fuel_urban`, `fuel_overland`, `fuel_overall`, `features`, `comments`, """+self.prepared_params["key_column"]+""", `price`, `export_price`, `vin_code`, href, mileage, location, when_scraped, phone, deleted, first_reg_country, el_range) VALUES (%(make)s, %(model)s, %(year)s, %(engine)s, %(fuel_type)s, %(body_type)s, %(color)s, %(gearbox)s, %(driven_wheels)s, %(damage)s, %(steering_column)s, %(door_count)s, %(cylinder_count)s, %(gear_count)s, %(seat_count)s, DATE(%(ts_to)s), %(weight)s, %(wheels)s, %(fuel_urban)s, %(fuel_overland)s, %(fuel_overall)s, %(features)s, %(comments)s, %(key_value)s, %(price)s, %(export_price)s, %(vin_code)s, %(href)s, %(mileage)s, %(location)s, unix_timestamp(), %(phone)s, 0, %(first_reg_country)s, %(el_range)s)""", self.prepared_params) self.prepared_params["id"] = cursor.lastrowid cursor.execute("INSERT INTO query_car_fk (query_id, car_id) VALUES (%(query_id)s, %(id)s)", self.prepared_params) cursor.executemany("INSERT INTO `car_pictures`(`car_id`, `picture_href`) VALUES (%s, %s)", [(self.prepared_params["id"], p_href) for p_href in self.prepared_params["picture_href"]]) cursor.connection.commit() cursor.connection.close() car = self.extract_relevant_attributes() return car
def get_models(make_id): with db_connect().cursor() as cursor: cursor.execute("SELECT * FROM models WHERE make_id=%s", make_id) models = cursor.fetchall() cursor.connection.close() return jsonify(models)