85 lines
3.4 KiB
Python
85 lines
3.4 KiB
Python
import aiosqlite
|
|
|
|
class Database:
|
|
def __init__(self, db_path: str):
|
|
self.db_path = db_path
|
|
|
|
async def create_tables(self):
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY,
|
|
user_id INTEGER UNIQUE,
|
|
sphere TEXT,
|
|
language TEXT,
|
|
preferences TEXT
|
|
)
|
|
""")
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS sent_vacancies (
|
|
user_id INTEGER,
|
|
vacancy_url TEXT,
|
|
PRIMARY KEY (user_id, vacancy_url)
|
|
)
|
|
""")
|
|
await db.commit()
|
|
|
|
async def is_vacancy_sent(self, user_id: int, url: str) -> bool:
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
async with db.execute(
|
|
"SELECT 1 FROM sent_vacancies WHERE user_id = ? AND vacancy_url = ?",
|
|
(user_id, url)
|
|
) as cursor:
|
|
return await cursor.fetchone() is not None
|
|
|
|
async def mark_vacancy_as_sent(self, user_id: int, url: str):
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
await db.execute(
|
|
"INSERT OR IGNORE INTO sent_vacancies (user_id, vacancy_url) VALUES (?, ?)",
|
|
(user_id, url)
|
|
)
|
|
await db.commit()
|
|
|
|
async def add_user(self, user_id: int):
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
await db.execute(
|
|
"INSERT OR IGNORE INTO users (user_id) VALUES (?)",
|
|
(user_id,)
|
|
)
|
|
await db.commit()
|
|
|
|
async def update_user_data(self, user_id: int, data: dict):
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
await db.execute(
|
|
"UPDATE users SET sphere = ?, language = ?, preferences = ? WHERE user_id = ?",
|
|
(data.get('sphere'), data.get('language'), data.get('preferences'), user_id)
|
|
)
|
|
await db.commit()
|
|
|
|
async def get_all(self):
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
async with db.execute("SELECT * FROM users") as cursor:
|
|
return await cursor.fetchall()
|
|
async def get_user(self, user_id: int):
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
async with db.execute(
|
|
"SELECT sphere, language, preferences FROM users WHERE user_id = ?",
|
|
(user_id,)
|
|
) as cursor:
|
|
return await cursor.fetchone()
|
|
|
|
async def count_users(self) -> int:
|
|
#Возвращает количество зарегистрированных пользователей
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
async with db.execute("SELECT COUNT(*) FROM users") as cursor:
|
|
result = await cursor.fetchone()
|
|
return result[0] if result else 0
|
|
async def clear_sent_vacancies(self, user_id: int):
|
|
#Очищает историю отправленных вакансий для пользователя
|
|
async with aiosqlite.connect(self.db_path) as db:
|
|
await db.execute(
|
|
"DELETE FROM sent_vacancies WHERE user_id = ?",
|
|
(user_id,)
|
|
)
|
|
await db.commit()
|