sample json to database sqlalchemy

sample_json_to_database_sqlalchemy.py

import json
import uuid
from sqlalchemy import create_engine, Column, Integer, String, Float, Boolean, DateTime, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER
from datetime import datetime

Base = declarative_base()

class Product(Base):
    __tablename__ = 'ECommerceSystem_products'
    id = Column(UNIQUEIDENTIFIER, primary_key=True, default=uuid.uuid4)
    name = Column(String(100), nullable=False)
    description = Column(String)
    category = Column(String, nullable=False)
    inStock = Column(Boolean, nullable=False)
    price = relationship("Price", uselist=False, back_populates="product")
    tags = relationship("ProductTag", back_populates="product")
    attributes = relationship("ProductAttribute", back_populates="product")
    images = relationship("ProductImage", back_populates="product")

class Price(Base):
    __tablename__ = 'ECommerceSystem_products_price'
    id = Column(Integer, primary_key=True)
    product_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_products.id'), unique=True)
    amount = Column(Float, nullable=False)
    currency = Column(String(3), nullable=False)
    product = relationship("Product", back_populates="price")

class ProductTag(Base):
    __tablename__ = 'ECommerceSystem_products_tags'
    id = Column(Integer, primary_key=True)
    product_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_products.id'))
    value = Column(String, nullable=False)
    product = relationship("Product", back_populates="tags")

class ProductAttribute(Base):
    __tablename__ = 'ECommerceSystem_products_additional_props'
    id = Column(Integer, primary_key=True)
    product_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_products.id'))
    prop_name = Column(String, nullable=False)
    prop_value = Column(String)
    product = relationship("Product", back_populates="attributes")

class ProductImage(Base):
    __tablename__ = 'ECommerceSystem_products_images'
    id = Column(Integer, primary_key=True)
    product_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_products.id'))
    url = Column(String, nullable=False)
    alt = Column(String)
    product = relationship("Product", back_populates="images")

class Customer(Base):
    __tablename__ = 'ECommerceSystem_customers'
    id = Column(UNIQUEIDENTIFIER, primary_key=True, default=uuid.uuid4)
    firstName = Column(String, nullable=False)
    lastName = Column(String, nullable=False)
    email = Column(String, nullable=False)
    phoneNumber = Column(String)
    dateOfBirth = Column(DateTime)
    addresses = relationship("Address", back_populates="customer")
    favorite_categories = relationship("FavoriteCategory", back_populates="customer")
    communication_preferences = relationship("CommunicationPreference", uselist=False, back_populates="customer")

class Address(Base):
    __tablename__ = 'ECommerceSystem_customers_addresses'
    id = Column(Integer, primary_key=True)
    customer_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_customers.id'))
    street = Column(String, nullable=False)
    city = Column(String, nullable=False)
    state = Column(String, nullable=False)
    zipCode = Column(String, nullable=False)
    country = Column(String, nullable=False)
    customer = relationship("Customer", back_populates="addresses")

class FavoriteCategory(Base):
    __tablename__ = 'ECommerceSystem_customers_preferences_favoriteCategories'
    id = Column(Integer, primary_key=True)
    customer_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_customers.id'))
    value = Column(String, nullable=False)
    customer = relationship("Customer", back_populates="favorite_categories")

class CommunicationPreference(Base):
    __tablename__ = 'ECommerceSystem_customers_preferences_communicationPreferences'
    id = Column(Integer, primary_key=True)
    customer_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_customers.id'), unique=True)
    email = Column(Boolean)
    sms = Column(Boolean)
    push = Column(Boolean)
    customer = relationship("Customer", back_populates="communication_preferences")

class Order(Base):
    __tablename__ = 'ECommerceSystem_orders'
    id = Column(UNIQUEIDENTIFIER, primary_key=True, default=uuid.uuid4)
    customerId = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_customers.id'))
    orderDate = Column(DateTime, nullable=False)
    status = Column(String, nullable=False)
    items = relationship("OrderItem", back_populates="order")
    totalAmount = relationship("OrderTotalAmount", uselist=False, back_populates="order")
    shippingAddress = relationship("OrderShippingAddress", uselist=False, back_populates="order")
    billingAddress = relationship("OrderBillingAddress", uselist=False, back_populates="order")
    paymentInfo = relationship("PaymentInfo", uselist=False, back_populates="order")
    customer = relationship("Customer")

class OrderItem(Base):
    __tablename__ = 'ECommerceSystem_orders_items'
    id = Column(Integer, primary_key=True)
    order_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_orders.id'))
    productId = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_products.id'), nullable=False)
    quantity = Column(Integer, nullable=False)
    price = relationship("OrderItemPrice", uselist=False, back_populates="order_item")
    order = relationship("Order", back_populates="items")
    product = relationship("Product")

class OrderItemPrice(Base):
    __tablename__ = 'ECommerceSystem_orders_items_price'
    id = Column(Integer, primary_key=True)
    order_item_id = Column(Integer, ForeignKey('ECommerceSystem_orders_items.id'), unique=True)
    amount = Column(Float, nullable=False)
    currency = Column(String(3), nullable=False)
    order_item = relationship("OrderItem", back_populates="price")

class OrderTotalAmount(Base):
    __tablename__ = 'ECommerceSystem_orders_totalAmount'
    id = Column(Integer, primary_key=True)
    order_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_orders.id'), unique=True)
    amount = Column(Float, nullable=False)
    currency = Column(String(3), nullable=False)
    order = relationship("Order", back_populates="totalAmount")

class OrderShippingAddress(Base):
    __tablename__ = 'ECommerceSystem_orders_shippingAddress'
    id = Column(Integer, primary_key=True)
    order_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_orders.id'), unique=True)
    street = Column(String, nullable=False)
    city = Column(String, nullable=False)
    state = Column(String, nullable=False)
    zipCode = Column(String, nullable=False)
    country = Column(String, nullable=False)
    order = relationship("Order", back_populates="shippingAddress")

class OrderBillingAddress(Base):
    __tablename__ = 'ECommerceSystem_orders_billingAddress'
    id = Column(Integer, primary_key=True)
    order_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_orders.id'), unique=True)
    street = Column(String, nullable=False)
    city = Column(String, nullable=False)
    state = Column(String, nullable=False)
    zipCode = Column(String, nullable=False)
    country = Column(String, nullable=False)
    order = relationship("Order", back_populates="billingAddress")

class PaymentInfo(Base):
    __tablename__ = 'ECommerceSystem_orders_paymentInfo'
    id = Column(Integer, primary_key=True)
    order_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_orders.id'), unique=True)
    method = Column(String, nullable=False)
    transactionId = Column(String, nullable=False)
    cardLastFour = Column(String(4))
    order = relationship("Order", back_populates="paymentInfo")

class Review(Base):
    __tablename__ = 'ECommerceSystem_reviews'
    id = Column(UNIQUEIDENTIFIER, primary_key=True, default=uuid.uuid4)
    productId = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_products.id'), nullable=False)
    customerId = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_customers.id'), nullable=False)
    rating = Column(Integer, nullable=False)
    title = Column(String(100))
    content = Column(String, nullable=False)
    datePosted = Column(DateTime, nullable=False)
    helpful = Column(Integer)
    images = relationship("ReviewImage", back_populates="review")
    product = relationship("Product")
    customer = relationship("Customer")

class ReviewImage(Base):
    __tablename__ = 'ECommerceSystem_reviews_images'
    id = Column(Integer, primary_key=True)
    review_id = Column(UNIQUEIDENTIFIER, ForeignKey('ECommerceSystem_reviews.id'))
    value = Column(String, nullable=False)
    review = relationship("Review", back_populates="images")

class Metadata(Base):
    __tablename__ = 'ECommerceSystem_metadata'
    id = Column(Integer, primary_key=True)
    lastUpdated = Column(DateTime, nullable=False)
    version = Column(String, nullable=False)

def load_json_to_sql(json_file_path: str, connection_string: str):
    engine = create_engine(connection_string)
    Session = sessionmaker(bind=engine)
    session = Session()

    try:
        with open(json_file_path, 'r') as f:
            data = json.load(f)

        insert_products(session, data.get('products', []))
        insert_customers(session, data.get('customers', []))
        insert_orders(session, data.get('orders', []))
        insert_reviews(session, data.get('reviews', []))
        insert_metadata(session, data.get('metadata', {}))

        session.commit()
        print("Data loaded successfully!")
    except Exception as e:
        session.rollback()
        print(f"An error occurred: {str(e)}")
    finally:
        session.close()

def insert_products(session, products):
    for product_data in products:
        try:
            product = Product(
                name=product_data['name'],
                description=product_data.get('description'),
                category=product_data['category'],
                inStock=product_data['inStock']
            )
            product.price = Price(amount=product_data['price']['amount'], currency=product_data['price']['currency'])
            product.tags = [ProductTag(value=tag) for tag in product_data.get('tags', [])]
            product.attributes = [ProductAttribute(prop_name=k, prop_value=v) for k, v in product_data.get('attributes', {}).items()]
            product.images = [ProductImage(url=img['url'], alt=img.get('alt')) for img in product_data.get('images', [])]
            session.add(product)
        except KeyError as e:
            print(f"Missing required field in product data: {str(e)}")

def insert_customers(session, customers):
    for customer_data in customers:
        try:
            customer = Customer(
                firstName=customer_data['firstName'],
                lastName=customer_data['lastName'],
                email=customer_data['email'],
                phoneNumber=customer_data.get('phoneNumber'),
                dateOfBirth=parse_date(customer_data.get('dateOfBirth'))
            )
            customer.addresses = [Address(**addr) for addr in customer_data['addresses']]
            if 'preferences' in customer_data:
                prefs = customer_data['preferences']
                customer.favorite_categories = [FavoriteCategory(value=cat) for cat in prefs.get('favoriteCategories', [])]
                if 'communicationPreferences' in prefs:
                    customer.communication_preferences = CommunicationPreference(**prefs['communicationPreferences'])
            session.add(customer)
        except KeyError as e:
            print(f"Missing required field in customer data: {str(e)}")

def insert_orders(session, orders):
    for order_data in orders:
        try:
            order = Order(
                customerId=order_data['customerId'],
                orderDate=parse_date(order_data['orderDate']),
                status=order_data['status']
            )
            order.items = [OrderItem(
                productId=item['productId'],
                quantity=item['quantity'],
                price=OrderItemPrice(amount=item['price']['amount'], currency=item['price']['currency'])
            ) for item in order_data['items']]
            order.totalAmount = OrderTotalAmount(amount=order_data['totalAmount']['amount'], currency=order_data['totalAmount']['currency'])
            order.shippingAddress = OrderShippingAddress(**order_data['shippingAddress'])
            order.billingAddress = OrderBillingAddress(**order_data['billingAddress'])
            order.paymentInfo = PaymentInfo(**order_data['paymentInfo'])
            session.add(order)
        except KeyError as e:
            print(f"Missing required field in order data: {str(e)}")

def insert_reviews(session, reviews):
    for review_data in reviews:
        try:
            review = Review(
                productId=review_data['productId'],
                customerId=review_data['customerId'],
                rating=review_data['rating'],
                title=review_data.get('title'),
                content=review_data['content'],
                datePosted=parse_date(review_data['datePosted']),
                helpful=review_data.get('helpful', 0)
            )
            review.images = [ReviewImage(value=url) for url in review_data.get('images', [])]
            session.add(review)
        except KeyError as e:
            print(f"Missing required field in review data: {str(e)}")

def insert_metadata(session, metadata):
    try:
        meta = Metadata(
            lastUpdated=parse_date(metadata['lastUpdated']),
            version=metadata['version']
        )
        session.add(meta)
    except KeyError as e:
        print(f"Missing required field in metadata