def update_item(table_name, new_value, **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!")
elif type(d) != type(new_value):
raise mvc_exc.ValueTypeError(f"Value: <{new_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:
update_it = s.query(table_name).filter_by(**name).one()
except Exception:
raise mvc_exc.ItemNotStored(f"Can't update {name.items()} because it is not stored")
for key, value in name.items():
setattr(update_it, key, new_value)
s.add(update_it)
def read_item(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__}'!")
read_it = s.query(table_name).filter_by(**name).all()
if not read_it:
raise mvc_exc.ItemNotStored(f"{name.items()} not stored")
for rows in read_it:
print(rows)
def read_from_range(conn, key, a, b, table_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
cur.execute(f"SELECT * FROM {table_name}")
keys = [description[0] for description in cur.description]
i = 0
for ids in keys:
if key == ids:
if (type(data_type[i]) != type(a)) or (type(data_type[i]) !=
type(b)):
raise mvc_exc.ValueTypeError(
f"Key {key} and value {a} type not the same!")
if (type(a) is int) and (type(b) is int):
cur.execute(
f"SELECT * FROM {table_name} WHERE ({key} > {a}) AND ({key} < {b}) "
)
result = cur.fetchall()
elif type(a) is str:
cur.execute(
f"SELECT * FROM {table_name} WHERE {key} LIKE '%{a}%' ")
result = cur.fetchall()
elif type(a) is bool:
cur.execute(f"SELECT * FROM {table_name} WHERE {key} = {a} ")
result = cur.fetchall()
else:
cur.execute(
f"SELECT * FROM {table_name} WHERE ({key} > '{a}') AND ({key} < '{b}') "
)
result = cur.fetchall()
if cur.rowcount == 0:
print(f"In table {table_name} no such objects")
else:
for rows in result:
print(rows)
cur.close()
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 create_item(table_name, *value):
keys = get_all_keys(table_name)
i = 0
for key in keys:
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[i]):
raise mvc_exc.ValueTypeError(f"Value: <{value[i]}> must be the same type as key!")
i = i + 1
with session_scope() as s:
check_id = {keys[0]: value[0]}
check = s.query(table_name).filter_by(**check_id).all()
if not check:
check_items = {keys[i]: value[i] for i in range(1, len(keys))}
else:
raise mvc_exc.ItemAlreadyStored(
f"Can't create item with {keys[0]} = {value[0]} because it is already exists")
check = s.query(table_name).filter_by(**check_items).all()
if not check:
create_it = {}
i = 0
for key in keys:
create_it[key] = value[i]
i = i + 1
else:
raise mvc_exc.ItemAlreadyStored(f"Can't create item because all data for its id {keys[0]} = {value[0]} "
f"already exists")
s.add(table_name(**create_it))
def create_item(conn, table_name, *value):
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()
if type(type_dat[j]) != type(value[j]):
raise mvc_exc.ValueTypeError(
f"Type of value '{value[j]}' not the same as type of key name!"
)
j = j + 1
cur.execute(f"SELECT * FROM {table_name}")
keys = [description[0] for description in cur.description]
cur.execute(f"SELECT * FROM {table_name} WHERE {keys[0]} = {value[0]}")
if cur.rowcount != 0:
raise mvc_exc.ItemAlreadyStored(f'ID: "{value[0]}" already stored!')
if table_name == 'users':
cur.execute(
f"SELECT * FROM {table_name} WHERE {keys[1]} = '{value[1]}' and {keys[2]} = {value[2]}"
)
if cur.rowcount != 0:
raise mvc_exc.ItemAlreadyStored(
f'Item with that value "{value[1]}" "{value[2]}" already stored!'
)
elif table_name == ('email' or 'folders'):
cur.execute(
f"SELECT * FROM {table_name} WHERE {keys[1]} = {value[1]} and {keys[2]} = '{value[2]}' "
f"and {keys[3]} = '{value[3]}'")
if cur.rowcount != 0:
raise mvc_exc.ItemAlreadyStored(
f'Item with that value "{value[1]}" "{value[2]}" "{value[3]}"'
f' already stored!')
elif table_name == 'messages':
cur.execute(
f"SELECT * FROM {table_name} WHERE {keys[1]} = {value[1]} and {keys[2]} = '{value[2]}' and "
f"{keys[3]} = '{value[3]}'")
if cur.rowcount != 0:
raise mvc_exc.ItemAlreadyStored(
f'Item with that value "{value[1]}" "{value[2]}" "{value[3]}"'
f' already stored!')
elif table_name == 'folders_messages':
cur.execute(
f"SELECT * FROM {table_name} WHERE {keys[0]} = {value[0]} and {keys[1]} = {value[1]}"
)
if cur.rowcount != 0:
raise mvc_exc.ItemAlreadyStored(
f'Item with that value "{value[0]}" "{value[1]}" '
f' already stored!')
if table_name == ('email' or 'folders'):
cur.execute(
f"INSERT INTO {table_name} ({keys[0]},{keys[1]},{keys[2]},{keys[3]}) "
f"VALUES ({value[0]}, {value[1]}, '{value[2]}', '{value[3]}')")
elif table_name == 'users':
cur.execute(
f"INSERT INTO {table_name} ({keys[0]},{keys[1]},{keys[2]}) "
f"VALUES ({value[0]}, '{value[1]}', {value[2]})")
elif table_name == 'messages':
cur.execute(
f"INSERT INTO {table_name} ({keys[0]},{keys[1]},{keys[2]},{keys[3]}) "
f"VALUES ({value[0]}, {value[1]}, '{value[2]}', '{value[3]}')")
elif table_name == 'folders_messages':
cur.execute(f"INSERT INTO {table_name} ({keys[0]},{keys[1]}) "
f"VALUES ({value[0]}, {value[1]})")
conn.commit()
cur.close()