d0SDVRQF

1import sqlite3
2
3
4def get_current_price(cursor, name):
5    query = """
6        SELECT prices.price
7        FROM products, prices
8        WHERE products.name = ? AND prices.product_id = products.id
9        ORDER BY prices.created_at DESC
10        LIMIT 1
11    """
12    cursor.execute(query, (name,))
13    row = cursor.fetchone()
14    if row is not None:
15        return row[0]
16
17
18# postgres://guest:guest@127.0.0.1:5432/site
19# <schema>://[[<username>:<password>@]host[:port]]/<database>[?param1=value1[&param2=value2[&...]]]
20connection = sqlite3.connect("test.db")
21cursor = connection.cursor()
22cursor.execute("""
23    CREATE TABLE IF NOT EXISTS products(
24        id INTEGER PRIMARY KEY AUTOINCREMENT,
25        name VARCHAR(64) NOT NULL UNIQUE,
26        created_at DATETIME NOT NULL DEFAULT (datetime('now','localtime'))
27    );
28""")
29cursor.execute("""
30    CREATE TABLE IF NOT EXISTS prices(
31        id INTEGER PRIMARY KEY AUTOINCREMENT,
32        price REAL NOT NULL,
33        product_id INTEGER NOT NULL,
34        created_at DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
35        FOREIGN KEY (product_id) REFERENCES products(id)
36    )
37""")
38
39
40products = (
41    ("Хлеб",),
42    ("Молоко",),
43    ("Огурцы",),
44    ("Водка",),
45)
46query = "INSERT INTO products (name) VALUES (?)"
47
48try:
49    cursor.executemany(query, products)
50except sqlite3.IntegrityError:
51    print("Products already exists")
52connection.commit()
53
54cursor.execute("SELECT * FROM products")
55products = []
56for id, name, created_at in cursor:
57    products.append((id, name, created_at))
58
59# prices = [
60#     (products[0][0], 25.55),
61#     (products[1][0], 63.95),
62#     (products[2][0], 25.00),
63#     (products[3][0], 154.99),
64# ]
65
66# query = "INSERT INTO prices (product_id, price) VALUES (?, ?)"
67# cursor.executemany(query, prices)
68# connection.commit()
69
70price = get_current_price(cursor, "Хлеб")
71print(price)