def test_save_outside_transaction(self): self._db.begin() self._db.set_value('four', 'five') self._db.commit() new_db = Database('test_db.csv') self.assertEqual('five', new_db.get_value('four'))
def test_save_inside_transaction(self): self._db.begin() self._db.set_value('four', 'five') self._db.save() new_db = Database('test_db.csv') self.assertIsNone(new_db.get_value('five'))
def search_movie(search_criteria): data = json.loads(search_criteria) search = data["search"] myDb = Database.dbConnection() print(myDb) sqlString = "SELECT * FROM titles WHERE primaryTitle LIKE %s" + "%'" + "AND titleType = 'movie'" if "genre:" in search: search = search.replace(" ", "") genre = search.rsplit(":")[-1] sqlString = "SELECT * FROM titles WHERE genres LIKE \'{}\' and titleType = \'movie\' limit 100".format( genre) elif "year:" in search: search = search.replace(" ", "") year = search.rsplit(":")[-1] sqlString = "SELECT * FROM titles WHERE startYear LIKE \'{}\' and titleType = \'movie\' limit 100".format( year) value = search result = Database.selectStatement(myDb, sqlString, value) fetch = result.fetchall() info = {} for row in fetch: info[row[0]] = { "movieID": row[0], "title": row[3], "isAdult": row[4], "startYear": row[5], "runtime": row[7], "genres": row[8] } return json.dumps(info)
def find_rate(service_type: str, zone: int, package: Package) -> float: """ After obtaining the zone, it finds the rate it needs and calculates the prices of the shipment even if it haves an exceeded weight it calculates it. :param service_type: str of service type to calculate :param zone: int of the calculated zone :param package: Package detail :return: float of shipment price """ exceeded_price = 0 if package.weight % 1 > 0: package_weight = int( package.weight ) + 1 # if it has more than an int weight it must add 1 else: package_weight = int(package.weight) # finds the exceeded weight and rate, with that it calculates the exceeded price if TYPE_KG_LIMIT[service_type] < package_weight: exceeded_weight = package_weight - TYPE_KG_LIMIT[service_type] package_weight = TYPE_KG_LIMIT[service_type] exceeded_query = { 'type': service_type, 'rates_by_zone.zone': zone, 'rates_by_zone.kg': '+' } exceeded_project = { '_id': 0, 'rates_by_zone': { '$elemMatch': { 'zone': zone, 'kg': '+' } }, 'rates_by_zone.rate': 1 } exceeded_rate = Database.find( ZONE_TO_RATE, exceeded_query, exceeded_project).next()['rates_by_zone'][0]['rate'] exceeded_price = exceeded_rate * exceeded_weight query = { 'type': service_type, 'rates_by_zone.zone': zone, 'rates_by_zone.kg': package_weight } project = { '_id': 0, 'rates_by_zone': { '$elemMatch': { 'zone': zone, 'kg': package_weight } }, 'rates_by_zone.rate': 1 } rate = Database.find(ZONE_TO_RATE, query, project).next()['rates_by_zone'][0]['rate'] return rate + exceeded_price
def get_subscribers(): myDb = Database.dbConnection() print(myDb) sqlString = "SELECT email FROM users WHERE isSubscribed = '1'" result = Database.execStatement(myDb, sqlString) fetch = result.fetchall() emailList = [] for i in fetch: emailList.append(i[0]) return emailList
def update_name(update_info): data = json.loads(update_info) name = data["name"] userid = str(data["userid"]) myDb = Database.dbConnection() print(myDb) sqlString = "UPDATE users SET fullname = %s WHERE userid = %s " values = (name, userid) result = Database.execStatement(myDb, sqlString, values) myDb.commit()
def update_subscription(update_info): data = json.loads(update_info) status = str(data["status"]) userid = str(data["userid"]) myDb = Database.dbConnection() print(myDb) sqlString = "UPDATE users SET isSubscribed = %s WHERE userid = %s" values = (status, userid) result = Database.execStatement(myDb, sqlString, values) myDb.commit()
def update_ticket(info): data = json.loads(info) ticket_id = data["ticketID"] myDb = Database.dbConnection() print(myDb) sqlString = "UPDATE tickets SET status = 'Resolved' WHERE ticket_id = %s" value = ticket_id result = Database.execStatement(myDb, sqlString), value myDb.commit()
def change_password(info): data = json.loads(info) current_hash = data["current_hash"] change_hash = data["change_hash"] userid = str(data["userid"]) myDb = Database.dbConnection() print(myDb) sqlString = "UPDATE users SET passwordHash = %s WHERE userid = %s AND passwordHash = %s" values = (current_hash, userid, change_hash) result = Database.execStatement(myDb, sqlString, values) myDb.commit()
def setUp(self): Database.create_tables([ Game, User, Country, DiplomaticRelationship, DiplomaticRelationshipsAudit, Terror, PR, MediaArticle, ])
def tearDown(self): Database.drop_tables([ Game, User, Country, DiplomaticRelationship, DiplomaticRelationshipsAudit, Terror, PR, MediaArticle, ])
def create_ticket(info): data = json.loads(info) user = data["user"] ticket_id = data["ticket_id"] myDb = Database.dbConnection() print(myDb) sqlString = "INSERT INTO tickets (ticket_id, user, status) VALUES (%s, %s, %s)" values = (ticket_id, user, 'Unresolved') result = Database.execStatement(myDb, sqlString, values) myDb.commit()
def is_available(package: Package) -> (dict, dict): try: area_from = Database.find(AREAS_COLLECTION, { 'zip_codes': package.origin_zipcode }).next() area_to = Database.find(AREAS_COLLECTION, { 'zip_codes': package.destiny_zipcode }).next() except StopIteration: raise ZipCodesNotFound("No Hay Disponibilidad") return area_from, area_to
def find_rate(cls, service_type: str, package: Package) -> dict: if package.weight % 1 > 0: package_weight = int( package.weight ) + 1 # if it has more than an int weight it must add 1 else: package_weight = int(package.weight) if package_weight >= TYPE_KG_LIMIT and service_type == SPECIAL_TYPE: service_type = "5522411" if (package.origin_zipcode in DF_ZIP_CODES and package.destiny_zipcode in DF_ZIP_CODES) and service_type != \ TYPES_STR_TO_ID['ESTAFETA_DIA_SIGUIENTE']: service_type = "8608731" if service_type == SPECIAL_TYPE: price, descriptions = cls.Graph.dijkstra(0, package_weight) result_descriptions = dict() for description in descriptions: if result_descriptions.get(description) is None: result_descriptions[description] = 1 if "TERRESTRE" in description: rate_data = list( Database.find("Estafeta_rates", {"type": description}))[0] result_descriptions["cuenta"] = rate_data['_id'] result_descriptions['covered_kg'] = rate_data['kg'] result_descriptions['extra_kg_rate'] = rate_data[ 'adicional'] # TODO Delete THIS when needed if result_descriptions["cuenta"] in ['8646027']: result_descriptions["cuenta"] = '8622603' else: result_descriptions[description] += 1 return {"price": price, "options": result_descriptions} rate = Database.find("Estafeta_rates", {"_id": service_type}).next() exceeded_price = 0 exceeded_weight = 0 if package_weight > rate['kg']: exceeded_weight = package_weight - rate['kg'] exceeded_price = rate['adicional'] * exceeded_weight final_rate = rate['total'] + exceeded_price # TODO Delete THIS when needed if service_type in ['8646027']: service_type = '8622603' options = { rate['type']: 1, 'adicional': exceeded_weight, "cuenta": service_type, 'extra_kg_rate': rate['adicional'], 'covered_kg': rate['kg'] } return {'price': final_rate, "options": options}
def get_movieID(movie_title): data = json.loads(movie_title) search = data["movieTitle"] myDb = Database.dbConnection() print(myDb) sqlString = "SELECT tconst FROM titles WHERE primaryTitle = %s AND titleType = 'movie'" value = search result = Database.execStatement(myDb, sqlString, value) fetch = result.fetchone()[0] info = {"titleID": fetch} return json.dumps(info)
def get_names(name_id): data = json.loads(name_id) search = data["nameID"] myDb = Database.dbConnection() print(myDb) sqlString = "SELECT primaryName FROM name_basic WHERE nconst = %s" value = search result = Database.execStatement(myDb, sqlString, value) fetch = result.fetchone() info = {"name": fetch} return json.dumps(info)
def create_user(user_info): data = json.loads(user_info) email = data["email"] userName = data["userName"] passwordHash = data["passwordHash"] fullName = data["fullName"] myDb = Database.dbConnection() print(myDb) sqlString = "INSERT INTO users (email, userName, passwordHash, fullName) VALUES (%s, %s, %s, %s)" values = (email, userName, passwordHash, fullName) result = Database.execStatement(myDb, sqlString, values) myDb.commit()
def __init__(self, **kwargs): super().__init__(**kwargs) self.INTERFACE = Builder.load_file('main.kv') self.database = Database() menu_items = [{"text":f"item {i}"} for i in range(50)] self.menu = MDDropdownMenu( caller=self.INTERFACE.ids.refCommande, items=menu_items, position="center", width_mult=8, ) self.menu.bind(on_release=self.set_item)
def setUp(self): data = [ ['foo', 'bar'], ['biz', 'baz'], ['spam', 'eggs'], ['bar', 'foo'], ['bat', 'foo'], ] f = open('test_db.csv', 'w') f.write('\n'.join(f'{key},{value}' for key, value in data)) f.close() self._db = Database('test_db.csv') super().setUp()
def get_tickets(): myDb = Database.dbConnection() print(myDb) sqlString = "SELECT * FROM tickets" result = Database.selectStatement(myDb, sqlString) fetch = result.fetchall() info = {} for ticket in fetch: info[ticket[0]] = { "ticket_id": ticket[0], "user": ticket[1], "status": ticket[2] } return json.dumps(info)
def get_user(login_info): data = json.loads(login_info) email = data["email"] passwordHash = data["passwordhash"] myDb = Database.dbConnection() print(myDb) sqlString = "SELECT passwordHash, userName FROM users WHERE email = " + "'" + email + "'" result = Database.selectStatement(myDb, sqlString) if (result.rowcount == 1): fetch = result.fetchone() if (fetch[0] == passwordHash): return fetch[1] else: return False
def create_graph() -> GraphUndirectedWeighted: rates = list( Database.find("Estafeta_rates", { "description": "TERRESTRE", "type": { "$ne": "METROPOLITANO" } }, {"_id": 0})) rates = {rate.pop('type'): dict(**rate) for rate in rates} graph = GraphUndirectedWeighted(22) for i in range(1, 21): if i <= rates['TERRESTRE_2']['kg']: description = 'TERRESTRE_2' rate = rates[description]['total'] elif rates['TERRESTRE_2']['kg'] < i <= rates['TERRESTRE_5']['kg']: description = 'TERRESTRE_5' rate = rates[description]['total'] elif rates['TERRESTRE_5']['kg'] < i <= rates['TERRESTRE_10']['kg']: description = 'TERRESTRE_10' rate = rates[description]['total'] else: description = 'TERRESTRE_20' rate = rates[description]['total'] graph.add_edge(0, i, rate, description) if i == 1: continue graph.add_edge(i, i + 1, rates['TERRESTRE_2']['adicional'], "adicional") return graph
def discard_types(self, package): option_types = list( Database.find("STF_ZP", {"zip_code": package.destiny_zipcode})) terrain_flag = False for opt in option_types: if opt['service_type'] == 'TERRESTRE': terrain_flag = True break if not option_types or not terrain_flag: option_types += [{ 'zip_code': package.destiny_zipcode, 'periodicity': 'SEMANAL', 'extra': 1, 'service_type': 'TERRESTRE', 'availability': [1, 1, 1, 1, 1, 0, 0] }] result = list() if isinstance(self.type, list): for type in self.type: for opt_type in option_types: if TYPES_ID_TOSERVICE_TYPE.get( opt_type['service_type']) == type: result.append(type) continue if result: self.type = result
def check_user(user_info): data = json.loads(user_info) email = data["email"] userName = data["userName"] myDb = Database.dbConnection() print(myDb) sqlString_Email = "SELECT email FROM users WHERE email = " + "'" + email + "'" sqlString_UserName = "******" + "'" + userName + "'" result1 = Database.selectStatement(myDb, sqlString_Email) result2 = Database.selectStatement(myDb, sqlString_UserName) if (result1.rowcount != 0): return 'email taken' elif (result2.rowcount != 0): return 'username taken' else: return 'clear'
def find_area(package: Package) -> (int, int): """ Given the package it determines the area of the destiny zipcode and the origin zipcode :param package: Package :return: tuple of areas """ try: area_from = Database.find(AREAS_COLLECTION, { 'zip_codes': package.origin_zipcode }).next()['Area'] area_to = Database.find(AREAS_COLLECTION, { 'zip_codes': package.destiny_zipcode }).next()['Area'] except StopIteration: raise ZipCodesNotFound("No Hay Disponibilidad") return area_from, area_to
def setUp(self): application = Flask(__name__) application.config['TESTING'] = True application.secret_key = 'you will not guess' application.permanent_session_lifetime = timedelta(seconds=1) self.application = application # Use an in memory database self.database = Database("sqlite://", 1, 0) self.session_manager = SessionStorage(self.database)
def get_cast(movie_id): data = json.loads(movie_id) search = data["titleID"] myDb = Database.dbConnection() print(myDb) sqlString = "Select a.nconst, a.primaryName, b.category, b.job, b.characters FROM 9uAko4qR3y.name_basic a, 9uAko4qR3y.cast b WHERE a.nconst = b.nconst AND b.tconst = %s" value = search result = Database.execStatement(myDb, sqlString, value) cast_fetch = result.fetchall() completeInfo = {} for row in cast_fetch: completeInfo[row[0]] = { "name": row[1], "category": row[2], "job": row[3], "characters": row[4] } return json.dumps(completeInfo)
def get_by_email(cls, email): """ given an email it returns the user object of the email given or raises an exception if the user is not found :param email: email of the user to find :return: user object """ user = Database.find_one(COLLECTION, {"email": email}) if user: return cls(**user) raise UserNotFoundException("El usuario con el correo dado no existe")
def get_user_info(info): data = json.loads(info) userid = str(data["userid"]) myDb = Database.dbConnection() print(myDb) sqlString = "SELECT username, fullname, isSubscribed FROM users WHERE userid = %s" value = userid result = Database.execStatement(myDb, sqlString, value) fetch = result.fetchall() if fetch != None: user_info = { "status": 1, "username": fetch[0][0], "fullname": fetch[0][1], "isSubscribed": fetch[0][2] } else: user_info = {"status": 0} return json.dumps(user_info)
def main(): db.drop_all() db.create_all() for row in clean_data: new_row = Database(rank=row[0], company=row[1], country=row[2], percentage=row[3], link=row[4]) db.session.add(new_row) db.session.commit()
def search_movie(search_criteria): data = json.loads(search_criteria) search = data["search"] myDb = Database.dbConnection() print(myDb) sqlString = "SELECT * FROM titles WHERE primaryTitle LIKE " + "'" + search + "%'" + "AND titleType = 'movie'" result = Database.selectStatement(myDb, sqlString) fetch = result.fetchall() info = {} for row in fetch: info[row[0]] = { "movieID": row[0], "title": row[3], "isAdult": row[4], "startYear": row[5], "runtime": row[7], "genres": row[8] } return json.dumps(info)
def find_zone(area_from: int, area_to: int) -> int: """ given the areas from which they are sending and receiving it gets the zone to which the rate will be calculates :param area_from: :param area_to: :return: """ zone = Database.find(AREA_TO_ZONE_COLLECTION, { 'x': area_from, 'y': area_to }).next() return zone['value']