数据库存储特价

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(255),
    RegularPrice DECIMAL(10, 2)
);

CREATE TABLE SpecialOffers (
    OfferID INT PRIMARY KEY,
    ProductID INT,
    DiscountedPrice DECIMAL(10, 2),
    StartDate DATE,
    EndDate DATE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

CREATE INDEX idx_productid ON SpecialOffers(ProductID);
CREATE INDEX idx_startdate ON SpecialOffers(StartDate);

from sqlalchemy import create_engine, Column, Integer, String, DECIMAL, Date, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()

class Product(Base):
    __tablename__ = 'Products'
    ProductID = Column(Integer, primary_key=True)
    Name = Column(String(255))
    RegularPrice = Column(DECIMAL(10, 2))

class SpecialOffer(Base):
    __tablename__ = 'SpecialOffers'
    OfferID = Column(Integer, primary_key=True)
    ProductID = Column(Integer, ForeignKey('Products.ProductID'))
    DiscountedPrice = Column(DECIMAL(10, 2))
    StartDate = Column(Date)
    EndDate = Column(Date)
    product = relationship("Product")

engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# 添加特价信息
new_offer = SpecialOffer(
    ProductID=1,
    DiscountedPrice=99.99,
    StartDate='2023-10-01',
    EndDate='2023-10-31'
)
session.add(new_offer)
session.commit()

# 查询特价信息
offers = session.query(SpecialOffer).filter_by(ProductID=1).all()
for offer in offers:
    print(f"Product ID: {offer.ProductID}, Discounted Price: {offer.DiscountedPrice}, Start Date: {offer.StartDate}, End Date: {offer.EndDate}")