subject table for an INSERT, UPDATE or DELETE expected,got 'comparison_bill_data'

Question

I am trying to write an insert query using Python sqlAlchemy. I keep getting an error sqlalchemy.exc.ArgumentError: subject table for an INSERT, UPDATE or DELETE expected, got 'comparison_bill_data'..My code is as follows-

from uuid import uuid4
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID
from base import Base
from src.app.database.database import SessionLocal
from sqlalchemy.dialects.postgresql import insert
import  datetime


class ComparisonBillData(Base):
    __tablename__ = 'comparison_bill_data'

    comparison_bill_data_id = sa.Column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    bill_id = sa.Column(sa.String(), nullable=False)
    account_id = sa.Column(sa.String(), nullable=False)
    service_location_id = sa.Column(sa.String(), nullable=False)
    usage = sa.Column(sa.Numeric(), nullable=False)
    cost = sa.Column(sa.Numeric(), nullable=False)
    business_type = sa.Column(sa.String(), nullable=False)
    tenant_id = sa.Column(sa.String(), nullable=False)
    commodity_type = sa.Column(sa.String(), nullable=False)
    bill_date = sa.Column(sa.Date(), nullable=False)
    usage_unit = sa.Column(sa.String(), nullable=False)
    created_at = sa.Column(sa.TIMESTAMP(), nullable=False)
    updated_at = sa.Column(sa.TIMESTAMP(), nullable=False, server_default=sa.text('now()'))

    def __init__(self, comparison_bill_data_id,bill_id,account_id,service_location_id,usage,cost,business_type,tenant_id,commodity_type,bill_date,usage_unit,created_at):
        self.comparison_bill_data_id = comparison_bill_data_id
        self.bill_id = bill_id
        self.account_id=account_id
        self.service_location_id=service_location_id
        self.usage=usage
        self.cost=cost
        self.business_type=business_type
        self.tenant_id=tenant_id
        self.commodity_type=commodity_type
        self.bill_date=bill_date
        self.usage_unit=usage_unit
        self.created_at=created_at


def upsert_comparison_bill_data(bill_data: ComparisonBillData):
    print("List="+ str(list))
    statement = insert(ComparisonBillData.__tablename__).values((bill_data),)
    # statement = insert(Base.metadata.tables[ComparisonBillData.__tablename__]).values((bill_data),)
    query = statement.on_conflict_do_update(
        constraint="ui_comparison_bill_data_service_location_id", set_={
            "usage": statement.excluded.usage,
            "cost": statement.excluded.cost
        }
    )
    with SessionLocal() as session:
        print("Executing query")
        session.execute(query)



if __name__ == "__main__":
    print("Starting")
    data = ComparisonBillData("aebebd26-4f3e-44c5-83ef-cf722f0d81e2", "bill_id_1", "account_id_1", "service_location_id_1", 26.3,
                              30.2,"Office", "tenant-1", "electric", datetime.datetime(2023, 1, 1)
                              , "kWh",datetime.datetime(2023, 1, 1))
    # data.comparison_bill_data_id = "aebebd26-4f3e-44c5-83ef-cf722f0d81e2"
    # data.cost = 26.3
    # data.usage = 30.2
    # data.bill_id = "bill_id_1"
    # data.account_id = "account_id_1"
    # data.service_location_id = "service_location_id_1"
    # data.tenant_id = "tenant_id_1"
    # data.commodity_type = "electric"
    # data.bill_date = datetime.datetime(2023, 1, 1)
    # data.usage_unit = "kWh"
    # data.created_at = datetime.datetime(2023, 1, 1)
    upsert_comparison_bill_data(data)

I found one of the related answers here and I tried replacing the line to create insert statement with following-

statement = insert(Base.metadata.tables[ComparisonBillData.__tablename__]).values((bill_data),)

However, then I get a different error- 'ComparisonBillData' object has no attribute 'items' Is there anything I am missing here?

Answer 1

The insert function expects to receive a Table object, and its .values() method expects to receive a dictionary of values, not an ORM model instance.

So the insert ought to look something like this:

# values should contain all the values that you would pass to `__init__`, as a dictionary.
statement = insert(ComparisonBillData.__table__).values(usage=26.3, cost=30.2)

Passing metadata.tables[ComaprisonBillData.__tablename__] to insert would work too.