def get_old_password(sysuser_id):
cursor = connection.cursor()
cursor.execute(
"""SELECT sysuser_passw FROM sys_user WHERE sysuser_id=%s""",
sysuser_id)
result = cursor.fetchone()
return result[0]
def delete_author_by_id(author_id: int) -> AuthorDto:
c = connection.cursor()
author = AuthorsService.get_author_by_id(author_id)
c.execute("DELETE FROM authors WHERE id=%s;", (author_id, ))
connection.commit()
return author
def change_user_password(username: str, password: str) -> UserDto:
c = connection.cursor()
password = get_password_hash(password)
c.execute("UPDATE users SET password=%s WHERE username=%s;",
(password, username))
connection.commit()
return UsersService.get_user_by_username(username)
def delete_cat_by_id(category_id: int) -> CategoryDto:
c = connection.cursor()
category = CategoriesService.get_category_by_id(category_id)
c.execute("DELETE FROM categories WHERE id=%s;", (category_id,))
connection.commit()
return category
def get_trainers_of_pokemon(pok_name):
# try:
with connection.cursor() as cursor:
get_trainers = f'SELECT trainerName FROM pokemon_ownership JOIN pokemons on pokemonId = id WHERE pokemons.name = \"{pok_name}\"'
cursor.execute(get_trainers)
trainer = cursor.fetchall()
return trainer
def insert_pokemon_table(id, name, types, height, weight):
with connection.cursor() as cursor:
pokemon_query = f"""INSERT INTO pokemons VALUES({id}, \"{name}\", {height}, {weight})"""
cursor.execute(pokemon_query)
connection.commit()
for t in types.split():
insert_pokemon_types_table(id, t)
def get_count() -> int:
try:
c = connection.cursor()
c.execute("SELECT COUNT(*) FROM posts;")
(count, ) = c.fetchone()
return count
except Exception as e:
raise PostsServiceException() from e
def delete_ownership(trainer, pok):
with connection.cursor() as cursor:
delete = f"""DELETE from pokemon_ownership
where trainerName = \'{trainer}\' and pokemonId = (SELECT id from pokemon where name = \'{pok}\')"""
cursor.execute(delete)
connection.commit()
# init_DB()
def get_user_by_id(user_id: int) -> UserDto:
c = connection.cursor()
c.execute(
"SELECT id, username, password, created_at, updated_at from users WHERE id=%s;",
(user_id, ),
)
user_data = c.fetchone()
return UserDto(*user_data)
def saveUser(id, nombres, apellidos, genero, anios):
try:
with connection.cursor() as cursor:
consulta = "INSERT INTO testuser(id,nombres,apellidos,genero,anios) VALUES (?,?,?,?,?);"
cursor.execute(consulta, id, nombres, apellidos, genero, anios)
print("=> Usuario guardado correctamente")
except Exception as e:
print("=> Ocurrió un error al insertar el usuario: ", e)
def delete(self, pokemon):
with connection.cursor() as cur:
try:
cur.execute("delete from pokedex where id=%s", (pokemon.id, ))
connection.commit()
except ValueError as error:
connection.rollback()
raise error
def CheckUserExsist(username, password):
with connection.cursor() as cur:
cur.execute(
"select * from user where username = '******' and password_hash = '" + password + "'")
data = cur.fetchone()
cur.close()
return data
def insert_pokemon_types_table(p_id, p_type):
with connection.cursor() as cursor:
try:
pokemon_query = f"""INSERT INTO pokemon_types VALUES({p_id}, \"{p_type}\")"""
cursor.execute(pokemon_query)
connection.commit()
except:
pass
def delete_pots_by_id(post_id: int) -> PostDto:
"""
Удалить пость по его ID и вернуть его после удаления.
"""
c = connection.cursor()
post = PostsService.get_post_by_id(post_id)
c.execute("DELETE FROM posts WHERE id=%s;", (post.id, ))
connection.commit()
return post
def getLessonsByClass(self):
self.cursor = connection.cursor()
self.cursor.execute(
"SELECT class.Name, lesson.Name FROM lesson INNER JOIN class ON lesson.Id = class.Id GROUP BY(class.Id)"
)
result = self.cursor.fetchall()
for i in result:
print(f"Sınıf: {i[0]} - Dersler: {i[1]}")
def find_by_name(cls, name):
cursor = connection.cursor()
query = "SELECT * FROM items WHERE name=%s"
cursor.execute(query, (name,))
row = cursor.fetchone()
if row:
return {'item': {'name': row[1],'price': row[2]}}
def delete(self, name):
cursor = connection.cursor()
query = "DELETE FROM items WHERE name=%s"
cursor.execute(query, (name,))
connection.commit()
return {'message': 'Item deleted'}
def get_session_by_token(token: str):
"""Получить запись из таблицы 'sessions' по токену"""
c = connection.cursor()
c.execute(
"SELECT id, token, user_id, payload, created_at, updated_at FROM sessions WHERE token=%s;",
(token, ),
)
session_data = c.fetchone()
return SessionDto(*session_data)
def create_app_tables(connection):
""" Creates tables needed for the application if they don't already exist """
with connection:
with connection.cursor() as cursor:
# Create event_type
cursor.execute(
"""
DO $$ BEGIN
CREATE TYPE event_type AS ENUM ('incoming', 'sale');
EXCEPTION
-- Types cannot be recreated easily
-- they dont have "IF NOT EXISTS" modifier either
-- thus catching this specific exception as per:
-- https://www.thetopsites.net/article/50011731.shtml
WHEN duplicate_object THEN null;
END $$;
"""
)
# Create `transactions` table
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS transactions (
transaction_id uuid PRIMARY KEY,
event event_type,
timestamp timestamptz,
store_number int,
item_number int,
amount int
);
"""
)
# Create `stores` table
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS stores (
id int PRIMARY KEY
);
"""
)
# Create `items` table
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS items (
id SERIAL,
item_id int,
store_id int,
amount int,
CONSTRAINT fk_store
FOREIGN KEY(store_id)
REFERENCES stores(id)
ON DELETE CASCADE
);
"""
)
logger.info("App tables created!")
def get_all_pokemons(self):
with connection.cursor() as cur:
cur.execute("select id, name, element from pokedex")
# on récupère des tuples et les transforme en objects Pokemon
result = [
Pokemon(id=item[0], name=item[1], element=item[2])
for item in cur.fetchall()
]
return result
def update(self, pokemon):
with connection.cursor() as cur:
try:
cur.execute(
"update pokedex set name=%s, element=%s where id=%s",
(pokemon.name, pokemon.element, pokemon.id))
connection.commit()
except ValueError as error:
connection.rollback()
raise error
def createNewLink(original_url):
generated_code = generator_code()
cur = connection.cursor()
cur.execute(
"INSERT INTO links (original_url, code, created_at) VALUES (%s, %s, %s) RETURNING id,code;",
(original_url, generated_code, datetime.now()),
)
connection.commit()
(link_id, link_code) = cur.fetchone()
return (link_id, link_code)
def edit_author_by_id(author_id, first_name, last_name) -> AuthorDto:
c = connection.cursor()
c.execute(
"""UPDATE authors SET first_name=%s,last_name=%s WHERE id=%s;""",
(first_name, last_name, author_id))
connection.commit()
author = AuthorsService.get_author_by_id(author_id)
return author
def get_all_books(self):
with connection.cursor() as cur:
cur.execute("select id, name, author from book")
# on récupère des tuples et les transforme en objects Book
result = [
Book(id=item[0], author=item[2], name=item[1])
for item in cur.fetchall()
]
return result
def getAllLinks():
cur = connection.cursor()
cur.execute("SELECT id, original_url, code, created_at from links;")
data = cur.fetchall()
return [{
"id": v[0],
"original_url": v[1],
"code": v[2],
"created_at": v[3]
} for v in data]
def redirect_to_link(req: Request) -> Response:
code = req.path[1:]
cur = connection.cursor()
cur.execute("SELECT id FROM links WHERE code=%s", (code, ))
only = cur.fetchone()
LinksService.increase_views_count(only[0])
cur.execute("SELECT original_url FROM links WHERE code=%s", (code, ))
orig = cur.fetchone()
res = RedirectResponse(orig[0])
return res
def updateFirstStepConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set Plan = %s, LastStep=%s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(sql, (self.Plan, self.Steps,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record from plan selection page")
cursor.close()
finally:
return ""
def edit_category_by_id(category_id, title) -> CategoryDto:
c = connection.cursor()
c.execute(
"""UPDATE categories SET title=%s WHERE id=%s;""",
(title , category_id)
)
connection.commit()
category = CategoriesService.get_category_by_id(category_id)
return category
def updateOnReviewConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set Feature = %s, LastStep=%s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(sql, (self.Feature, self.Steps,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record on 7th step Updated a featured or not")
cursor.close()
finally:
return ""
def create_new_session() -> SessionDto:
"""Создает новую запись в таблице 'sessions'"""
token = gen_random_str(20)
now = datetime.now()
c = connection.cursor()
c.execute(
"INSERT INTO sessions (token, created_at, updated_at) VALUES (%s, %s, %s);",
(token, now, now),
)
connection.commit()
return SessionsService.get_session_by_token(token)
