database_utils.py - master - evdokimov/car-wave

database_utils.py
104 строки2 KB
evdokimov
first_commit
05 сен 2024, 02:28
05 сен 2024, 02:282549e5e
import sqlite3

DB_NAME = "bulletins.db"


def initialize_database():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    cursor.execute(
        """CREATE TABLE IF NOT EXISTS bulletins (
            link TEXT PRIMARY KEY,
            brand TEXT,
            model TEXT,
            vehicle_year INTEGER,
            price TEXT,
            description TEXT,
            location TEXT,
            timestamp TEXT
        )"""
    )

    conn.commit()
    conn.close()


def get_existing_records():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    cursor.execute("SELECT link, price FROM bulletins")
    records = cursor.fetchall()

    conn.close()

    # Convert the records into a dictionary for easier lookup
    return {record[0]: record[1] for record in records}


def insert_bulletin(bulletin):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    cursor.execute(
        """INSERT INTO bulletins (link, brand, model, vehicle_year, price, description, location, timestamp)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
        (
            bulletin["link"],
            bulletin["brand"],
            bulletin["model"],
            bulletin["vehicle_year"],
            bulletin["price"],
            bulletin["description"],
            bulletin["location"],
            bulletin["timestamp"],
        ),
    )

    conn.commit()
    conn.close()


def update_bulletin(bulletin):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    cursor.execute(
        """UPDATE bulletins
        SET price = ?, timestamp = ?
        WHERE link = ?""",
        (
            bulletin["price"],
            bulletin["timestamp"],
            bulletin["link"],
        ),
    )

    conn.commit()
    conn.close()


def delete_removed_bulletins(bulletins):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    links = tuple(bulletin["link"] for bulletin in bulletins)
    brand = bulletins[0]["brand"]
    model = bulletins[0]["model"]
    cursor.execute(
        """DELETE FROM bulletins
        WHERE brand = ?
        AND model = ?
        AND link NOT IN ({seq})""".format(
            seq=",".join(["?"] * len(links))
        ),
        (
            brand,
            model,
            *links,
        ),
    )

    conn.commit()
    conn.close()