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)
