def delete_items(conn, table_name, **name):
cur = conn.cursor()
cur.execute(
f"SELECT column_name, data_type FROM information_schema.columns "
f"WHERE table_schema='public' AND table_name = '{table_name}'")
data_type = cur.fetchall()
type_dat = []
for item in data_type:
type_dat += [item[1]]
j = 0
for item in type_dat:
if type_dat[j] == 'integer':
type_dat[j] = 1
if type_dat[j] == 'boolean':
type_dat[j] = False
if type_dat[j] == 'date':
type_dat[j] = DT.datetime.strptime('2020,1,1', '%Y,%m,%d').date()
j = j + 1
f = False
for key, value in name.items():
i = 0
for item in data_type:
if item[0] == key:
f = True
if type(type_dat[i]) != type(value):
raise mvc_exc.ValueTypeError(
f"Type of argument {value} is not the same as key type"
)
i = i + 1
if f is False:
raise mvc_exc.KeyNameError(f"Key name {key} doesn't exist")
if type(value) is (int or bool):
cur.execute(f"SELECT * FROM {table_name} WHERE {key} = {value}")
if cur.rowcount == 0:
raise mvc_exc.ItemNotStored(
'Can\'t delete "{}" because it\'s not stored'.format(name))
else:
cur.execute(f"DELETE FROM {table_name} WHERE {key} = {value}")
else:
cur.execute(f"SELECT * FROM {table_name} WHERE {key} = '{value}'")
if cur.rowcount == 0:
raise mvc_exc.ItemNotStored(
'Can\'t delete "{}" because it\'s not stored'.format(name))
else:
cur.execute(
f"DELETE FROM {table_name} WHERE {key} = '{value}'")
conn.commit()
cur.close()
return conn
def read_item(name):
global items
my_items = list(filter(lambda x: x['name'] == name, items))
if my_items:
return my_items[0]
else:
raise m_ex.ItemNotStored(f'{name} not stored!')
def read_item(name):
global items
myitems = list(filter(lambda x: x['name'] == name, items))
if myitems:
return myitems[0]
else:
raise mvc_exc.ItemNotStored('Can\'t read "{}" because it\'s not stored'.format(name))
def read_item(name):
global items
myitems = list(filter(lambda x: x['name'] == name, items))
if myitems:
return myitems[0]
else:
raise mvc_exc.ItemNotStored(f'Item with name: "{name}"')
def select_one(conn, name, table_name):
"""Select a single item in a table.
The dataset library returns a result as an OrderedDict.
Parameters
----------
name : str
name of the record to look for in the table
table_name : str
conn : dataset.persistence.database.Database
Raises
------
mvc_exc.ItemNotStored: if the record is not stored in the table.
"""
table = conn.load_table(table_name)
row = table.find_one(name=name)
if row is not None:
return dict(row)
else:
raise mvc_exc.ItemNotStored(
'Can\'t read "{}" because it\'s not stored in table "{}"'.format(
name, table.table.name))
def update_one(conn, name, price, quantity, table_name):
"""Update a single item in the table.
Note: dataset update method is a bit counterintuitive to use. Read the docs
here: https://dataset.readthedocs.io/en/latest/quickstart.html#storing-data
Dataset has also an upsert functionality: if rows with matching keys exist
they will be updated, otherwise a new row is inserted in the table.
Parameters
----------
name : str
price : float
quantity : int
table_name : str
conn : dataset.persistence.database.Database
Raises
------
mvc_exc.ItemNotStored: if the record is not stored in the table.
"""
table = conn.load_table(table_name)
row = table.find_one(name=name)
if row is not None:
item = {"name": name, "price": price, "quantity": quantity}
table.update(item, keys=["name"])
else:
raise mvc_exc.ItemNotStored(
'Can\'t update "{}" because it\'s not stored in table "{}"'.format(
name, table.table.name))
def delete_item(name):
global items
# Python 3.x removed tuple parameters unpacking (PEP 3113), so we have to do it manually (i_x is a tuple, idxs_items is a list of tuples)
if (typeof == 'autores'):
idxs_items = list(filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
else:
idxs_items = list(filter(lambda i_x: i_x[1]['title'] == name, enumerate(itemsAllBooks)))
idxs_items2 = list(filter(lambda i_x: i_x[1]['title'] == name, enumerate(items)))
if idxs_items:
i, item_to_delete = idxs_items[0][0], idxs_items[0][1]
if (typeof == 'autores'):
del items[i]
else:
del itemsAllBooks[i]
del items[idxs_items2[0][0]]
try:
with open(pathFile[typeof],'w') as filehandle: #a -> append
if (typeof == 'autores'):
json.dump(items,filehandle,indent=2, separators=(',', ': '))
else:
json.dump(itemsAllBooks,filehandle,indent=2, separators=(',', ': '))
filehandle.close()
except Exception as Error:
print("Error leyendo archivo")
else:
raise mvc_exc.ItemNotStored(
'No se puede borrar "{}" porque no existen registros'.format(name))
def read_item(name):
global items
myitems = list(filter(lambda x: x['name'] == name, items))
if myitems:
return myitems[0]
else:
raise mvc_exc.ItemNotStored('"{}" does not exist'.format(name))
def delete_items(table_name, **name):
with session_scope() as s:
keys = get_all_keys(table_name)
for key, value in name.items():
f = False
for item in keys:
if key == item:
f = True
d = getattr(table_name, key).type
if type(d) is Integer:
d = 0
elif type(d) is Boolean:
d = False
elif type(d) is Date:
d = DT.datetime.strptime('2020,1,1', '%Y,%m,%d').date()
elif type(d) is String:
d = 'str'
if type(d) != type(value):
raise mvc_exc.ValueTypeError(f"Value: <{value}> must be the same type as key!")
if f is False:
raise mvc_exc.KeyNameError(f"Key: <{key}> doesn't exist in table '{table_name.__tablename__}'!")
try:
delete_it = s.query(table_name).filter_by(**name).one()
except Exception:
raise mvc_exc.ItemNotStored(f"Can't delete {name.items()} because it is not stored")
s.delete(delete_it)
def delete_item(name):
global items
# Python 3.x removed tuple parameters unpacking (PEP 3113), so we have to do it manually (i_x is a tuple, idxs_items is a list of tuples)
found_item = list(filter(lambda x: x[1]['name'] == name, enumerate(items)))
if found_item:
del items[found_item[0][0]]
else:
raise mvc_exc.ItemNotStored(f'name: "{name}" not stored')
def update_item(name, price, quantity):
global items
idxs_items = list(filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
if idxs_items:
i, item_to_update = idxs_items[0][0], idxs_items[0][1]
items[i] = {'name': name, 'price': price, 'quantity': quantity}
else:
raise mvc_exc.ItemNotStored('Can not update "{}" because it is not stored'.format(name))
def read_item(name):
global items
myitems = list(filter(lambda x: x['name'] == name, items))
if myitems: # truthy hvis elementer fundet
return myitems[0]
else:
raise mvc_exceptions.ItemNotStored(
'Can\'t read "{}", because it is not stored.'.format(name))
def get_one(id):
global persons
myitems = list(filter(lambda x: x['id'] == id, persons))
if myitems:
return myitems[0]
else:
raise mvc_exc.ItemNotStored(
'Can\'t read "{}" because it\'s not stored'.format(id))
def delete_item(name):
global items
idxs_items = list(filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
if idxs_items:
i, item_to_delete = idxs_items[0][0], idxs_items[0][1]
del items[i]
else:
raise mvc_exc.ItemNotStored('Can not update "{}" because it is not stored'.format(name))
def read_item(name):
global items
myitems = list(filter(lambda x: x["name"] == name, items))
if myitems:
return myitems[0]
else:
raise mvc_exc.ItemNotStored(
"Can't read \"{}\" because it's not stored".format(name))
def delete_item(name):
global items
idxs_items = list(
filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
if idxs_items:
i, item_to_delete = idxs_items[0][0], idxs_items[0][1]
del items[i]
else:
raise m_ex.ItemNotStored(f'Can\'t delete {name}. Is not stored!')
def update(id, name, age):
global persons
idxs_items = list(
filter(lambda i_x: i_x[1]['id'] == id, enumerate(persons)))
if idxs_items:
i, item_to_update = idxs_items[0][0], idxs_items[0][1]
persons[i] = {'id': id, 'name': name, 'age': age}
else:
raise mvc_exc.ItemNotStored(
'Can\'t update "{}" because it\'s not stored'.format(id))
def delete_item(name):
global items
# Python 3.x removed tuple parameters unpacking (PEP 3113), so we have to do it manually (i_x is a tuple, idxs_items is a list of tuples)
idxs_items = list(
filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
if idxs_items:
i, item_to_delete = idxs_items[0][0], idxs_items[0][1]
del items[i]
else:
raise mvc_exc.ItemNotStored(
'Can\'t delete "{}" because it\'s not stored'.format(name))
def delete_one(id):
global persons
idxs_items = list(
filter(lambda i_x: i_x[1]['id'] == id, enumerate(persons)))
if idxs_items:
i, item_to_delete = idxs_items[0][0], idxs_items[0][1]
del persons[i]
print(' id delete {}'.format(id))
else:
raise mvc_exc.ItemNotStored(
'Can\'t delete "{}" because it\'s not stored'.format(id))
def select_day(conn, day_name):
# Create query for obtaining day
# sql = 'SELECT * FROM Days WHERE name="{}"'.format(day_name)
# c = conn.execute(sql)
day_tuple = select_one(conn, day_name, "Days")
# Check if result is not zero
if day_tuple is not None:
# Create new day object
day = Day(day_tuple[1])
# Create query for obtaining meals ids
sql = 'SELECT * FROM DaysMeals WHERE day_id="{}"'.format(day_tuple[0])
c = conn.execute(sql)
dm_tuples = c.fetchall()
# Check if result is not zero
if dm_tuples is not None:
for dm_tuple in dm_tuples:
sql = 'SELECT * FROM Meals WHERE id="{}"'.format(dm_tuple[2])
c = conn.execute(sql)
meal_tuples = c.fetchall()
# Create list for meal objects
meals = list()
# Check if result is not zero
if meal_tuples is not None:
for meal_tuple in meal_tuples:
meals.append(select_meal(conn, meal_tuple[1]))
day.add_meals(meals)
else:
raise mvc_exc.ItemNotStored(
'Can\'t read "{}" because it\'s not stored in table DaysMeals'
.format(day_tuple[0]))
return day
else:
raise mvc_exc.ItemNotStored(
'Can\'t read "{}" because it\'s not stored in table Days'
.format(day_name))
def update_item(name, price, quantity):
global items
# Python 3.x removed tuple parameters unpacking (PEP 3113), so we have to do
# it manually (i_x is a tuple, idxs_items is a list of tuples)
idxs_items = list(
filter(lambda i_x: i_x[1]["name"] == name, enumerate(items)))
if idxs_items:
i, item_to_update = idxs_items[0][0], idxs_items[0][1]
items[i] = {"name": name, "price": price, "quantity": quantity}
else:
raise mvc_exc.ItemNotStored(
"Can't update \"{}\" because it's not stored".format(name))
def update_item(name, price, quantity):
global items
# since Python 3.x - no more tuple unpacking (PEP 3113) => we have to
# do it manually (i_x = tuple, idxs_items = list(tuples))
idxs_items = list(
filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
if idxs_items:
i, item_to_update = idxs_items[0][0], idxs_items[0][1]
items[i] = {'name': name, 'price': price, 'quantity': quantity}
else:
raise m_ex.ItemNotStored(f'Can\'t update {name}. Is not stored!')
def select_one(conn, item_name, table_name):
table_name = scrub(table_name)
item_name = scrub(item_name)
sql = 'SELECT * FROM {} WHERE name="{}"'.format(table_name, item_name)
c = conn.execute(sql)
result = c.fetchone()
if result is not None:
return tuple_to_dict(result)
else:
raise mvc_exc.ItemNotStored(
'Can\'t read "{}" because it\'s not stored in table "{}"'
.format(item_name, table_name))
def update_item(name, price, quantity):
global items
# Python 3.x removed tuple parameters unpacking (PEP 3113),
# so we have to do it manually (i_x is a tuple, idxs_items is a list of tuples)
idxs_items = list(
filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
if idxs_items:
i, item_to_update = idxs_items[0][0], idxs_items[0][1]
items[i] = {'name': name, 'price': price, 'quantity': quantity}
else:
raise mvc_exc.ItemNotStored(
'Can\'t update "{}" because it\'s not stored'.format(name))
def read_item(name):
global items
if (typeof == 'autores'):
myitems = list(filter(lambda x: x['name'] == name, items))
else:
myitems = list(filter(lambda x: x['title'] == name, items))
if myitems:
return myitems[0]
else:
raise mvc_exc.ItemNotStored(
'No puedes obtener nada de "{}" porque no existen registros'.format(name))
def select_one(conn, name, table_name):
# Create query for obtaining item
sql = 'SELECT * FROM "{}" WHERE name="{}"'.format(table_name, name)
c = conn.execute(sql)
result = c.fetchone()
# Check if result is not zero
if result is not None:
return result
else:
raise mvc_exc.ItemNotStored(
'Can\'t read "{}" because it\'s not stored in table "{}"'
.format(name, table_name))
def select_ingredient(conn, ingredient_name):
# Create query for obtaining ingredient
sql_ingredient = 'SELECT * FROM Ingredients WHERE name="{}"'.format(ingredient_name)
c = conn.execute(sql_ingredient)
ingredient_tuple = c.fetchone()
# Check if result is not zero
if ingredient_tuple is not None:
return convert_ingredient(ingredient_tuple)
else:
raise mvc_exc.ItemNotStored(
'Can\'t read "{}" because it\'s not stored in table Ingredient'
.format(ingredient_name))
def update_item(name):
global items, typeof
# Python 3.x removed tuple parameters unpacking (PEP 3113), so we have to do it manually (i_x is a tuple, idxs_items is a list of tuples)
if (typeof == 'autores'):
idxs_items = list(filter(lambda i_x: i_x[1]['name'] == name, enumerate(items)))
else:
idxs_items = list(filter(lambda i_x: i_x[1]['title'] == name, enumerate(itemsAllBooks)))
idxs_items2 = list(filter(lambda i_x: i_x[1]['title'] == name, enumerate(items)))
if idxs_items:
i, item_to_update = idxs_items[0][0], idxs_items[0][1]
#print(f"i: {i}")
#print(f"len(itemsAllBooks): {len(itemsAllBooks)}")
#print(f"idxs_items[0][0]: {idxs_items[0][0]}")
if (typeof == 'autores'):
items[i] = {'authorid' : items[i]['authorid'],
'name' : input("Ingresa el nuevo nombre: "),
'workcount' : 0,
'fan_count' : 0,
'image_url' : 0,
'about' : input("Ingresa la nueva bio: "),
'country' : input("Ingresa el nuevo pais: ")
}
else:
itemsAllBooks[i] = {'id' : itemsAllBooks[i]['id'],
'title' : input("Ingresa el nuevo titulo del libro: "),
'authors' : input("Ingresa los autores separados por comas: ")
}
items[idxs_items2[0][0]] = {'id' : items[idxs_items2[0][0]]['id'],
'title' : itemsAllBooks[i]['title'],
'authors' : itemsAllBooks[i]['authors']
}
try:
with open(pathFile[typeof],'w') as filehandle: #a -> append
if (typeof == 'autores'):
json.dump(items,filehandle,indent=2, separators=(',', ': '))
else:
json.dump(itemsAllBooks,filehandle,indent=2, separators=(',', ': '))
filehandle.close()
except Exception as Error:
print("Error leyendo archivo")
#print(idxs_items2[0][0])
else:
raise mvc_exc.ItemNotStored(
'No se puede actualizar "{}" porque no existen registros'.format(name))
def delete_one(conn, name, table_name):
table_name = scrub(table_name)
sql_check = "SELECT EXISTS(SELECT 1 FROM {} WHERE name=? LIMIT 1)".format(
table_name)
table_name = scrub(table_name)
sql_delete = "DELETE FROM {} WHERE name=?".format(table_name)
c = conn.execute(sql_check, (name, )) # we need the comma
result = c.fetchone()
if result[0]:
c.execute(sql_delete, (name, )) # we need the comma
conn.commit()
else:
raise mvc_exc.ItemNotStored(
'Can\'t delete "{}" because it\'s not stored in table "{}"'.format(
name, table_name))
def update_one(conn, name, price, quantity, table_name):
table_name = scrub(table_name)
sql_check = "SELECT EXISTS(SELECT 1 FROM {} WHERE name=? LIMIT 1)".format(
table_name)
sql_update = "UPDATE {} SET price=?, quantity=? WHERE name=?".format(
table_name)
c = conn.execute(sql_check, (name, )) # we need the comma
result = c.fetchone()
if result[0]:
c.execute(sql_update, (price, quantity, name))
conn.commit()
else:
raise mvc_exc.ItemNotStored(
'Can\'t update "{}" because it\'s not stored in table "{}"'.format(
name, table_name))
