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']
