/
fowgwg
/
project
Обзор
Документация
Войти
/
fowgwg
/
project
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
CI/CD
Аналитика
Безопасность
master
database.py
186 строк
7 KB
fowgwg
upload files
24 дек 2025, 11:49
24 дек 2025, 11:49
ff1e9e0
Код
Авторство
О чём код?
import sqlite3 from datetime import datetime import pandas as pd class Database: def __init__(self, db_name='electronics_store.db'): self.connection = sqlite3.connect(db_name) self.create_tables() def create_tables(self): cursor = self.connection.cursor() # Таблица товаров cursor.execute(''' CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, category TEXT NOT NULL, price REAL NOT NULL, quantity INTEGER NOT NULL, warranty_period INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') # Таблица продаж cursor.execute(''' CREATE TABLE IF NOT EXISTS sales ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER, product_name TEXT NOT NULL, quantity INTEGER NOT NULL, price REAL NOT NULL, total REAL NOT NULL, sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, customer_name TEXT, FOREIGN KEY ( product_id ) REFERENCES products ( id ) ) ''') self.connection.commit() def add_product(self, name, category, price, quantity, warranty_period): cursor = self.connection.cursor() cursor.execute(''' INSERT INTO products (name, category, price, quantity, warranty_period) VALUES (?, ?, ?, ?, ?) ''', (name, category, price, quantity, warranty_period)) self.connection.commit() return cursor.lastrowid def get_all_products(self): cursor = self.connection.cursor() cursor.execute('SELECT * FROM products') return cursor.fetchall() def get_product_by_id(self, product_id): cursor = self.connection.cursor() cursor.execute('SELECT * FROM products WHERE id = ?', (product_id,)) return cursor.fetchone() def update_product_quantity(self, product_id, quantity): cursor = self.connection.cursor() cursor.execute(''' UPDATE products SET quantity = quantity - ? WHERE id = ? ''', (quantity, product_id)) self.connection.commit() def add_sale(self, product_id, product_name, quantity, price, customer_name=""): total = quantity * price cursor = self.connection.cursor() cursor.execute(''' INSERT INTO sales (product_id, product_name, quantity, price, total, sale_date, customer_name) VALUES (?, ?, ?, ?, ?, ?, ?) ''', (product_id, product_name, quantity, price, total, datetime.now(), customer_name)) # Обновляем количество товара self.update_product_quantity(product_id, quantity) self.connection.commit() return cursor.lastrowid def get_all_sales(self): cursor = self.connection.cursor() cursor.execute(''' SELECT s.*, p.category FROM sales s LEFT JOIN products p ON s.product_id = p.id ORDER BY s.sale_date DESC ''') return cursor.fetchall() def get_sales_report(self, start_date=None, end_date=None): cursor = self.connection.cursor() if start_date and end_date: cursor.execute(''' SELECT * FROM sales WHERE DATE (sale_date) BETWEEN ? AND ? ORDER BY sale_date DESC ''', (start_date, end_date)) else: cursor.execute('SELECT * FROM sales ORDER BY sale_date DESC') return cursor.fetchall() def get_sales_summary(self): cursor = self.connection.cursor() # Общая сумма продаж cursor.execute('SELECT SUM(total) FROM sales') total_sales = cursor.fetchone()[0] or 0 # Количество проданных товаров cursor.execute('SELECT SUM(quantity) FROM sales') total_items = cursor.fetchone()[0] or 0 # Продажи по категориям cursor.execute(''' SELECT p.category, SUM(s.total) as category_total FROM sales s LEFT JOIN products p ON s.product_id = p.id GROUP BY p.category ''') sales_by_category = cursor.fetchall() return { 'total_sales': total_sales, 'total_items': total_items, 'sales_by_category': sales_by_category }